Click here to Skip to main content
15,844,477 members
Articles / Web Development / ASP.NET
Tip/Trick

Return Multiple Result Set using Entity Framework from Stored Procedure

Rate me:
Please Sign up or sign in to vote.
4.87/5 (12 votes)
19 Jan 2016CPOL1 min read 65.7K   15   6
How to return Multiple Result Set using Entity Framework with 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:

SQL
        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

Image 1

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:

Image 2

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

Image 3

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.

C#
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; }
}
C#
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:

C#
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:

C#
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

Image 4

Product List:

Image 5

Console Output:

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

    }
}

Image 6

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Desme Bangladesh
Bangladesh Bangladesh
Software Engineer and have been working at desme from 2013.I have been working on Asp.Net and developed several websites are running successfully in different countries. I'm very much interested and passionate about learning new technologies.

Comments and Discussions

 
Questionsuggestions for common code for all type of records, if any Pin
Member 770101513-May-20 4:31
Member 770101513-May-20 4:31 
QuestionIs this done by calling two times stored procedure. Pin
Nitin Jain 100523-Jan-19 23:25
Nitin Jain 100523-Jan-19 23:25 
AnswerRe: Is this done by calling two times stored procedure. Pin
Ali_10015-Apr-20 10:59
Ali_10015-Apr-20 10:59 
GeneralMy vote of 5 Pin
Pratik Bhuva19-Jan-16 19:14
professionalPratik Bhuva19-Jan-16 19:14 
GeneralMy vote of 4 Pin
Manas_Kumar18-Jan-16 19:10
professionalManas_Kumar18-Jan-16 19:10 
GeneralRe: My vote of 4 Pin
Md.Shamim Hassan19-Jan-16 5:34
professionalMd.Shamim Hassan19-Jan-16 5:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.