Entity framework and Stored procedure output parameter

One of my friend was having problem in Entity framework and stored procedure output parameter. He was confused how to use output parameter with stored procedures.So I did some search on internet and I found a great way to use output parameter so I thought I should write a blog post for the same. So in this blog post I am going to explain you how we can use output parameter of entity framework.

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. Stay tuned for more. Till then happy programming.. Namaste!!