C# Multiple Result Sets

The DataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data in the Data Source. When the ExecuteReader method in SqlCommand Object execute , it will instantiate a SqlClient.SqlDataReader Object in C#.

  SqlDataReader sqlReader = sqlCmd.ExecuteReader();

In some situations we need to execute multiple SQL statements with the Command Object. In these types of situations the SqlDataReader returns multiple ResultSets also. In order to retrieving multiple ResultSets from SqlDataReader we use the NextResult() method of the SqlDataReader.

  sqlReader.NextResult();

In the following source code you can see how to get multiple result sets from SqlDataReader() .




using System;
using System.Windows.Forms;
using System.Data.SqlClient; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection sqlCnn ;
            SqlCommand sqlCmd ;
            string sql = null;

            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            sql = "Select top 2 * from product; select top 2 * from ordermaster; select top 2 * from orderdetails";

            sqlCnn = new SqlConnection(connetionString);
            try
            {
                sqlCnn.Open();
                sqlCmd = new SqlCommand(sql, sqlCnn);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                while (sqlReader.Read())
                {
                    MessageBox.Show ("From first SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));
                }

                sqlReader.NextResult();

                while (sqlReader.Read())
                {
                    MessageBox.Show("From second SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));
                }

                sqlReader.NextResult();

                while (sqlReader.Read())
                {
                    MessageBox.Show("From third SQL - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));
                }

                sqlReader.Close();
                sqlCmd.Dispose();
                sqlCnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
        }
    }
}

 C# ADO.NET data Providers - Related Contents


 C# ADO.NET data Providers - Related Programs

.