Return Multiple Result Set using Entity Framework from Stored Procedure

Download Link

Introduction

Returning multiple result sets from a stored procedure has been supported since Entity Framework (EF) 5.0. For any business, we may need multiple result set from database stored procedure and we don’t need to call database multiple times. This tip will show us how to return multiple result set from database stored procedure using entity framework.

Required Tools

• Visual Studio 2012 or 2013
• .NET Framework 4.5
• Entity Framework 5.0 or 6.0
• MSSQL SERVER 2008 or more

Stored Procedure on Database

Create database in the SQL Server 2008 or you can use the latest version of SQL Server. You can use online sample Northwind Database. Create a stored procedure for that result set:

        USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[SPR_GETMultipleResultSP]    Script Date: 01/18/2016 20:01:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SPR_GETMultipleResultSP]

AS
BEGIN
	SET NOCOUNT ON;
	Select Top 10 * from Customer where Country='Germany'

    Select Top 10 * from Product where UnitPrice>18.00
    
END    

Execute Stored Procedure

Visual Studio Project

Here, I have used Entity Framework Database-First approach. Create project using Entity Framework database-first, You check MSDN Tutorial. I am not going to import stored procedure directly from database and I have used Visual Studio 2013.

Create Visual Studio console application:

Use Entity Framework code generation template. On MSDN tutorial, you can check details about creating project with existing database.

Connection string has been established on App.config file.

<connectionstrings> <add connectionstring="metadata=res://*/Entity.DatabaseContext.csdl|res://*/Entity.DatabaseContext.ssdl|res://*/Entity.DatabaseContext.msl;provider=System.Data.SqlClient;provider connection string="data source=(local);initial catalog=Northwind;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" name="DatabaseContext" providername="System.Data.EntityClient">

Execute Code of Calling Stored Procedure

Create custom Customer and Product entity because we need List of Customer and Product entity from stored procedure. Create a domain entity that will hold those two lists of Customer and Product.

public class CustomCustomer
{
    public int Id { get; set; }
    public String FirstName { get; set; }
    public String LastName { get; set; }
    public String City { get; set; }
    public String Country { get; set; }
    public String Phone { get; set; }
}

public class CustomProduct
 {
     [Key]
     public int Id { get; set; }
     public String ProductName { get; set; }
     public int SupplierId { get; set; }
     public decimal UnitPrice { get; set; }
     public String Package { get; set; }
     public bool IsDiscontinued { get; set; }
 }

And Domain entity is:

public class MultiResultDomain
 {
     public List<customcustomer> Customer { get; set; }
     public List<customproduct> Product { get; set; }
 }

Create ProductBLL class that will hold all business logic of products, public method to get DomainEntity with list of Product and Customer from Stored Procedure. I have used IObjectContextContext as database function repository. Implement the following method:

public class ProductBLL
 {
     private readonly DatabaseContext _DatabaseContext = new DatabaseContext();
     public MultiResultDomain GetMultipleResultSetFromSP()
     {
         MultiResultDomain domainEntity = new MultiResultDomain();
         var command = _DatabaseContext.Database.Connection.CreateCommand();
         command.CommandText = "[dbo].[SPR_GETMultipleResultSP]";
         command.CommandType = CommandType.StoredProcedure;
         try
         {
             _DatabaseContext.Database.Connection.Open();
             var reader = command.ExecuteReader();

         List<customcustomer> _listOfCustomer =
         ((IObjectContextAdapter)_DatabaseContext).ObjectContext.Translate<customcustomer>
         (reader).ToList();
             reader.NextResult();
             List<customproduct> _listOfProduct =
                 ((IObjectContextAdapter)_DatabaseContext).ObjectContext.Translate<customproduct>
         (reader).ToList();

             foreach (var cust in _listOfCustomer)
             {
                 Console.WriteLine("Name: Mr.{0} And Country: {1}", cust.FirstName,
                 cust.Country);
             }

             foreach (var product in _listOfProduct)
             {
                 Console.WriteLine("ProductName: {0} And Package: {1}",
                 product.ProductName, product.Package);
             }

             domainEntity.Customer = _listOfCustomer;
             domainEntity.Product = _listOfProduct;
             return domainEntity;
         }
         finally
         {
             _DatabaseContext.Database.Connection.Close();
         }
     }
 }

Debug Mode: Customer List

Product List:

Console Output:

class Program
{
    static void Main(string[] args)
    {
        MultiResultDomain domainEntity = new ProductBLL().GetMultipleResultSetFromSP();
        Console.ReadKey();

    }
}