LINQ to SQL Insert,Delete and Update Operations

The LINQ to SQL has the facility to maintain the changes whatever we do with the objects like adding, removing, or updating the items in the collection of the object till we submit the changes by using the method SubmitChanges() . After submitting the changes, the LINQ to SQL will translate our actions to SQL and submit the changes to the database.

Syntax of LINQ to SQL Insert/Update and Delete

Here is the syntax of Insert/Update or Delete operations using in LINQ to SQL.

LINQ to SQL Insert Operations

Here is the syntax of using the LINQ to SQL Insert operations query in C# to insert the data in the database.

  EmployeeDBDataContext db = new EmployeeDBDataContext();  EmployeeDetail emp = new EmployeeDetail();  emp.EmpName = txtname.Text;  emp.Location = txtlocation.Text;  db.EmployeeDetails.InsertOnSubmit(emp);  db.SubmitChanges();  

LINQ to SQL Update Operations

Here is the syntax of using the LINQ to SQL update operation query to update the data in the database.

  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();  

LINQ to SQL Delete Operations

Here is the syntax of using the LINQ to SQL delete operations in C# to delete the data in the database.

  EmployeeDBDataContext db = new EmployeeDBDataContext();  EmployeeDetail emp = new EmployeeDetail();  emp = db.EmployeeDetails.Single(x => x.EmpId == empid);  db.EmployeeDetails.DeleteOnSubmit(emp);  db.SubmitChanges();  

LINQ to SQL Insert, Update, Delete Operations

Before we start to implement the LINQ to SQL Insert, update and delete operations, firstly there is a need to create a database with required tables and map those tables to LINQ to SQL file (.dbml).

Once we create and map the required tables to .dbml file now, we want to show the data in our application. For that, we will right-click on the application->Select Add->New Item->Select Web Form->Give name as Default.aspx and click Ok button.

Now open Default.aspx page and write the code as shown below:

  <%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>    <!DOCTYPE html>    <html xmlns=”http://www.w3.org/1999/xhtml”>  <head runat=”server”>      <title></title>  </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 the file and write the code as shown below:

  using System;  using System.Collections.Generic;  using System.Drawing;  using System.Linq;  using System.Web;  using System.Web.UI;  using System.Web.UI.WebControls;    public partial class _Default : System.Web.UI.Page  {      EmployeeDataContext db = new EmployeeDataContext();      protected void Page_Load(object sender, EventArgs e)      {          if (!Page.IsPostBack)            {                BindGridview();            }      }      protected void BindGridview()        {            var result = from ed in db.EmployeeDetails                         select new                         {                             Id = ed.Id,                             Name = ed.Name,                             Location = ed.Location,                             Gender = ed.Gender                         };            gvDetails.DataSource = result;            gvDetails.DataBind();        }      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 = new EmployeeDetail();                emp.Name = 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();          }      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 = new EmployeeDetail();            emp = db.EmployeeDetails.Single(x => x.Id == empid);            emp.Name = 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”;      }      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 = new EmployeeDetail();            emp = db.EmployeeDetails.Single(x => x.Id == empid);            db.EmployeeDetails.DeleteOnSubmit(emp);            db.SubmitChanges();            BindGridview();            lblresult.ForeColor = Color.Green;            lblresult.Text = empname + ” details deleted successfully”;        }  }  

In the above example, we are implementing LINQ to SQL select, insert, and update, delete operations. Now we will run the application and see the result.

Next TopicLINQ to ADO.NET

LINQ to SQL CRUD Operations

LINQ to SQL Insert,Delete and Update Operations

Syntax of LINQ to SQL Insert/Update and Delete

LINQ to SQL Insert Operations

LINQ to SQL Update Operations

LINQ to SQL Delete Operations

LINQ to SQL Insert, Update, Delete Operations

LINQ Sorting Operators

Kill command

You may also like