Asp.net grid CRUD with ADO.NET

Asp.net grid CRUD with ADO.NET

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 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 given any name as 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.

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.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
10. (  
11.     [Id] ASC  
12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
13. ) ON [PRIMARY]  
14.   
15. GO 

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.   
13.     ELSE IF(@Event='Add')  
14.     BEGIN  
15.     INSERT INTO Employee (FirstName,LastName,PhoneNumber,EmailAddress,Salary,CreatedDate) VALUES(@FirstName,@LastName,@PhoneNumber,@EmailAddress,@Salary,GETDATE());  
16.     END  
17.   
18.     ELSE IF(@Event='Update')  
19.     BEGIN  
20.     UPDATE Employee SET FirstName=@FirstName,LastName=@LastName,PhoneNumber=@PhoneNumber,EmailAddress=@EmailAddress,Salary=@Salary where Id=@EmpId;  
21.     END  
22.   
23.     ELSE  
24.     BEGIN  
25.     DELETE FROM Employee WHERE Id=@EmpId;  
26.     END  
27. END  
28.   
29. GO 

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

Code for GridViewDemo.aspx

I have added some CSS style for buttons , textboxes, and 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.      
22.     background-color: #E3EAEB;  
23.     border-width: 1px;  
24.     font-family: Verdana;  
25.     border-style: solid;  
26.     font-size: 12pt;  
27.     color: black;  
28.     border: 1px solid black;  
29. }  
30.         .grid_alternate {  
31.     border-width: 1px;  
32.     font-family: Verdana;  
33.     border-style: solid;  
34.     font-size: 12pt;  
35.     color: black;  
36.     background-color: White;  
37. }  
38.   
39.         .button4 {  
40.             border-radius: 9px;  
41.         }  
42.   
43.        
44.         input[type=text], select {  
45.         width: 40%;  
46.         padding: 12px 20px;  
47.         margin: 10px 0;  
48.         display: inline-block;  
49.         border: 1px solid #ccc;  
50.         border-radius: 4px;  
51.         box-sizing: border-box;  
52.         font-family: 'Montserrat', sans-serif;    
53.         text-indent: 10px;    
54.         color: blue;    
55.         text-shadow: 0 1px 2px rgba(0, 0, 0, 0.3);    
56.         font-size: 20px;    
57.     }  
58.     </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.   
7. <script type="text/javascript">  
8.     jQuery(document).ready(function () {  
9.         jQuery("#form1").validationEngine();  
10.     });  
11. </script> 

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

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

1. <tr>  
2.                          
3.                         <td style="text-align:center">  
4.                             <asp:TextBox runat="server" placeholder="Enter Phone Number.." ID="txtPhoneNumber" ValidationGroup="add" CssClass="validate[required,custom[phone]" ></asp:TextBox>  
5.                         </td>  
6.                         <td></td>  
7. </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.                                           
8.                                     </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.                                          
8.                                     </asp:TemplateField>  
9.                                     <asp:TemplateField HeaderText="Delete">  
10.                                         <HeaderStyle HorizontalAlign="Left" />  
11.                                         <ItemStyle HorizontalAlign="Left" />  
12.                                         <ItemTemplate>                                                                          
13.                                                 <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" />  
14.                                             </span>  
15.                                         </ItemTemplate>                                         
16.                                     </asp:TemplateField>   

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

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

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 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.             }  
8.             btnUpdate.Visible = false;  
9.             btnAddEmployee.Visible = true;  
10.         } 

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 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 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 SqlConnection object, you will receive exception. If you use connection object one time, use Dispose. If connection object must be reused, use 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.                       
19.                     ShowAlertMessage("Record Is Inserted Successfully");  
20.                     BindEmployeeData();  
21.                     ClearControls();  
22.                 }  
23.                 else  
24.                 {  
25.                       
26.                     ShowAlertMessage("Failed");  
27.                 }  
28.             }  
29.             catch (Exception ex)  
30.             {  
31.                   
32.                 ShowAlertMessage("Check your input data");  
33.                 
34.             }  
35.             finally  
36.             {  
37.                 CloseConnection();  
38.                 DisposeConnection();  
39.             }  
40.         }   

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 click 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.              
6.                 int RowIndex = e.NewEditIndex;  
7.                 Label empid = (Label)grvEmployee.Rows[RowIndex].FindControl("lblEmpId");  
8.                 Session["id"] = empid.Text;  
9.                  
10.                 txtFirstName.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl("lblFirstName")).Text.ToString();  
11.                 txtLastName.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl("lblLastName")).Text.ToString();  
12.                 txtPhoneNumber.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl("lblPhoneNumber")).Text.ToString();  
13.                 txtEmailAddress.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl("lblEmailAddress")).Text.ToString();  
14.                 txtSalary.Text = ((Label)grvEmployee.Rows[RowIndex].FindControl("lblSalary")).Text.ToString();  
15.            
16.         } 

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.                       
16.                     ShowAlertMessage("Record Is Deleted Successfully");  
17.                     grvEmployee.EditIndex = -1;  
18.                     BindEmployeeData();  
19.                 }  
20.                 else  
21.                 {  
22.                     lblMessage.Text = "Failed";  
23.                     lblMessage.ForeColor = System.Drawing.Color.Red;  
24.                     BindEmployeeData();  
25.                 }  
26.             }  
27.             catch (Exception ex)  
28.             {  
29.                  
30.                 ShowAlertMessage("Check your input data");  
31.             }  
32.             finally  
33.             {  
34.                 CloseConnection();  
35.                 DisposeConnection();  
36.             }  
37.         }  
38. protected void grvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
39.         {  
40.             grvEmployee.EditIndex = -1;  
41.             BindEmployeeData();  
42.         }  
43.   
44.         protected void grvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)  
45.         {  
46.             grvEmployee.PageIndex = e.NewPageIndex;  
47.             BindEmployeeData();  
48.         } 

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

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 add name “add name="myconnection"” , I added in my code behind file.

OUTPUT
Url Is - http://localhost:50023/GridViewDemo.aspx

Check for grid view if no data is there.

Without any input click Add button.

Then put some dummy data then Click 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 backend after inserting.

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

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

Then click on delete link.

Then check for backend after delete.

Gif Images for better understanding.

Validation and Reset.

Update , insert, Delete.