LINQ to SQL Insert Update Delete Operations

  Total Views : 170
  Comments : 0
 
  Keywords : Crud operations using linq to sql in c# / vb.net with example, Linq to sql insert update delete query in c# / vb.net with example, Linq to sql insert / update / delete data in c# / vb.net

Here we will learn LINQ to SQL insert, update delete operations in c#, vb.net with examples or how to perform crud (insert, update, delete) operations in LINQ to SQL using c#, vb.net with examples.

LINQ to SQL Crud (Insert, Update, Delete) Operations

The LINQ to SQL is having facility to maintain the changes whatever we do to the objects like adding or removing or updating items in collection object till 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 database.

Syntax of LINQ to SQL Insert / Update / Delete

Following are the syntax of insert or update or delete operations using 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 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 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 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 implement 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 don't worry check this link create and map database tables to LINQ to SQL file (.dbml).

 

Once we create and map required tables to .dbml file now we will show data in our application for that Right click on 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 like 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 code behind file and write the code like 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

' LINQ to SQL Select Operation

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

' LINQ to SQL Insert Operation

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

' LINQ to SQL Update Operation

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

' LINQ to SQL Delete Operation

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 above example we implemented LINQ to SQL select, insert, update and delete operations. Now we will run and see the result of application.

Output of LINQ to SQL CRUD Operations Example

Following is the result of LINQ to SQL crud operations (insert, update, delete and select) example.

 

LINQ to SQL Insert Update and Delete Operations Example Result in C#, VB.NET

 

This is how we can use LINQ to SQL to perform insert, update, delete and select operations in c#, vb.net.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User