Introduction
Recently, I was working on a project and in that project there is a new scenario for me. The scenario is that when communicating with the database we need to work with the Stored Procedure using Entity Framework. So, I am creating an article series and in the first part I am dealing with the database using Entity Framework for it.
In this article, we'll learn to communicate with the database using the Entity Framework with a Stored Procedure. So, let's begin with the step-by-step procedure and follow the sections given below:
- Working with Stored Procedure
- Creating Library Project and Adding ADO.NET Entity Data Model
- Stored Procedure Mapping with Entity
- Import Function
- Adding Class to do CRUD Operations
Working with Stored Procedure
At first, we need to have the Stored Procedure in the database, so let's create the Stored Procedure of Insert, Update and Delete.
Creating Stored Procedure to Insert
Suppose we have the table and now we are creating the Stored Procedure to insert the record. Have a look at the following code:
- 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
- begin
- set @ReturnValue=0
- end
- select @ReturnValue=SCOPE_IDENTITY()
- End
- GO
Creating Stored Procedure to Edit
Now we are creating the Stored Procedure to edit the record. Have a look at the following code:
- 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
- begin
- set @ReturnValue=0
- end
- select @ReturnValue
- end
- GO
Creating Stored Procedure to Delete
Now we are creating the Stored Procedure to delete the record. Have a look at the following code:
- Create Procedure [dbo].[SP_DeleteCollegeDetails]
- (
- @CollegeID int
- )
- as
- Begin
- Declare @ReturnValue int
- IF EXISTS(SELECT CollegeEmailID FROM CollegeDetails)
- begin
- Delete CollegeDetails where CollegeID= @CollegeID
- set @ReturnValue=1
- end
- begin
- set @ReturnValue=0
- end
- select @ReturnValue
- end
- GO
Creating Library Project and Adding ADO.NET Entity Data Model
Now, we'll create the class library in which we'll do the CRUD operations. So, just follow the procedure given below.
Step 1
Open Visual Studio and create a Class Library project.
Step 2
In this project, now we'll add an ADO.NET Entity Data Model.
Step 3
At first we select the model content by choosing model contents.
Step 4
Add the new connection to add the database.
Step 5
Now we'll choose the Entity Framework version to work with it.
Step 6
In the next wizard, we'll select the database objects.
Now the entity model has been added in the library project. Have a look at the following screenshot:
Stored Procedure Mapping with Entity
In this section we'll create the mapping of Stored Procedure with the Entity Model. So, let's start with the following procedure.
Step 1
Just right-click on the entity model and select the Stored Procedure Mappting.
Step 2
In the Mapping Details wizard, when you click on the first dropdownlist 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 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 proerty in the Rows Affected column. Have a look:
Step 4
In the Result Column Bindings, write the out value and when you hit the 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 the 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 your 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
- {
-
- public List<CollegeDetail> GetCollegeRecords()
- {
- using (CollegeDataEntities context = new CollegeDataEntities())
- {
- return context.CollegeDetails.ToList();
- }
- }
-
-
- 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;
- }
- }
- }
-
-
-
- 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;
- }
- }
- }
-
-
-
- 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!!