Introduction
It is a known fact that stored procedures are always better when compared with standalone queries whenever performance is considered. So, here we will use the ADO.NET Entity Data Model Designer to map the insert
, update
, and delete
operations of an entity type to stored procedures. As we know very well, the default behaviour of each entity type EDM on insert
, update
and delete
operations is executing auto generated queries.
Observation
Let us assume that we have a single table Department
in our database MyOrg
and we will add it to our EDM MyOrg.edmx in a console application named ModificationFunctions
.
The data in the table is:
Now let us perform Insert
, Update
and Delete
operations and observe the default behaviour.
namespace ModificationFunctions
{
class Program
{
static void Main(string[] args)
{
using (MyOrgEntities OE = new MyOrgEntities())
{
OE.Departments.Add(new Department() { DName = "Production", HOD = "Zing", Gender = "M" });
OE.SaveChanges();
var dept = OE.Departments.Where(x => x.Did == 1003).FirstOrDefault();
dept.HOD = "Martin";
OE.SaveChanges();
var dept1 = OE.Departments.Where(x => x.Did == 1013).FirstOrDefault();
OE.Departments.Remove(dept1);
OE.SaveChanges();
}
}
}
}
To observe the default behavior, I will use IntelliTrace Window.
Auto generated insert query:
Auto generated update query:
Auto generated delete query:
Implementation
Step 1
Now let us try to change this default behaviour by replacing all these queries with user defined stored procedures, i.e.,
Create Proc InsertDepartment
(@DName as Varchar(50), @HOD as varchar(50), @Gender as Varchar(50))
as
INSERT INTO Department (DName,HOD,Gender) VALUES (@DName,@HOD,@Gender)
Create Proc UpdateDepartment
(@DName as Varchar(50), @HOD as varchar(50), @Gender as Varchar(50),@Did as int)
as
Update Department Set DName=@DName,HOD=@HOD,Gender=@Gender where Did=@Did
Create Proc DeleteDepartment
(@Did as int)
as
Delete from Department where Did=@Did
Step 2
Now go to MyOrg.edmx designer surface, right-click and select update model from database and then select all these 3 stored procedures from Stored Procedures and Functions and make sure that you uncheck the option Import selected stored procedures and functions into the entity model and then click Finish button. As shown below:
Step 3
Now right-click the entity type Department
to map the insert
, update
and delete
operations and select Stored Procedures Mapping. The Map Entity to Functions view of the Mapping Details window appears as shown below:
Step 4
From the drop-down list, select the stored procedure to which the insert
, update
and delete
operation is to be mapped. The window is populated with default mappings between entity properties and stored procedure parameters as shown below and save .edmx file.
Now let us examine the same insert
, update
and delete
operations using IntelliTrace Window and now it should use the imported stored procedures for insert
, update
and delete
operations instead of default queries.
Insert
Update
Delete
Thanks for reading!