DotnetProgramming: Entity framework CRUD operations using Stored Procedure

In this post I would like to share some thing regarding Entity Framework, how we can implement CRUD operations using stored procedures in Entity Framework.

In this explain in two ways of implementing CRUD operations

By calling Stored Procedures using ExecuteStoreCommand and ExecuteStoreQuery, with out mapping to Model
By mapping Stored Procedures in to the Model.

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

Create an Empty web application from your VS2010

First let us create a sample table, as I am not having SQL Server installed in my machine, I am adding SQL Server Database as follows

Here you can name Database1.mdf as per your naming convention. Now in the server explorer you can see your database, we will add a table and some Stored Procedures here as follows

Add the required columns and save the table with a 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 follows on which we are going to perform CRUD operations.

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)

)

Begin

insert into Employee(EmpID,Emp_Name,Emp_Address)values(@ID,@EmpName,@EmpAddress)

END

Delete Stored Procedure

Create PROCEDURE dbo.deleteEmp

(

@ID int

)

Begin

delete from Employee where EmpID=@ID

End

Select

Create PROCEDURE dbo.SelectEmployee

Begin

select * from Employee

End

Update

Create PROCEDURE dbo.UpdateEmployee

(@ID int,

@EmpName varchar(50),

@EmpAddress varchar(50))

Begin

update Employee set Emp_Name=@EmpName,Emp_Address=@EmpAddress where EmpID=@ID

End

We are finished up with our database, now let us create a sample page and add Entity Model in our application.

Adding an Entity Model to your application

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

Select New Connection from the Choose your data

Here on Data source you will have some different sources which you can see by clicking on Change, as I have created by database in my application I will go with Microsoft SQL Server Database File (SqlClient), if any one using SQL Server you can change that to SQL Server from the options available.

As I am going with Microsoft SQL Server Database File (SqlClient) I will browse for my Database file and click on OK.

Here you can 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, as I create only one table and 4 stored procedures I will select them

Initial Window

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

Now we are finished up with creating database and adding it to an Entity Model. Now we will see how can perform CRUD operations with out mapping the stored procedures in to the model.

I also included some LINQ queries where ever 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 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 screen shots :

When you first run the application

You can see as there are no records in the table grid view is showing empty. Also you can see the Employee ID is read only, to avoid duplicated I make this one if you wan't you can remove that and do as per required.

Now we will see what happens after submitting data

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, as per here I am having only one Employee so I will do for that.

Before editing Employee Address