Multiple resultsets in SQL Server and handling them in C# (Part III – Multiple Resultsets in Stored Procedure with LINQTOSQL)

Introduction and background

In the previous articles, we had learnt about Multiple resultsets in SQL Server and handling them in C#  and Multiple Resultsets in Embedded SQL, Stored Procedure with DataSet we explored the multiple resultsets in SQL server and their handling with SQL Datareader and dataset in C#.

In this article, we would continue to delve further and would see the implementation with LINQTOSQL. With the tool for LINQTOSQL, the method stub generated for stored procedure supports single result and hence it needs to be modified to support multiple results. This can be achieved through implementation of IMultipleResults.

Let’s look at the example for this.

Objective

To learn the multiple resultsets from database stored procedure and its implementation in C# with LINQTOSQL.

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.

In this example, familiarity with LINQTOSQL, tool and DBML is assumed. Continuing the example with database AdventureWorks for earlier articles, we would stroll further.

The name “DataClassesDataContext” is given to auto-generated class for data context.

Step 1: Drag the database tables (Product and Customer) from database AdventureWorks

Step 2:Drag the SP named “Demo_MultipleResultSets”. The method generated is as follows

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.Demo_MultipleResultSets")]
 
public ISingleResult<Demo_MultipleResultSetsResult> Demo_MultipleResultSets()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<Demo_MultipleResultSetsResult>)(result.ReturnValue));
}

As seen above, the return type of this method is ISingleResult which in turn executes only one query. To get the multiple result sets, we need to modify the method. This can be done in other class with the same name. Of course such class needs to be partial.

Please do not forget to remove the original method once you program for new method in partial class.

partial class DataClassesDataContext
    {
        [FunctionAttribute(Name = "dbo.Demo_MultipleResultSets")] 
        [ResultType(typeof(Product))] 
        [ResultType(typeof(Customer))] 
 
        public IMultipleResults Demo_MultipleResultSets()
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
            return ((IMultipleResults)(result.ReturnValue));
        }
    }

The return type is changed to IMultipleResults instead of ISingleResult and also the method is decorated with additional attributes for specifying the actual types for results.

Let’s use this method and see the output

class MultipleResultSetInLINQTOSQL
    {
               
        public void ExecuteStoredProcedureThroughLINQTOSQL()
        {
            
            using (DataClassesDataContext context = new DataClassesDataContext()) 
            {
 
                var result = context.Demo_MultipleResultSets();
                List<Product> products = result.GetResult<Product>().ToList(); 
    
                List<Customer> customers = result.GetResult<Customer>().ToList();
 
                Console.WriteLine("***********************************************************************");
                Console.WriteLine("Result of executing first SQL statement");
 
                foreach (Product product in products)
                {
                    //As we know there are 2 columns, best approach would be to iterate through column collection
                    Console.WriteLine(product.ProductID.ToString() + " - " + product.Name.ToString());
                }
                Console.WriteLine("***********************************************************************");
                Console.WriteLine("Result of executing second SQL statement");
                foreach (Customer customer in customers)
                {
                    //As we know there are 2 columns, best approach would be to iterate through column collection
                    Console.WriteLine(customer.CustomerID.ToString() + " - " + customer.TerritoryID.ToString());
                }
            }
        
        }
    }
  

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)
        {
MultipleResultSetInLINQTOSQL multipleResultSetInLINQTOSQL = new MultipleResultSetInLINQTOSQL();
            multipleResultSetInLINQTOSQL.ExecuteStoredProcedureThroughLINQTOSQL();     
 
        }
    }

Output:

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

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 stored procedure and its consequent handling in C# for LINQTOSQL. The code snippets for implementation of this and also for stored procedure are supplied above.  With LINQTOSQL, the auto-generated code has method stubs with ISingleResult return type which would execute only one SQL statement/query in the stored procedure.

We saw how the method representing the stored procedure can be modified and decorated to support multiple results. This is little extra work needed to ensure the execution of multiple SQL statements as intended.

As stated earlier, 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 LINQTOSQL. This article ends the series on this topic and I hope all readers would have benefited out of this.

HAPPY PROGRAMMING!!!

Reference

Please see this link.