CRUD Operations using Linq and Entity Framework in Asp.net and Cascade Delete using Linq Query
In this series, I am going to tell you how to perform CRUD operations on a database using entity framework. I use database-first approach for this tutorial which supposes that you already have existing database and you want to automatically generate Entity Model based on this database.
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
Designer Page:
<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”DisplayData.aspx.cs” Inherits=”EntityFramework.DisplayData” %>
<!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 ID=”GridView1″ runat=”server” AutoGenerateColumns=”false”
CellPadding=”4″ ForeColor=”#333333″ GridLines=”None” DataKeyNames=”Uid”
onrowcommand=”GridView1_RowCommand” onrowdeleting=”GridView1_RowDeleting”
onrowediting=”GridView1_RowEditing”>
<AlternatingRowStyle BackColor=”White” />
<Columns>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<%# Eval(“Fname”).ToString() +’ ‘+ Eval(“Lname”).ToString() %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Email”>
<ItemTemplate>
<asp:Label ID=”lblEmail” runat=”server” Text='<%# Eval(“Email”) %> ‘></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Gender”>
<ItemTemplate>
<asp:Label ID=”lblGender” runat=”server” Text='<%# Eval(“Gender”) %> ‘></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Address”>
<ItemTemplate>
<asp:Label ID=”lblAdd” runat=”server” Text='<%# Eval(“Address”) %> ‘></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Salary”>
<ItemTemplate>
<asp:Label ID=”lblSAl” runat=”server” Text='<%# Eval(“Salary”) %> ‘></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID=”lnkEdit” runat=”server” Text=”Edit” CommandName=”Edit” CommandArgument='<%# Eval(“Uid”) %>’></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID=”lnkDelete” runat=”server” Text=”Delete” CommandName=”Delete” CommandArgument='<%# Eval(“Uid”) %>’></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor=”#7C6F57″ />
<FooterStyle BackColor=”#1C5E55″ Font-Bold=”True” ForeColor=”White” />
<HeaderStyle BackColor=”#1C5E55″ Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#666666″ ForeColor=”White” HorizontalAlign=”Center” />
<RowStyle BackColor=”#E3EAEB” />
<SelectedRowStyle BackColor=”#C5BBAF” Font-Bold=”True” ForeColor=”#333333″ />
<SortedAscendingCellStyle BackColor=”#F8FAFA” />
<SortedAscendingHeaderStyle BackColor=”#246B61″ />
<SortedDescendingCellStyle BackColor=”#D4DFE1″ />
<SortedDescendingHeaderStyle BackColor=”#15524A” />
</asp:GridView>
</form>
</body>
</html>
ADO.NET Data Entity Model or Edmx
Set Property For Cascade Delete
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;
namespace EntityFramework
{
public partial class DisplayData : System.Web.UI.Page
{
FrameEntities dbContext = new FrameEntities();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
ViewState[“Uid”] = null;
btnUpdate.Visible = false;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
User objUser = new User() { Fname = txtFname.Text.Trim(), Lname = txtLaname.Text.Trim(), Email = txtEmail.Text.Trim(), Gender = rdGender.SelectedItem.Text, Address = txtAddress.Text.Trim() };
UserDeatil objDetails = new UserDeatil() { Salary = Convert.ToDouble(txtSalary.Text.Trim()),CreatedDate=DateTime.Now };
objUser.UserDeatils.Add(objDetails);
dbContext.Users.AddObject(objUser);
dbContext.SaveChanges();
ClearField();
BindGrid();
}
//This code is used for fetch data from database
private void BindGrid()
{
var getRecord = from t in dbContext.Users
join u in dbContext.UserDeatils on t.Uid equals u.Uid
select new {t.Uid, t.Fname, t.Lname, t.Email, t.Gender, t.Address, u.Salary };
GridView1.DataSource = getRecord.ToList();
GridView1.DataBind();
}
private 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;
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
}
// This code is used for Edit or Delete functionality using Row Command
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == “Edit”)
{
GridViewRow row = (GridViewRow)((Control)e.CommandSource).NamingContainer;
int userid = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);
var getSelectedRecord = (from t in dbContext.Users
join u in dbContext.UserDeatils on t.Uid equals u.Uid
where t.Uid == userid
select new {t.Fname, t.Lname, t.Email, t.Gender, t.Address, u.Salary }).ToList();
txtFname.Text = getSelectedRecord[0].Fname.ToString();
txtLaname.Text = getSelectedRecord[0].Lname.ToString();
txtEmail.Text = getSelectedRecord[0].Email.ToString();
if (getSelectedRecord[0].Gender.ToString() == “Male”)
{
rdGender.Items[0].Selected = true;
}
else
{
rdGender.Items[1].Selected = true;
}
txtAddress.Text = getSelectedRecord[0].Address.ToString();
txtSalary.Text = getSelectedRecord[0].Salary.ToString();
ViewState[“Uid”] = userid;
btnSave.Visible = false;
btnUpdate.Visible = true;
}
else if (e.CommandName == “Delete”)
{
//Perforrming cascade delete using refrences
GridViewRow row = (GridViewRow)((Control)e.CommandSource).NamingContainer;
int userid = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);
User objUser = dbContext.Users.SingleOrDefault(p => p.Uid == userid);
dbContext.Users.DeleteObject(objUser);
dbContext.SaveChanges();
BindGrid();
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
}
//This code is used for Update functionality
protected void btnUpdate_Click(object sender, EventArgs e)
{
int uid = Convert.ToInt32(ViewState[“Uid”]);
User objUser = dbContext.Users.First(p => p.Uid==uid);
objUser.Fname = txtFname.Text.Trim();
objUser.Lname = txtLaname.Text.Trim();
objUser.Email = txtEmail.Text.Trim();
objUser.Gender = rdGender.SelectedItem.Text;
objUser.Address = txtAddress.Text.Trim();
UserDeatil objDetails = dbContext.UserDeatils.First(p => p.Uid == uid);
objDetails.Salary = Convert.ToDouble(txtSalary.Text.Trim());
dbContext.SaveChanges();
ClearField();
BindGrid();
}
}
}
