Introduction
In this blog we will discuss how to retrieve multiple results sets using
DataReader object.
What is DataReader?
- The DataReader is a forward-only, read-only retrieval of record sets
from the Data Source.
- The DataReader object cannot be used to update the Data Source.
- 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 !!