Execute Stored Procedure In Entity framework

In this article we will learn how to use execute stored procedure in entity framework.

In this article we are going to learn step by step how to execute stored procedure which you have already created in SQL Server in code first approach in entity framework.

STEP 1

Execute following query in SQL Server

  1. CREATE DATABASE ENTITYDB  
  2. GO  
  3. USE ENTITYDB  
  4. GO  
  5. CREATE TABLE tblDepartments  
  6. (  
  7. DepartmentID INT PRIMARY KEY IDENTITY(1,1),  
  8. DepartmentName VARCHAR(20)   
  9. )  
  10. INSERT INTO tblDepartments VALUES  
  11. ('IT'),('HR'),('ACCOUNT')  
  12. GO  
  13.   
  14. CREATE TABLE tblEmployees  
  15. (  
  16. EmployeeID INT PRIMARY KEY IDENTITY(1,1),  
  17. Name VARCHAR(50),  
  18. Age INT,  
  19. Gender VARCHAR(10),  
  20. DepartmentID INT  
  21.   
  22. )  
  23.  GO  
  24. INSERT INTO tblEmployees VALUES  
  25.  ('MARK',21,'MALE',1),  
  26. ('JOHN',22,'MALE',1),  
  27. ('MACK',23,'MALE',2),  
  28. ('RIYA',20,'FEMALE',2),  
  29. ('ABRAM',21,'MALE',3)  
  30. GO  
CREATE PROCEDURE FOR GET ALL EMPLOYEE WITH DEPARTMENT
  1. CREATE PROCEDURE SP_GETEMPLOYEE  
  2. AS  
  3. BEGIN  
  4. SELECT E.EMPLOYEEID,E.NAME,E.GENDER,E.AGE,D.DEPARTMENTNAME FROM TBLEMPLOYEES E JOIN TBLDEPARTMENTS D  
  5. ON E.DEPARTMENTID=D.DEPARTMENTID  
  6. END  
CREATE PROCEDURE FOR GET EMPLOYEE BY EMPLOYEEID
  1. CREATE PROCEDURE SP_GETEMPLOYEEBYEMPLOYEEID 2  
  2. (  
  3. @EMPID INT  
  4.   
  5. )  
  6. AS  
  7. BEGIN  
  8. SELECT E.NAME,E.AGE,E.GENDER,D.DEPARTMENTNAME FROM TBLEMPLOYEES E JOIN TBLDEPARTMENTS D  
  9. ON E.DEPARTMENTID=D.DEPARTMENTID  
  10. WHERE E.EMPLOYEEID=@EMPID  
  11. END  
STEP 2

Open visual studio and add new empty website, then add reference of System.Data.Entity

For adding this, here's the image.

Right click in references folder and open Nuget and download Entityframework dll and install it. After installation it gets automatically added in your references folder.



STEP 3

Now add the following two classes:
  1. EmployeeContext.cs
  2. Employee.cs

EmployeeContext.cs

  1. using System.Collections.Generic;  
  2. using System.Linq;  
  3. using System.Data.Entity;  
  4. using System.Data.SqlClient;  
  5. namespace SqlProcAccessInCodeFirstApproach  
  6. {  
  7.     public class EmployeeContext : DbContext  
  8.     {  
  9.         public EmployeeContext()  
  10.       : base("DBCS")// DBCS name of connection string it available in Web.Config                                         
  11.     {  
  12.   
  13.         }  
  14.         public List<Employee> GetAllEmployee()  
  15.         {  
  16.             List<Employee> Employees = new List<Employee>();  
  17.             Employee emp;  
  18.             using (EmployeeContext cx = new EmployeeContext())  
  19.             {  
  20.                  
  21.                 var result = cx.Database.SqlQuery<Employee>("SP_GETEMPLOYEE""");//Here          you also write sql query.  
  22.                 foreach (Employee e in result)  
  23.                 {  
  24.                     emp = new Employee();  
  25.                     emp.EmployeeID = e.EmployeeID;  
  26.                     emp.Name = e.Name;  
  27.                     emp.Gender = e.Gender;  
  28.                     emp.Age = e.Age;  
  29.                     emp.DepartmentName = e.DepartmentName;  
  30.                     Employees.Add(emp);  
  31.                 }  
  32.             }  
  33.             return Employees;  
  34.         }  
  35.         public Employee GetEmployeeByID(int ID)  
  36.         {  
  37.             EmployeeContext cx = new EmployeeContext();  
  38.             SqlParameter param = new SqlParameter("@EMPID", ID);  
  39.             var result = cx.Database.SqlQuery<Employee>("SP_GETEMPLOYEEBYEMPLOYEEID @EMPID", param).SingleOrDefault();//Here you also write sql query.  
  40.             return result;  
  41.         }  
  42.     }  
  43. }  
Employee.cs
  1. namespace SqlProcAccessInCodeFirstApproach  
  2. {  
  3.     public class Employee  
  4.     {  
  5.         public int EmployeeID { getset; }  
  6.         public string Name { getset; }  
  7.         public string Gender { getset; }  
  8.         public int Age { getset; }  
  9.         public string DepartmentName { getset; }  
  10.           
  11.     }  
  12. }  
Note

Add connection string in web.config file
  1. <connectionStrings>  
  2. <add name="DBCS" connectionString="SERVER=piyush-pc;DATABASE=ENTITYDB;USER ID=sa;PASSWORD=pass.123" providerName="System.Data.SqlClient"/>  
  3. </connectionStrings>  
You have to write provider name in connection string.

STEP 4

Add a new web page and give it a name.

Now write the following code within form tag in your aspx page:

WebForm1.aspx
  1. <div>  
  2.         <asp:GridView ID="GridView1" runat="server" AutoGenerateSelectButton="True"                   CellPadding="4"  
  3.             ForeColor="#333333" GridLines="None" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">  
  4.             <AlternatingRowStyle BackColor="White" />  
  5.             <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />  
  6.             <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />  
  7.             <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />  
  8.             <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />  
  9.             <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />  
  10.             <SortedAscendingCellStyle BackColor="#FDF5AC" />  
  11.             <SortedAscendingHeaderStyle BackColor="#4D0000" />  
  12.             <SortedDescendingCellStyle BackColor="#FCF6C0" />  
  13.             <SortedDescendingHeaderStyle BackColor="#820000" />  
  14.         </asp:GridView>  
  15.         <br />  
  16.         <table border="1" id="tblShow" runat="server" visible="false">  
  17.             <tr>  
  18.                 <td colspan="2">  
  19.                     <b>  
  20.                         <asp:Label ID="lblName" Text="" runat="server" /></b>  
  21.                 </td>  
  22.             </tr>  
  23.             <tr>  
  24.                 <td>  
  25.                     Gender  
  26.                 </td>  
  27.                 <td>  
  28.                     <asp:Label ID="lblGender" Text="" runat="server" />  
  29.                 </td>  
  30.             </tr>  
  31.             <tr>  
  32.                 <td>  
  33.                     Age  
  34.                 </td>  
  35.                 <td>  
  36.                     <asp:Label ID="lblAge" Text="" runat="server" />  
  37.                 </td>  
  38.             </tr>  
  39.             <tr>  
  40.                 <td>  
  41.                     Department Name  
  42.                 </td>  
  43.                 <td>  
  44.                     <asp:Label ID="lblDName" Text="" runat="server" />  
  45.                 </td>  
  46.             </tr>  
  47.         </table>  
  48.     </div>  
And write the following code in your webform cs page.

WebForm1.aspx.cs
  1. using System;  
  2.   
  3. namespace SqlProcAccessInCodeFirstApproach  
  4. {  
  5.     public partial class WebForm1 : System.Web.UI.Page  
  6.     {  
  7.         EmployeeContext cx;  
  8.         protected void Page_Load(object sender, EventArgs e)  
  9.         {  
  10.             if (!IsPostBack)  
  11.             {  
  12.                 BindGrid();  
  13.             }  
  14.   
  15.         }  
  16.         void BindGrid()  
  17.         {  
  18.             using (cx = new EmployeeContext())  
  19.             {  
  20.                 GridView1.DataSource = cx.GetAllEmployee();  
  21.                 GridView1.DataBind();  
  22.             }  
  23.         }  
  24.   
  25.         protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)  
  26.         {  
  27.             int i = GridView1.SelectedIndex;  
  28.             int empId = Convert.ToInt32(GridView1.Rows[i].Cells[1].Text);  
  29.             using (cx = new EmployeeContext())  
  30.             {  
  31.                 Employee emp = cx.GetEmployeeByID(empId);  
  32.   
  33.                 lblName.Text = emp.Name;  
  34.                 lblGender.Text = emp.Gender;  
  35.                 lblAge.Text = emp.Age.ToString();  
  36.                 lblDName.Text = emp.DepartmentName;  
  37.                 tblShow.Visible = true;  
  38.             }  
  39.   
  40.         }  
  41.     }  
  42. }  
For knowing more about code first approach in entity framework refer my previous article on code first approach.