CRUD Operation Using Stored Procedure In ASP.NET GridView

 

CRUD Operation Using Stored Procedure In ASP.NET GridView

Introduction

A GridView is a graphical control element that presents a tabular view of data. A typical Grid View also supports some or all of the following:

Clicking a column header to change the sort order of the Grid.

Dragging column headers to change their size and their order.

The GridView control displays the values of a data source in a table. Each column represents a field, while each row represents a record.

• Support to data source controls, such as SqlDataSource.
• Support sort capabilities.
• Support update and delete capabilities.
• Support paging capabilities.
• Support row selection capabilities.
• Code-behind feature access to the GridView object model to dynamically set properties, handle events, and so on.
• Many key fields.
• Many data fields for the hyperlink columns.
• Customized style layout through themes and styles using CSS and javascript.

The following operations can be performed using GridView control in ASP.NET using C# code behind.

• Feature to Bind data to GridView column
• Feature to Edit data in GridView
• Feature to Delete rows from GridView
• Feature to Update row from the database

Note

The most important feature that we can get by using GridView events are — RowEditing, RowUpdating, RowDeleting, RowCommand, RowDataBound, RowCancelingEdit, and Pagination.

Fields

BoundColumn To control the order and rendering of columns.

HyperLinkColumn Presents the bound data in HyperLink controls

ButtonColumn Bubbles a user command from within a row to the grid event handler

TemplateColumn Controls which controls are rendered in the column

CommandField Displays Edit, Update, and Cancel links in response to changes in the

GridView control’s EditItemIndex property.

Details Of Fields

By explicitly creating a BoundColumn in the Grid’s Columns collection, the order and rendering of each column can be controlled. In the BoundField properties, when the DataField and the SortExpressions are given, sorting and rendering the data can be easily done.

A HyperLinkColumn presents the bound data in HyperLink controls. This is typically used to navigate from an item in the grid to a Details view on another page by directly assigning the page URL in NavigationUrl or by rendering it from the database.

With a TemplateColumn, the controls which are rendered in the column and the data fields bound to the controls can be controlled. By using the TemplateColumn, any type of data control can be inserted.

The EditCommandColumn is a special column type that supports in-place editing of the data in one row in the grid. EditCommandColumn interacts with another property of the grid: EditItemIndex. By default, the value of EditItemIndex is -1, meaning none of the rows (items) in the grid are being edited. If EditItemIndex is -1, an “edit” button is displayed in the EditCommandColumn for each of the rows in the grid.

When the “edit” button is clicked, the grid’s EditCommand event is thrown. It’s up to the programmer to handle this event in the code. The typical logic sets EditItemIndex to the selected row, and then rebinds the data to the grid.

When EditItemIndex is set to a particular row, the EditCommandColumn displays “update” and “cancel” buttons for that row (“edit” is still displayed for the other rows). These buttons cause the UpdateCommand and CancelCommand events to be thrown, respectively.

Types of Events

PageIndexChanging event occurs when the property of the grid AllowPaging is set to true, and in the code behind the PageIndexChanging event is fired.

Paging in GridView is enabled by setting AllowPaging to true.

Paging in The GridView provides the means to display a group of records from the data source (for example, the first 20), and then navigates to the “page” containing the next 20 records, and so on through the data.

When enabled, the grid will display page navigation buttons either as “next/previous” buttons or as numeric buttons. When a page navigation button is clicked, the PageIndexChanged event is thrown. It’s up to the programmer to handle this event in the code.

The GridView fires the RowCommand event when any button is pressed. We can give any name as a command name, and based on that, the check will be done and the loop will be executed.

The GridView fires the RowCreate event when a new row is created.

The GridView fires the RowDeleting event when the command name is given as Delete.

The GridView fires the RowUpdating event when the command name is given as Update.

The GridView fires the RowEditing event when the command name is given as Edit.

The GridView fires the RowDatabound event when a data row is bound to data in a GridView control.

Data in a Grid is commonly sorted by clicking the header of the column to sort. Sorting in a DataGrid can be enabled by setting AllowSorting to true. When enabled, the grid renders LinkButton controls in the header for each column. When the button is clicked, the grid’s SortCommand event is thrown. It’s up to the programmer to handle this event in the code. Because DataGrid always displays the data in the same order it occurs in the data source, the typical logic sorts the data source and then rebinds the data to the grid.

Github link project download

check my blog in website

Steps to be followed

Step1

First, create a table named “Employee”.

Code Ref

1. CREATE TABLE [dbo].[Employee](
2. [Id] [int] IDENTITY(1,1) NOT NULL,
3. [FirstName] [varchar](50) NULL,
4. [LastName] [varchar](50) NULL,
5. [PhoneNumber] [nvarchar](15) NULL,
6. [EmailAddress] [nvarchar](50) NULL,
7. [Salary] [decimal](18, 2) NULL,
8. [CreatedDate] [datetime] NULL,
9. )

Step2

Then, create a stored procedure named “Sp_GridCrud”. Here, we are using one stored procedure for multiple operations, like insert, update, delete, and select.

Code Ref

1. CREATE PROC [dbo].[Sp_GridCrud]
2. (
3. @EmpId int=0,@FirstName varchar(50)=Null,@LastName varchar(50)=Null,@PhoneNumber nvarchar(15)=Null,
4. @EmailAddress nvarchar(50)=Null,@Salary decimal=Null,@Event varchar(10)
5. )
6. AS
7. BEGIN
8. IF(@Event=’Select’)
9. BEGIN
10. SELECT * FROM Employee ORDER BY FirstName ASC;
11. END
12. ELSE IF(@Event=’Add’)
13. BEGIN
14. INSERT INTO Employee (FirstName,LastName,PhoneNumber,EmailAddress,Salary,CreatedDate) VALUES(@FirstName,@LastName,@PhoneNumber,@EmailAddress,@Salary,GETDATE());
15. END
16. ELSE IF(@Event=’Update’)
17. BEGIN
18. UPDATE Employee SET FirstName=@FirstName,LastName=@LastName,PhoneNumber=@PhoneNumber,EmailAddress=@EmailAddress,Salary=@Salary where Id=@EmpId;
19. END
20. ELSE
21. BEGIN
22. DELETE FROM Employee WHERE Id=@EmpId;
23. END
24. END

Step3

Create an ASP.NET Web Application named “GridViewDemo”.

Step4

Add some CSS and JavaScript-related files in the below-mentioned folders for form validation purposes in ASP.NET.

Step5

Add a Web Form named “GridViewDemo.aspx”.

Code Ref. Of GridViewDemo.aspx

1. <%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”GridViewDemo.aspx.cs” Inherits=”GridViewDemo.GridViewDemo” %>
2. <!DOCTYPE html>
3. <html xmlns=”http://www.w3.org/1999/xhtml">
4. <head runat=”server”>
5. <title>Satyaprakash Samantaray</title>
6. <% — Botstrap Part — %>
7. <style>
8. .button {
9. background-color: #4CAF50;
10. border: none;
11. color: white;
12. padding: 15px 32px;
13. text-align: center;
14. text-decoration: none;
15. display: inline-block;
16. font-size: 16px;
17. margin: 4px 2px;
18. cursor: pointer;
19. }
20. .DataGridFixedHeader {
21. color: White;
22. font-size: 13px;
23. font-family: Verdana;
24. background-color:yellow
25. }
26. .grid_item {
27. background-color: #E3EAEB;
28. border-width: 1px;
29. font-family: Verdana;
30. border-style: solid;
31. font-size: 12pt;
32. color: black;
33. border: 1px solid black;
34. }
35. .grid_alternate {
36. border-width: 1px;
37. font-family: Verdana;
38. border-style: solid;
39. font-size: 12pt;
40. color: black;
41. background-color: White;
42. }
43. .button4 {
44. border-radius: 9px;
45. }
46. input[type=text], select {
47. width: 40%;
48. padding: 12px 20px;
49. margin: 10px 0;
50. display: inline-block;
51. border: 1px solid #ccc;
52. border-radius: 4px;
53. box-sizing: border-box;
54. font-family: ‘Montserrat’, sans-serif;
55. text-indent: 10px;
56. color: blue;
57. text-shadow: 0 1px 2px rgba(0, 0, 0, 0.3);
58. font-size: 20px;
59. }
60. </style>
61. <% — Botstrap Part — %>
62. <% — Validation Part — %>
63. <link href=”css/template.css” rel=”stylesheet” type=”text/css” />
64. <link href=”css/validationEngine.jquery.css” rel=”stylesheet” type=”text/css” />
65. <script src=”js/jquery-1.6.min.js” type=”text/javascript”></script>
66. <script src=”js/jquery.validationEngine-en.js” type=”text/javascript” charset=”utf-8"></script>
67. <script src=”js/jquery.validationEngine.js” type=”text/javascript” charset=”utf-8"></script>
68. <script type=”text/javascript”>
69. jQuery(document).ready(function () {
70. jQuery(“#form1”).validationEngine();
71. });
72. </script>
73. <% — Validation Part — %>
74. <% — <link rel=”stylesheet” type=”text/css” href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">--%>
75. </head>
76. <body>
77. <form id=”form1" runat=”server”>
78. <fieldset>
79. <legend style=”font-family: Arial Black;background-color:yellow; color:red; font-size:larger;font-style: oblique”>Satyaprakash’s Real-Time Project</legend>
80. <table align=”center”>
81. <tr>
82. <td colspan=”3" align=”center” class=”auto-style1">
83. <strong style=”background-color: Yellow;color: Blue; text-align: center; font-style: oblique”>Satyaprakash’s Real-Time GridView CRUD Using Stored Procedure In Asp.Net</strong>
84. </td>
85. </tr>
86. <tr>
87. <td style=”text-align:center”>
88. <asp:TextBox runat=”server” ID=”txtFirstName” placeholder=”Enter First Name..” ValidationGroup=”add” CssClass=”validate[required]”></asp:TextBox>
89. </td>
90. </tr>
91. <tr>
92. <td style=”text-align:center”>
93. <asp:TextBox runat=”server” ID=”txtLastName” placeholder=”Enter Last Name..” ValidationGroup=”add” CssClass=”validate[required]”></asp:TextBox>
94. </td>
95. </tr>
96. <tr>
97. <td style=”text-align:center”>
98. <asp:TextBox runat=”server” placeholder=”Enter Phone Number..” ID=”txtPhoneNumber” ValidationGroup=”add” CssClass=”validate[required,custom[phone]” ></asp:TextBox>
99. </td>
100. <td></td>
101. </tr>
102. <tr>
103. <td style=”text-align:center”>
104. <asp:TextBox runat=”server” ID=”txtEmailAddress” placeholder=”Enter Email Address..” ValidationGroup=”add” CssClass=”validate[required,custom[email]”></asp:TextBox>
105. </td>
106. </tr>
107. <tr>
108. <td style=”text-align:center”>
109. <asp:TextBox runat=”server” ID=”txtSalary” placeholder=”Enter Salary..” ValidationGroup=”add” CssClass=”validate[required,custom[number]”></asp:TextBox>
110. </td>
111. </tr>
112. <tr>
113. <td colspan=”3" align=”center”>
114. <asp:Button runat=”server” ID=”btnAddEmployee” Text=”Add” OnClick=”btnAddEmployee_Click” class=”button button4" ValidationGroup=”add”/>
115. <asp:Button runat=”server” ID=”btnUpdate” Text=”Update” class=”button button4" OnClick=”btnUpdate_Click”/>
116. <asp:Button runat=”server” ID=”btnReset” Text=”Reset” class=”button button4" OnClick=”btnReset_Click”/>
117. </td>
118. </tr>
119. <tr>
120. <td colspan=”3" align=”center”>
121. <br />
122. <asp:Label runat=”server” ID=”lblMessage”></asp:Label>
123. <br />
124. <br />
125. </td>
126. </tr>
127. <tr>
128. <td colspan=”3">
129. <asp:GridView ID=”grvEmployee” runat=”server” AllowPaging=”true” CellPadding=”2" EnableModelValidation=”True”
130. ForeColor=”red” GridLines=”Both” ItemStyle-HorizontalAlign=”center” EmptyDataText=”There Is No Records In Database!” AutoGenerateColumns=”false” Width=”1100px”
131. HeaderStyle-ForeColor=”blue” OnPageIndexChanging=”grvEmployee_PageIndexChanging” OnRowCancelingEdit=”grvEmployee_RowCancelingEdit” OnRowDeleting=”grvEmployee_RowDeleting” OnRowEditing=”grvEmployee_RowEditing”>
132. <HeaderStyle CssClass=”DataGridFixedHeader” />
133. <RowStyle CssClass=”grid_item” />
134. <AlternatingRowStyle CssClass=”grid_alternate” />
135. <FooterStyle CssClass=”DataGridFixedHeader” />
136. <Columns>
137. <asp:TemplateField HeaderText=”EmpId”>
138. <HeaderStyle HorizontalAlign=”Left” />
139. <ItemStyle HorizontalAlign=”Left” />
140. <ItemTemplate>
141. <asp:Label runat=”server” ID=”lblEmpId” Text=’<%#Eval(“id”) %>’></asp:Label>
142. </ItemTemplate>
143. </asp:TemplateField>
144. <asp:TemplateField HeaderText=”FirstName”>
145. <HeaderStyle HorizontalAlign=”Left” />
146. <ItemStyle HorizontalAlign=”Left” />
147. <ItemTemplate>
148. <asp:Label runat=”server” ID=”lblFirstName” Text=’<%#Eval(“FirstName”) %>’></asp:Label>
149. </ItemTemplate>
150. </asp:TemplateField>
151. <asp:TemplateField HeaderText=”LastName”>
152. <HeaderStyle HorizontalAlign=”Left” />
153. <ItemStyle HorizontalAlign=”Left” />
154. <ItemTemplate>
155. <asp:Label runat=”server” ID=”lblLastName” Text=’<%#Eval(“LastName”) %>’></asp:Label>
156. </ItemTemplate>
157. </asp:TemplateField>
158. <asp:TemplateField HeaderText=”Phone No.”>
159. <HeaderStyle HorizontalAlign=”Left” />
160. <ItemStyle HorizontalAlign=”Left” />
161. <ItemTemplate>
162. <asp:Label runat=”server” ID=”lblPhoneNumber” Text=’<%#Eval(“PhoneNumber”) %>’></asp:Label>
163. </ItemTemplate>
164. </asp:TemplateField>
165. <asp:TemplateField HeaderText=”Email”>
166. <HeaderStyle HorizontalAlign=”Left” />
167. <ItemStyle HorizontalAlign=”Left” />
168. <ItemTemplate>
169. <asp:Label runat=”server” ID=”lblEmailAddress” Text=’<%#Eval(“EmailAddress”) %>’></asp:Label>
170. </ItemTemplate>
171. </asp:TemplateField>
172. <asp:TemplateField HeaderText=”Salary”>
173. <HeaderStyle HorizontalAlign=”Left” />
174. <ItemStyle HorizontalAlign=”Left” />
175. <ItemTemplate>
176. <asp:Label runat=”server” ID=”lblSalary” Text=’<%#Eval(“Salary”) %>’></asp:Label>
177. </ItemTemplate>
178. </asp:TemplateField>
179. <asp:TemplateField HeaderText=”Update”>
180. <HeaderStyle HorizontalAlign=”Left” />
181. <ItemStyle HorizontalAlign=”Left” />
182. <ItemTemplate>
183. <asp:LinkButton runat=”server” ID=”btnEdit” Text=”Edit” CommandName=”Edit” ToolTip=”Click here to Edit the record” />
184. </ItemTemplate>
185. </asp:TemplateField>
186. <asp:TemplateField HeaderText=”Delete”>
187. <HeaderStyle HorizontalAlign=”Left” />
188. <ItemStyle HorizontalAlign=”Left” />
189. <ItemTemplate>
190. <asp:LinkButton runat=”server” ID=”btnDelete” Text=”Delete” CommandName=”Delete” OnClientClick=”return confirm(‘Are You Sure You want to Delete the Record?’);” ToolTip=”Click here to Delete the record” />
191. </span>
192. </ItemTemplate>
193. </asp:TemplateField>
194. </Columns>
195. </asp:GridView>
196. </td>
197. </tr>
198. </table>
199. </fieldset>
200. </form>
201. </body>
202. <br />
203. <br />
204. <footer>
205. <p style=”background-color: Yellow; font-weight: bold; color:blue; text-align: center; font-style: oblique”>© <script> document.write(new Date().toDateString()); </script></p>
206. </footer>
207. </html>

Code for GridViewDemo.aspx

I have added some CSS styles for buttons, textboxes, GridView, etc.

1. <style>
2. .button {
3. background-color: #4CAF50;
4. border: none;
5. color: white;
6. padding: 15px 32px;
7. text-align: center;
8. text-decoration: none;
9. display: inline-block;
10. font-size: 16px;
11. margin: 4px 2px;
12. cursor: pointer;
13. }
14. .DataGridFixedHeader {
15. color: White;
16. font-size: 13px;
17. font-family: Verdana;
18. background-color:yellow
19. }
20. .grid_item {
21. background-color: #E3EAEB;
22. border-width: 1px;
23. font-family: Verdana;
24. border-style: solid;
25. font-size: 12pt;
26. color: black;
27. border: 1px solid black;
28. }
29. .grid_alternate {
30. border-width: 1px;
31. font-family: Verdana;
32. border-style: solid;
33. font-size: 12pt;
34. color: black;
35. background-color: White;
36. }
37. .button4 {
38. border-radius: 9px;
39. }
40. input[type=text], select {
41. width: 40%;
42. padding: 12px 20px;
43. margin: 10px 0;
44. display: inline-block;
45. border: 1px solid #ccc;
46. border-radius: 4px;
47. box-sizing: border-box;
48. font-family: ‘Montserrat’, sans-serif;
49. text-indent: 10px;
50. color: blue;
51. text-shadow: 0 1px 2px rgba(0, 0, 0, 0.3);
52. font-size: 20px;
53. }
54. </style>

Then, I have added code for validation control part using CSS and JavaScript file, as described in Step4.

1. <link href=”css/template.css” rel=”stylesheet” type=”text/css” />
2. <link href=”css/validationEngine.jquery.css” rel=”stylesheet” type=”text/css” />
3. <script src=”js/jquery-1.6.min.js” type=”text/javascript”></script>
4. <script src=”js/jquery.validationEngine-en.js” type=”text/javascript” charset=”utf-8"></script>
5. <script src=”js/jquery.validationEngine.js” type=”text/javascript” charset=”utf-8"></script>
6. <script type=”text/javascript”>
7. jQuery(document).ready(function () {
8. jQuery(“#form1”).validationEngine();
9. });
10. </script>

Then, I added some textboxes and buttons in GridView in Table using td and tr tag.

I added textbox validation using JS and CSS. For example,

1. <tr>
2. <td style=”text-align:center”>
3. <asp:TextBox runat=”server” placeholder=”Enter Phone Number..” ID=”txtPhoneNumber” ValidationGroup=”add” CssClass=”validate[required,custom[phone]” ></asp:TextBox>
4. </td>
5. <td></td>
6. </tr>

Here “CssClass=”validate[required,custom[phone]”” will validate the phone number input as mentioned in “jquery.validationEngine-en.js” of CSS and JS Folder.

I have added some CSS Style to GridView for rows and datas.

1. <asp:GridView ID=”grvEmployee” runat=”server” AllowPaging=”true” AutoGenerateColumns=”false” Width=”1100px”
2. HeaderStyle-ForeColor=”blue” OnPageIndexChanging=”grvEmployee_PageIndexChanging” OnRowCancelingEdit=”grvEmployee_RowCancelingEdit” OnRowDeleting=”grvEmployee_RowDeleting” OnRowEditing=”grvEmployee_RowEditing”>
3. <HeaderStyle CssClass=”DataGridFixedHeader” />
4. <RowStyle CssClass=”grid_item” />
5. <AlternatingRowStyle CssClass=”grid_alternate” />
6. <FooterStyle CssClass=”DataGridFixedHeader” />

This part:

1. <HeaderStyle CssClass=”DataGridFixedHeader” />
2. <RowStyle CssClass=”grid_item” />
3. <AlternatingRowStyle CssClass=”grid_alternate” />
4. <FooterStyle CssClass=”DataGridFixedHeader” />

Then, I bind table columns in GridView.

1. <asp:TemplateField HeaderText=”FirstName”>
2. <HeaderStyle HorizontalAlign=”Left” />
3. <ItemStyle HorizontalAlign=”Left” />
4. <ItemTemplate>
5. <asp:Label runat=”server” ID=”lblFirstName” Text=’<%#Eval(“FirstName”) %>’></asp:Label>
6. </ItemTemplate>
7. </asp:TemplateField>

Then, I have added Edit and Delete link button to update and remove datas.

1. <asp:TemplateField HeaderText=”Update”>
2. <HeaderStyle HorizontalAlign=”Left” />
3. <ItemStyle HorizontalAlign=”Left” />
4. <ItemTemplate>
5. <asp:LinkButton runat=”server” ID=”btnEdit” Text=”Edit” CommandName=”Edit” ToolTip=”Click here to Edit the record” />
6. </ItemTemplate>
7. </asp:TemplateField>
8. <asp:TemplateField HeaderText=”Delete”>
9. <HeaderStyle HorizontalAlign=”Left” />
10. <ItemStyle HorizontalAlign=”Left” />
11. <ItemTemplate>
12. <asp:LinkButton runat=”server” ID=”btnDelete” Text=”Delete” CommandName=”Delete” OnClientClick=”return confirm(‘Are You Sure You want to Delete the Record?’);” ToolTip=”Click here to Delete the record” />
13. </span>
14. </ItemTemplate>
15. </asp:TemplateField>

I have added 3 important gridview events for update and delete purposes.

1. OnPageIndexChanging=”grvEmployee_PageIndexChanging” OnRowCancelingEdit=”grvEmployee_RowCancelingEdit” OnRowDeleting=”grvEmployee_RowDeleting” OnRowEditing=”grvEmployee_RowEditing”

I have added code for empty gridview if there is no data.

1. CellPadding=”2" EnableModelValidation=”True” ForeColor=”red” GridLines=”Both” ItemStyle-HorizontalAlign=”center” EmptyDataText=”There Is No Records In Database!”

Step6

Code Ref. Of GridViewDemo.aspx.cs

1. using System;
2. using System.Collections.Generic;
3. using System.Configuration;
4. using System.Data;
5. using System.Data.SqlClient;
6. using System.Linq;
7. using System.Web;
8. using System.Web.UI;
9. using System.Web.UI.WebControls;
10. namespace GridViewDemo
11. {
12. public partial class GridViewDemo : System.Web.UI.Page
13. {
14. private string strConnectionString = ConfigurationManager.ConnectionStrings[“myconnection”].ConnectionString;
15. private SqlCommand _sqlCommand;
16. private SqlDataAdapter _sqlDataAdapter;
17. DataSet _dtSet;
18. protected void Page_Load(object sender, EventArgs e)
19. {
20. if (!IsPostBack)
21. {
22. BindEmployeeData();
23. }
24. btnUpdate.Visible = false;
25. btnAddEmployee.Visible = true;
26. }
27. private static void ShowAlertMessage(string error)
28. {
29. System.Web.UI.Page page = System.Web.HttpContext.Current.Handler as System.Web.UI.Page;
30. if (page != null)
31. {
32. error = error.Replace(“‘“, “\’”);
33. System.Web.UI.ScriptManager.RegisterStartupScript(page, page.GetType(), “err_msg”, “alert(‘“ + error + “‘);”, true);
34. }
35. }
36. public void CreateConnection()
37. {
38. SqlConnection _sqlConnection = new SqlConnection(strConnectionString);
39. _sqlCommand = new SqlCommand();
40. _sqlCommand.Connection = _sqlConnection;
41. }
42. public void OpenConnection()
43. {
44. _sqlCommand.Connection.Open();
45. }
46. public void CloseConnection()
47. {
48. _sqlCommand.Connection.Close();
49. }
50. public void DisposeConnection()
51. {
52. _sqlCommand.Connection.Dispose();
53. }
54. public void BindEmployeeData()
55. {
56. try
57. {
58. CreateConnection();
59. OpenConnection();
60. _sqlCommand.CommandText = “Sp_GridCrud”;
61. _sqlCommand.CommandType = CommandType.StoredProcedure;
62. _sqlCommand.Parameters.AddWithValue(“@Event”, “Select”);
63. _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);
64. _dtSet = new DataSet();
65. _sqlDataAdapter.Fill(_dtSet);
66. grvEmployee.DataSource = _dtSet;
67. grvEmployee.DataBind();
68. }
69. catch (Exception ex)
70. {
71. Response.Redirect(“The Error is “ + ex);
72. }
73. finally
74. {
75. CloseConnection();
76. DisposeConnection();
77. }
78. }
79. protected void btnAddEmployee_Click(object sender, EventArgs e)
80. {
81. try
82. {
83. CreateConnection();
84. OpenConnection();
85. _sqlCommand.CommandText = “Sp_GridCrud”;
86. _sqlCommand.CommandType = CommandType.StoredProcedure;
87. _sqlCommand.Parameters.AddWithValue(“@Event”, “Add”);
88. _sqlCommand.Parameters.AddWithValue(“@FirstName”, Convert.ToString(txtFirstName.Text.Trim()));
89. _sqlCommand.Parameters.AddWithValue(“@LastName”, Convert.ToString(txtLastName.Text.Trim()));
90. _sqlCommand.Parameters.AddWithValue(“@PhoneNumber”, Convert.ToString(txtPhoneNumber.Text.Trim()));
91. _sqlCommand.Parameters.AddWithValue(“@EmailAddress”, Convert.ToString(txtEmailAddress.Text.Trim()));
92. _sqlCommand.Parameters.AddWithValue(“@Salary”, Convert.ToDecimal(txtSalary.Text));
93. int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
94. if (result > 0)
95. {
96. ShowAlertMessage(“Record Is Inserted Successfully”);
97. BindEmployeeData();
98. ClearControls();
99. }
100. else
101. {
102. ShowAlertMessage(“Failed”);
103. }
104. }
105. catch (Exception ex)
106. {
107. ShowAlertMessage(“Check your input data”);
108. }
109. finally
110. {
111. CloseConnection();
112. DisposeConnection();
113. }
114. }
115. public void ClearControls()
116. {
117. txtFirstName.Text = “”;
118. txtLastName.Text = “”;
119. txtPhoneNumber.Text = “”;
120. txtEmailAddress.Text = “”;
121. txtSalary.Text = “”;
122. }
123. protected void grvEmployee_RowEditing(object sender, GridViewEditEventArgs e)
124. {
125. btnAddEmployee.Visible = false;
126. btnUpdate.Visible = true;
127. int RowIndex = e.NewEditIndex;
128. Label empid = (Label)grvEmployee.Rows[RowIndex].FindControl(“lblEmpId”);
129. Session[“id”] = empid.Text;
130. txtFirstName.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblFirstName”)).Text.ToString();
131. txtLastName.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblLastName”)).Text.ToString();
132. txtPhoneNumber.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblPhoneNumber”)).Text.ToString();
133. txtEmailAddress.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblEmailAddress”)).Text.ToString();
134. txtSalary.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblSalary”)).Text.ToString();
135. }
136. protected void grvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
137. {
138. try
139. {
140. CreateConnection();
141. OpenConnection();
142. Label id = (Label)grvEmployee.Rows[e.RowIndex].FindControl(“lblEmpId”);
143. _sqlCommand.CommandText = “Sp_GridCrud”;
144. _sqlCommand.Parameters.AddWithValue(“@Event”, “Delete”);
145. _sqlCommand.Parameters.AddWithValue(“@EmpId”, Convert.ToInt32(id.Text));
146. _sqlCommand.CommandType = CommandType.StoredProcedure;
147. int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
148. if (result > 0)
149. {
150. ShowAlertMessage(“Record Is Deleted Successfully”);
151. grvEmployee.EditIndex = -1;
152. BindEmployeeData();
153. }
154. else
155. {
156. lblMessage.Text = “Failed”;
157. lblMessage.ForeColor = System.Drawing.Color.Red;
158. BindEmployeeData();
159. }
160. }
161. catch (Exception ex)
162. {
163. ShowAlertMessage(“Check your input data”);
164. }
165. finally
166. {
167. CloseConnection();
168. DisposeConnection();
169. }
170. }
171. protected void grvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
172. {
173. grvEmployee.EditIndex = -1;
174. BindEmployeeData();
175. }
176. protected void grvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
177. {
178. grvEmployee.PageIndex = e.NewPageIndex;
179. BindEmployeeData();
180. }
181. protected void btnReset_Click(object sender, EventArgs e)
182. {
183. ClearControls();
184. }
185. protected void btnUpdate_Click(object sender, EventArgs e)
186. {
187. try
188. {
189. CreateConnection();
190. OpenConnection();
191. _sqlCommand.CommandText = “Sp_GridCrud”;
192. _sqlCommand.CommandType = CommandType.StoredProcedure;
193. _sqlCommand.Parameters.AddWithValue(“@Event”, “Update”);
194. _sqlCommand.Parameters.AddWithValue(“@FirstName”, Convert.ToString(txtFirstName.Text.Trim()));
195. _sqlCommand.Parameters.AddWithValue(“@LastName”, Convert.ToString(txtLastName.Text.Trim()));
196. _sqlCommand.Parameters.AddWithValue(“@PhoneNumber”, Convert.ToString(txtPhoneNumber.Text.Trim()));
197. _sqlCommand.Parameters.AddWithValue(“@EmailAddress”, Convert.ToString(txtEmailAddress.Text.Trim()));
198. _sqlCommand.Parameters.AddWithValue(“@Salary”, Convert.ToDecimal(txtSalary.Text));
199. _sqlCommand.Parameters.AddWithValue(“@EmpId”, Convert.ToDecimal(Session[“id”]));
200. int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
201. if (result > 0)
202. {
203. ShowAlertMessage(“Record Is Updated Successfully”);
204. grvEmployee.EditIndex = -1;
205. BindEmployeeData();
206. ClearControls();
207. }
208. else
209. {
210. ShowAlertMessage(“Failed”);
211. }
212. }
213. catch (Exception ex)
214. {
215. ShowAlertMessage(“Check your input data”);
216. }
217. finally
218. {
219. CloseConnection();
220. DisposeConnection();
221. }
222. }
223. }
224. }

Code Description Of GridViewDemo.aspx.cs: Here I added Ado.Net related namespaces to access ado.net objects to perform crud operation using backend.

1. using System.Data;
2. using System.Data.SqlClient;

Here I added connection string add name.

1. private string strConnectionString = ConfigurationManager.ConnectionStrings[“myconnection”].ConnectionString;

Added ado.net related objects.

1. private SqlCommand _sqlCommand;
2. private SqlDataAdapter _sqlDataAdapter;
3. DataSet _dtSet;

Then in the page load event I used one function definition named “BindEmployeeData();” and Enable Add button and Disable Update button.

1. protected void Page_Load(object sender, EventArgs e)
2. {
3. if (!IsPostBack)
4. {
5. BindEmployeeData();
6. }
7. btnUpdate.Visible = false;
8. btnAddEmployee.Visible = true;
9. }

Then I build this function to bind data with gridview.

1. public void BindEmployeeData()
2. {
3. try
4. {
5. CreateConnection();
6. OpenConnection();
7. _sqlCommand.CommandText = “Sp_GridCrud”;
8. _sqlCommand.CommandType = CommandType.StoredProcedure;
9. _sqlCommand.Parameters.AddWithValue(“@Event”, “Select”);
10. _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);
11. _dtSet = new DataSet();
12. _sqlDataAdapter.Fill(_dtSet);
13. grvEmployee.DataSource = _dtSet;
14. grvEmployee.DataBind();
15. }
16. catch (Exception ex)
17. {
18. Response.Redirect(“The Error is “ + ex);
19. }
20. finally
21. {
22. CloseConnection();
23. DisposeConnection();
24. }
25. }

Then I created a show alert message script to give users messages in javascript format.

1. private static void ShowAlertMessage(string error)
2. {
3. System.Web.UI.Page page = System.Web.HttpContext.Current.Handler as System.Web.UI.Page;
4. if (page != null)
5. {
6. error = error.Replace(“‘“, “\’”);
7. System.Web.UI.ScriptManager.RegisterStartupScript(page, page.GetType(), “err_msg”, “alert(‘“ + error + “‘);”, true);
8. }
9. }

Ado.net objects Create, open, close, and dispose of connection property for connecting database.

1. public void CreateConnection()
2. {
3. SqlConnection _sqlConnection = new SqlConnection(strConnectionString);
4. _sqlCommand = new SqlCommand();
5. _sqlCommand.Connection = _sqlConnection;
6. }
7. public void OpenConnection()
8. {
9. _sqlCommand.Connection.Open();
10. }
11. public void CloseConnection()
12. {
13. _sqlCommand.Connection.Close();
14. }
15. public void DisposeConnection()
16. {
17. _sqlCommand.Connection.Dispose();
18. }

An application can call Close more than one time. No exception is generated. If you called Dispose method SqlConnection object state will be reset. If you try to call any method on disposed of SqlConnection object, you will receive an exception. If you use a connection object one time, use Dispose. If the connection object must be reused, use the Close method. Inside add button click event added code for insert data operation.

1. protected void btnAddEmployee_Click(object sender, EventArgs e)
2. {
3. try
4. {
5. CreateConnection();
6. OpenConnection();
7. _sqlCommand.CommandText = “Sp_GridCrud”;
8. _sqlCommand.CommandType = CommandType.StoredProcedure;
9. _sqlCommand.Parameters.AddWithValue(“@Event”, “Add”);
10. _sqlCommand.Parameters.AddWithValue(“@FirstName”, Convert.ToString(txtFirstName.Text.Trim()));
11. _sqlCommand.Parameters.AddWithValue(“@LastName”, Convert.ToString(txtLastName.Text.Trim()));
12. _sqlCommand.Parameters.AddWithValue(“@PhoneNumber”, Convert.ToString(txtPhoneNumber.Text.Trim()));
13. _sqlCommand.Parameters.AddWithValue(“@EmailAddress”, Convert.ToString(txtEmailAddress.Text.Trim()));
14. _sqlCommand.Parameters.AddWithValue(“@Salary”, Convert.ToDecimal(txtSalary.Text));
15. int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
16. if (result > 0)
17. {
18. ShowAlertMessage(“Record Is Inserted Successfully”);
19. BindEmployeeData();
20. ClearControls();
21. }
22. else
23. {
24. ShowAlertMessage(“Failed”);
25. }
26. }
27. catch (Exception ex)
28. {
29. ShowAlertMessage(“Check your input data”);
30. }
31. finally
32. {
33. CloseConnection();
34. DisposeConnection();
35. }
36. }

For reset controls created one function.

1. public void ClearControls()
2. {
3. txtFirstName.Text = “”;
4. txtLastName.Text = “”;
5. txtPhoneNumber.Text = “”;
6. txtEmailAddress.Text = “”;
7. txtSalary.Text = “”;
8. }

Then I added code for after clicking edit link all data values will go to respected to controls.

1. protected void grvEmployee_RowEditing(object sender, GridViewEditEventArgs e)
2. {
3. btnAddEmployee.Visible = false;
4. btnUpdate.Visible = true;
5. int RowIndex = e.NewEditIndex;
6. Label empid = (Label)grvEmployee.Rows[RowIndex].FindControl(“lblEmpId”);
7. Session[“id”] = empid.Text;
8. txtFirstName.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblFirstName”)).Text.ToString();
9. txtLastName.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblLastName”)).Text.ToString();
10. txtPhoneNumber.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblPhoneNumber”)).Text.ToString();
11. txtEmailAddress.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblEmailAddress”)).Text.ToString();
12. txtSalary.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl(“lblSalary”)).Text.ToString();
13. }

For deleting the link the respective code is mentioned below.

1. protected void grvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
2. {
3. try
4. {
5. CreateConnection();
6. OpenConnection();
7. Label id = (Label)grvEmployee.Rows[e.RowIndex].FindControl(“lblEmpId”);
8. _sqlCommand.CommandText = “Sp_GridCrud”;
9. _sqlCommand.Parameters.AddWithValue(“@Event”, “Delete”);
10. _sqlCommand.Parameters.AddWithValue(“@EmpId”, Convert.ToInt32(id.Text));
11. _sqlCommand.CommandType = CommandType.StoredProcedure;
12. int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
13. if (result > 0)
14. {
15. ShowAlertMessage(“Record Is Deleted Successfully”);
16. grvEmployee.EditIndex = -1;
17. BindEmployeeData();
18. }
19. else
20. {
21. lblMessage.Text = “Failed”;
22. lblMessage.ForeColor = System.Drawing.Color.Red;
23. BindEmployeeData();
24. }
25. }
26. catch (Exception ex)
27. {
28. ShowAlertMessage(“Check your input data”);
29. }
30. finally
31. {
32. CloseConnection();
33. DisposeConnection();
34. }
35. }
36. protected void grvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
37. {
38. grvEmployee.EditIndex = -1;
39. BindEmployeeData();
40. }
41. protected void grvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
42. {
43. grvEmployee.PageIndex = e.NewPageIndex;
44. BindEmployeeData();
45. }

Then in Reset button control, I added the function name.

1. protected void btnReset_Click(object sender, EventArgs e)
2. {
3. ClearControls();
4. }

Inside update button click event I added code to update the existing data.

1. protected void btnUpdate_Click(object sender, EventArgs e)
2. {
3. try
4. {
5. CreateConnection();
6. OpenConnection();
7. _sqlCommand.CommandText = “Sp_GridCrud”;
8. _sqlCommand.CommandType = CommandType.StoredProcedure;
9. _sqlCommand.Parameters.AddWithValue(“@Event”, “Update”);
10. _sqlCommand.Parameters.AddWithValue(“@FirstName”, Convert.ToString(txtFirstName.Text.Trim()));
11. _sqlCommand.Parameters.AddWithValue(“@LastName”, Convert.ToString(txtLastName.Text.Trim()));
12. _sqlCommand.Parameters.AddWithValue(“@PhoneNumber”, Convert.ToString(txtPhoneNumber.Text.Trim()));
13. _sqlCommand.Parameters.AddWithValue(“@EmailAddress”, Convert.ToString(txtEmailAddress.Text.Trim()));
14. _sqlCommand.Parameters.AddWithValue(“@Salary”, Convert.ToDecimal(txtSalary.Text));
15. _sqlCommand.Parameters.AddWithValue(“@EmpId”, Convert.ToDecimal(Session[“id”]));
16. int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
17. if (result > 0)
18. {
19. ShowAlertMessage(“Record Is Updated Successfully”);
20. grvEmployee.EditIndex = -1;
21. BindEmployeeData();
22. ClearControls();
23. }
24. else
25. {
26. ShowAlertMessage(“Failed”);
27. }
28. }
29. catch (Exception ex)
30. {
31. ShowAlertMessage(“Check your input data”);
32. }
33. finally
34. {
35. CloseConnection();
36. DisposeConnection();
37. }
38. }

In every link button event and button click event, I added the show alert function, as mentioned below.

1. ShowAlertMessage(“Check your input data”);

Step7

I have added connection string in Web.Config file.

Code Ref

1. <connectionStrings>
2. <add name=”myconnection” connectionString=”Put Your Connection String Here.” providerName=”System.Data.SqlClient”/>
3. </connectionStrings>

Code Description

Here is the additional name “add name=”myconnection”” , I added in my code-behind file.

OUTPUT

Url Is — http://localhost:24319/GridViewDemo.aspx

Check for grid view if no data is there.

Without any input click Add button.

Then put some dummy data then Click the reset button to clear all images.

Then Check for valid phone no. and email address.

Then enter all valid data and click add.

Then check for the backend after inserting.

Then click on the edit link in grid view and update data.

Then check for the backend after updating the email address and created date will show the date-time in which the user inserted and updated and deleted.

Then click on the delete link.

Then check for backend after delete.