Visual C#, SQL query from an existing DataTable?

Question

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.

Answer 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);