Retrieve two tables of data at the same time by using data reader

Include 2 select statements either in a stored procedure or in a select command and call the ExecuteReader() method on the command object. This will automatically fill the DataReader with 2 Tables of data.

The datareader will always return the data from first table only. If you want to get the second table then you need to use ReaderObject.NextResult() method. The NextResult() method will return true if there is another table.

Example:


SqlConnection con = new SqlConnection("trusted_connection=yes;database=northwind;server=localhost");
SqlCommand cmd = new SqlCommand("select * from categories;select * from products", con);
con.Open();

SqlDataReader dr=cmd.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();


while (dr.NextResult())
{
GridView2.DataSource = dr;
GridView2.DataBind();

}