KishoR NaiK: Linq - Multiple Result set of Procedure using LINQ to SQL

In this article i will show you how to return multiple result set of stored procedure using Linq to SQL.

As you probably know LINQ to SQL supports stored procedure (SP). You can drag & drop a SP in the LINQ to SQL Designer and the magic happens: a new method inside the DataContext class is generated and a new return type is defined by the designer to represent this result (the name of the type is composed by the SP name followed by Result).

It is possible to read multiple result set of procedure using Linq.In general scenario when you drag and drop procedure in .dbml its always returns ISingleResult and only consider first result, based of first result it will create data contracts.

Now let see how we can read multiple result set using LINQ

Step 1
Download northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a northwind database into MS-SQL server.

Step 3
Lets Create a stored procedure of multiple results.Execute below stored procedure in Northwind Database

CREATE PROC [dbo].[SPViewData]
 @ContactName Nvarchar(30)
AS
 /*Get Employee Data*/ /* No Parameter */
 SELECT TOP 5 Employees.FirstName,Employees.LastName,Employees.City FROM Employees
 
 /*  Get Customers Data */ /* With Parameter */
 SELECT Customers.ContactName,Customers.City FROM Customers
  WHERE Customers.ContactName=@ContactName
GO

Step 4
Create a Web Application and give the solution name as SolMultipleResultSetLinqToSQL.

Step 5
Add a App_Code Folder,Add Linq to SQL class,Right click on App_Code Folder and select Add New Item, select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this

Click on image for better view

Step 6
Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this

Click on image for better view

Click on image for better view

Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 7

To map Tables and stored procedure in DataContext class.go to the server explorer,select northwind database,go to the Stored Procedure and select SPViewData stored procedure and select Employees table and Customers table from Table ,it is look like this

Click on image for better view

Drag and drop Employees and Customers Table and SpViewData stored procedure from Server explorer onto the design surface of the O/R Designer,it is look like this

Click on image for better view

When you drag and drop this procedure you can see function in NorthwindDC.designer.cs file which look like following

[global::System.Data.Linq.Mapping.FunctionAttribute(Name = "dbo.SPViewData")]
    public ISingleResult<SPViewDataResult> SPViewData([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "ContactName", DbType = "NVarChar(30)")] string contactName)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), contactName);
        return ((ISingleResult<SPViewDataResult>)(result.ReturnValue));
    }

You can see it generates ISignleResult result and having only single entity which is SpViewDataResult and when we see the definition of SpViewDataResult class we can see that it only consider Employees results and exclude Customers result.

Click on image for better view

Step 8
To get multiple results we have to create new file having same class name and added with partial keyword.The method should be in same namespace and with same class,it is look like this

using System;
using System.Collections.Generic;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Data.Linq;

/// 
/// Summary description for NorthwindDCDataContext
/// 
public partial class NorthwindDCDataContext
{
    [FunctionAttribute(Name = "dbo.SpViewData")]
    [ResultType(typeof(Employee))]
    [ResultType(typeof(Customer))]
    public IMultipleResults GetMultipleResultSet([System.Data.Linq.Mapping.ParameterAttribute(Name="ContactName", DbType="NVarChar(30)")] string ContactName)
    {
        try
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),ContactName);
            return (IMultipleResults)(result.ReturnValue);

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }
    }
}

We have added Employee and Customer as ResultType as our procedure is returning these two result sets, this will going to use by MethodInfo in order to return IMultipleResults. In addition to this we have to change return type, it was before ISingleResult now we have to make it IMultipleResults and finally we add ParameterAttribute in method which user set a parameter value for stored procedure.Now our method is ready to return multiple results.

Step 9
Add a two GridView on page it's look like this

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2>Employee Data</h2>
        <asp:GridView ID="GvEmployee" runat="server" AutoGenerateColumns="False" 
            EnableModelValidation="True">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" />
                <asp:BoundField DataField="City" HeaderText="City" />
            </Columns>
        </asp:GridView>

        <h2>Customer Data</h2>
        <asp:GridView ID="GvCustomer" runat="server" AutoGenerateColumns="False" 
            EnableModelValidation="True">
            <Columns>
                <asp:BoundField DataField="ContactName" HeaderText="ContactName" />
                <asp:BoundField DataField="City" HeaderText="City" />
            </Columns>
        </asp:GridView>

    </div>
    </form>
</body>
</html>

Step 10
Get the results form IMultipleResults and bind data to the GridViews,it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Linq;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                ViewData();
            }
        }
        catch (Exception)
        { 
        }
    }

    #region Methods

    private void ViewData()
    {
        try
        {
            //Create a Object of Northwind DataContext Class
            NorthwindDCDataContext DC = new NorthwindDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString.ToString());

            // Get Multiple Result Set and Store in IMultipleResults Interface
            IMultipleResults Results = DC.GetMultipleResultSet("Thomas Hardy");

            // Get Employee Data from IMultipleResults Interface Object
            var Employees = Results.GetResult<Employee>().ToList<Employee>();

            // Get Customer Data from IMultipleResults Interface Object
            var Customers = Results.GetResult<Customer>().ToList<Customer>();

            //Bind Employee Data to gridview
            if (Employees != null)
            {
                if (Employees.Count >= 0)
                {
                    GvEmployee.DataSource = Employees;
                    GvEmployee.DataBind();
                }
            }

            // Bind Customer Data to gridview
            if (Customers != null)
            {
                if (Customers.Count >= 0)
                {
                    GvCustomer.DataSource = Customers;
                    GvCustomer.DataBind();
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }
    }

    

    #endregion
}

Run the Project.

Output