SqlDataReader with two tables

Question

I have a very simple code which fetches data from two tables

1. Inventory
2. Category

and displays them in two different GridView controls using SqlDataReader in the Page_Load event of an ASP.NET web application.

My point is, shouldn't SqlDataReader automatically traverse to the next table (Category) after reading the data from the previous table (Inventory)? Instead of using NextResult(), if I had reused the rdr object again for the next grid i.e. CategoryGridView without executing NextResult(), I'd get nothing (meaning it has moved on from Inventory?!)

So in a nutshell my question is where is the rdr pointing towards after reading from the first table (Inventory)?

using (SqlConnection con = new SqlConnection(CS))
{
 SqlCommand cmd = new SqlCommand("Select * from tblInventory; Select * from tblCategory", con);
 con.Open();
 using (SqlDataReader rdr = cmd.ExecuteReader())
 {
 InventoryGridView.DataSource = rdr;
 InventoryGridView.DataBind();
 rdr.NextResult();
 CategoryGridView.DataSource = rdr;
 CategoryGridView.DataBind();
 }
}

• marc_s: After completely reading the first result set, the reader points to "nowhere" - to past the end of the first result set. But it will not automatically jump to the second result set - you must use .NextResult() to achieve this
• Zaid Khan: Interesting, actually the confusion came from using SQLDataReader to read multiple rows which requires Read() before retrieving values.

Answer 1

connection.Open();
SqlCommand command = new SqlCommand("Select * from tblInventory; Select * from tblCategory", connection);
using (SqlDataReader reader = command.ExecuteReader())
{
 InventoryGridView.DataSource = reader;
 InventoryGridView.DataBind();
 while (reader.NextResult())
 {
 CategoryGridView.DataSource = reader;
 CategoryGridView.DataBind();
 }
}

• ojonasplima: The best practice is to add a reader.Read() inside the reader.NextResult() so you can avoid calling blank results. In fact, if (reader.NextResult()){ while (reader.Read()) } works better.