CRUD Operations Using Entity Framework In ASP.NET

Aniket Narvankar
Dec 28, 2015
8.3k
0
0
Article

Firstly, create a table in database. I have created it with Name Tester including three columns.

Id Primary Key int Not Null.
Name varchar(200)
Email varchar(100)

Now open Visual Studio, Select File then New Project and Add New ASP.NET Web Application.
Now in the Solutions Explorer, right click on the Application then select Add New Item, then Select Data from the left pane, Select ADO.Net Entity Model, Click Add, then select Generate From Database.
Now click Next, then Click New Connection, Provide your Server Name to Connect to Database.
Use SQL Server Authentication, provide your UserName and Password and select the Database Name, Click Ok. Click on Yes, include sensitive data in the connection string. A new Connection String gets added into Web.Config of your Application by DatabaseNameEntities.
Now click Next. Now the next window comes, Choose your Database Objects and Settings that includes what Tables and StoredProcedure, Functions, Views we want to include in our Entity Model.
Now expand the table section in that Window, it will show the List of Tables in the Database, Select our Table, you will be able to see a Table by Name Tester. elect that Table and click Finish.
Now a Design Window appears in which Table has been Added by Name Tester.
Also Look at Solution Explorer, there is a Model with extension .edmx, in my Case it is Employee.Edmx as I have given Name Employee while Adding ADO.NET Entity Model, Expand it and click on Employee.tt which is an Entity Template, after clicking you can see the system has created a class of type Entity Object with name Tester.cs here Tester is the Table Name.
Just click on that Class, we can see three Properties are added, in that class Id, Name and Email which are nothing but Column Name of that Table.
Now right click on Application and click Add New Item and dd WebForm on which we will display data from table. Writing functionality to Insert Data, Update and Delete Data from Database.
On the WebForm include two TextBoxes for Name and Email respectively. A Hidden Field to Store Id Value, which is Primary Key. Include a GridView to bind data and two buttons: Submit and Cancel. Also two spans with chkName and chkEmail have been included which will be displayed if user enters invalid Name or Email in TextBox. The following is the code on Design Page:
1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="CrudEntity.WebForm1" %>
2. <!DOCTYPE html>
3. <html xmlns="http://www.w3.org/1999/xhtml">
5. <head runat="server">
6. <script src="Scripts/jquery-1.10.2.js"></script>
7. <script type="text/javascript" lang="ja">
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")
14. .style.display = "none";
15. return true;
16. }
17. else
18. {
19. document.getElementById("chkName")
20. .style.display = "inline";
21. return false;
22. }
23. }
24. </script>
25. <title></title>
26. </head>
28. <body>
29. <form id="form1" runat="server">
30. <div>
31. <table>
32. <tr>
33. <td> Name : </td>
34. <td>
35. <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>
36. <td> </td>
37. </tr>
38. <tr>
39. <td> Email : </td>
40. <td>
41. <asp:TextBox ID="txtEmail" runat="server"> </asp:TextBox> <span id="chkEmail" style="color: Red; display: none" runat="server">Please Enter Valid Email ID</span> </td>
42. </tr>
43. <tr>
44. <td>
45. <asp:HiddenField ID="hdfId" runat="server" /> </td>
46. </tr>
47. <tr>
48. <td>
49. <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" /> </td>
50. <td>
51. <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" /> </td>
52. </tr>
53. <tr>
54. <td>
55. <asp:GridView ID="gvDisplay" runat="server" AutoGenerateColumns="false" DataKeyNames="Id" OnPageIndexChanging="gvDisplay_PageIndexChanging" OnRowCommand="gvDisplay_RowCommand" OnRowDeleting="gvDisplay_RowDeleting" OnSelectedIndexChanging="gvDisplay_SelectedIndexChanging" AllowPaging="true" PageSize="5">
56. <Columns>
57. <asp:BoundField DataField="Name" HeaderText="Name" />
58. <asp:BoundField DataField="Email" HeaderText="Email" />
59. <asp:CommandField ButtonType="Button" SelectText="Edit" ShowSelectButton="True" />
60. <asp:TemplateField>
61. <ItemTemplate>
62. <asp:Button runat="server" ID="btnDelete" OnClientClick="return confirm('Are you want to Delete this record?');" Text="Delete" CommandName="Delete" /> </ItemTemplate>
63. </asp:TemplateField>
64. </Columns>
65. </asp:GridView>
66. </td>
67. </tr>
68. </table>
69. </div>
70. </form>
71. </body>
73. </html>
Now the GridView gvDisplay has Property DataKeyNames which has been assigned value Id which is the Primary Key in our Table.
Set this Property to specify field that represents Primary Key of the DataSource. This is Set to Field that are required to uniquely identify each row, it will allow automatic update and delete features of GridView Control to work. The value of this field is passed to DataSource control, in order to specify the row to Update or Delete, to retrieve the DataKey when Updating or Deleting a row use the DataKeys property of GridView.
Now on CS side write the following code:
1. using System;
2. using System.Collections.Generic;
3. using System.Configuration;
4. using System.Data.SqlClient;
5. using System.Linq;
6. using System.Text.RegularExpressions;
7. using System.Web;
8. using System.Web.UI;
9. using System.Web.UI.WebControls;
10. namespace CrudEntity
11. {
12. public partial class WebForm1: System.Web.UI.Page
13. {
14. LearningDBEntities context = new LearningDBEntities();
15. protected void Page_Load(object sender, EventArgs e)
16. {
17. if (!Page.IsPostBack)
18. {
19. gvDisplay.Visible = false;
20. btnCancel.Visible = false;
21. BindDisplay();
22. }
23. }
24. private void BindDisplay()
25. {
26. try
27. {
28. if (context.Testers.Count() > 0)
29. {
30. var query = (from test in context.Testers select new
31. {
32. test.Id,
33. test.Name,
34. test.Email
35. })
36. .ToList();
37. gvDisplay.DataSource = query;
38. gvDisplay.DataBind();
39. gvDisplay.Visible = true;
40. }
41. }
42. catch (Exception ex)
43. {
44. throw ex;
45. }
46. }
47. protected void btnSubmit_Click(object sender, EventArgs e)
48. {
49. try
50. {
51. if (CheckBlank())
52. {
53. ValidateEmail();
54. }
55. }
56. catch (Exception ex)
57. {
58. throw ex;
59. }
60. }
61. private bool CheckBlank()
62. {
63. bool flag = true;
64. string message = string.Empty;
65. if (txtEmail.Text == string.Empty)
66. {
67. message += "Please Enter Email, ";
68. flag = false;
69. }
70. if (txtName.Text == string.Empty)
71. {
72. message += "Please Enter Name, ";
73. flag = false;
74. }
75. if (message.Length > 0)
76. {
77. message = message.Remove(message.Length - 2);
78. ShowMessage(message);
79. }
80. return flag;
81. }
82. private void ShowMessage(string Message)
83. {
84. try
85. {
86. ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Alert", "alert('" + Message + "');", true);
87. }
88. catch (Exception ex)
89. {
90. throw ex;
91. }
92. }
93. private void SaveUpdate()
94. {
95. try
96. {
97. Tester test = new Tester();
98. if (btnSubmit.Text.Equals("Submit"))
99. {
100. test.Name = txtName.Text;
101. test.Email = txtEmail.Text;
102. context.Testers.Add(test);
103. context.SaveChanges();
104. ShowMessage("Records Inserted Successfully");
105. }
106. if (btnSubmit.Text.Equals("Update"))
107. {
108. int StudentId = Convert.ToInt32(hdfId.Value);
109. var singleRecord = context.Testers.First(student => student.Id == StudentId);
110. singleRecord.Name = txtName.Text;
111. singleRecord.Email = txtEmail.Text;
112. context.SaveChanges();
113. btnSubmit.Text = "Submit";
114. ShowMessage("Information Updated Successfulluy");
115. btnCancel.Visible = false;
116. }
117. BindDisplay();
118. Clear();
119. }
120. catch (Exception ex)
121. {
122. throw ex;
123. }
124. }
125. private void Clear()
126. {
127. try
128. {
129. txtEmail.Text = "";
130. txtName.Text = "";
131. }
132. catch (Exception ex)
133. {
134. throw ex;
135. }
136. }
137. protected void gvDisplay_PageIndexChanging(object sender, GridViewPageEventArgs e)
138. {
139. try
140. {
141. gvDisplay.PageIndex = e.NewPageIndex;
142. BindDisplay();
143. }
144. catch (Exception ex)
145. {
146. throw ex;
147. }
148. }
149. protected void gvDisplay_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
150. {
151. try
152. {
153. int Id = Convert.ToInt32(gvDisplay.DataKeys[e.NewSelectedIndex].Value);
154. hdfId.Value = Convert.ToString(Id);
155. var singleRecord = context.Testers.First(student => student.Id == Id);
156. txtEmail.Text = singleRecord.Email;
157. txtName.Text = singleRecord.Name;
158. btnSubmit.Text = "Update";
159. btnCancel.Visible = true;
160. }
161. catch (Exception ex)
162. {
163. throw ex;
164. }
165. }
166. protected void gvDisplay_RowCommand(object sender, GridViewCommandEventArgs e)
167. {}
168. protected void gvDisplay_RowDeleting(object sender, GridViewDeleteEventArgs e)
169. {
170. try
171. {
172. int Id = Convert.ToInt32(gvDisplay.DataKeys[e.RowIndex].Value);
173. var singleStudent = context.Testers.First(student => student.Id == Id);
174. context.Testers.Remove(singleStudent);
175. context.SaveChanges();
176. ShowMessage("Records Deleted Successfully");
177. BindDisplay();
178. }
179. catch (Exception ex)
180. {
181. throw ex;
182. }
183. }
184. protected void btnCancel_Click(object sender, EventArgs e)
185. {
186. try
187. {
188. btnSubmit.Text = "Submit";
189. BindDisplay();
190. btnCancel.Visible = false;
191. Clear();
192. }
193. catch (Exception ex)
194. {
195. throw ex;
196. }
197. }
198. private void ValidateEmail()
199. {
200. try
201. {
202. string email = txtEmail.Text;
203. Regex regex = new Regex(@ "^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$");
204. Match match = regex.Match(email);
205. if (!match.Success)
206. {
207. chkEmail.Style["display"] = "inline";
208. }
209. else
210. {
211. chkEmail.Style["display"] = "none";
212. SaveUpdate();
213. }
214. }
215. catch (Exception ex)
216. {
217. throw ex;
218. }
219. }
220. }
221. }
As you can see the events of GridView: SelectedIndexChanging, RowDeleting, RowCommand, PageIndexChanging. You can see the line:
1. int Id = Convert.ToInt32(gvDisplay.DataKeys[e.NewSelectedIndex].Value);
Here, DataKeys property is been used which gives the Id of respective record.

Also, there is a method for saving and updating the data which is SaveUpdate, there is a method ValidateEmail to validate email which is been called on Submit button click. There is a method BindDisplay, which binds the GridView.

A object has been created of LearningDBEntities; LearningDBEntities is a DBContext class where LearningDB is my database name. This is a class derived From DbContext Class.DbContext class is the Primary Class which is responsible for Interacting with data. Also I have created object of Tester Class, Tester is our table name.
1. Tester test = new Tester();

See this line inside SaveUpdate Method,using this we can access properties of Tester Class, which are nothing but column names. Also on saving the data, method used is Add to which we passed the Object test which includes values assigned to columns in the Table. For Delete method is Remove and SaveChanges is been used to save changes in the database.

I will come with more details about Entity Framework in next article. If anything is missing, please let me know about it in the comments section.