Working With Stored Procedures Using Entity Framework: Part 1

CREATE PROCEDURE [dbo].[Insert_CollegeDetails]
    (
        @CollegeName varchar(100),
        @CollegeAddress nvarchar(150),
        @CollegePhone bigint,
        @CollegeEmailID nvarchar(50),
        @ContactPerson varchar(50),
        @ContactPersonPhoneNo bigint,
        @ContactPersonEmailID nvarchar(50),
        @ContactPersonDesignation varchar(50),
        @FirstVisitDate date,
        @FirstVisitMode varchar(20),
        @State varchar(100),
        @City varchar(100),
        @FDP varchar(20),
        @Workshops int,
        @CreatedBy varchar(25),
        @ReturnValue int OUT
    )
AS
BEGIN
    IF EXISTS(SELECT CollegeEmailID FROM CollegeDetails)
    BEGIN
        INSERT INTO CollegeDetails (
            CollegeName, CollegeAddress, CollegePhone, CollegeEmailID, ContactPerson,
            ContactPersonPhoneNo, ContactPersonEmailID, ContactPersonDesignation,
            FirstVisitDate, FirstVisitMode, State, City, FDP, Workshops, CreatedBy
        )
        VALUES (
            @CollegeName, @CollegeAddress, @CollegePhone,
            @CollegeEmailID, @ContactPerson, @ContactPersonPhoneNo,
            @ContactPersonEmailID, @ContactPersonDesignation, @FirstVisitDate,
            @FirstVisitMode, @State, @City, @FDP, @Workshops, @CreatedBy
        )
        SET @ReturnValue = 1
    END
    ELSE
    BEGIN
        SET @ReturnValue = 0
    END

    SELECT @ReturnValue = SCOPE_IDENTITY()
END
GO

CREATE PROCEDURE [dbo].[SP_EditCollegeDetails]
(
    @CollegeID int,
    @CollegeName varchar(100),
    @ContactPerson varchar(50),
    @ContactPersonPhoneNo bigint,
    @ContactPersonEmailID nvarchar(50),
    @FDP varchar(20),
    @Workshops int,
    @CreatedBy varchar(25),
    @EditedBy varchar(50)
)
AS
BEGIN
    DECLARE @ReturnValue int

    IF EXISTS(SELECT CollegeEmailID FROM CollegeDetails)
    BEGIN
        UPDATE CollegeDetails SET
            CollegeName = @CollegeName,
            ContactPerson = @ContactPerson,
            ContactPersonPhoneNo = @ContactPersonPhoneNo,
            ContactPersonEmailID = @ContactPersonEmailID,
            FDP = @FDP,
            Workshops = @Workshops,
            CreatedBy = @CreatedBy,
            EditedBy = @EditedBy
        WHERE CollegeID = @CollegeID

        SET @ReturnValue = 1
    END
    ELSE
    BEGIN
        SET @ReturnValue = 0
    END

    SELECT @ReturnValue
END
GO

CREATE PROCEDURE [dbo].[SP_DeleteCollegeDetails]
(
    @CollegeID int
)
AS
BEGIN
    DECLARE @ReturnValue int

    IF EXISTS(SELECT CollegeEmailID FROM CollegeDetails WHERE CollegeID = @CollegeID)
    BEGIN
        DELETE FROM CollegeDetails WHERE CollegeID = @CollegeID
        SET @ReturnValue = 1
    END
    ELSE
    BEGIN
        SET @ReturnValue = 0
    END

    SELECT @ReturnValue
END
GO

Step 2. In the Mapping Details wizard, when you click on the first drop-down list option, you'll see the Stored Procedure name to add. Now, we'll choose the specific Stored Procedure that is used to insert the records.

Step 3. After selecting the option, you can see all the model properties are mapped automatically. There is an out keyword named ReturnedValue that is not mapped so we need to map it with another property otherwise it will throw an error when you save the application.

So, just click on the checkbox of that specific property in the Rows Affected column. Have a look,

Step 4. In the Result Column Bindings, write the out value and when you hit Enter, it'll automatically map with the specific property of the table.

Updating Model

Now we need to update the model to add a new Stored Procedure. Use the following procedure.

Step 1. Open the Model Browser, right-click on the Model, and choose Update model from the database.

Step 2. Select the Stored Procedure name to add.

Step 3. Now, again we need to map the entity model with the Stored Procedure. Right-click on the model and choose Stored Procedure mapping. Select the update function and choose the update procedure.

Step 4. Now select the delete function and choose the delete procedure.

Import Function

Now we'll need to add the function to call the procedure using the Entity Framework. Just use the procedure below.

Step 1. Right-click on the model and click on Add New to add the function Import.

Step 2. Specify the name for Insert and choose the specific procedure to add.

Step 3. Repeat the procedure to add a function for the update.

Step 4. Now do the same for the delete.

Now you can check our model that contains your function and Stored Procedure.

Adding Class to do CRUD Operations

In this section, we'll add a class to write the code for doing the CRUD Operations.

Step 1. Right-click on the project to add a new class.

Step 2. Just write the following code.

using System;
using System.Collections.Generic;
using System.Data.Entity.Core.Objects;
using System.Linq;

namespace CollegeDetailsLibrary
{
    public class CollegeDataOperation
    {
        // This Method is used to access all data
        public List<CollegeDetail> GetCollegeRecords()
        {
            using (CollegeDataEntities context = new CollegeDataEntities())
            {
                return context.CollegeDetails.ToList();
            }
        }

        // This Method is used to insert the college records
        public bool InsertCollegeRecords(CollegeDetail detail)
        {
            using (CollegeDataEntities context = new CollegeDataEntities())
            {
                ObjectParameter ReturnedValue = new ObjectParameter("ReturnValue", typeof(int));
                context.InsertCollegeDetails(
                    detail.CollegeName, detail.CollegeAddress,
                    detail.CollegePhone, detail.CollegeEmailID,
                    detail.ContactPerson, detail.ContactPersonPhoneNo,
                    detail.ContactPersonEmailID, detail.ContactPersonDesignation,
                    detail.FirstVisitDate, detail.FirstVisitMode,
                    detail.State, detail.City,
                    detail.FDP, detail.Workshops,
                    detail.CreatedBy, ReturnedValue
                );
                if (Convert.ToInt32(ReturnedValue.Value) >= 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        // This method is used to update the college details based on the college id
        // Last Created in 20th June
        public bool UpdateCollegeRecords(int? id, CollegeDetail collegeDetail)
        {
            using (CollegeDataEntities context = new CollegeDataEntities())
            {
                int ReturnedValue = context.UpdateCollege_Details(
                    id, collegeDetail.CollegeName, collegeDetail.ContactPerson,
                    collegeDetail.ContactPersonPhoneNo, collegeDetail.ContactPersonEmailID,
                    collegeDetail.FDP, collegeDetail.Workshops,
                    collegeDetail.CreatedBy, collegeDetail.EditedBy
                );
                if (ReturnedValue >= 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        // This method is used to delete the college details based on the college id
        // Last Created in 20th June
        public bool DeleteCollegeRecords(int? id)
        {
            using (CollegeDataEntities context = new CollegeDataEntities())
            {
                int ReturnedValue = context.DeleteCollege_Details(id);
                if (ReturnedValue >= 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}

That's it.

Summary

This article described how to work with Stored Procedures using the Entity Framework in the ASP.NET Class Library. We'll see in the next article how to use and call this on the ASP.NET Web Application. Thanks for reading and Happy Coding!