Articles → ADO.NET → Call stored procedure using entity framework

Call stored procedure using entity framework







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


Picture showing the project structure in visual studio

Click to Enlarge



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




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

Click to Enlarge





Picture showing the Attribute table added in entity framework

Click to Enlarge



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






Picture showing the context menu for updating model from database

Click to Enlarge



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

Click to Enlarge



Picture showing the stored procedure added in entity framework

Click to Enlarge



  1. 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.
  2. Second stored procedure GetAttribute returns an entity called ‘Attribute’.
  3. Third procedure checks if the attribute exists or not. The return type is string.


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

Click to Enlarge



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

Click to Enlarge





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


Picture showing the output of calling the stored procedure using entity framework

Click to Enlarge


Posted By  -  Karan Gupta
 
Posted On  -  Thursday, January 8, 2015

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250