LINQ UPDATE Query and LINQ DELTE Query in .Net
In LINQ we can write update and delete queries where easily like select, insert queries. For example take LINQ to SQL example where we are getting table data from SQL Server database, modify the table data by using Update and Delete statements.
Before going to create sample asp.net web application to check update and delete statements, Open SQL Server management studio and create emp database and empInfo table by using following SQL script.
USE [emp]
GO
/****** Object: Table [dbo].[empInfo] Script Date: 02/19/2012 15:03:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[empInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Role] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[JoiningDate] [datetime] NOT NULL,
CONSTRAINT [PK_empInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Now open visual studio, create asp.net web site. Right click on Solution explorer and select Add New Item => select LINQ to SQL Classes and name it as EmpData.dbml. Open Server Explorer=> Right Click on Server Explorer =>Select Add Connection and mention data base server name and database name just before you created(that is database name is emp here) .
Expand emp database in Server explorer, drag and drop empInfo table on to EmpData.dbml.
Then add below code to Default.aspx page which Employee Input form and Employee grid which displays all employee information from database table empInfo.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>LINQ Update and Delete Queries</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div>
<asp:Label ID="lbl1" runat="server" Text="<b>Before Update and Delete statements</b>"></asp:Label><br />
<asp:GridView ID="gvEmp" runat="server"></asp:GridView><br />
<asp:Button ID="btnUpdate" runat="server" Text="Update"
onclick="btnUpdate_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete"
onclick="btnDelete_Click" />
</div>
</div>
</form>
</body>
</html>
Add below code to Default.aspx.cs file which updates and deletes the data from empInfo database table and displays all employees’ information from empInfo database table.
public partial class _Default : System.Web.UI.Page
{
EmpDataDataContext db = new EmpDataDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
BindEmpData();
}
void BindEmpData()
{
var empData = from emp in db.empInfos
select emp;
gvEmp.DataSource = empData;
gvEmp.DataBind();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
lbl1.Text = "<b>After Executing Update Query</b>";
//by using query syntax
//var empData = from emp in db.empInfos
// select emp;
//foreach (var emp in empData)
//{
// if (emp.Name.ToUpper().StartsWith("J"))
// {
// emp.Name = "Mr. John";
// }
//}
//db.SubmitChanges();
//by using method syntax
var empData = db.empInfos.Where(emp => emp.Name.ToUpper().StartsWith("J"));
foreach (var emp in empData)
{
if (emp.Name.ToUpper().StartsWith("J"))
{
emp.Name = "Mr. John";
}
}
db.SubmitChanges();
BindEmpData();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
lbl1.Text = "<b>After Executing Delete Query</b>";
//by using query syntax
//var empData = from emp in db.empInfos
// where emp.Name.ToUpper().StartsWith("S")
// select emp;
//db.empInfos.DeleteOnSubmit(empData.SingleOrDefault());
//db.SubmitChanges();
//by using method syntax
var empData = db.empInfos.Where(emp=>emp.Name.ToUpper().StartsWith("S")).SingleOrDefault();
db.empInfos.DeleteOnSubmit(empData);
db.SubmitChanges();
BindEmpData();
}
}
Here we writes the update and delete queries by using query syntax as well as method syntax.
Before executing the update and delete queries data from empInfo table display as shown below.
Now execute the LINQ Update query which updates empInfos table rows whose name start with “J”. Here we write the LINQ Update query by using query syntax and method syntax.
By using query syntax, LINQ Update Query:
//by using query syntax
var empData = from emp in db.empInfos
select emp;
foreach (var emp in empData)
{
if (emp.Name.ToUpper().StartsWith("J"))
{
emp.Name = "Mr. John";
}
}
db.SubmitChanges();
By using method syntax LINQ Update Query:
//by using method syntax
var empData = db.empInfos.Where(emp => emp.Name.ToUpper().StartsWith("J"));
foreach (var emp in empData)
{
if (emp.Name.ToUpper().StartsWith("J"))
{
emp.Name = "Mr. John";
}
}
db.SubmitChanges();
The output displays as shown below after executing the LINQ Update Query.
Now execute the LINQ Delete query which delete rows from empInfos table whose name start with “S”. Here we write the LINQ Delete query by using query syntax and method syntax.
By using query syntax, LINQ Delete Query:
//by using query syntax
var empData = from emp in db.empInfos
where emp.Name.ToUpper().StartsWith("S")
select emp;
db.empInfos.DeleteOnSubmit(empData.SingleOrDefault());
db.SubmitChanges();
By using method syntax LINQ Delete Query:
//by using method syntax
var empData = db.empInfos.Where(emp=>emp.Name.ToUpper().StartsWith("S")).SingleOrDefault();
db.empInfos.DeleteOnSubmit(empData);
db.SubmitChanges();
The output displays as shown below after executing the LINQ Update Query.
