---
title: "Retrieving Multiple Result Sets"  
description: "Introduction  In this blog we will discuss how to retrieve multiple results sets using DataReader object.What is DataReader?1.    The DataReader is a"  
author: "Shankar M"  
published: 2013-04-15  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/484/retrieving-multiple-result-sets  
category: "c#"  
tags: ["c#"]  
reading_time: 3 minutes  

---

# Retrieving Multiple Result Sets

##### Introduction

In this blog we will discuss how to retrieve multiple results sets using [DataReader](https://www.mindstick.com/forum/160514/how-to-use-datareader-in-asp-dot-net-c-sharp) object.

##### What is DataReader?

1. The DataReader is a forward-only, read-only [retrieval](https://www.mindstick.com/interview/99/what-s-the-dot-net-datatype-that-allows-the-retrieval-of-data-by-a-unique-key) of record sets from the [Data Source](https://www.mindstick.com/interview/808/what-is-a-data-source).

2. The DataReader object cannot be used to update the Data Source.

3. The ExecuteReader () method in [SqlCommand](https://www.mindstick.com/forum/91/difference-between-sqlcommand-and-sqlcommandbuilder) object is used to execute the SQL Statements.

##### Instantiating a DataReader object

[Creating an instance](https://www.mindstick.com/forum/156698/can-you-call-the-base-class-method-without-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](https://www.mindstick.com/articles/13012/what-makes-ethernet-connection-better-than-wifi) 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](https://www.mindstick.com/forum/529/rename-table-name-and-column-name-using-sql-query) which we have used to return the Record Sets. Here we retreive record set from [multiple tables](https://www.mindstick.com/forum/159848/how-to-get-multiple-tables-using-entity-framework-core).

```
   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](https://www.mindstick.com/forum/159373/how-to-bulk-update-multiple-rows-in-sql-server-at-a-time-with-different-matching-conditions) in the reader object and bind it to the [ListView](https://www.mindstick.com/articles/12744/listview-in-android) 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 !!

---

Original Source: https://www.mindstick.com/blog/484/retrieving-multiple-result-sets

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.