EF Code First:Executing Stored Procedure

Code First in Entity Framework does not support Stored Procedure by default. We can not even map our Stored procedure to Entity. There are a many scenario we have seen where we are bound to use stored procedure for any database modifications (insert/update/delete). Here is how we can use stored procedure. 

Here i have created two Entities 

 public class Employee  
{
public int Id { get; set; }
public string Name { get; set; }
}
public class Address:Employee
{
public string Address1 { get; set; }
public string Address2 { get; set; }
public string City { get; set; }
public int Pin { get; set; }
}

——————————————————————————————————————————————————————– 

Now we create EmployeeDBContext 


 public class EmployeeContext : DbContext  
{
public DbSet EmployeeSet { get; set; }
public DbSet
Address { get; set; }
public void AddEmp(Address Emp)
{
this.Database.ExecuteSqlCommand("exec AddEmpData @Name,@Address1,@Address2,@City,@pin,@Discriminator",
new SqlParameter("@Name", Emp.Name),
new SqlParameter("@Address1",Emp.Address1),
new SqlParameter("@Address2", Emp.Address2),
new SqlParameter("@City", Emp.City),
new SqlParameter("@Pin", Emp.Pin),
new SqlParameter("@Discriminator", "Address"));
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove();
}
}


——————————————————————————————————————————————————————–

 using System;  
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
namespace Calling_SP
{
class Program
{
static void Main(string[] args)
{
var Address = new Address
{
Name="Nirbhay",
Address1 = "Wakad-1",
Address2 = "Silver Society",
City = "pune",
Pin = 411010
};
//Save Data to database
using (var context = new EmployeeContext())
{
context.AddEmp(Address);
}
Console.Write("Person saved !");
Console.ReadLine();
//Retrieving data from Database
using (var context = new EmployeeContext())
{
var result = context.Address.SqlQuery("GetEmpData").ToList
();
foreach (var item in result)
{
Console.Write(item.Id);
Console.Write(item.Name);
Console.Write(item.Address1);
Console.Write(item.Address2);
Console.Write(item.City);
Console.Write(item.Pin);
Console.WriteLine();
}
}
Console.ReadLine();
}
}
}
——————————————————————————————————————————————————————–
In App.Config set the desired connection string
   





——————————————————————————————————————————————————————–
Stored Procedure
 USE [Employee]  
GO
/****** Object: StoredProcedure [dbo].[AddEmpData] Script Date: 05/23/2012 12:27:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[AddEmpData]
@Name nvarchar(max),
@Address1 nvarchar(max),
@Address2 nvarchar(max),
@City nvarchar(max),
@Pin int,
@Discriminator nvarchar(128)
AS
Begin
insert into Employee (Name,Address1,Address2,City,Pin,Discriminator)
values
(@Name,@Address1,@Address2,@City,@Pin,@Discriminator)
End
GO
*********************************************************************************************
USE [Employee]
GO
/****** Object: StoredProcedure [dbo].[GetEmpData] Script Date: 05/23/2012 12:28:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[GetEmpData]
AS
Begin
select * from Employee
End
GO

———————————————————————————————————————————————————————
Following are the 2 tables Created By EF.


You can see in the above DB Diagram there is a field Called Discriminator, it is Created by EF because we have inherited Employee Class in Address and the value for this field is Address.




Published by Nirbhay

Technology Geek,Blogger,Reviewer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: