How to Handle Stored Procedure Output Parameter with ADO.NET Entity Framework

Introduction

In this article, I'm going to explain how to handle Output Parameter in Stored Procedure with Entiry Framework.The Entity Framework is getting popular and many developers love it.So let’s start coding for that.For demo, I have create a table called ‘Customer’ which contains just two columns CustomerId and CustomerName. Following is the script for that.

/****** Object:  Table [dbo].[Customer]    Script Date: 09/09/2011 00:18:33 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Customer](
    [CustomerId] [int] NOT NULL,
    [CustomerName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
    [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

Now as our table are ready. Let’s create a stored procedure which will return the number of records as output parameter. Following is script for that.

/****** Object:  StoredProcedure [dbo].[esp_GetCustomerCount]    Script Date: 09/09/2011 00:20:21 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[esp_GetCustomerCount]
    @CustomerCount INT OUTPUT
AS
    select @CustomerCount=COUNT(customerId) from Customer
GO

Now our database part is ready so its time to create a entity model. So first I have created a console application and I added a new entity model via project-> right click-> Add new item and selected ADO.NET Entity Model like following.

Once I clicked add a wizard will start asking for choosing model contents like following.

Here I have selected Generate from database and clicked next it will ask for connection string. I have selected connection string and click next it will ask to select object of database. Here I have selected tables and  stored procedure which we have created earlier like following.

Now once we have done our model creation its time to create a function import for store procedure. So do that we need to first open Experiment.edmx in visual studio like below.

Once you click Model browser it will  reopen model browser in right side of your edmx like following.

Now in your store you need to expand store part and select stored procedure and click Add function import like following to create function for stored procedure.

Once you click Add function import a dialog box will open to select stored procedure and return type like following.

Here I have changed name of function called ‘GetCustomerCount’ now once you click OK it will create a function called ‘GetCustomerCount’ in entity framework model. Now its time to use that in code. Following is the code from where can find the output parameter value.

using System;
 
namespace ExperimentConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            using (ExperimentEntities myContext = new ExperimentEntities())
            {
                System.Data.Objects.ObjectParameter output = new System.Data.Objects.ObjectParameter("CustomerCount", typeof(int));
                myContext.GetCustomerCount(output);
                Console.WriteLine(output.Value);
            }
        }
    }
}

Here in above code you can see that I have created a object parameter and passed that to function as output parameter is there. Once this function will complete execution it will return value and you can get value with .value property. I have printed that in console application. Following is the output as expected as I have added four records in database.

So that’s it. It’s very easy to use output parameter. Hope you liked it.