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).
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.
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
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();
}
}Step 5: Run Code
Figure 7: Fill Record
Now fill in some records and save them in the database.
Figure 8: Record Display in Grid using LINQ to SQL
Now create an update and delete record process using an Edit and a Delete button command as in the following.
Figure 9: Update and Delete record
Figure 10: Record Updated
I hope you understand how to use LINQ to SQL Classes to do SQL Table CRUD operations.
Source: Medium - Sameer Gaikwad
The Tech Platform