Multiple resultsets in SQL Server and handling them in C# (Part I – Embedded SQL and Stored Procedures)

Vishvvas
Posted by in .NET Framework category on for Advance level | Points: 300 | Views : 11479 red flag
Rating: 4 out of 5  
 1 vote(s)

This article explores the feature (of SQL server) of returning the multiple resultsets and handling such multiple resultsets in C# with SQL Data reader through inline SQL and as well as through stored procedure.

Introduction background


 In SQL world, it is relatively not a well-known fact that multiple result sets can be returned by SQL server even though it is relatively an old feature. More precisely, batch queries (i.e. multiple SQL statement) can be implemented in a stored procedures/ embedded (or inline) SQL to avoid database trip for every individual query. And the execution of such multiple SQL queries results into multiple result sets. This can lead to considerable performance gain if used wisely. Mostly in scenarios

  1. The lookup data is to be displayed in UI
  2. When we need the ID first and based on id, some result set is to be returned along-with the ID’s

In case of DataReaders e.g. SQL DataReader, such multiple resultsets can be accessed through the method.NextResult(). When dataset is employed, the different resultsets are available in different tables of the dataset.

Let’s look at these examples with SQL data readers.


Objective

To learn the multiple resultsets from database and its implementation in C#. 


Description


Note: For these examples the sample database i.e. AdvntureWorks and SQL 2008 R2 is employed and a console project in VS2010 is used. For demonstration, the code for creating SQL connection, executing the inline SQL and stored procedure is also included in the class. Generally database constitutes a different component.

2 examples with inline SQL and stored procedure are demonstrated. The following class implements these 2 examples in addition to having a method for getting SQL connection.

Example#1: Handling multiple resultsets for inline SQL through SQL data reader

Example#2: Handling multiple resultsets for stored procedure through SQL data reader

public class MultipleResultSetSQLDataReader

    {

        /// <summary>

        /// This returns the SQL connection

        /// </summary>

        /// <returns></returns>

     

       private SqlConnection GetSqlConnection()

       {

 

           string connectionString = null;

           SqlConnection retVal;

 

           connectionString = "Data Source=SERVERNAME;Initial Catalog=AdventureWorks;Integrated Security=True";

 

           retVal = new SqlConnection(connectionString);

           return retVal;

       }

        /// <summary>

        /// Executes the SQL statements in InlineSQL and displays the first 2 records on the console

        /// </summary>

       public void ExecuteInlineSqlWithSQLDataReader()

       {

 

           SqlConnection sqlConnection;

           SqlCommand sqlCmd;

           string sql = null;

 

           sql = "SELECT TOP 2 * from Production.Product; SELECT TOP 2 * from Sales.Customer;";

           sqlConnection = GetSqlConnection();

           sqlConnection.Open();

           sqlCmd = new SqlCommand(sql, sqlConnection);

           SqlDataReader sqlReader = sqlCmd.ExecuteReader();

           Console.WriteLine("********Example#1:Multiple SQL statements in Embedded SQL**************");

 

           Console.WriteLine("Result of executing first SQL statement");

           while (sqlReader.Read())

           {

              

               Console.WriteLine(sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));

 

           }

 

           sqlReader.NextResult();

 

          Console.WriteLine("***********************************************************************");

           Console.WriteLine("Result of executing second SQL statement");

           while (sqlReader.Read())

           {

             

               Console.WriteLine(sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));

 

           }          

 

           sqlReader.Close();

           sqlCmd.Dispose();

 

       }

       /// <summary>

       /// Executes the SQL statements in Stored procedure and displays the first 2 records on the console

       /// </summary>

       public void ExecuteStoredprocedureWithSQLDataReader()

       {

 

           SqlConnection sqlConnection;

           SqlCommand sqlCmd;

           string sql = null;

 

          

           sqlConnection = GetSqlConnection();

           sqlConnection.Open();

           sqlCmd = new SqlCommand("Demo_MultipleResultSets", sqlConnection);

           sqlCmd.CommandType = CommandType.StoredProcedure;

 

           SqlDataReader sqlReader = sqlCmd.ExecuteReader();

           Console.WriteLine("******Example#2:Multiple SQL statements in Stored Procdure**********");

           Console.WriteLine("Result of executing first SQL statement");

 

           while (sqlReader.Read())

           {              

              

               Console.WriteLine(sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));

           }

 

           sqlReader.NextResult();

 

          Console.WriteLine("***********************************************************************");

           Console.WriteLine("Result of executing second SQL statement");

 

           while (sqlReader.Read())

           {

             

               Console.WriteLine(sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1));

 

           }

 

          Console.WriteLine("***********************************************************************");

 

           sqlReader.Close();

           sqlCmd.Dispose();

 

       }

     

    }


PL SQL Code for stored procedure.

USE [AdventureWorks]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[Demo_MultipleResultSets]

         

          AS

BEGIN

 

          SET NOCOUNT ON;

 

   

          SELECT TOP 2 * from Production.Product;

          SELECT TOP 2 * from Sales.Customer;

         

          SET NOCOUNT OFF;

END


 

Console application

 

class Program

    {

        static void Main(string[] args)

        {

            MultipleResultSetSQLDataReader multipleResultSet = newMultipleResultSetSQLDataReader();

            multipleResultSet.ExecuteInlineSqlWithSQLDataReader();

 

            multipleResultSet.ExecuteStoredprocedureWithSQLDataReader();

 

        }

    }



Output:

********Example#1:Multiple SQL statements in Embedded SQL**************

Result of executing first SQL statement

1 - Adjustable Race

2 - Bearing Ball

***********************************************************************

Result of executing second SQL statement

1 - 1

2 - 1

******Example#2:Multiple SQL statements in Stored Procdure**********

Result of executing first SQL statement

1 - Adjustable Race

2 - Bearing Ball

***********************************************************************

Result of executing second SQL statement

1 - 1

2 - 1

***********************************************************************

Press any key to continue . . .


Summary and Conclusion


The output demonstrates the feature of multiple resultsets in SQL server and its consequent handling in C# with SQL data reader. The code snippets for imlementation of this and also for stored procedure are supplied above.  With SQL data reader the resultssets are handled with method NextResult() .

The multiple resultsets can be really handy when database round trips can be avoided. This would often result into the performance gains. The number of multiple statements in embedded SQL and stored procedure better be kept minimal as having more number of SQL statements can be counterproductive.

Hope this article helps to understand the multiple resultsets and its handling.In forthcoming articles, we would explore the multiple result sets with dataset and also get insights into the implementation of IMultipleResults in C#. 

HAPPY PROGRAMMING!!!


Page copy protected against web site content infringement by Copyscape

About the Author

Vishvvas
Full Name: Vishwas Sutar
Member Level: HonoraryPlatinum
Member Status: Member,MVP
Member Since: 5/30/2011 2:13:10 AM
Country: India

http://www.dotnetfunda.com
Extensive and rich experience across gamut of technologies and programming languages like PB,VB,C++,VB.NET, C#, Classic ASP,ASP.NET, ASP.NET MVC.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)