About ADO.Net Multiple ResultSet Processing Example

Multiple Result Sets Processing in Ado.net

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()
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
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:

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.


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:

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:

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:

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

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.