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 TABLE [dbo].[Attribute](
	[AttributeId] [int] IDENTITY(1,1) NOT NULL,
	[AttributeName] [varchar](50) NULL
)




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)

Go 
-- Procedure to get attribute based on attribute
CREATE PROC GetAttribute
@AttributeId int
As
Select * from Attribute where AttributeId = @AttributeId

Go
-- Procedure to check if the attribute exists
Create 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
	GetAttribute_Result 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