Sameer Gaikwad LINQ to SQL in ASP.Net With CRUD Operations

6 min readFeb 15, 2022

Follow me Sameer Gaikwad then follow the link below! 👉 💯day challenge 📩 Reach out to me on Twitter or Linkedin, or Blogspot if you want to discuss this further. :)

MS SQL SERVER

Getting Started with SQL

Asp.Net Core MVC with Bootstrap

ASP.NET Core Web App with Blazor
Microsoft Ado.Net Entity Framework
ASP.NET MVC 5 with Bootstrap
ASP .Net Interview Questions
JavaScript Interview Questions

Sameer Gaikwad CRUD Operation SQL stored procedure mesameergaikwad “Programming Microsoft LINQ in .NET Framework 4” (Developer Reference)

Language-Integrated Query (LINQ)

In Language-Integrated Query (LINQ) to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work within your own programming language (as per the MSDN).

check out my blog post

All LINQ query operations consist of the following three distinct actions:

Obtain the data source.
Create the query.
Execute the query.

In this article, LINQ is done against a SQL database. So this explains how to do an insert, an edit, an update, and a delete of records with LINQ to SQL step-by-step.

check out my blog post

Github link download

Step 1: Database

Create a LINQUser table as in the following.

create table LinqUser
(
Id int identity(1,1) primary key,
Name varchar(30),
Email nvarchar(50),
Image nvarchar(max)
)

Step 2: Visual Studio

Go to Solution Explorer and LINQ to SQL classes as in the following.

Figure 1: LINQ to SQL Classes

Click the add button and open as in the following dialog box and click the Yes button.

Figure 2: Add App_Code

And the LINQ class is created in the App_code folder in the project’s Solution Explorer as in the following.

Figure 3: Add .dbml

Now maintain your database connection with Visual Studio and add a table to the LinqClass.dbml surface area as in the following from the Server Explorer.

Figure 4: Add Table check out my blog post

Figure 5: Add Webform

Step 3: UI design

Now I will write the design code inside the .aspx page. On this page add some TextBox controls, a Button control, and a GridView Control. And add a GridView Action event for an edit, a cancel, a delete, and a paging event.

The following is the UI design code.

<%@ Page Title=”” Language=”C#” MasterPageFile=”~/Master/Master.master” AutoEventWireup=”true” CodeFile=”LinqCRUDdemo.aspx.cs” Inherits=”UI_LinqCRUDdemo” %>
<asp:Content ID=”Content1" ContentPlaceHolderID=”head” Runat=”Server”>
</asp:Content>
<asp:Content ID=”Content2" ContentPlaceHolderID=”ContentPlaceHolder1" Runat=”Server”>
<div style=”background-color: #66FF99; border: thin groove #FF0000; height: 138px; width: 400px;”>
<fieldset style=”width: 340px”><legend>Linq To Classes Demo</legend>
<asp:Table runat=”server”>
<asp:TableRow><asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox ID=”txtName” runat=”server”></asp:TextBox></asp:TableCell></asp:TableRow>
<asp:TableRow><asp:TableCell>Email ID</asp:TableCell><asp:TableCell><asp:TextBox ID=”txtEmail” runat=”server”></asp:TextBox></asp:TableCell></asp:TableRow>
<asp:TableRow><asp:TableCell>Upload Image</asp:TableCell><asp:TableCell><asp:FileUpload ID=”FileUpload1" runat=”server” /></asp:TableCell></asp:TableRow>
<asp:TableRow><asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID=”btnsave” runat=”server” Text=”Save” onclick=”btnsave_Click” /> </asp:TableCell></asp:TableRow>
</asp:Table>
</fieldset>
</div><br />
<asp:GridView ID=”GridUser” runat=”server” AutoGenerateColumns=”False” DataKeyNames=”id”
AllowPaging=”True” PageSize=”3"
OnRowCancelingEdit=”GridUser_RowCancelingEdit”
OnRowDeleting=”GridUser_RowDeleting”
OnRowEditing=”GridUser_RowEditing”
OnRowUpdating=”GridUser_RowUpdating” OnPageIndexChanging=”GridUser_PageIndexChanging” BackColor=”#DEBA84" BorderColor=”#DEBA84" BorderStyle=”None” BorderWidth=”1px” CellPadding=”3" CellSpacing=”2">
<Columns>
<asp:TemplateField HeaderText=”Name”>
<EditItemTemplate>
<asp:TextBox ID=”txtName” runat=”server” Text=’<%# Eval(“Name”) %>’></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server” Text=’<%# Eval(“Name”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Email ID”>
<EditItemTemplate>
<asp:TextBox ID=”txtEmail” runat=”server” Text=’<%# Eval(“Email”) %>’></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”lblEmail” runat=”server” Text=’<%# Eval(“Email”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Image”>
<ItemTemplate>
<asp:Image ID=”Image1" runat=”server” ImageUrl=’<%# Eval(“Image”) %>’ Height=”100px” Width=”100px”/>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton=”True” />
<asp:CommandField ShowDeleteButton=”True” />
</Columns>
<FooterStyle BackColor=”#F7DFB5" ForeColor=”#8C4510" />
<HeaderStyle BackColor=”#A55129" Font-Bold=”True” ForeColor=”White” />
<PagerSettings PageButtonCount=”8" />
<PagerStyle ForeColor=”#8C4510" HorizontalAlign=”Center” />
<RowStyle BackColor=”#FFF7E7" ForeColor=”#8C4510" />
<SelectedRowStyle BackColor=”#738A9C” Font-Bold=”True” ForeColor=”White” />
<SortedAscendingCellStyle BackColor=”#FFF1D4" />
<SortedAscendingHeaderStyle BackColor=”#B95C30" />
<SortedDescendingCellStyle BackColor=”#F1E5CE” />
<SortedDescendingHeaderStyle BackColor=”#93451F” />
</asp:GridView>
</asp:Content>

Go to the GridView control’s property and add a GridView Action Event as in the following.

Figure 6: Grid Action

And also maintain a Data key field in the GridView to edit and delete commands.

Step 4: UI Code

First, write code to insert the TextBox data into the database using LINQ to SQL Classes. Then the data is retrieved from the database and the record is edited and deleted with the LINQ to SQL classes as in the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class UI_LinqCRUDdemo : System.Web.UI.Page
{
LinqClassDataContext db = new LinqClassDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bindgrid();
}
}
protected void btnsave_Click(object sender, EventArgs e)
{
string image = Server.MapPath(“~/Images/”) + Guid.NewGuid() + FileUpload1.PostedFile.FileName;
FileUpload1.PostedFile.SaveAs(image);
string File = image.Substring(image.LastIndexOf(“\\”));
string[] split = File.Split(‘\\’);
string ImgPath = split[1];
string imagepath = “~/Images/” + ImgPath;
LinqUser LU = new LinqUser();
LU.Name = txtName.Text;
LU.Email = txtEmail.Text;
LU.Image = imagepath;
db.LinqUsers.InsertOnSubmit(LU);
db.SubmitChanges();
Clear();
Bindgrid();
}
private void Clear()
{
txtName.Text = string.Empty;
txtEmail.Text = string.Empty;
}
private void Bindgrid()
{
var bind = from c in db.LinqUsers select c;
GridUser.DataSource = bind;
GridUser.DataBind();
}
protected void GridUser_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int ID = Convert.ToInt32(GridUser.DataKeys[e.RowIndex].Value);
LinqUser LU = db.LinqUsers.First(x => x.Id == ID);
db.LinqUsers.DeleteOnSubmit(LU);
db.SubmitChanges();
Bindgrid();
}
protected void GridUser_RowEditing(object sender, GridViewEditEventArgs e)
{
GridUser.EditIndex = e.NewEditIndex;
Bindgrid();
}
protected void GridUser_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridUser.EditIndex = -1;
Bindgrid();
}
protected void GridUser_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = GridUser.Rows[e.RowIndex];
TextBox txtName = (TextBox)row.FindControl(“txtName”);
TextBox txtEmail = (TextBox)row.FindControl(“txtEmail”);
int ID = Convert.ToInt32(GridUser.DataKeys[e.RowIndex].Value);
LinqUser LU = db.LinqUsers.First(x => x.Id == ID);
LU.Name = txtName.Text;
LU.Email = txtEmail.Text;
db.SubmitChanges();
GridUser.EditIndex = -1;
Bindgrid();
}
protected void GridUser_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridUser.PageIndex = e.NewPageIndex;
Bindgrid();
}
}