C# - DataReader fails to return multiple result sets

Question

What I am trying to do is similar to what the OP asked here:

Multiples Table in DataReader

I am trying to store the results of one or more SQL queries into separate DataTables. The problem with the above question's approach is that it only works for discrete values in each result set. It would be preferable to use DataTables Load(reader) method to store each result set in a single statement than having to iterate over all of DataReader's columns.

In the code below I have two SQL queries (although this should work for an arbitrary number of queries) whose results I attempt to store in a temporary data-table and then to a list of datatables. The problem with my code is that it only returns the first query's result set, and adding subsequent queries throws a Reader is closed exception.

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand
               ("SELECT TOP 10 Column1, Column2 FROM Table1; SELECT TOP 10 Column1, Column2 FROM Table2", connection))
        {
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                    while (reader.Read())
                    {
                        tempTable.Reset();
                        tempTable.Load(reader);
                        dataTables.Add(tempTable);
                    }

                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            tempTable.Reset();
                            tempTable.Load(reader);
                            dataTables.Add(tempTable);
                        }
                    }
            }
        }
    }

I have struggled for hours with this. I am not sure if the DataTable/DataReader is just poorly designed for my use case or if there is something basic I am missing here.

your reader.NextResult() should be outside your first while(reader.Read()) — Ali Baghdadi, Jun 26 '14 at 10:58
Updated as per your suggestion. This also throws "Reader is closed exception" though. — GoofyBall, Jun 26 '14 at 11:13
the problem is that tempTable.Load(reader) automatically closes the connection after loading the last result, so you can't embed it inside a while(reader.Read()) — Ali Baghdadi, Jun 26 '14 at 11:26
if you still want to use tempTable.Load(reader), you can do it if you know how many query results you have, in this case you have 2, so you loop it inside for loop until it reaches the number of results — Ali Baghdadi, Jun 26 '14 at 11:29
You could try using `SqlDataAdapter` instead of `SqlDataReader`. — Daniel Kelley, Jun 26 '14 at 11:30

GoofyBall · Answer 1 · 2014-06-26T12:34:42.350

The DataReader did not suit my needs and it's use would have resulted in verbose code so I used a DataAdapter as suggested by Daniel Kelly. The code to read multiple result sets is now relatively simple:

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand
               ("SELECT TOP 10 Name, [Description] FROM Business; SELECT TOP 10 ConnectionId FROM Connection;SELECT TOP 10 Name FROM Business", connection))
        {
            connection.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet set = new DataSet(); 
                adapter.SelectCommand = command;

                //Note here, that the adapter will create multiple tables for each result set
                adapter.Fill(set); 

                foreach (DataTable t in set.Tables)
                {
                    dataTables.Add(t);
                }

                dataTables.Add(tempTable);
        }
    }

After reading your comments I found my understanding of reader.Read() and NextResult() is flawed. Also, every time the DataReader's load method is called the reader's connection will be closed.

Thank you everybody for your comments and advice. I won't accept my own answer because that would be conceited.

I'd suggest wrapping usage of the `SqlDataAdapter` in a `using` block as I believe it implements `IDisposable`. — Daniel Kelley, Jun 26 '14 at 12:35
Thank you, Daniel. I have already done this in my other code. The code I posted above was from a hacky console application that I wrote to solve my problem quickly. — GoofyBall, Jun 26 '14 at 12:47

score 4 · Answer 2 · answered Jun 26 '14 at 12:17

if you are preferring to use datatable.Load(reader), try this solution:

using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand
                   ("SELECT TOP 10 Column1, Column2 FROM Table1; SELECT TOP 10 Column1, Column2 FROM Table2", connection))
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    bool haveresult = true;
                        while (haveresult)
                        {
                            DataTable tempTable = new DataTable();
                            tempTable.Load(reader);
                            dataTables.Add(tempTable);
                            try
                            {
                                reader.Read();
                            }
                            catch
                            {
                                haveresult = false;
                            }

                        }

                    }

            }
        }

you use this approach because you can't control when the connection is closed after the reader gets the last result. Note that this can be used for any number of queries you have

when using DataTable.Load(reader), there is no need to use reader.NextResult(), reader.Read() will loop over tables, try it yourself — Ali Baghdadi, Jun 26 '14 at 12:27
Sure I will try but how you are creating multiple tables. If reader gets closed after load then how you are creating 2nd table? — Hassan, Jun 26 '14 at 12:29

C# - DataReader fails to return multiple result sets

2 Answers2