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!!!
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.