EF Basic 30:CRUD Using SP

CRUD using Stored Procedure

In the previous chapter, we have seen how to get data using a stored procedure. In this chapter, we will use stored procedures for CUD (create, update, delete) operation using DbContext. That means context will execute stored procedures instead of DDL statements on context.SaveChanges().

We will use the following stored procedures:

1. sp_InsertStudentInfo stored procedure to insert a new student into the database
2. sp_UpdateStudent to update the student
3. sp_DeleteStudent to delete the student in the database.

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 of all, add these stored procedures into EDM and make sure that the Import selected stored procedures 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

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 Insert Function>, <Select Update Function>, and <Select Delete Function>. Select the appropriate stored procedure for each one, e.g. Select sp_InsertStudentInfo for Insert function, as shown below:

Get paid to share your links!

sp_InsertStudentInfo returns new auto generated StudentId. 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 to ensure 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 calls SaveChanges after adding a new student, it will 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 further operation.