Articles → ADO.NET → Call Stored Procedure Using Entity Framework

Call Stored Procedure Using Entity Framework

In this tutorial I will explain how to call a stored procedure using entity framework. Before we start working on it lets do some basic preparation like creating a project, creating a table etc.

Before you start reading this tutorial you should satisfy following requirements in terms of technical knowledge

You should know how to create a table in database
You must be aware of entity framework and how to add tables in entity framework.
You must be aware of stored procedure in SQL server.

So let’s start. First of all create a new project of type console application (you can select any type of application). See the screen shot below for project structure.

Picture showing the project structure in visual studio

Click to Enlarge

Next step is to create a table. Here I am creating a table ‘Attribute’ which contains 2 columns i.e. AttributeId which is the primary key of the table and AttributeName.

CREATE VIEW vw_GetEmployeeData AS 
SELECT 
  e.EmployeeId, 
  e.EmployeeName, 
  d.DepartmentName 
FROM 
  Employee e 
  INNER JOIN Department d ON e.DepartmentId = d.DepartmentId

Last step which we are going to do is to add a new item of type ‘ADO.NET Entity Data Model’ in the project

Picture showing adding the new item in the project of type ADO.NET Entity Data Model

Click to Enlarge

And add the table which we have just created in ‘ADO.NET Entity Data Model’.

Once you have added table in entity framework this is how your solution explorer and entity framework designer will look like

Picture showing the Attribute table added in entity framework

Click to Enlarge

Now we have done all our preparation lets work on the actual stuff. First of all create three stored procedures with different signatures.

-- Procedure to insert value in attribute table
Create Proc InsertAttribute @Attribute VARCHAR(50) As Insert into Attribute 
values 
  (@Attribute) -- Procedure to get attribute based on attribute
  CREATE PROC GetAttribute @AttributeId int As 
Select 
  * 
from 
  Attribute 
where 
  AttributeId = @AttributeId -- Procedure to check if the attribute exists
  ALTER PROC IsAttributeExists @AttributeId int As IF EXISTS(
    Select 
      * 
    from 
      Attribute 
    where 
      AttributeId = @AttributeId
  ) 
SELECT 
  'EXIST' ELSE 
SELECT 
  'NOTEXIST'

Here I am not explaining the functionality of stored procedures as they are pretty straight forward. Let’s add stored procedure in entity framework.

First of all right click on the entity framework designer and click on ‘Update Model from database’

Picture showing the context menu for updating model from database

Click to Enlarge

A window appears as shown in figure below. In the window you can select stored procedures and click on ‘Finish’

Picture showing the update model wizard to select the database objects to be updated

Click to Enlarge

Once you click on Finish you could see stored procedure added in entity framework.

Picture showing the stored procedure added in entity framework

Click to Enlarge

Now next step is to inform entity framework about the return type of procedure.

In the first stored procedure i.e. InsertAttribute we are inserting value in table and we are not returning any value so return type in void.
Second stored procedure GetAttribute returns an entity called ‘Attribute’.
Third procedure checks if the attribute exists or not. The return type is string.

To specify return type of procedure that right click on procedure and click on ‘Add Function Import..’

Picture showing the Add function import option in stored procedure in entity framework

Click to Enlarge

Once you click on ‘Add Function Import..’ a window comes up to set the output type.

Picture showing the add function import window for setting the output type

Click to Enlarge

In the figure above I have highlighted how to select return type of the procedure. You have to specify the return type of each procedure individually.

Now you can call all the procedure

using(ABCEntities context = new ABCEntities()) {
	//  Proc 1
	context.InsertAttribute("Name");

	//Proc 2
	string status = context.IsAttributeExists(1).First();
	Console.WriteLine(string.Format("status = {0}", status));
	// Proc 3
	Attribute attr = context.GetAttribute(1).First();

	Console.WriteLine(string.Format("Attributeid= {0}{1}AttributeName= {2}", attr.AttributeId, Environment.NewLine, attr.AttributeName));

	Console.ReadLine();
}

Output

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Thursday, January 8, 2015

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250