How to retrieve Multiple Result Sets from DataReader in .NET

Introduction

In this snippets, i will show to you How to retrieve Multiple Result Sets from DataReader in .NET. When we are work with .NET Applications, the ADO.NET is support to retrieve data from the database.but if we are do the many database call, then application performance will lose.In this situation, we must consider application performance always.in order to keep good performance, we have to use the DataReader.The DataReader is most powerful object to read the data from database in one way direction(forward only).

Note:More about DataReader here

Sometimes we need to retrieve multiple results from our query within the stored procedures or inline sql query.In this article,i'm going to focus, how to read the multiple results set return form the database query with DataReader.let see how to implement.

Implementation

Let's create a simple stored procedures with multiple select statements.

create proc spMaster
as
BEGIN
set nocount oN;

select customerID,Name from t_cus_master;
select OrderID, Item from t_ord_master;

END

In the above sql procedure is select customerID and Name from customer master table and select orderID and itemname from the order master table.Now we have to compose data access codee to read data from above query.

C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    public class DataAcessManager
    {
        public static void GetMultipleSets()
        {
            using (SqlConnection connection = new SqlConnection("ConnectionString"))
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "spMaster";
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        do
                        {
                            // here you have know, what is first set , second sets and etc
                            while (reader.Read())
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    string nameOfColumn = reader.GetName(i);
                                    Console.WriteLine(string.Format("{0},{1}", nameOfColumn, reader.GetValue(reader.GetOrdinal(nameOfColumn)).ToString()));
                                }
                            }
                        } while (reader.NextResult());
                    }
                }
            }
        }
    }
}

I have read multiple results set using while with NextResult() method.

Note:If you are select multiple select statements in your query, then you can use the NextResult() to decide,the reader has next set of result to process.

In my sample code, i just read values and write in console, but in your project may need to assign custom object or bind to control.i hope you can do it yourself. if you need more information, please post comments.Thank for reading.