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
  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
  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
  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. 　 {
  13. }
  14. public List<Employee> GetAllEmployee()
  15. {
  16. List<Employee> Employees = new List<Employee>();
  17. Employee emp;
  18. using (EmployeeContext cx = new EmployeeContext())
  19. {
  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 { get; set; }
  6. public string Name { get; set; }
  7. public string Gender { get; set; }
  8. public int Age { get; set; }
  9. public string DepartmentName { get; set; }
  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;
  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. }
  15. }
  16. void BindGrid()
  17. {
  18. using (cx = new EmployeeContext())
  19. {
  20. GridView1.DataSource = cx.GetAllEmployee();
  21. GridView1.DataBind();
  22. }
  23. }
  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);
  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. }
  40. }
  41. }
  42. }
For knowing more about code first approach in entity framework refer my previous article on code first approach.