LINQ to SQL in ASP.Net With CRUD Operations #mesameergaikwad

 

LINQ to SQL in ASP.Net With CRUD Operations

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).

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

1. Obtain the data source.
2. Create the query.
3. 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.

Github link download

Step 1: Database

Create a LINQUser table as in the following.

1. create table LinqUser
2. (
3. Id int identity(1,1) primary key,
4. Name varchar(30),
5. Email nvarchar(50),
6. Image nvarchar(max)
7. )

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.

1. <%@ Page Title=”” Language=”C#” MasterPageFile=”~/Master/Master.master” AutoEventWireup=”true” CodeFile=”LinqCRUDdemo.aspx.cs” Inherits=”UI_LinqCRUDdemo” %>
2. <asp:Content ID=”Content1" ContentPlaceHolderID=”head” Runat=”Server”>
3. </asp:Content>
4. <asp:Content ID=”Content2" ContentPlaceHolderID=”ContentPlaceHolder1" Runat=”Server”>
5. <div style=”background-color: #66FF99; border: thin groove #FF0000; height: 138px; width: 400px;”>
6. <fieldset style=”width: 340px”><legend>Linq To Classes Demo</legend>
7. <asp:Table runat=”server”>
8. <asp:TableRow><asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox ID=”txtName” runat=”server”></asp:TextBox></asp:TableCell></asp:TableRow>
9. <asp:TableRow><asp:TableCell>Email ID</asp:TableCell><asp:TableCell><asp:TextBox ID=”txtEmail” runat=”server”></asp:TextBox></asp:TableCell></asp:TableRow>
10. <asp:TableRow><asp:TableCell>Upload Image</asp:TableCell><asp:TableCell><asp:FileUpload ID=”FileUpload1" runat=”server” /></asp:TableCell></asp:TableRow>
11. <asp:TableRow><asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID=”btnsave” runat=”server” Text=”Save” onclick=”btnsave_Click” /> </asp:TableCell></asp:TableRow>
12. </asp:Table>
13. </fieldset>
14. </div><br />
15. <asp:GridView ID=”GridUser” runat=”server” AutoGenerateColumns=”False” DataKeyNames=”id”
16. AllowPaging=”True” PageSize=”3"
17. OnRowCancelingEdit=”GridUser_RowCancelingEdit”
18. OnRowDeleting=”GridUser_RowDeleting”
19. OnRowEditing=”GridUser_RowEditing”
20. OnRowUpdating=”GridUser_RowUpdating” OnPageIndexChanging=”GridUser_PageIndexChanging” BackColor=”#DEBA84" BorderColor=”#DEBA84" BorderStyle=”None” BorderWidth=”1px” CellPadding=”3" CellSpacing=”2">
21. <Columns>
22. <asp:TemplateField HeaderText=”Name”>
23. <EditItemTemplate>
24. <asp:TextBox ID=”txtName” runat=”server” Text=’<%# Eval(“Name”) %>’></asp:TextBox>
25. </EditItemTemplate>
26. <ItemTemplate>
27. <asp:Label ID=”lblName” runat=”server” Text=’<%# Eval(“Name”) %>’></asp:Label>
28. </ItemTemplate>
29. </asp:TemplateField>
30. <asp:TemplateField HeaderText=”Email ID”>
31. <EditItemTemplate>
32. <asp:TextBox ID=”txtEmail” runat=”server” Text=’<%# Eval(“Email”) %>’></asp:TextBox>
33. </EditItemTemplate>
34. <ItemTemplate>
35. <asp:Label ID=”lblEmail” runat=”server” Text=’<%# Eval(“Email”) %>’></asp:Label>
36. </ItemTemplate>
37. </asp:TemplateField>
38. <asp:TemplateField HeaderText=”Image”>
39. <ItemTemplate>
40. <asp:Image ID=”Image1" runat=”server” ImageUrl=’<%# Eval(“Image”) %>’ Height=”100px” Width=”100px”/>
41. </ItemTemplate>
42. </asp:TemplateField>
43. <asp:CommandField ShowEditButton=”True” />
44. <asp:CommandField ShowDeleteButton=”True” />
45. </Columns>
46. <FooterStyle BackColor=”#F7DFB5" ForeColor=”#8C4510" />
47. <HeaderStyle BackColor=”#A55129" Font-Bold=”True” ForeColor=”White” />
48. <PagerSettings PageButtonCount=”8" />
49. <PagerStyle ForeColor=”#8C4510" HorizontalAlign=”Center” />
50. <RowStyle BackColor=”#FFF7E7" ForeColor=”#8C4510" />
51. <SelectedRowStyle BackColor=”#738A9C” Font-Bold=”True” ForeColor=”White” />
52. <SortedAscendingCellStyle BackColor=”#FFF1D4" />
53. <SortedAscendingHeaderStyle BackColor=”#B95C30" />
54. <SortedDescendingCellStyle BackColor=”#F1E5CE” />
55. <SortedDescendingHeaderStyle BackColor=”#93451F” />
56. </asp:GridView>
57. </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.

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Web;
5. using System.Web.UI;
6. using System.Web.UI.WebControls;
7. public partial class UI_LinqCRUDdemo : System.Web.UI.Page
8. {
9. LinqClassDataContext db = new LinqClassDataContext();
10. protected void Page_Load(object sender, EventArgs e)
11. {
12. if (!IsPostBack)
13. {
14. Bindgrid();
15. }
16. }
17. protected void btnsave_Click(object sender, EventArgs e)
18. {
19. string image = Server.MapPath(“~/Images/”) + Guid.NewGuid() + FileUpload1.PostedFile.FileName;
20. FileUpload1.PostedFile.SaveAs(image);
21. string File = image.Substring(image.LastIndexOf(“\\”));
22. string[] split = File.Split(‘\\’);
23. string ImgPath = split[1];
24. string imagepath = “~/Images/” + ImgPath;
25. LinqUser LU = new LinqUser();
26. LU.Name = txtName.Text;
27. LU.Email = txtEmail.Text;
28. LU.Image = imagepath;
29. db.LinqUsers.InsertOnSubmit(LU);
30. db.SubmitChanges();
31. Clear();
32. Bindgrid();
33. }
34. private void Clear()
35. {
36. txtName.Text = string.Empty;
37. txtEmail.Text = string.Empty;
38. }
39. private void Bindgrid()
40. {
41. var bind = from c in db.LinqUsers select c;
42. GridUser.DataSource = bind;
43. GridUser.DataBind();
44. }
45. protected void GridUser_RowDeleting(object sender, GridViewDeleteEventArgs e)
46. {
47. int ID = Convert.ToInt32(GridUser.DataKeys[e.RowIndex].Value);
48. LinqUser LU = db.LinqUsers.First(x => x.Id == ID);
49. db.LinqUsers.DeleteOnSubmit(LU);
50. db.SubmitChanges();
51. Bindgrid();
52. }
53. protected void GridUser_RowEditing(object sender, GridViewEditEventArgs e)
54. {
55. GridUser.EditIndex = e.NewEditIndex;
56. Bindgrid();
57. }
58. protected void GridUser_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
59. {
60. GridUser.EditIndex = -1;
61. Bindgrid();
62. }
63. protected void GridUser_RowUpdating(object sender, GridViewUpdateEventArgs e)
64. {
65. GridViewRow row = GridUser.Rows[e.RowIndex];
66. TextBox txtName = (TextBox)row.FindControl(“txtName”);
67. TextBox txtEmail = (TextBox)row.FindControl(“txtEmail”);
68. int ID = Convert.ToInt32(GridUser.DataKeys[e.RowIndex].Value);
69. LinqUser LU = db.LinqUsers.First(x => x.Id == ID);
70. LU.Name = txtName.Text;
71. LU.Email = txtEmail.Text;
72. db.SubmitChanges();
73. GridUser.EditIndex = -1;
74. Bindgrid();
75. }
76. protected void GridUser_PageIndexChanging(object sender, GridViewPageEventArgs e)
77. {
78. GridUser.PageIndex = e.NewPageIndex;
79. Bindgrid();
80. }
81. }

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. #mesameergaikwad