CRUD Operations using Stored Procedure in Entity Framework

In the previous chapter, we have seen how to read data using a stored procedure. In this chapter, we will use stored procedures for CURD (create, update,Read,delete) operations for an entity when we call the SaveChanges() method in the database-first approach.

We will use the following stored procedures for Student entity:

1. sp_InsertStudentInfo: Inserts a new student record into the database
2. sp_UpdateStudent: Updates a student record
3. sp_DeleteStudent: Deletes a student record in the database.

The followings are SQL scripts for each stored procedure.

Sp_InsertStudentInfo:

CREATE PROCEDURE [dbo].[sp_InsertStudentInfo]
    -- Add the parameters for the stored procedure here
    @StandardId int = null,
    @StudentName varchar(50)
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

sp_UpdateStudent:

CREATE PROCEDURE [dbo].[sp_UpdateStudent]
    -- Add the parameters for the stored procedure here
    @StudentId int,
    @StandardId int = null,
    @StudentName varchar(50)
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

sp_DeleteStudent

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, you need to update your existing EDM to add these stored procedures into your EDM. Right click on your designer and click Update Model from Database.. to open the update wizard. Expand the Stored Procedures and Functions node, select the above stored procedures and uncheck the Import selected stored procedures and function into the entity model checkbox because we will map these procedures with the Student entity directly.

Click on the Finish button. The Model Browser will display the procedures in the Storage model section, but not in the Function Imports, as shown below.

You need to map these stored procedures with the Student entity. In the EDM designer, right click on the Student entity and select Stored Procedure Mapping to open Mapping details, as shown below.

In the Mapping Details, as shown below, you will see <Select Insert Function>, <Select Update Function>, and <Select Delete Function>. Select the appropriate stored procedure for each one in the dropdown, e.g. sp_InsertStudentInfo for the Insert function, sp_UpdateStudent for the update function, and sp_DeleteStudent for the delete function.

sp_InsertStudentInfo returns the value of StudentId and maps it with the Student entity's StudentID, as shown below:

Complete the mapping of Insert, Update and Delete procedures parameter with the appropriate properties of Student entities, as shown below.

Now, we need to validate it before executing to ensure that there will not be a run time error. To accomplish this, right click on the Student entity in the designer and click Validate and make sure that there are no warnings or errors.

Now, whenever you add, update, or delete the Student entity, EF will use these stored procedures for CUD operations, instead of executing SQL commands. The following example demonstrates this:

using (var context = new SchoolDBEntities())
{
    Student student = new Student() { StudentName = "New student using SP"};

    context.Students.Add(student);
    //will execute sp_InsertStudentInfo 
    context.SaveChanges();

    student.StudentName = "Edit student using SP";
    //will execute sp_UpdateStudent
    context.SaveChanges();

    context.Students.Remove(student);
    //will execute sp_DeleteStudentInfo 
    context.SaveChanges();
}

The above example will execute the following statements on each SaveChanges() call:

exec [dbo].[sp_InsertStudentInfo] @StandardId=NULL,@StudentName='New student using SP'
go

exec [dbo].[sp_UpdateStudent] @StudentId=47,@StandardId=NULL,@StudentName='Edit student using SP'
go

exec [dbo].[sp_DeleteStudent] @StudentId=47
go

Note: Once it executes the SaveChanges method after adding a new student, it will assign a database generated value to the StudentID property. This is necessary in order to track it and perform further actions on that entity object. The following image shows the value in the debug view of Visual Studio.