1

Say if I populate a DataTable with a SQL command like this:

SQLCmd.Parameters.AddWithValue("@Date",DateTime.Today());
SQLCmd.CommandText = @"select ID, Name, Date from TestTable1 where Date=@Date";

SqlDataAdapter SQLAdapter = new SqlDataAdapter(SQLCmd);
DataTable dt = new DataTable();
SQLAdapter.Fill(dt);

Is it possible do a further query looking for something in another table which is also in dt?

For example, do something like

select ID 
from TestTable2 
where TestTable2.ID = dt["ID"];

Something like that... assuming both TestTable1 and TestTable2 have a column ID.

Improve this question

3 Answers 3

Reset to default
1

You could use linkserver to get the data at a time or else below code may help you out. Get all the IDs with "," separated and passed it to second query.

string ids = String.Join(",", dt.AsEnumerable().Select(x => x.Field<int>("ID").ToString()).ToArray());
SQLCmd.Parameters.AddWithValue("@ID",ids);
SQLCmd.CommandText = @"select ID from TestTable2 where ID in ("+@ID+")";
SqlDataAdapter SQLAdapter = new SqlDataAdapter(SQLCmd);
DataTable dt2 = new DataTable();
SQLAdapter.Fill(dt2);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Just as an alternative option to think about, you could JOIN TestTable2 like:

SELECT t1.[ID]
    ,t1.[Name]
    ,t1.[DATE]
    ,t2.[ID]
FROM TestTable1 t1
INNER JOIN TestTable2 t2 ON t1.ID = t2.ID
WHERE t1.[DATE] = @Date
Improve this answer

5 Comments

It will be great if I could! But the problem is... those 2 tables are from "different database"!!! >"<
Ok so it sounds like you’ll need to assign the ID value/s to a variable and use it in another query using another database connection. You could also be tricky and use a linked server name to access the TestTable2 in the 2nd database and then in your join use the linked server name <linkedServerName>.<database>.<schema>.<TestTable2>.. but I dunno, I’m sure someone will have a solution involving the usage of the variable in another sql query.
Are you suggesting that I could join 2 tables from 2 different database!? I might have to look it up(!!!) this <linkedServerName> thing... Thanks for helping!!!
I don’t think it’s the best solution to your question, but it’s an option. We are forced to do this at work because our development environment has just one SQL Server but in production we have many which in some instances use linked server connections. In development, we can use these linked server names to develop and test new or changing work all while every database exists on the same server. Additionally you could consider calling a stored procedure from your C# code and have the stored procedure do all of the retrieval of data to reduce the calls to the SQL Server.
Thanks!!! But my situation is actually kind of more complicated!!! The second query string is based on "the combination of the first result"!!! Ex: string.format("{0}-{1}",dt["ID"],dt["Name"]); so I don't think a simple join will solve the problem!!! Thanks tons anyway!!!
0

You can filter using DataView

DataView dv = new DataView(dt);
dv.RowFilter = "query"; // query example = "DATE = 'Your Date'"
Improve this answer

1 Comment

I'm sorry to say that your answer is "other way around"! What I want is to find in TestTable2 where all the ID also in dt["ID"]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.