CRUD Operations Using LINQ To SQL In ASP.NET

Aniket Narvankar
8y
5.7k
0
0

Blog

LINQ

LINQ Stands For Language Integrated Query, It is a common programming model that can be used to query all types of data such as database, dataset, XML and many other type of data.

First Open SQL Server and Create a Table name tblDetails which include three fields:

Id Primary Key,
Name varchar(50),
ImgPath varchar(100)
Open Visual Studio and Create a New Web Application. File-New Project-Asp.Net Web Application Name it as LINQ Application.
Right Click on Application And Create New Folder Name it as Images to Store Path of the Images which will be Uploaded.
Right Click on Application and Add New Item Web Form Name it as Details.aspx.
Add two textboxes txtSearch, txtName, tow buttons btnSubmit and btnSearch and a Grid View to display Data. Take two Hidden Fields one for storing Id which is primary key which will be required for updating and Deleting Record. Another for storing image Path. Take a File Upload Control for Image Upload. Just write following code on aspx page.
1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Details.aspx.cs" Inherits="LINQApplication.Details" %>
2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3. <html xmlns="http://www.w3.org/1999/xhtml">
5. <head runat="server">
6. <script src="Scripts/jquery-1.10.2.min.js" type="text/javascript"></script>
7. <script type="text/javascript" language="javascript">
8. function ValidateName(e)
9. {
10. var keyCode = (e.which) ? e.which : e.keyCode
11. if (((keyCode >= 65 && keyCode <= 90) || (keyCode >= 97 && keyCode <= 122)) || keyCode == 32)
12. {
13. document.getElementById("chkName").style.display = "none";
14. return true;
15. }
16. else
17. {
18. document.getElementById("chkName").style.display = "inline";
19. return false;
20. }
21. }
22. </script>
23. <title></title>
24. </head>
26. <body>
27. <form id="form1" runat="server">
28. <div>
29. <table>
30. <tr>
31. <td>
32. <asp:HiddenField ID="hdnValue" runat="server" /> </td>
33. </tr>
34. <tr>
35. <td>
36. <asp:HiddenField ID="hdfHidden" runat="server" /> </td>
37. </tr>
38. <tr>
39. <td> Name : </td>
40. <td>
41. <asp:TextBox ID="txtName" runat="server" onkeypress="return ValidateName(event);"> </asp:TextBox> <span id="chkName" style="color: Red; display: none">Name Should Not Contain Numeric Values</span> </td>
42. </tr>
43. <tr>
44. <td> Please Select Image : </td>
45. <td>
46. <asp:FileUpload ID="imgFile" runat="server" /> </td>
47. </tr>
48. <tr>
49. <td>
50. <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /> </td>
51. <td>
52. <asp:Button ID="btnCancel" runat="server" Text="Cancel" onclick="btnCancel_Click" /> </td>
53. </tr>
54. <tr>
55. <td> Search by Name: </td>
56. <td>
57. <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
58. </td>
59. <td>
60. <asp:Button ID="btnSearch" runat="Server" Text="Search" onclick="btnSearch_Click" /> </td>
61. </tr>
62. <tr>
63. <td>
64. <asp:GridView ID="gvDisplayDetails" DataKeyNames="Id" runat="server" AutoGenerateColumns="false" onrowcommand="gvDisplayDetails_RowCommand" onrowdeleting="gvDisplayDetails_RowDeleting" onselectedindexchanging="gvDisplayDetails_SelectedIndexChanging">
65. <Columns>
66. <asp:BoundField DataField="Name" HeaderText="Name" />
67. <asp:TemplateField HeaderText="Images">
68. <ItemTemplate>
69. <asp:Image ID="img" runat="server" ImageUrl='<%# Bind("ImgPath", "~/Images/{0}") %>' /> </ItemTemplate>
70. </asp:TemplateField>
71. <asp:CommandField ButtonType="Button" SelectText="Edit" ShowSelectButton="True" />
72. <asp:TemplateField>
73. <ItemTemplate>
74. <asp:Button runat="server" ID="btnDelete" OnClientClick="return confirm('Are you sure,you want to delete this record ?');" Text="Delete" CommandName="Delete" /> </ItemTemplate>
75. </asp:TemplateField>
76. </Columns>
77. </asp:GridView>
78. </td>
79. </tr>
80. </table>
81. </div>
82. </form>
83. </body>
85. </html>
Now again right click on the Application and Add New a LINQ to SQL Class. Select Add New Item then in Installed Templates.

Select Data then Select LINQ to Sql Classes, Name it as PersonalDetails. Extension is .dbml.
Now From Server Explorer just drag and drop your table on dbml file and save it. You will see in web.config a connection string is created automatically:
1. <add name="TestDBConnectionString" connectionString="Data Source=LIVINGROOM\SQLEXPRESS;Initial Catalog=Test DB;Integrated Security=True"
2. providerName="System.Data.SqlClient" />
Here TestDB is my DataBase Name.
Generate Events for GridView Selected Index Changing, OnRowCommand, OnRowDeleting.
Just write following code on CS side.
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. using System.IO;
8. using System.Configuration;
9. using System.Data.SqlClient;
10. namespace LINQApplication
11. {
12. public partial class Details: System.Web.UI.Page
13. {
14. PersonalDetailsDataContext obj = new PersonalDetailsDataContext();
15. SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDBConnectionString"].ToString());
16. protected void Page_Load(object sender, EventArgs e)
17. {
18. if (!Page.IsPostBack)
19. {
20. BindGrid();
21. btnCancel.Visible = false;
22. }
23. }
24. private void BindGrid()
25. {
26. try
27. {
28. var Details = from details in obj.tblDetails
29. select new
30. {
31. details.Id,
32. details.Name,
33. details.ImgPath
34. };
35. if (Details.Any())
36. {
37. gvDisplayDetails.DataSource = Details;
38. gvDisplayDetails.DataBind();
39. }
40. else
41. {
42. Showmsg("No Record Found");
43. }
44. }
45. catch (Exception ex)
46. {
47. throw ex;
48. }
49. }
50. protected void btnSubmit_Click(object sender, EventArgs e)
51. {
52. try
53. {
54. if (btnSubmit.Text == "Submit")
55. {
56. if (checkBlank())
57. {
58. Save();
59. }
60. }
61. else if (btnSubmit.Text == "Update")
62. {
63. Update();
64. }
65. }
66. catch (Exception ex)
67. {
68. throw ex;
69. }
70. }
71. private void Update()
72. {
73. try
74. {
75. string filename = UploadImages();
76. int id = Convert.ToInt32(hdnValue.Value);
77. var singleRecord = obj.tblDetails.First(dtl => dtl.Id == id);
78. singleRecord.Name = txtName.Text;
79. if (imgFile.HasFile)
80. {
81. singleRecord.ImgPath = filename;
82. }
83. else
84. {
85. singleRecord.ImgPath = hdfHidden.Value;
86. }
87. obj.SubmitChanges();
88. btnSubmit.Text = "Submit";
89. Showmsg("Information Updated Successfulluy");
90. BindGrid();
91. Clear();
92. btnCancel.Visible = false;
93. }
94. catch (Exception ex)
95. {
96. throw ex;
97. }
98. }
99. private void Save()
100. {
101. try
102. {
103. string fileName = UploadImages();
104. tblDetail objDetail = new tblDetail();
105. objDetail.Name = txtName.Text;
106. if (imgFile.HasFile)
107. {
108. objDetail.ImgPath = fileName;
109. }
110. else
111. {
112. objDetail.ImgPath = hdfHidden.Value;
113. }
114. obj.tblDetails.InsertOnSubmit(objDetail);
115. obj.SubmitChanges();
116. Showmsg("Details Inserted Successfully");
117. BindGrid();
118. Clear();
119. }
120. catch (Exception ex)
121. {
122. throw ex;
123. }
124. }
125. private string UploadImages()
126. {
127. string result = string.Empty;
128. if (imgFile.HasFile)
129. {
130. string extension = System.IO.Path.GetExtension(imgFile.PostedFile.FileName);
131. if (extension == ".jpg" || extension == ".png" || extension == ".img")
132. {
133. result = imgFile.PostedFile.FileName;
134. imgFile.PostedFile.SaveAs(MapPath("~") + "/Images/" + result);
135. }
136. }
137. return result;
138. }
139. private bool checkBlank()
140. {
141. try
142. {
143. bool flag = true;
144. string msg = string.Empty;
145. if (!imgFile.HasFile)
146. {
147. msg += "Please Upload File, ";
148. flag = false;
149. }
150. else
151. {
152. if (!(Path.GetExtension(imgFile.PostedFile.FileName).Equals(".jpg") || Path.GetExtension(imgFile.PostedFile.FileName).Equals(".img") || Path.GetExtension(imgFile.PostedFile.FileName).Equals(".png")))
153. {
154. msg += "Please Upload Valid File, ";
155. flag = false;
156. }
157. }
158. if (txtName.Text == string.Empty)
159. {
160. msg += "Please Enter Name, ";
161. flag = false;
162. }
163. if (msg.Length > 0)
164. {
165. msg = msg.Remove(msg.Length - 2);
166. Showmsg(msg);
167. }
168. return flag;
169. }
170. catch (Exception ex)
171. {
172. throw ex;
173. }
174. }
175. private void Showmsg(string Message)
176. {
177. ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Alert", "alert('" + Message + "');", true);
178. }
179. private void Clear()
180. {
181. txtName.Text = "";
182. txtSearch.Text = "";
183. }
184. protected void gvDisplayDetails_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
185. {
186. try
187. {
188. string id = gvDisplayDetails.DataKeys[e.NewSelectedIndex].Value.ToString();
189. hdnValue.Value = id;
190. var singleRecord = obj.tblDetails.Single(dtls => dtls.Id == Convert.ToInt32(id));
191. txtName.Text = singleRecord.Name;
192. hdfHidden.Value = singleRecord.ImgPath;
193. btnSubmit.Text = "Update";
194. btnCancel.Visible = true;
195. }
196. catch (Exception ex)
197. {
198. throw ex;
199. }
200. }
201. protected void gvDisplayDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
202. {
203. try
204. {
205. int Id = Convert.ToInt32(gvDisplayDetails.DataKeys[e.RowIndex].Value);
206. var singleRecord = obj.tblDetails.First(dtl => dtl.Id == Id);
207. obj.tblDetails.DeleteOnSubmit(singleRecord);
208. obj.SubmitChanges();
209. Showmsg("Records Deleted Successfully");
210. BindGrid();
211. }
212. catch (Exception ex)
213. {
214. throw ex;
215. }
216. }
217. protected void gvDisplayDetails_RowCommand(object sender, GridViewCommandEventArgs e)
218. {}
219. protected void btnCancel_Click(object sender, EventArgs e)
220. {
221. try
222. {
223. btnSubmit.Text = "Submit";
224. BindGrid();
225. btnCancel.Visible = false;
226. Clear();
227. }
228. catch (Exception ex)
229. {
230. throw ex;
231. }
232. }
233. protected void btnSearch_Click(object sender, EventArgs e)
234. {
235. try
236. {
237. if (txtSearch.Text != string.Empty && txtSearch.Text != "")
238. {
239. string txt = txtSearch.Text;
240. BindSearch(txt);
241. Clear();
242. }
243. else
244. {
245. Showmsg("Please Enter a Keyword to Search");
246. }
247. }
248. catch (Exception ex)
249. {
250. throw ex;
251. }
252. }
253. private void BindSearch(string name)
254. {
255. try
256. {
257. var results = from details in obj.tblDetails where details.Name.Contains(name)
258. select new
259. {
260. details.Id,
261. details.Name,
262. details.ImgPath
263. };
264. if (results.Any())
265. {
266. gvDisplayDetails.DataSource = results;
267. gvDisplayDetails.DataBind();
268. }
269. else
270. {
271. Showmsg("No Record Found for Particular Keyword");
272. BindGrid();
273. }
274. }
275. catch (Exception ex)
276. {
277. throw ex;
278. }
279. }
280. }
281. }
We can see I have created a object of PersonalDataContext class it contains all entitys that map to tables in databases. On page load on If(!Page.IsPostBack) i have called a method BindGrid() which bind Details to Gridview. Also I have used Any to see if result of LINQ query contains data.
Also there are two method, one for submit and another for update, for submit and update I have used insert on submit and just passed the object ofthe table entity to it. Then submit changes to save changes. For Deletion used Deletion Submit.