In this article I will explain with an example, how to perform Select, Insert, Edit, Update and Delete operations using LINQ to SQL Framework in ASP.Net using C# and VB.Net.
This process is also known as CRUD i.e. Create, Read, Update and Delete in GridView using LINQ to SQL Framework in ASP.Net.
Database
I have made use of the following table Customers with the schema as follows.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
I have already inserted few records in the table.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
Configuring and connecting LINQ to SQL Framework to database
Following are the steps to configure and add LINQ to SQL Framework and also how to connect it with SQL Server database.
1. You will need to add LINQ to SQL classes to your project using Add New Item Dialog as shown below.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
As soon as you add the LINQ to SQL classes to your project you will be prompted with the following dialog. You need to click YES button.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
2. Then you need to connect to the database by right clicking Data Connections node in Server Explorer and then click Add Connection from the context menu.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
3. The above step will open up Choose DataSource Dialog in which you will need to select the Data Source, i.e. SQL Server and then click Continue.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
4. In the next dialog, you will need to provide the details of the SQL Server i.e. Server Name and Authentication details and then select the Database.
Once above is done you can Test Connection and if it works then click OK.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
As you can see, the LINQ to SQL (dbml) classes are generated in the App_Code folder of Solution Explorer.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
And also the CustomersDB database is available in the Server Explorer.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
5.Finally, you need to drag and drop the Customers Table into the LINQ to SQL Framework (dbml) class (as shown below) and the save it.
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
HTML Markup
The HTML Markup consists of:
GridView:-
Columns
GridView consists of two TemplateField columns and a CommandField column.
TemplateField – Name and Country fields are set in the TemplateField columns with ItemTemplate with a Label and EditItemTemplate with TextBox for displaying and editing data respectively.
Properties
DataKeyNames – For permitting to set the names of the Column Fields, that we want to use in code but do not want to display it. Example Primary Keys, ID fields, etc.
EmptyDataText – For displaying text when there is no data in the GridView.
Events
GridView has been assigned with the following five event handlers i.e. OnRowDataBound, OnRowEditing, OnRowCancelingEdit, OnRowUpdating and OnRowDeleting.
TextBox– For capturing records to be added.
Button– For adding records.
The Button has been assigned with an OnClick event handler.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="CustomerId"
OnRowDataBound="OnRowDataBound" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit"
OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting" EmptyDataText="No records has been added.">
<Columns>
<asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCountry" runat="server" Text='<%# Eval("Country") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
<tr>
<td style="width: 150px">
Name:<br />
<asp:TextBox ID="txtName" runat="server" Width="140" />
</td>
<td style="width: 150px">
Country:<br />
<asp:TextBox ID="txtCountry" runat="server" Width="140" />
</td>
<td style="width: 100px">
<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
</td>
</tr>
</table>
Binding the GridView using LINQ to SQL Framework
Inside the Page Load event handler, the BindGrid method is called.
The BindGrid method fetches the records from the database using LINQ to SQL Framework and assigned to the DataSource property of the GridView.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
using (CustomersDataContext ctx = new CustomersDataContext())
{
GridView1.DataSource = from customer in ctx.Customers
select customer;
GridView1.DataBind();
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Using ctx As New CustomersDataContext()
GridView1.DataSource = From customer In ctx.Customers _
Select customer
GridView1.DataBind()
End Using
End Sub
Insert
When Add button is clicked, the following event handler is executed.
Inside this event handler, the Name and Country values are of the Customer is fetched from their respective TextBoxes and then passed through an object of Customer class using InsertOnSubmit method of LINQ to SQL Framework.
The SubmitChanges method of LINQ to SQL Framework is called which updates the changes to the database and the contents of the TextBoxes are removed.
Finally, the GridView is populated using the BindGrid method which in-turn refreshes the GridView with updated records.
C#
protected void Insert(object sender, EventArgs e)
{
using (CustomersDataContext ctx = new CustomersDataContext())
{
Customer customer = new Customer
{
Name = txtName.Text,
Country = txtCountry.Text
};
ctx.Customers.InsertOnSubmit(customer);
ctx.SubmitChanges();
}
txtName.Text = "";
txtCountry.Text = "";
this.BindGrid();
}
VB.Net
Protected Sub Insert(sender As Object, e As EventArgs)
Using ctx As New CustomersDataContext()
Dim customer As New Customer() With { _
.Name = txtName.Text, _
.Country = txtCountry.Text _
}
ctx.Customers.InsertOnSubmit(customer)
ctx.SubmitChanges()
End Using
txtName.Text = ""
txtCountry.Text = ""
Me.BindGrid()
End Sub
Edit
When the Edit Button is clicked, the GridView’s OnRowEditing event handler is triggered, where the EditIndex of the GridView is updated with the Row Index of the GridView Row to be edited.
C#
protected void OnRowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
this.BindGrid();
}
VB.Net
Protected Sub OnRowEditing(sender As Object, e As GridViewEditEventArgs)
GridView1.EditIndex = e.NewEditIndex
Me.BindGrid()
End Sub
Update
When the Update Button is clicked, the GridView’s OnRowUpdating event handler is triggered.
Inside this event handler, the CustomerId (primary key) is fetched from the DataKey property of GridView while the Name and Country fields are fetched from their respective TextBoxes and are set to the Customer object fetched from the LINQ to SQL Framework collection using the CustomerId.
The SubmitChanges method of LINQ to SQL Framework is called which updates the changes to the database.
Finally, the BindGrid method is called which refreshes the GridView with updated records.
C#
protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = GridView1.Rows[e.RowIndex];
int customerId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
string name = (row.FindControl("txtName") as TextBox).Text;
string country = (row.FindControl("txtCountry") as TextBox).Text;
using (CustomersDataContext ctx = new CustomersDataContext())
{
Customer customer = (from c in ctx.Customers
where c.CustomerId == customerId
select c).FirstOrDefault();
customer.Name = name;
customer.Country = country;
ctx.SubmitChanges();
}
GridView1.EditIndex = -1;
this.BindGrid();
}
VB.Net
Protected Sub OnRowUpdating(sender As Object, e As GridViewUpdateEventArgs)
Dim row As GridViewRow = GridView1.Rows(e.RowIndex)
Dim customerId As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Values(0))
Dim name As String = TryCast(row.FindControl("txtName"), TextBox).Text
Dim country As String = TryCast(row.FindControl("txtCountry"), TextBox).Text
Using ctx As New CustomersDataContext()
Dim customer As Customer = (From c In ctx.Customers Where c.CustomerId = customerId _
Select c).FirstOrDefault()
customer.Name = name
customer.Country = country
ctx.SubmitChanges()
End Using
GridView1.EditIndex = -1
Me.BindGrid()
End Sub
Cancel Edit
When the Cancel Button is clicked, the GridView’s OnRowCancelingEdit event handler is triggered and the EditIndex is set to -1 and again the GridView is populated with data using the BindGrid method.
C#
protected void OnRowCancelingEdit(object sender, EventArgs e)
{
GridView1.EditIndex = -1;
this.BindGrid();
}
VB.Net
Protected Sub OnRowCancelingEdit(sender As Object, e As EventArgs)
GridView1.EditIndex = -1
Me.BindGrid()
End Sub
Delete
When the Delete Button is clicked, the GridView’s OnRowDeleting event handler is triggered.
CustomerId (primary key) is fetched from the DataKey property of GridView and is used to fetch the Customer object from the LINQ to SQL Framework collection.
The fetched Customer object is passed to the DeleteOnSubmit method of LINQ to SQL Framework which deletes the record from the database and the SubmitChanges method of LINQ to SQL Framework is called which finalizes the changes in the database.
Finally, the GridView is populated using the BindGrid method which in-turn refreshes the GridView with updated records.
C#
protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
{
int customerId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
using (CustomersDataContext ctx = new CustomersDataContext())
{
Customer customer = (from c in ctx.Customers
where c.CustomerId == customerId
select c).FirstOrDefault();
ctx.Customers.DeleteOnSubmit(customer);
ctx.SubmitChanges();
}
this.BindGrid();
}
VB.Net
Protected Sub OnRowDeleting(sender As Object, e As GridViewDeleteEventArgs)
Dim customerId As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Values(0))
Using ctx As New CustomersDataContext()
Dim customer As Customer = (From c In ctx.Customers Where c.CustomerId = customerId _
Select c).FirstOrDefault()
ctx.Customers.DeleteOnSubmit(customer)
ctx.SubmitChanges()
End Using
Me.BindGrid()
End Sub
Delete Confirmation Message
The delete operation will be confirmed using JavaScript Confirmation Box, thus inside the OnRowDataBound event handler, the Delete LinkButton is assigned with a JavaScript OnClick event handler.
C#
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != GridView1.EditIndex)
{
(e.Row.Cells[2].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');";
}
}
VB.Net
Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow AndAlso e.Row.RowIndex <> GridView1.EditIndex Then
TryCast(e.Row.Cells(2).Controls(2), LinkButton).Attributes("onclick") = "return confirm('Do you want to delete this row?');"
End If
End Sub
Screenshot
![LINQ to SQL CRUD: Select Insert Edit Update and Delete using LINQ to SQL in ASP.Net]()
Downloads
