Click here to Skip to main content
Click here to Skip to main content

Entity Framework CRUD Operations Using Stored Procedures

, 6 Jan 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
How to implement CRUD operations using Stored Procedures in Entity Framework.

Introduction

In this article I would like to share something regarding Entity Framework, how we can implement CRUD operations using Stored Procedures in Entity Framework.

In this explanation there are two ways of implementing CRUD operations:

  1. By calling Stored Procedures using ExecuteStoreCommand and ExecuteStoreQuery, without mapping to the Model.
  2. By mapping Stored Procedures to the Model.

OK, first we will see how we can map Stored Procedures to the Model to implement CRUD operations.

Create an empty web application from VS2010, see:

Image1.jpg

First let us create a sample table. Since I do not have SQL Server installed in my machine, I am adding a SQL Server database as in the following:

Image2.jpg

Image3.jpg

Here you can use the name Database1.mdf depending on your naming convention. Now in Server Explorer, you will see your database, we will add a table and some Stored Procedures as follows:

Image4.jpg

Add the required columns and save the table with the desired name. The most important aspect before you start working on Entity Framework is to have a Primary Key in your table.

Now my table looks as in the following, on which we are going to perform CRUD operations.

Image5.jpg

OK now let's create Stored Procedures for Insert, Update, Delete, and Select operations.

Insert Stored Procedure

CreatePROCEDURE dbo.InsertEmployee
(
  @ID int,
 @EmpName varchar(50),
 @EmpAddress varchar(50)
)
AS
Begin
insert into Employee(EmpID,Emp_Name,Emp_Address)values(@ID,@EmpName,@EmpAddress)
END

Delete Stored Procedure

Create PROCEDURE dbo.deleteEmp
(
 @ID int
)
As
Begin
delete from Employee where EmpID=@ID
End

Select

Create PROCEDURE dbo.SelectEmployee
As
Begin
select * from Employee
End

Update

Create PROCEDURE dbo.UpdateEmployee
(@ID int,
@EmpName varchar(50),
@EmpAddress varchar(50))
As
Begin
update Employee set Emp_Name=@EmpName,Emp_Address=@EmpAddress where EmpID=@ID
End

We are finished with our database. Now let us create a sample page and add an Entity Model to our application.

Adding an Entity Model to your application:

Image6.jpg

After adding a Model you will immediately have this Entity Data Model Wizard where you have to select Generate from the database and click on Next:

Image7.jpg

Select New Connection from Choose your data:

Image8.jpg

Image9.jpg

Here on the Data Source you will have various sources which you will see by clicking on Change, as I have created my database in my application I will use Microsoft SQL Server Database File (SqlClient), if anyone is using SQL Server you can change that to SQL Server from the options available.

Since I am using a Microsoft SQL Server Database File (SqlClient) I will browse for my Database file and click on "OK".

Image10.jpg

Image11.jpg

Here you will see my Database file and also the connection settings in Web.Config will be saved with the name EntitySampleEntities. Click Next where you will find all your tables and Stored Procedures that you have created. Select the required one. Since I created only one table and 4 Stored Procedures I will select them.

Initial Window

Image12.jpg

Image13.jpg

Click on "Finish" after you are finished, then you will see your model with the tables you added and if there are any relations it will also map them. As of now I created just one table that will be shown as follows:

Image14.jpg

Now we are finished with creating the database and adding it to an Entity Model. Now we will see how to perform CRUD operations without mapping the Stored Procedures to the model.

I also included some LINQ queries wherever needed, for example to auto-generate Employee ID and binding the drop-down list.

Create a web page and add the following design to that page:

<%@ Page Language="C#" AutoEventWireup="true" 
          CodeBehind="crud.aspx.cs" Inherits="CRUDentity.crud" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
            "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <center>
            <h3>
                Display data in gridview using Entity Framework with out Mapping Stored Procedure
                to Model
            </h3>
            <div style="width: 800px; margin: 0 auto; float: left;">
                <asp:GridView ID="grdEmployess" runat="server" BackColor="White" BorderColor="#999999"
                    DataKeyNames="EmpID" BorderStyle="None" BorderWidth="1px" CellPadding="3"GridLines="Vertical">
                    <AlternatingRowStyle BackColor="#DCDCDC" />
                    <EmptyDataTemplate>
                        No record to show
                    </EmptyDataTemplate>
                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                    <SortedAscendingHeaderStyle BackColor="#0000A9" />
                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                    <SortedDescendingHeaderStyle BackColor="#000065" />
                </asp:GridView>
            </div>
            <br />
            <div style="width: 800px; margin: 0 auto; float: left;">
                <h3>
                    Insert Data to table using Entity Framework with out Mapping Stored Procedures to
                    Model</h3>
                <table>
                    <tr>
                        <td>
                            Employee ID :
                        </td>
                        <td>
                            <asp:TextBox ID="txtEmpID" ReadOnly="true" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Name :
                        </td>
                        <td>
                            <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rqrdEmployeeName" runat="server" ErrorMessage="*"
                                ControlToValidate="txtEmployeeName" ToolTip="Employee name required"ValidationGroup="g"
                                Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Address :
                        </td>
                        <td>
                            <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rqrdAddress" runat="server" 
                                ErrorMessage="*"ControlToValidate="txtAddress"
                                ToolTip="Address required" ValidationGroup="g" 
                                Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>
                        </td>
                    </tr>
                    <tr>
                        <td>
                        </td>
                    </tr>
                    <tr align="center">
                        <td colspan="3">
                            <asp:Button ID="btnInsert" runat="server" Text="Insert" 
                                 ValidationGroup="g"OnClick="btnInsert_Click" />
                        </td>
                    </tr>
                </table>
            </div>
            <br />
            <div style="width: 800px; margin: 0 auto; float: left;">
                <h3>
                    Edit and Update data using storedprocedure With out mapping it to Model</h3>
                <table>
                    <tr>
                        <td>
                            Select Employee ID :
                        </td>
                        <td>
                            <asp:DropDownList ID="ddleditEmpID" runat="server" 
                               AutoPostBack="true" 
                               OnSelectedIndexChanged="ddleditEmpID_SelectedIndexChanged">
                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Name :
                        </td>
                        <td>
                            <asp:TextBox ID="txtedtEmployeeName" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rqrdedtEmpName" runat="server" 
                                ErrorMessage="*"ControlToValidate="txtedtEmployeeName"
                                ToolTip="Employee name required" ValidationGroup="g1" 
                                Display="Dynamic"ForeColor="Red"></asp:RequiredFieldValidator>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Address :
                        </td>
                        <td>
                            <asp:TextBox ID="txtedtEmpAddress" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rqrdedtEmpAddress" runat="server" ErrorMessage="*"
                                ControlToValidate="txtedtEmpAddress" ToolTip="Address required" ValidationGroup="g1"
                                Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>
                        </td>
                    </tr>
                    <tr>
                        <td>
                        </td>
                    </tr>
                    <tr align="center">
                        <td colspan="4">
                            <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
                        </td>
                    </tr>
                </table>
            </div>
            <br />
            <div style="width: 800px; margin: 0 auto; float: left;">
                <h3>
                    Delete data using storedprocedure With out mapping it to Model</h3>
                <table>
                    <tr>
                        <td>
                            Select Employee ID to Delete :
                        </td>
                        <td>
                            <asp:DropDownList ID="ddlEmpID" runat="server">
                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td>
                        </td>
                    </tr>
                    <tr align="center">
                        <td colspan="2">
                            <asp:Button ID="btnDelete" runat="server" 
                                      ValidationGroup="g1" Text="Delete"OnClick="btnDelete_Click" />
                        </td>
                    </tr>
                </table>
            </div>
        </center>
    </div>
    </form>
</body>
</html>
Yourpage.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;

namespace CRUDentity
{
    public partial class crud : System.Web.UI.Page
    {
        EntitySampleEntities entities = new EntitySampleEntities();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                checkMax();
                loadGrid();
                bindDDL();
            }
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            Page.Validate("g");

            if (Page.IsValid)
            {
                var ietsParameterID = new SqlParameter("@ID", System.Data.SqlDbType.Int);
                ietsParameterID.Value = Convert.ToInt16(txtEmpID.Text);
                var ietsParameterEmpName = new SqlParameter("@EmpName", txtEmployeeName.Text);
                var ietsParameterEmpAddress = new SqlParameter("@EmpAddress", txtAddress.Text);

                entities.ExecuteStoreCommand("InsertEmployee @ID,@EmpName,@EmpAddress", 
                          ietsParameterID, ietsParameterEmpName, ietsParameterEmpAddress);
                loadGrid();
                checkMax();
                bindDDL();
                txtAddress.Text = string.Empty;
                txtEmployeeName.Text = string.Empty;
            }
        }

        public void checkMax()
        {
            int? maxEmpID = entities.Employees.Max(q => (int?)q.EmpID);

            if (maxEmpID != null)
            {
                maxEmpID = maxEmpID + 1;
                txtEmpID.Text = maxEmpID.ToString();
            }
            else
            {
                maxEmpID = 1000;
                txtEmpID.Text = maxEmpID.ToString();
            }
        }

        public void loadGrid()
        {
            var selectData = entities.ExecuteStoreQuery<Employee>("SelectEmployee").ToList();
            grdEmployess.DataSource = selectData;
            grdEmployess.DataBind();
        }

        public void bindDDL()
        {
            var display = from e in entities.Employees select new { e.EmpID };

            ddlEmpID.DataSource = display.ToList();
            ddlEmpID.DataTextField = "EmpID";
            ddlEmpID.DataValueField = "EmpID";
            ddlEmpID.DataBind();
            ddlEmpID.Items.Insert(0, "--Select--");

            ddleditEmpID.DataSource = display.ToList();
            ddleditEmpID.DataTextField = "EmpID";
            ddleditEmpID.DataValueField = "EmpID";
            ddleditEmpID.DataBind();
            ddleditEmpID.Items.Insert(0, "--Select--");
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            if (ddlEmpID.SelectedItem.Text != "--Select--")
            {
                var ietsParameterID = new SqlParameter("@ID", ddlEmpID.SelectedItem.Text);
                entities.ExecuteStoreCommand("deleteEmp @ID", ietsParameterID);
                loadGrid();
                checkMax();
                bindDDL();
            }
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            Page.Validate("g1");
            if (Page.IsValid)
            {
                if (ddleditEmpID.SelectedItem.Text != "--Select--")
                {
                    var ietsParameterID = new SqlParameter("@ID", System.Data.SqlDbType.Int);
                    ietsParameterID.Value = Convert.ToInt16(ddleditEmpID.SelectedItem.Text);
                    var ietsParameterEmpName = new SqlParameter("@EmpName", txtedtEmployeeName.Text);
                    var ietsParameterEmpAddress = new SqlParameter("@EmpAddress", txtedtEmpAddress.Text);

                    entities.ExecuteStoreCommand("UpdateEmployee @ID,@EmpName,@EmpAddress", 
                               ietsParameterID, ietsParameterEmpName, ietsParameterEmpAddress);
                    loadGrid();
                }
            }
        }

        protected void ddleditEmpID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddleditEmpID.SelectedItem.Text != "--Select--")
            {
                int id = Convert.ToInt16(ddleditEmpID.SelectedValue.ToString());
                var display = from e1 in entities.Employees
                              where e1.EmpID.Equals(id)
                              select new { e1.Emp_Name, e1.Emp_Address };

                foreach (var v in display)
                {
                    txtedtEmployeeName.Text = v.Emp_Name;
                    txtedtEmpAddress.Text = v.Emp_Address;
                }
            }
        }
    }
}
Sample screenshots

When you first run the application:

Image15.jpg

Since there are no records in the table you will see the grid view is empty. Also you will see the Employee ID is read only, to avoid duplicates I make this one if you want you can remove that and do whatever you need to.

Now we will see what happens after submitting data:

Image16.jpg

Now we will edit the record see here I will change the Employee Address initially it is Hyderabad I will change it to some other. To do that select the Employee ID that you need to edit and update. Since here I have only one Employee I will do for that.

Before editing Employee Address

Image17.jpg

Image18.jpg

Let's do the deleting; for this I will add another employee to the table as shown and then will delete it.

Before delete

Image19.jpg

After Delete

Image20.jpg

That's it, this is how we can do basic CRUD operations using Entity Framework without mapping Stored Procedures to the Model.

Wait for the next one on how we can implement CRUD operations using Entity Framework by mapping Stored Procedures to the Model.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

demouser743
Software Developer
India India
I am Dorababu, working as a Software engineer. Web development in Asp.Net with C#, WinForms and MS sql server are the experience tools that I have had for the past 3 years. Yet to work on WCF, WPF, Silverlight and other latest ones.
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionConfusion on above code PinmemberMember 384654124-Feb-13 20:29 
GeneralMy vote of 4 Pinmembermasewise8-Jan-13 7:53 
QuestionYou can create stored procedures automatically PinmemberSteve Wellens7-Jan-13 8:57 
I blogged about it here:
 
Automatically Generate Stored Procedures with Visual Studio[^]
 
Original post here:
 
http://weblogs.asp.net/stevewellens/archive/2009/12/11/automatically-generate-stored-procedures-with-visual-studio.aspx[^]
Steve Wellens

GeneralMy vote of 4 Pinmemberprem parihar6-Jan-13 6:28 
GeneralRe: My vote of 4 PinmemberDorababu7436-Jan-13 20:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411019.1 | Last Updated 6 Jan 2013
Article Copyright 2013 by demouser743
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid