SqlDataReader with two tables

Question

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

Inventory
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();
    }
}

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 — marc_s
– marc_s, Commented Jan 7, 2017 at 7:48
Interesting, actually the confusion came from using SQLDataReader to read multiple rows which requires Read() before retrieving values. — Zaid Khan
– Zaid Khan, Commented Jan 8, 2017 at 5:45

Hameed Syed · Accepted Answer · 2017-01-08 03:18:27Z

2

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();
    }
}

answered Jan 8, 2017 at 3:18

Hameed Syed

4,3532 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ojonasplima Over a year ago

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.

Collectives™ on Stack Overflow

SqlDataReader with two tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related