1. Introduction to Multiple Result Sets
In this Example, we will see packing more than one SQL statement in a SqlCommand and process it through the SqlDataReader object. From the past articles on ado.net we are already familiar with Connection, Command and Reader objects. Hence, we will focus on dealing with multiple result sets.
Have a look at the below picture. There are three result sets returned by the SqlCommand. 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 result set to read.
Data Reader NextResult()
We can use this technique to avoid multiple hits to the database and process multiple result sets with one DB hit. In our example, we process three results one at a time, avoiding multiple hits to the database.
2. About This Example
The below screenshot shows the example we are going to create:
About ADO.Net Multiple ResultSet Processing Example
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 we click the Get Data button (Marked as 4), the example fetches all the data through a single SqlCommand formed by three SQL statements.
3. Form Design of This Example
The below video explains making the sample application:
Video 1: Making the Sample App (Low Audio)
4. Code Walk-Through of Multiple Result Set Processing
4.1 Namespace for SQL Server DB
First, we include the required namespace for our form-behind code. Here we include
System.Data.SqlClient
to talk to the SQL Server DB. The code is below:
|
1 2 |
//Sample 01: Using Statements using System.Data.SqlClient; |
4.2 Connection String to Pubs Database
Here, we handle the Click Event for the Get Data button. In the handler, we create the Ado.Net
SqlConnection
object which tells how the application can connect to the SQL Server’s Pubs DB. Note; we refer the connection string from the application settings like
Properties.Settings.Default.PubsConstr
. The
open()
method opens connection to the Pubs DB. We can refer how to make a Connection String in the video placed below the code snippet.
|
1 2 3 4 |
//Sample 02: Open connection to Pub Db of Sql Server SqlConnection PubsDbCon = new SqlConnection(Properties.Settings.Default.PubsConstr); PubsDbCon.Open(); |
Video 2: Forming the connection string
4.3 Ado.Net SqlCommand With Multiple Select Queries
After we have a valid connection object, we create the Ado.Net SqlCommand object. After we create the
SqlCommand
object, we form a single string having three SQL queries and supply it to the
SqlCommand
object through its
CommandText
property. Note, we also hand over the database connection through the Connection property. The
CommandText
property holds SQL queries which we separated by the semi-colon. Now the SqlCommand can return multiple result sets from the database. Preparing the
SqlCommand
object is in the below code:
|
1 2 3 4 5 6 7 |
//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.4 Get SqlDataReader from SqlCommand
The call to ExecuteReader on the SqlCommand object returns the SqlDataReader object. Since the SqlCommand contains three SQL select queries, there will be three result set objects. Simply our SqlDataReader is holding multiple result sets. Below is the code, which gets the reader object:
|
1 2 |
//Sample 04: Open the Reader and Iterate through all three result sets SqlDataReader ReaderMultiSet = MultiCmd.ExecuteReader(); |
4.5 Reading Multiple Result Sets From SqlDataReader
Once we have the reader in hand, we can retrieve all the data returned as three separate result sets. To iterate through these multiple result sets, we 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, this method returns false. This will be useful if we want to form a While Loop to iterate over multiple result-set in the reader object. In our example, we are not using the loops.
Once we are at the required result set, we can read the individual record from it by making the call to
Read()
method on the
SqlDataReader
object. Ado.Net places the Result sets in the same order in which we pushed the select queries 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
//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.Items.Add(AuthorName); 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(); lstBStores.Items.Add(StoreName); } } |
5. Running the Example
To run the example, you need Pubs sample database. Running the Example is shown in the below video:
Video 3: Running the Example
Source Code: Download Ado.Net Multiple ResultSet Processing Example form Google Drive
Categories: Ado.Net
Tags: CommandText, NextResult(), Read(), SqlCommand, SqlDataReader
Do you like this Example? Please comment about it for others!!
This site uses Akismet to reduce spam. Learn how your comment data is processed.