Post
Ask Question

Working With Stored Procedures Using Entity Framework: Part 1

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 a 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 a 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:
  1. CREATE Procedure[dbo]. [Insert_CollegeDetails]  
  2.     (  
  3.         @CollegeName varchar(100),  
  4.         @CollegeAddress nvarchar(150),  
  5.         @CollegePhone bigint,  
  6.         @CollegeEmailID nvarchar(50),  
  7.         @ContactPerson varchar(50),  
  8.         @ContactPersonPhoneNo bigint,  
  9.         @ContactPersonEmailID nvarchar(50),  
  10.         @ContactPersonDesignation varchar(50),  
  11.         @FirstVisitDate date,  
  12.         @FirstVisitMode varchar(20),  
  13.         @State varchar(100),  
  14.         @City varchar(100),  
  15.         @FDP varchar(20),  
  16.         @Workshops int,  
  17.         @CreatedBy varchar(25),  
  18.         @ReturnValue int OUT  
  19.     )  
  20.   
  21. as  
  22. Begin  
  23. IF EXISTS(SELECT CollegeEmailID FROM CollegeDetails)  
  24. begin  
  25. Insert into CollegeDetails(  
  26.     CollegeName, CollegeAddress, CollegePhone, CollegeEmailID, ContactPerson,  
  27.     ContactPersonPhoneNo, ContactPersonEmailID, ContactPersonDesignation,  
  28.     FirstVisitDate, FirstVisitMode, State, City, FDP, Workshops, CreatedBy)  
  29. values(  
  30.     @CollegeName, @CollegeAddress, @CollegePhone,  
  31.     @CollegeEmailID, @ContactPerson, @ContactPersonPhoneNo,  
  32.     @ContactPersonEmailID, @ContactPersonDesignation, @FirstVisitDate,  
  33.     @FirstVisitMode, @State, @City, @FDP, @Workshops, @CreatedBy)  
  34. set @ReturnValue = 1  
  35. end  
  36. begin  
  37. set @ReturnValue = 0  
  38. end  
  39. select @ReturnValue = SCOPE_IDENTITY()  
  40. End  
  41. GO 
Creating a Stored Procedure to Edit
 
Now we are creating the Stored Procedure to edit the record. Have a look at the following code:
    1. CREATE Procedure[dbo]. [SP_EditCollegeDetails]  
    2. (  
    3.         @CollegeID int,  
    4.         @CollegeName varchar(100),  
    5.         @ContactPerson varchar(50),  
    6.         @ContactPersonPhoneNo bigint,  
    7.         @ContactPersonEmailID nvarchar(50),  
    8.         @FDP varchar(20),  
    9.         @Workshops int,  
    10.         @CreatedBy varchar(25),  
    11.         @EditedBy varchar(50)  
    12. )  
    13. as  
    14. Begin  
    15. Declare @ReturnValue int  
    16. IF EXISTS(SELECT CollegeEmailID FROM CollegeDetails)  
    17. begin  
    18. Update CollegeDetails set  
    19. CollegeName = @CollegeName, ContactPerson = @ContactPerson,  
    20.     ContactPersonPhoneNo = @ContactPersonPhoneNo,  
    21.     ContactPersonEmailID = @ContactPersonEmailID,  
    22.     FDP = @FDP, Workshops = @Workshops,  
    23.     CreatedBy = @CreatedBy, EditedBy = @EditedBy  
    24. where CollegeID = @CollegeID  
    25. set @ReturnValue = 1  
    26. end  
    27. begin  
    28. set @ReturnValue = 0  
    29. end  
    30. select @ReturnValue  
    31. end  
    32. GO 
    Creating a Stored Procedure to Delete
     
    Now we are creating the Stored Procedure to delete the record. Have a look at the following code:
      1. Create Procedure[dbo]. [SP_DeleteCollegeDetails]  
      2.     (  
      3.         @CollegeID int  
      4.     )  
      5. as  
      6. Begin  
      7. Declare @ReturnValue int  
      8. IF EXISTS(SELECT CollegeEmailID FROM CollegeDetails)  
      9. begin  
      10. Delete CollegeDetails where CollegeID = @CollegeID  
      11. set @ReturnValue = 1  
      12. end  
      13. begin  
      14. set @ReturnValue = 0  
      15. end  
      16. select @ReturnValue  
      17. end  
      18. GO 
      Creating a 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.
       
      Creating Class Library
       
      Step 2
       
      In this project, now we'll add an ADO.NET Entity Data Model.
       
      Creating Entity Model
       
      Step 3
       
      At first, we select the model content by choosing model contents.
       
      Selecting Model Contents in Entity Model
       
      Step 4
       
      Add the new connection to add the database.
       
      Creating Connection Properties in Entity Model
       
      Step 5
       
      Now we'll choose the Entity Framework version to work with it.
       
      Selecting Entity Framework Version
       
      Step 6
       
      In the next wizard, we'll select the database objects.
       
      Selecting Database Objects
       
      Now the entity model has been added to the library project. Have a look at the following screenshot:
       
      Entity Model
       

      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 Mapping.
       
      Creting Stored Procedure Mapping
       
      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.
       
      Selecting insert Function in Mapping
       
      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:
       
      Rows Affecting Value
       
      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.
       
      Result Column Binding in Mapping Details
       

      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.
       
      Update Model from Database
       
      Step 2
       
      Select the Stored Procedure name to add.
       
      Updating Model in Update Wizard
       
      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.
       
      Adding Update Function in Mapping Details
       
      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.
       
      Adding Function Import of Entity
       
      Step 2
       
      Specify the name for Insert and choose the specific procedure to add.
       
      Performing Function Import Adding
       
      Step 3
       
      Repeat the procedure to add a function for the update.
       
      Performing Updating in Function Import
       
      Step 4
       
      Now do the same for the delete.
       
      Performing Deleting in Function Import
       
      Now you can check our model that contains your function and Stored Procedure.
       
      Model Browser
       

      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.
       
      Creating Class
       
      Step 2
       
      Just write the following code:
      1. using System;  
      2. using System.Collections.Generic;  
      3. using System.Data.Entity.Core.Objects;  
      4. using System.Linq;  
      5.    
      6. namespace CollegeDetailsLibrary  
      7. {  
      8.     public class CollegeDataOperation  
      9.     {  
      10.         //This Method is used to access all data  
      11.         public List<CollegeDetail> GetCollegeRecords()  
      12.         {  
      13.             using (CollegeDataEntities context = new CollegeDataEntities())  
      14.             {  
      15.                 return context.CollegeDetails.ToList();  
      16.             }  
      17.         }  
      18.    
      19.         //This Method is used to insert the college records  
      20.         public bool InsertCollegeRecords(CollegeDetail detail)  
      21.         {  
      22.             using (CollegeDataEntities context = new CollegeDataEntities())  
      23.             {  
      24.                 ObjectParameter ReturnedValue = new ObjectParameter("ReturnValue", typeof(int));  
      25.                 context.InsertCollegeDetails  
      26.                     (  
      27.                     detail.CollegeName, detail.CollegeAddress,  
      28.                     detail.CollegePhone, detail.CollegeEmailID,  
      29.                     detail.ContactPerson, detail.ContactPersonPhoneNo,  
      30.                     detail.ContactPersonEmailID, detail.ContactPersonDesignation,  
      31.                     detail.FirstVisitDate, detail.FirstVisitMode,  
      32.                     detail.State, detail.City,  
      33.                     detail.FDP, detail.Workshops,  
      34.                     detail.CreatedBy, ReturnedValue  
      35.                     );  
      36.                 if (Convert.ToInt32(ReturnedValue.Value) >= 1)  
      37.                 {  
      38.                     return true;  
      39.                 }  
      40.                 else  
      41.                 {  
      42.                     return false;  
      43.                 }  
      44.             }  
      45.         }  
      46.    
      47.         //This method is used to update the coollege details based on the college id  
      48.         //Last Created in 20th June  
      49.         public bool UpdateCollegeRecords(int? id, CollegeDetail collegeDetail)  
      50.         {  
      51.             using (CollegeDataEntities context = new CollegeDataEntities())  
      52.             {  
      53.                 int ReturnedValue = context.UpdateCollege_Details  
      54.                     (  
      55.                     id, collegeDetail.CollegeName, collegeDetail.ContactPerson,   
      56.                     collegeDetail.ContactPersonPhoneNo, collegeDetail.ContactPersonEmailID,   
      57.                     collegeDetail.FDP, collegeDetail.Workshops,   
      58.                     collegeDetail.CreatedBy, collegeDetail.EditedBy  
      59.                     );  
      60.                 if (ReturnedValue >= 1)  
      61.                 {  
      62.                     return true;  
      63.                 }  
      64.                 else  
      65.                 {  
      66.                     return false;  
      67.                 }  
      68.             }  
      69.         }  
      70.    
      71.         //This method is used to delete the college details based on the college id  
      72.         //Last Created in 20th June  
      73.         public bool DeleteCollegeRecords(int? id)  
      74.         {  
      75.             using (CollegeDataEntities context = new CollegeDataEntities())  
      76.             {  
      77.                 int ReturnedValue = context.DeleteCollege_Details(id);  
      78.                 if (ReturnedValue >= 1)  
      79.                 {  
      80.                     return true;  
      81.                 }  
      82.                 else  
      83.                 {  
      84.                     return false;  
      85.                 }  
      86.             }  
      87.         }     
      88.     }  
      89. }  
      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!!


      Similar Articles