Friday, April 10, 2015

[ Ado.Net ] - Processing Multiple result set using SqlDataReader

1. Introduction

In this short article, we will see packing multiple SQL statements in SqlCommand and process it through the SqlDataReader object. The previous article on already made you familiar with Connection, Command and Reader objects. Hence, we will concentrate on processing multiple results.

Have a look at the below picture. There are three result sets returned by the SqlCommand and the SQLDataReader object processes all of them. The Read method reads records of the single result set and the when there is no record to read the method return false stating that. Similarly, the NextResult method of the SqlDataReader iterates through the result sets and returns false when there is no more to read.

Fig. 1: Data Reader NextResult()

You can use this technique to avoid multiple hits to the database. In our example, we process three results one at a time avoiding multiple hits to the database.

2. About the Example

The below screen shot shows the example we are going to create:

Fig.2 : Application Screenshot

The example retrieves the data from the SQL Server sample database Pubs. A total number of authors queried from the table authors is displayed in a label control marked as 1 and author name from the same table is displayed in the combo box item marked as 2. The list box marked as 3 displays all store names by querying the table stores from the Pubs database. When Get Data button is clicked (Marked as 4), all the data is retrieved through a single SqlCommand formed by three SQL statements.

3. Making the Example

The below video explains making the sample application:

Video 1: Making the Sample App

4. Code Explanation

1) A using statement is placed at the top of form code file frmResults.cs and the code is given below:

//Sample 01: Using Statements
using System.Data.SqlClient;

2) Click event for the “Get Data” button is handled and in the handler, SqlConnection object is created which tells how the application can make a successful connection to the ‘SQL Server Pubs’ database. Note that the connection string is referred from the application settings like “Properties.Settings.Default.PubsConstr”. Making the connection string can be referred in the video mentioned below the code snippet.

//Sample 02: Open connection to Pub Db of Sql Server
SqlConnection PubsDbCon = new SqlConnection(Properties.Settings.Default.PubsConstr);

Video 2: Forming the connection string

3) After we have a valid connection object, SqlCommand object is created. Once SqlCommand object is created, a single string containing three SQL queries is supplied to it through its property CommandText and the same way database connection also supplied through the property Connection. Note that the SQL queries are separated by the semi-colon. Preparing the SqlCommand object is shown in the below code:

//Sample 03: Form Multiple Single Command for More than one Query
String sqlQuery = "Select count(au_id) as TotAuthors from authors;" +
                          "Select Au_fname + ' ' + Au_lname as FullName from authors;" +
                          "Select stor_name from stores;";
SqlCommand MultiCmd = new SqlCommand();
MultiCmd.Connection = PubsDbCon;
MultiCmd.CommandText = sqlQuery;

4) The call to ExecuteReader on the SqlCommand object returns the SqlDataReader object. Since the SqlCommand contains three SQL select statements there will be three corresponding result set objects. Below is the code, which retrieves the reader object:

//Sample 04: Open the Reader and Iterate through all three result sets
SqlDataReader ReaderMultiSet = MultiCmd.ExecuteReader();

5) Once we have the reader in hand, we can retrieve all the data returned as three separate result sets. To iterate through these results sets, make a call to the NextResult method and this method moves the reader to the next valid result set. When there is no result to process, the methods returns false. This will be useful if you want to form a while loop based on the returned value. In our example, we are not using the loops. Once your reader is at the required result set you can read the individual record from the result set by making the call to Read() method on the SqlDataReader object. Note that the Result sets are ordered in the same order it was given to the SqlCommand object. In our case, the first result set is, Total authors (One Record), the next result is a list of authors and the final one is the list of stores. Have a look at the picture at the Introduction section again to have a better understanding. Below is the piece of code, which iterates through the records on each result sets:

//4.1: Process First Result Set.
bool ret = ReaderMultiSet.Read();
if (ret == true)
    lblTotAuthors.Text = ReaderMultiSet["TotAuthors"].ToString();

//4.2: Retrive List of Authors from Next Result set
bool ResultExits = ReaderMultiSet.NextResult();
if (ResultExits == true)
    while (ReaderMultiSet.Read())
        string AuthorName = ReaderMultiSet["FullName"].ToString(); ;
        cmbAuthors.SelectedIndex = 0;

//4.3: Retrive List of Stores from Next Result set
ResultExits = ReaderMultiSet.NextResult();
if (ResultExits == true)
    while (ReaderMultiSet.Read())
        string StoreName = ReaderMultiSet["stor_name"].ToString(); ;

5. Running the Example

To run the example you need Pubs sample database, visit the page to get the sample database. Visit this video to know creating the Connection string. The below video shows running the Example:

Video 3: Running the Example

Source Code: Download 
Like this site? Tell it to your Firend :)