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<Employee> EmployeeSet { get; set; }
public DbSet<Address> 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<PluralizingTableNameConvention>();
}
}
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
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<Address>();
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
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="EmployeeContext" connectionString="data source=SY113;initial catalog=Employee;integrated security=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.
