DotnetProgramming: Continued Entity framework CRUD operations using Stored Procedure

In the last blog I posted how can directly call Stored Procedures with out mapping them to a Model. Check here Entity Framework

Download code

Now I will show how you can map the stored procedures and do.

First Step :

Right click Employee and select Stored Procedure Mapping

Step 2 :

You can now see Mapping Details for Employee as follows

Step 3 : You can see the different functions here for Insert, Update and Delete. Now we will map each and every function with the required Stored procedure as follows

I will show for Insert Function the same way you can do for Update and Delete functions

Steep 4 : Mapping Insert Functions with properties

Step 5 : Mapping each property to the Parameters

Step 6 : Assign each and every parameter the you can have your Insert Function as follows

Like this do for Update and Delete Functions too, and save it.

Now we will look how to create functions for the mapped Stored Procedures

Inside the Model window right click select -> Add-> Function Import

Step A :

After this you can see the list of available Stored Procedures that you have include for the Model.

Step B :

Select the required Stored Procedure Name and give a Function Import Name, I will show for InsertEmployee in the same way you can do for Update and Delete. For Select I will show how to do

Step C :

Repeat (from Step A - Step C) the same for Update and Delete too..

When coming for Select follow as per Insert with the following changes, as it will return data from table we have to make it as Complex.

Get Column Information

Create Complex Type

Save the Model and now we will check whether this functions are imported or not through code.

Create a new web page copy Paste the following design

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="crud1.aspx.cs" Inherits="CRUDentity.crud1" %>

<!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 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 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 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" ValidationGroup="g1" 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 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" Text="Delete" OnClick="btnDelete_Click" />
</td>
</tr>
</table>
</div>
</center>
</div>
</form>
</body>
</html>

Now you code is as follows

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 crud1 : 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)

{

entities.InsertData(Convert.ToInt16(txtEmpID.Text), txtEmployeeName.Text, txtAddress.Text);

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 select = entities.selectData().ToList();

grdEmployess.DataSource = select;

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--")

{

entities.deleteEmp(Convert.ToInt16(ddleditEmpID.SelectedValue.ToString()));

loadGrid();

checkMax();

bindDDL();

}

protected void btnUpdate_Click(object sender, EventArgs e)

{

Page.Validate("g1");

if (Page.IsValid)

{

if (ddleditEmpID.SelectedItem.Text != "--Select--")

{

entities.updateEmp(Convert.ToInt16(ddleditEmpID.SelectedValue.ToString()), txtedtEmployeeName.Text, txtedtEmpAddress.Text);

loadGrid();

txtedtEmployeeName.Text = string.Empty;

txtedtEmpAddress.Text = string.Empty;

}

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;

}

Any comments welcome..

Please don't hesitate to ask for queries.

Download code

Happy Coding

DotnetProgramming

Wednesday, December 26, 2012

Continued Entity framework CRUD operations using Stored Procedure

No comments:

Post a Comment

Popular Posts

Translate