Answer is using a stored procedure:
CRUD using Stored Procedure::
Entity Framework has ability to automatically build native commands for the database based on your LINQ to Entities or Entity SQL queries, as well as build the commands for inserting, updating, or deleting data. You may want to override these steps and use your own predefined stored procedures. You can use stored procedures either to get the data or to add/update/delete the records to one or multiple database tables.
Here Stored procedures for create, update, delete operation will be done using DbContext. That means context will execute stored procedure instead of DDL statements on context.SaveChanges().
Will use
- sp_InsertStudentInfo stored procedure to insert a new student into
the database
- sp_UpdateStudent to update the student
sp_DeleteStudent to delete the student in the database.
CREATE PROCEDURE [dbo].[sp_InsertStudentInfo]
-- Add the parameters for the stored procedure here
@StandardId int = null,
@StudentName varchar
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [SchoolDB].[dbo].[Student]([StudentName],[StandardId])
VALUES(@StudentName, @StandardId)
SELECT SCOPE_IDENTITY() AS StudentId
END
CREATE PROCEDURE [dbo].[sp_UpdateStudent]
-- Add the parameters for the stored procedure here
@StudentId int,
@StandardId int = null,
@StudentName varchar
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update [SchoolDB].[dbo].[Student]
set StudentName = @StudentName,StandardId = @StandardId
where StudentID = @StudentId;
END
CREATE PROCEDURE [dbo].[sp_DeleteStudent]
-- Add the parameters for the stored procedure here
@StudentId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE FROM [dbo].[Student]
where StudentID = @StudentId
END
First of all, add these stored procedures into EDM and make sure Import selected stored procudures and function into the entity model checkbox is unchecked, as we will map these procedures with Student entity directly.
Now Model Browser will add procedures into Storage model but not in Function Imports
Now, in the EDM designer, right click on Student entity and select Stored Procedure Mapping to open Mapping details:
In the Mapping Details, you will see , , . Select the appropriate stored procedure for each one eg. Select sp_InsertStudentInfo for Insert function as shown below:
sp_InsertStudentInfo returns new auto generated StudentId. So map that with Student Entity’s StudentID as shown below:
Complete the mapping of Insert, Update and Delete procedures as shown below:
Now, we need to validate it before executing, in order to make sure that there will not be a run time error. To accomplish this, right click on Student entity in the designer and click Validate and make sure that there are no warnings or errors:
Now you can add, update and delete student as shown below:
using (var context = new SchoolDBEntities())
{
Student newStudent = new Student() { StudentName = "New Student using SP"};
context.Students.Add(newStudent);
//will execute sp_InsertStudentInfo
context.SaveChanges();
newStudent.StudentName = "Edited student using SP";
//will execute sp_UpdateStudent
context.SaveChanges();
context.Students.Remove(newStudent);
//will execute sp_DeleteStudentInfo
context.SaveChanges();
}
The code shown above will execute the following stored procedures on each SaveChanges():
exec [dbo].[sp_InsertStudentInfo] @StandardId=NULL,@StudentName='New Student using SP'
go
exec [dbo].[sp_UpdateStudent] @StudentId=47,@StandardId=NULL,@StudentName='Edited student using SP'
go
exec [dbo].[sp_DeleteStudent] @StudentId=47
go
Note: Once context call SaveChagnes after adding a new student, it assign new StudentID to StudentID property of the Student entity because sp_InsertStudentInfo returns StudentId. This is necessary in order to use that entity object for a further operation.
See here more practically.
