Retrieving Multiple Result Sets

Introduction

In this blog we will discuss how to retrieve multiple results sets using DataReader object.

What is DataReader?

1. The DataReader is a forward-only, read-only retrieval of record sets from the Data Source.
2. The DataReader object cannot be used to update the Data Source.
3. The ExecuteReader () method in SqlCommand object is used to execute the SQL Statements.

Instantiating a DataReader object

Creating an instance of DataReader is quite different from other ADO .NET objects.

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

The CommandBehavior.CloseConnection indicates that the connection object is closed when the associated DataReader object is closed.

Program

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace MultipleResultsets
{
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            private void btn_cancel_Click(object sender, EventArgs e)
            {
                this.Close();
            }
            private void btn_Load_Click(object sender, EventArgs e)
            {
                ListViewItem item;
                lv_orders.Columns.Add("Order No", 50);
                lv_orders.Columns.Add("Name", 100);
                lv_orders.Columns.Add("Quantity", 50);
                lv_orders.View = View.Details;
                lv_orders.GridLines = true;
                lv_customer.Columns.Add("ID", 50);
                lv_customer.Columns.Add("Customer Name", 50);
                lv_customer.View = View.Details;
                lv_customer.GridLines = true;
                lv_employee.Columns.Add("ID", 50);
                lv_employee.Columns.Add("Name", 100);
                lv_employee.Columns.Add("Job", 50);
                lv_employee.Columns.Add("Manager", 70);
                lv_employee.Columns.Add("Joining Date", 100);
                lv_employee.Columns.Add("Salary", 100);
                lv_employee.Columns.Add("Commission", 100);
                lv_employee.Columns.Add("Department", 50);
                lv_employee.View = View.Details;
                lv_employee.GridLines = true;
                string connectionstring = "Data Source=servername;Initial Catalog=DatabaseName;User ID=UserName;Password=password";
                string Sql = "SELECT TOP 2 *  FROM ORDERS;SELECT TOP 2* FROM CUSTOMERS;SELECT * FROM EMP";
                using (SqlConnection conn = new SqlConnection(connectionstring))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(Sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        //MessageBox.Show("First SQL - " + reader.GetValue(0) + " - " + reader.GetValue(1)+ " - " + reader.GetValue(2));
                        item = new ListViewItem( new string[] { reader.GetValue(0).ToString(),
                                  reader.GetValue(1).ToString(), reader.GetValue(2).ToString()});
                        lv_orders.Items.Add(item);
                    }
                    reader.NextResult();
                    while (reader.Read())
                    {
                        //MessageBox.Show("Second SQL - " + reader.GetValue(0) + " - " + reader.GetValue(1));
                        item = new ListViewItem(new string[] { reader.GetValue(0).ToString(), reader.GetValue(1).ToString()});
                        lv_customer.Items.Add(item);
                    }
                    reader.NextResult();
                    while (reader.Read())
                    {
                        item = new ListViewItem(new string[] { reader.GetValue(0).ToString(),
                                            reader.GetValue(1).ToString(),
                                            reader.GetValue(2).ToString(),
                                            reader.GetValue(3).ToString(),
                                            reader.GetValue(4).ToString(),
                                            reader.GetValue(5).ToString(),
                                            reader.GetValue(6).ToString(),
                                            reader.GetValue(7).ToString()
                                            });
                        lv_employee.Items.Add(item);
                    }
                    reader.Close();
                }
            }
        }
    }

Explanation

string connectionstring = "Data Source=servername;Initial Catalog=DatabaseName;User ID=UserName;Password=password";

Is the connectionString to to the Data Source

string Sql = "SELECT TOP 2 *  FROM ORDERS;SELECT TOP 2* FROM CUSTOMERS;SELECT * FROM EMP";

This is the SQL query which we have used to return the Record Sets. Here we retreive record set from multiple tables.

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

This statement executes the SQL query.

while (reader.Read())
{
    //MessageBox.Show("First SQL - " + reader.GetValue(0) + " - " + reader.GetValue(1)+ " - " + reader.GetValue(2));
    item = new ListViewItem( new string[] { reader.GetValue(0).ToString(),
    reader.GetValue(1).ToString(), reader.GetValue(2).ToString()});
    lv_orders.Items.Add(item);
}

Here we loop through the multiple rows in the reader object and bind it to the ListView control lv_orders.

reader.NextResult();

To retrieve multiple result sets from the SqlDataReader objects we the NextResult() Method of the SqlDataReader.

reader.Close();

Close the reader object.

Thanks for Reading !!