Calling an stored procedure using ADO.NET Entity Model (Entity Framework LINQ to SQL)

Category: ASP.NETENTITY FRAMEWORKLINQ Comments: No comments


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

linqtost

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.

1

 

 

 

 

 

 

2

 

 

 

 

 

 

 

3

 

 

 

 

 

 

 

 

 

4

 

 

 

 

 

 

 

 

 

 

5

 

 

 

 

 

 

 

 

6

 

 

 

 

 

 

 

 

7

 

 

 

 

 

 

 

8

 

 

 

 

 

 

 

 

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=&quot;data source=.\SQLExpress;initial catalog=EntityFramework;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;” 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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

HTML tags are not allowed.