The LINQ to SQL can maintain the changes whatever we do to the objects, like adding or removing or updating items in the collection object until we submit the changes by using SubmitChanges() method. Once we submit our changes, the LINQ to SQL will translate our LINQ actions to SQL and submit our changes to the database.
Syntax of LINQ to SQL Insert / Update / Delete
Following are the syntax of insert, update, or delete operations used in LINQ to SQL.
LINQ to SQL Insert Operation
Following is the syntax of using LINQ to Insert operation query in c#, vb.net to insert data in the database.
C# Code
EmployeeDBDataContext db = new EmployeeDBDataContext();
EmployeeDetail emp = new EmployeeDetail();
emp.EmpName = txtname.Text;
emp.Location = txtlocation.Text;
db.EmployeeDetails.InsertOnSubmit(emp);
db.SubmitChanges();
VB.NET
Private db As New EmployeeDBDataContext()
Dim emp As New EmployeeDetail()
emp.EmpName = txtname.Text
emp.Location = txtlocation.Text
db.EmployeeDetails.InsertOnSubmit(emp)
db.SubmitChanges()
LINQ to SQL Update Operation
Following is the syntax of using LINQ to SQL update operation query in c#, vb.net to update data in the database.
C# Code
EmployeeDBDataContext db = new EmployeeDBDataContext();
EmployeeDetail emp = new EmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.EmpId == empid);
emp.EmpName = txtname.Text;
emp.Location = txtlocation.Text;
emp.Gender = txtgender.Text;
db.SubmitChanges();
VB.NET Code
Private db As New EmployeeDBDataContext()
Dim emp As New EmployeeDetail()
emp = db.EmployeeDetails.[Single](Function(x) x.EmpId = empid)
emp.EmpName = txtname.Text
emp.Location = txtlocation.Text
emp.Gender = txtgender.Text
db.SubmitChanges()
LINQ to SQL Delete Operation
Following is the syntax of using LINQ to SQL delete operation in c#, vb.net to delete data in the database.
C# Code
EmployeeDBDataContext db = new EmployeeDBDataContext();
EmployeeDetail emp = new EmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.EmpId == empid);
db.EmployeeDetails.DeleteOnSubmit(emp);
db.SubmitChanges();
VB.NET Code
Private db As New EmployeeDBDataContext()
Dim emp As New EmployeeDetail()
emp = db.EmployeeDetails.[Single](Function(x) x.EmpId = empid)
db.EmployeeDetails.DeleteOnSubmit(emp)
db.SubmitChanges()
LINQ to SQL Insert Update Delete Example
Before we start implementing LINQ to SQL, Insert update and delete operations first, we need to create a database with required tables and map those tables to LINQ to SQL file (.dbml). If you don't know the process check this link create and map database tables to LINQ to SQL file (.dbml).
Once we create and map required tables to the .dbml file now, we will show data in our application for that Right-click on the application à select Add à New Item à Select Web Form à Give name as Default.aspx and click OK button.
Now open the Default.aspx page and write the code as shown below.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>LINQ to SQL Insert, Update, Delete Operations in ASP.Net Gridview</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: #df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="Id,Name" OnPageIndexChanging="gvDetails_PageIndexChanging" OnRowCancelingEdit="gvDetails_RowCancelingEdit" OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating" OnRowDeleting="gvDetails_RowDeleting" OnRowCommand ="gvDetails_RowCommand" >
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%# Eval("Name")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfName" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Location">
<ItemTemplate>
<asp:Label ID="lblLocation" runat="server" Text='<%# Eval("Location")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtLocation" runat="server" Text='<%# Eval("Location")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfLocation" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<ItemTemplate>
<asp:Label ID="lblGender" runat="server" Text='<%# Eval("Gender")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtGender" runat="server" Text='<%# Eval("Gender")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtfGender" runat="server" />
<asp:Button ID="btnAdd" CommandName="AddNew" runat="server" Text="Add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
Now open the code behind file and write the code as shown below.
C# Code
using System;
using System.Drawing;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace LINQ2SQL
{
public partial class Default : System.Web.UI.Page
{
EmployeeDBDataContext db = new EmployeeDBDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGridview();
}
}
// LINQ to SQL Select Operation
protected void BindGridview()
{
var result = from ed in db.EmployeeDetails
select new
{
Id = ed.EmpId,
Name = ed.EmpName,
Location = ed.Location,
Gender = ed.Gender
};
gvDetails.DataSource = result;
gvDetails.DataBind();
}
// LINQ to SQL Insert Operation
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtname = (TextBox)gvDetails.FooterRow.FindControl("txtfName");
TextBox txtlocation = (TextBox)gvDetails.FooterRow.FindControl("txtfLocation");
TextBox txtgender = (TextBox)gvDetails.FooterRow.FindControl("txtfGender");
EmployeeDetail emp = newEmployeeDetail();
emp.EmpName = txtname.Text;
emp.Location = txtlocation.Text;
emp.Gender = txtgender.Text;
db.EmployeeDetails.InsertOnSubmit(emp);
db.SubmitChanges();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtname.Text + " details inserted successfully";
BindGridview();
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindGridview();
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
// LINQ to SQL Update Operation
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int empid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["Id"].ToString());
TextBox txtname = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtName");
TextBox txtlocation = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtLocation");
TextBox txtgender = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtGender");
EmployeeDetail emp = newEmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.EmpId == empid);
emp.EmpName = txtname.Text;
emp.Location = txtlocation.Text;
emp.Gender = txtgender.Text;
db.SubmitChanges();
gvDetails.EditIndex = -1;
BindGridview();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtname.Text + " details updated successfully";
}
// LINQ to SQL Delete Operation
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int empid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["Id"].ToString());
string empname = gvDetails.DataKeys[e.RowIndex].Values["Name"].ToString();
EmployeeDetail emp = newEmployeeDetail();
emp = db.EmployeeDetails.Single(x => x.EmpId == empid);
db.EmployeeDetails.DeleteOnSubmit(emp);
db.SubmitChanges();
BindGridview();
lblresult.ForeColor = Color.Green;
lblresult.Text = empname + " details deleted successfully";
}
}
}
VB.NET
Imports System.Drawing
Public Class WebForm1
Inherits System.Web.UI.Page
Private db As New EmployeeDBDataContext()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindGridview()
End If
End Sub
Protected Sub BindGridview()
Dim result = From ed In db.EmployeeDetails Select New With {.Id = ed.EmpId, .Name = ed.EmpName, .Location = ed.Location, .Gender = ed.Gender}
gvDetails.DataSource = result
gvDetails.DataBind()
End Sub
Protected Sub gvDetails_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
If e.CommandName.Equals("AddNew") Then
Dim txtname As TextBox = DirectCast(gvDetails.FooterRow.FindControl("txtfName"), TextBox)
Dim txtlocation As TextBox = DirectCast(gvDetails.FooterRow.FindControl("txtfLocation"), TextBox)
Dim txtgender As TextBox = DirectCast(gvDetails.FooterRow.FindControl("txtfGender"), TextBox)
Dim emp As New EmployeeDetail()
emp.EmpName = txtname.Text
emp.Location = txtlocation.Text
emp.Gender = txtgender.Text
db.EmployeeDetails.InsertOnSubmit(emp)
db.SubmitChanges()
lblresult.ForeColor = Color.Green
lblresult.Text = txtname.Text + " details inserted successfully"
BindGridview()
End If
End Sub
Protected Sub gvDetails_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
gvDetails.EditIndex = e.NewEditIndex
BindGridview()
End Sub
Protected Sub gvDetails_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
gvDetails.EditIndex = -1
BindGridview()
End Sub
Protected Sub gvDetails_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
gvDetails.PageIndex = e.NewPageIndex
BindGridview()
End Sub
Protected Sub gvDetails_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
Dim empid As Integer = Convert.ToInt32(gvDetails.DataKeys(e.RowIndex).Values("Id").ToString())
Dim txtname As TextBox = DirectCast(gvDetails.Rows(e.RowIndex).FindControl("txtName"), TextBox)
Dim txtlocation As TextBox = DirectCast(gvDetails.Rows(e.RowIndex).FindControl("txtLocation"), TextBox)
Dim txtgender As TextBox = DirectCast(gvDetails.Rows(e.RowIndex).FindControl("txtGender"), TextBox)
Dim emp As New EmployeeDetail()
emp = db.EmployeeDetails.[Single](Function(x) x.EmpId = empid)
emp.EmpName = txtname.Text
emp.Location = txtlocation.Text
emp.Gender = txtgender.Text
db.SubmitChanges()
gvDetails.EditIndex = -1
BindGridview()
lblresult.ForeColor = Color.Green
lblresult.Text = txtname.Text + " details updated successfully"
End Sub
Protected Sub gvDetails_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
Dim empid As Integer = Convert.ToInt32(gvDetails.DataKeys(e.RowIndex).Values("Id").ToString())
Dim empname As String = gvDetails.DataKeys(e.RowIndex).Values("Name").ToString()
Dim emp As New EmployeeDetail()
emp = db.EmployeeDetails.[Single](Function(x) x.EmpId = empid)
db.EmployeeDetails.DeleteOnSubmit(emp)
db.SubmitChanges()
BindGridview()
lblresult.ForeColor = Color.Green
lblresult.Text = empname & Convert.ToString(" details deleted successfully")
End Sub
End Class
If you observe the above example, we implemented LINQ to SQL select, insert, update and delete operations. Now we will run and see the result of the application.
Result of LINQ to SQL CRUD Operations Example
Following is the result of LINQ to SQL crud operations (insert, update, delete and select) example.
This is how we can use LINQ to SQL to perform insert, update, delete and select operations in c#, vb.net.
