Using Stored Procedure with Entity Framework Code First in Asp.Net

In this demo, we have to learn how to use stored procedure with entity framework code first approach.

To use Stored Procedure, We have create Blank ASP.Net Web Form website and add Entity framework reference using NuGet Package manager.

In this demo we have created local database and also add one table called Students.

We have also create two stored procedures PRC_Student and SelectStudent as shown below -

ALTER PROCEDURE dbo.PRC_Student
(
@Id int,
@Name nvarchar(50),
@Address nvarchar(MAX)
)
AS
BEGIN
INSERT INTO Students(Name,Address) VALUES(@Name,@Address)
END

ALTER PROCEDURE dbo.SelectStudent
AS
BEGIN
SELECT * FROM Students
END

Now we have to create POCO and DbContext class StudentModel.cs and add below code -

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
namespace EntityModelDemo
{
    public class Student
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
    }
    public class StudentContext : DbContext
    {
        public DbSet<Student> Students { get; set; }
    }
}

Now we have create another class called DalClass.cs and add below code -

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
namespace EntityModelDemo
{
    public class DALClass
    {
        StudentContext std = new StudentContext();
        public void AddStudent(Student _std)
        {
            std.Database.ExecuteSqlCommand("exec PRC_Student @Id, @Name, @Address",
                new SqlParameter("@Id", _std.Id),
                new SqlParameter("@Name", _std.Name),
                new SqlParameter("@Address", _std.Address)
                );
        }
        public IEnumerable<Student> GetStudent()
        {
            IEnumerable<Student> getstd = std.Database.SqlQuery<Student>("exec SelectStudent").AsEnumerable();
            return getstd;
        }
    }
}

In above code we have called stored procedure.

Now add Default.aspx page and add below code-

<form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" >
            <Columns>
                <asp:TemplateField HeaderText="Id" SortExpression="Id">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Id") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Name" SortExpression="Name">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Name") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Address" SortExpression="Address">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Address") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("Address") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
   
    </div>
    <div>
    <table>
     <tr>
     <td>
         <asp:Label ID="lbl_id" runat="server" Text="Id"></asp:Label>
     </td>
     <td>
         <asp:TextBox ID="txt_id" runat="server"></asp:TextBox>
     </td>
     </tr>
     <tr>
     <td>
         <asp:Label ID="lbl_name" runat="server" Text="Name"></asp:Label>
     </td>
     <td>
         <asp:TextBox ID="txt_name" runat="server"></asp:TextBox>
     </td>
     </tr>
     <tr>
     <td>
         <asp:Label ID="lbl_address" runat="server" Text="Degree"></asp:Label>
     </td>
     <td>
         <asp:TextBox ID="txt_adress" runat="server"></asp:TextBox>
     </td>
     </tr>
     <tr>
     <td>
       
     </td>
     <td>
         <asp:Button ID="btn_save" runat="server" Text="Save" onclick="btn_save_Click" />
     </td>
     </tr>
    </table>
    </div>
    <div>
        <asp:Label ID="lbl_success" runat="server"></asp:Label>
    </div>
    </form>

In above code, we have add one gridview and also we have add one insert form.

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
namespace EntityModelDemo
{
    public partial class Default : System.Web.UI.Page
    {
        DALClass ctx = new DALClass();
        protected void Page_Load(object sender, EventArgs e)
        {
            GridView1.DataSource = ctx.GetStudent().ToList();
            GridView1.DataBind();
        }
        protected void btn_save_Click(object sender, EventArgs e)
        {
            var ext = new Student()
            {
                Name = txt_name.Text,
                Address = txt_adress.Text
            };
                ctx.AddStudent(ext);
        }
    }
}

Web.config

<connectionStrings>
  <add name="StudentContext"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Users\KISAN\Downloads\EntityModelDemo\EntityModelDemo\EntityModelDemo\App_Data\Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

Now run the project and you will below screen.

Download Complete Source Code