LINQ to SQL Stored Procedures that Return Multiple Result Shapes using C#

Updated on 09 Apr 2012,
Published on 27 Dec 2010

The SQL stored procedures may return multiple types of result sets based on their input parameters. LINQ to SQL also provides the feature to handle the multiple result shapes returned by the stored procedures where it is unknown that what type of result set the stored procedure will return. The IMultipleResults interface enables to fetch and represent the results of the mapped function or query with variable result sequences. To understand the concept of LINQ to SQL Stored Procedure that returns multiple results shapes using C# code we have created a stored procedure in the Northwind SQL database. It accepts only single parameter and returns the resulting rows from a database table based on the specified parameter value.

Stored Procedure

CREATE PROCEDURE sproc_SelectProductsOrCategories
 @ResultType   INT
AS
 IF (@ResultType = 1)
  SELECT   CategoryID, 
      CategoryName, 
      Description, 
      Picture 
  FROM   Categories
 ELSE IF (@ResultType = 2)
  SELECT   ProductID,  
      ProductName,
      SupplierID,
      CategoryID,
      QuantityPerUnit,
      UnitPrice,
      UnitsInStock,
      UnitsOnOrder,
      ReorderLevel,
      Discontinued
  FROM   Products

The procedure will return all the categories for the input parameter value as 1 and all the products for the value 2. LINQ to SQL O/R mapper tool does not generate the automated code for multiple result shapes but you can create its LINQ based code by extending the DataContext partial class manually. The following C# code shows the partial DataContext class and the function for implementing the IMultipleResults interface to manage the multiple types of result sets that the underlying stored procedure may return:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

// namespace for accessing MethodInfo class
using System.Reflection;

// namespace for accessing LINQ methods
// and LINQ mapping
using System.Data.Linq;
using System.Data.Linq.Mapping;

/// <summary>
/// Summary description for LinqDataContextClass
/// </summary>
public partial class DataClassesDataContext
{
    [Function(Name = "dbo.sproc_SelectProductsOrCategories")]
    [ResultType(typeof(Category))]
    [ResultType(typeof(Product))]
    public IMultipleResults SelectProductsOrCategories([Parameter(DbType="Int")] System.Nullable<int> ResultType)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), ResultType);
        return (IMultipleResults)result.ReturnValue;
    }
}

The following C# code can be used to call the store procedure using LINQ that will retrieve and return the result set based on the value of input parameter:

public void GetProductsOrCategories(int ResultType)
{
    DataClassesDataContext dc = new DataClassesDataContext();
    IMultipleResults results = dc.SelectProductsOrCategories(ResultType);

    if (ResultType == 1)
    {
        foreach (Category category in results.GetResult<Category>())
        {
            Response.Output.Write("{0}<br />", category.CategoryName);
        }
    }

    if (ResultType == 2)
    {
        foreach (Product product in results.GetResult<Product>())
        {
            Response.Output.Write("{0}<br />", product.ProductName);
        }
    }
}

The IMultipleResults itself does not provide Enumerable object instead it provides a GetResult<T> method that enables to retrieve the specified type of sequence from the result set.

Continue to the next tutorial: LINQ to SQL Stored Procedures that Return Sequential Result Shapes using C# to learn how to display the multiple types of sequential results retrieved from the stored procedure.

Previous Tutorial

Next Tutorial