Multiples Table in DataReader

I normally use DataSet because It is very flexible. Recently I am assigned code optimization task , To reduce hits to the database I am changing two queries in a procedure. one Query returns the count and the other returns the actual data. That is , My stored procedure returns two tables. Now, I know how to read both tables using DataSets, But I need to read both tables using DataReader. In search of that I found This.

I follow the article and wrote my code like this:

dr = cmd.ExecuteReader();
while (dr.Read())
{


}
if (dr.NextResult()) // this line throws exception
{
   while (dr.Read())
{

But I am getting an exception at dt.NextResult. Exception is :

Invalid attempt to call NextResult when reader is closed.

I also googled above error , but still not able to solve the issue.
Any help will be much appreciated. I need to read multiple tables using datareader, is this possible?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try this because this will close connection ,data reader and command once task get over , so that this will not give datareader close exception

Also do check like this if(reader.NextResult()) to check there is next result,

using (SqlConnection connection = new SqlConnection("connection string here"))
{
    using (SqlCommand command = new SqlCommand
           ("SELECT Column1 FROM Table1; SELECT Column2 FROM Table2", connection))
    {
        connection.Open(); 
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                MessageBox.Show(reader.GetString(0), "Table1.Column1");
            }

            if(reader.NextResult())
            {
               while (reader.Read())
              {
                MessageBox.Show(reader.GetString(0), "Table2.Column2");
              }
            }
        }
    }
}

Method 2

I have tried to reproduce this issue (also because i haven’t used multiple tables in a reader before). But it works as expected, hence i assume that you’ve omitted the related code.

Here’s my test code:

using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
{
    using (var cmd = new SqlCommand("SELECT TOP 10 * FROM tabData; SELECT TOP 10 * FROM tabDataDetail;", con))
    {
        int rowCount = 0;
        con.Open();
        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                String object1 = String.Format("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]);
            }
            if (rdr.NextResult())
            {
                rowCount = 0;
                while (rdr.Read())
                {
                    String object1 = String.Format("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]);
                }
            }
        }
    }
}

Method 3

I built on Pranay Rana’s answer because I like keeping it as small as possible.

string rslt = "";
using (SqlDataReader dr = cmd.ExecuteReader())
{
    do
    {
        while (dr.Read())
        {
            rslt += $"ReqID: {dr["REQ_NR"]}, Shpr: {dr["SHPR_NR"]}, MultiLoc: {dr["MULTI_LOC"]}rn";
        }
    } while (dr.NextResult());
}

Method 4

The question is old but I find the answers are not correct.
Here’s how I do it:

        List<DataTable> dataTables = new();
        using IDataReader dataReader = command.ExecuteReader();
        do
        {
            DataTable dataTable = new();
            dataTable.Load(dataReader);
            dataTables.Add(dataTable);
        }
        while (!dataReader.IsClosed);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0