Multiple resultsets in SQL Server and handling them in C# (Part II – Multiple Resultsets in Embedded SQL, Stored Procedure with DataSet)

Vishvvas
Posted by in .NET Framework category on for Advance level | Points: 300 | Views : 12348 red flag

This article delves into the feature (of SQL server) of returning the multiple resultsets and handling such multiple resultsets implemented through inline SQL and as well as through stored procedure in C# with dataset.

Introduction and background

In the previous article , we explored the multiple resultsets in SQL server and their handling with SQL Datareader in C#.

In this article, we would continue to delve further and would see the implementation with dataset. As mentioned, when dataset is employed, the different resultsets are available in different tables of the dataset. Let’s look at these examples with dataset.

Objective

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

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.

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

Example#1: Handling multiple resultsets for inline SQL through dataset.

Example#2: Handling multiple resultsets for stored procedure through dataset.

public class MultipleResultSetWithDataSet

    {

        /// <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 ExecuteInlineSqlWithDataSet()

       {

 

           SqlConnection sqlConnection;

           SqlCommand sqlCmd;

           string sql = null;

           DataSet dataSet=new DataSet();

           SqlDataAdapter sqlDataAdapter;

 

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

           sqlConnection = GetSqlConnection();

           sqlConnection.Open();

           sqlCmd = new SqlCommand(sql, sqlConnection);

           sqlDataAdapter = new SqlDataAdapter(sql.ToString(), sqlConnection);

 

           sqlDataAdapter.Fill(dataSet);

 

           if (dataSet.Tables.Count > 0)

           {

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

 

               //We know there are 2 tables but the best approach would be iterate through table collection

               if (dataSet.Tables[0].Rows.Count > 0)

               {

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

 

                   foreach (DataRow existingRow in dataSet.Tables[0].Rows)

                   {

                       //As we know there are 2 columns, best approach would be to ieterate through column collection

                       Console.WriteLine(existingRow.ItemArray[0].ToString() + " - " + existingRow.ItemArray[1].ToString());                      

                   }

 

               }

 

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

 

               if (dataSet.Tables[1].Rows.Count > 0)

               {

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

 

                   foreach (DataRow existingRow in dataSet.Tables[1].Rows)

                   {

                       //As we know there are 2 columns, best approach would be to ieterate through column collection

                       Console.WriteLine(existingRow.ItemArray[0].ToString() + " - " +existingRow.ItemArray[1].ToString());

                          

                   }

 

               }

 

           }

           else

           {

               Console.WriteLine("No matching records found.");

           }

 

           sqlCmd.Dispose();

           sqlDataAdapter.Dispose();

           dataSet.Dispose();

 

       }

       /// <summary>

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

       /// </summary>

       public void ExecuteStoredProcedureWithDataSet()

       {

 

           SqlConnection sqlConnection;

           SqlCommand sqlCmd;

           string sql = null;

           DataSet dataSet = new DataSet();

           SqlDataAdapter sqlDataAdapter;

          

           sqlConnection = GetSqlConnection();

           sqlConnection.Open();

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

           sqlCmd.CommandType = CommandType.StoredProcedure;

 

           sqlDataAdapter = new SqlDataAdapter(sqlCmd);

 

           sqlDataAdapter.Fill(dataSet);

 

           if (dataSet.Tables.Count > 0)

           {

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

 

               //We know there are 2 tables but the best approach would be iterate through table collection

               if (dataSet.Tables[0].Rows.Count > 0)

               {

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

 

                   foreach (DataRow existingRow in dataSet.Tables[0].Rows)

                   {

                       //As we know there are 2 columns, best approach would be to ieterate through column collection

                       Console.WriteLine(existingRow.ItemArray[0].ToString() + " - " + existingRow.ItemArray[1].ToString());                      

                   }

 

               }

 

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

 

               if (dataSet.Tables[1].Rows.Count > 0)

               {

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

 

                   foreach (DataRow existingRow in dataSet.Tables[1].Rows)

                   {

                       //As we know there are 2 columns, best approach would be to ieterate through column collection

                       Console.WriteLine(existingRow.ItemArray[0].ToString() + " - " + existingRow.ItemArray[1].ToString());

                   }

               }

           }

           else

           {

               Console.WriteLine("No matching records found.");

           }

 

           sqlCmd.Dispose();

           sqlDataAdapter.Dispose();

           dataSet.Dispose();

 

       }

     

    } 

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)

        {

            MultipleResultSetWithDataSet multipleResultSetWithDataSet = new MultipleResultSetWithDataSet();

            multipleResultSetWithDataSet.ExecuteInlineSqlWithDataSet();

            multipleResultSetWithDataSet.ExecuteStoredProcedureWithDataSet();

 

        }

    }

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#1:Multiple SQL statements in Stored Procedure**************

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 clearly demonstrates the feature of multiple resultsets in SQL server and its consequent handling in C# with dataset. The code snippets for imlementation of this and also for stored procedure are supplied above.  With dataset, the multiple resultssets culminates into different tables.

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 article, we would explore the 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)