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
- The lookup data is to be displayed in UI
- 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!!!