Processing Multiple Queries With Single SQLCommand in ADO.Net

Processing Multiple Queries With Single SQLCommand in ADO.Net

Introduction

This short article shows how to pack multiple SQL statements into a SqlCommand and process it using theSqlDataReader object. The previous article on ADO.Net already explained Connections, Commands and Reader objects. Hence, we will concentrate on processing multiple results.

Have a look at the following 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 returns 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.

 

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.

About the Example

 

1. //Sample 01: Using Statements   

1.   

1. using System.Data.SqlClient;  

1. //Sample 02: Open connection to Pub Db of Sql Server   

1. SqlConnection PubsDbCon = new SqlConnection(Properties.Settings.Default.PubsConstr);   

1. PubsDbCon.Open();  

1. //Sample 03: Form Multiple Single Command for More than one Query   

1.   

1. String sqlQuery = "Select count(au_id) as TotAuthors from authors;" +   

1.   

1.                           "Select Au_fname + ' ' + Au_lname as FullName from authors;" +    

1.   

1.                           "Select stor_name from stores;";   

1.   

1. SqlCommand MultiCmd = new SqlCommand();   

1.   

1. MultiCmd.Connection = PubsDbCon;   

1.   

1. MultiCmd.CommandText = sqlQuery;   

1. //Sample 04: Open the Reader and Iterate through all three result sets   

1. SqlDataReader ReaderMultiSet = MultiCmd.ExecuteReader();  

1. //4.1: Process First Result Set.    

1. bool ret = ReaderMultiSet.Read();   

1. if (ret == true)   

1.     lblTotAuthors.Text = ReaderMultiSet["TotAuthors"].ToString();   

1.   

1. //4.2: Retrive List of Authors from Next Result set   

1. bool ResultExits = ReaderMultiSet.NextResult();   

1. if (ResultExits == true)   

1. {   

1.     while (ReaderMultiSet.Read())   

1.     {   

1.         string AuthorName = ReaderMultiSet["FullName"].ToString(); ;   

1.         cmbAuthors.Items.Add(AuthorName);   

1.         cmbAuthors.SelectedIndex = 0;   

1.     }   

1. }   

1.   

1. //4.3: Retrive List of Stores from Next Result set   

1. ResultExits = ReaderMultiSet.NextResult();   

1. if (ResultExits == true)   

1. {   

1.     while (ReaderMultiSet.Read())   

1.     {   

1.         string StoreName = ReaderMultiSet["stor_name"].ToString(); ;   

1.         lstBStores.Items.Add(StoreName);    

1.     }   

1. }  

The following screen shot shows the example we will create:

The example retrieves the data from the SQL Server sample database Pubs. The total number of authors queried from the table authors is displayed in a label control marked as 1 and the 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 the Get Data button is clicked (marked as 4), all the data is retrieved using a single SqlCommand formed by three SQL statements.

Making the Example

The following video explains the making of the sample application:

Code Explanation

A using statement is placed in the top of form code file frmResults.cs and the following is the code:

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

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

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. The following is the code that retrieves the reader object:

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 records from the result set by making a call to the Read() method on the SqlDataReader object. Note that the Result sets are ordered in the same order as 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 get a better understanding. The following is the piece of code that iterates through the records on each result set:

Running the Example

To run the example you need a Pubs sample database, visit the page to get the sample database. Visit thisvideo to understand creating the Connection string. The following video shows running the example: