Skip to main content

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.

Entity Data Model for stored procedure output parameter

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

Choose model context for entity framework stored proecdure output parameter

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.

Stored procedure and tables with Entity Framework

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.

Model Browser for Entity framework for stored procedure without parameter

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

Model Browser in entity framework

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.

Fuction import for entity framework stored procedure output parmeter

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

GetCustomerCount function for Entity framework stored procedure output parameter


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.

Output paremeter result for entity framework

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!!

Shout itkick it on DotNetKicks.com

Comments

  1. How to retrieve an outputparameter of type "uniqueidentifier"? thanks!

    ReplyDelete
  2. It will be same as this! No special things needed.

    ReplyDelete

Post a Comment

Your feedback is very important to me. Please provide your feedback via putting comments.

Popular posts from this blog

How to create Rest API(Web API) with ASP.NET Core 1.0

Recently Microsoft has released ASP.NET Core 1.0 RC2, I am getting lots of request from readers that about creating Web API. So I thought it will be a good idea to write a blog post about how to create Rest API(Web API) with ASP.NET Core 1.0.

So let’s get started via creating an ASP.NET Core 1.0 Web Application like following.



Once you click on ASP.NET Web Application, It will ask whether you need to create Web Application or Web API application. We are going to Web API so I am going to select Web API Application like following. Please note that in ASP.NET Core 1.0 there is no separate libraries or DLLs required for creating web APIs. This is just a project template.



Now once you click on OK It will create a Web API application with default values controller and program.cs.  As you know Program.cs is now starting point for the ASP.NET Core 1.0 application so It contains all the required configuration and startup items. Following is a code for that.
using System.IO; using Microsoft.Asp…

How to use migration with Entity Framework Core

Entity framework core is  the lightweight, extensible and cross-platform version of Entity Framework. Before some time, Microsoft has Released a new version of Entity Framework RC2. I have written a couple of blog post about Entity framework code first migration earlier for Entity framework 6.0. So there was a couple of request coming for writing a blog post about Entity Framework Core RC2 migration. So I thought it will be a good idea to give an overview how database migration works in Entity Framework Core RC2. This post will cover a basic scenario where we are going to create the database with existing ASP.NET Identity migration and then we are going to create a new model and have that migration applied in the database.

How to use Entity Framework Migrations: Let’s get started, To demonstrate entity framework core migrations, I am going to create a sample asp.net core web application like following.



Once we select asp.net core application it will appear the following dialog.



Now w…

Solution: gulp is not recognized as internal or external command- Visual Studio

Recently, After Upgrading to the newer version of Gulp, I was getting this error.  After doing lots of  finding online and in my system I was able to figure out that Modules like Gulp does not installed to the path. So when you try to run it from the command line it was showing below error.
gulp is not recognized as internal or external command How to solve : gulp is not recognized as internal or external command To solve this error we just need to create a new environment variable and set the NPM modules path there.

To create an environment variable,  Right click My Computer/ This Pc and click on properties.  It will open following windows.



Go to Advance System Settings and it will open system properties windows like below.



Click on Environment Variables it will open an environment and system variables windows like below.



Click on new System variable and put NODE_PATH in system variable name and %AppData%\npm\node_modules into the variable value like below.



Click on Ok. That’s it. N…