In this article I would like to share something regarding Entity Framework, how we can implement operations using Stored Procedures in Entity Framework.
Table Script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[Uid] [int] IDENTITY(1,1) NOT NULL,
[Fname] [nvarchar](50) NULL,
[Lname] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserDeatils](
[UdetaiId] [int] IDENTITY(1,1) NOT NULL,
[Salary] [float] NULL,
[CreatedDate] [datetime] NULL,
[Uid] [int] NULL,
CONSTRAINT [PK_UserDeatils] PRIMARY KEY CLUSTERED
(
[UdetaiId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserDeatils] WITH CHECK ADD CONSTRAINT [FK_UserDeatils_Users] FOREIGN KEY([Uid])
REFERENCES [dbo].[Users] ([Uid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[UserDeatils] CHECK CONSTRAINT [FK_UserDeatils_Users]
GO
Generating the Model
While there a lot of pictures in the below steps, they are pretty straightforward and self-explanatory. What’s really powerful here is the options you’d have if you were after something more than a bare bones way of calling a stored procedure.
Designer Page :
<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”Linq2StoredProc.aspx.cs”
Inherits=”Linq2StoredProcedure.Linq2StoredProc” %>
<!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”>
<table border=”0″ cellpadding=”0″ cellspacing=”0″>
<tr>
<td>
First Name:
</td>
<td>
<asp:TextBox ID=”txtFname” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Last Name:
</td>
<td>
<asp:TextBox ID=”txtLaname” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Email:
</td>
<td>
<asp:TextBox ID=”txtEmail” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Gender:
</td>
<td>
<asp:RadioButtonList ID=”rdGender” runat=”server”>
<asp:ListItem Value=”1″ id=”M” runat=”server”>Male</asp:ListItem>
<asp:ListItem Value=”2″ id=”F” runat=”server”>Female</asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td>
Address:
</td>
<td>
<asp:TextBox ID=”txtAddress” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Salary:
</td>
<td>
<asp:TextBox ID=”txtSalary” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Button ID=”btnSave” runat=”server” Text=”Save” OnClick=”btnSave_Click” />
<%– <asp:Button ID=”btnUpdate” runat=”server” Text=”Update”
onclick=”btnUpdate_Click” />–%>
</td>
</tr>
</table>
<asp:GridView runat=”server” ID=”GridData” AutoGenerateColumns=”false” DataKeyNames=”Uid”>
<Columns>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text='<%# Eval(“Name”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Email”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text='<%# Eval(“Email”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text='<%# Eval(“Gender”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text='<%# Eval(“Address”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text='<%# Eval(“Salary”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Edit”>
<ItemTemplate>
<asp:LinkButton ID=”lnkEdit” runat=”server” CommandArgument='<%# Eval(“Uid”) %>’ CommandName=”Edit” Text=”Edit”></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Delete”>
<ItemTemplate>
<asp:LinkButton ID=”lnkDelete” runat=”server” CommandArgument='<%# Eval(“Uid”) %>’ CommandName=”Edit” Text=”Delete”></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>
Web Config Setting
<configuration>
<system.web>
<compilation debug=”true” targetFramework=”4.0″ />
</system.web>
<connectionStrings>
<add name=”FrameEntities” connectionString=”metadata=res://*/EntityFrame.csdl|res://*/EntityFrame.ssdl|res://*/EntityFrame.msl;provider=System.Data.SqlClient;provider connection string="data source=.\SQLExpress;initial catalog=EntityFramework;integrated security=True;multipleactiveresultsets=True;App=EntityFramework"” providerName=”System.Data.EntityClient” />
</connectionStrings>
</configuration>
Code Page:
using System.Data.Entity;
EntityFrameworkEntities dbContext = new EntityFrameworkEntities();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetResult();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
dbContext.InsertData(txtFname.Text, txtLaname.Text, txtEmail.Text, rdGender.SelectedItem.Text, txtAddress.Text, Convert.ToDouble(txtSalary.Text));
dbContext.SaveChanges();
GetResult();
ClearField();
}
void ClearField()
{
txtFname.Text = string.Empty;
txtLaname.Text = string.Empty;
txtEmail.Text = string.Empty;
txtAddress.Text = string.Empty;
txtSalary.Text = string.Empty;
rdGender.SelectedIndex = -1;
}
private void GetResult()
{
GridData.DataSource = dbContext.GetAllDetails();
GridData.DataBind();
}
Download Code: Linq2StoredProcedure