GridView – Rich Data Control – This is a highly flexible Grid Control used to render data as tables through rows and columns. It offers many extraordinary features including selection, paging, sorting, and editing of displayed records. Additionally, its functionality can be extended using templates with the GridView Control.
The key feature of the GridView Control, compared to the DataGrid Control, is that it can be used without writing any program logic code. It can render stored data from the underlying database or any other collection effortlessly. With the GridView Control, common tasks like paging and selection don't require writing any code, unlike the DataGrid Control where event handlers need to be created for these tasks. This is why GridView is very popular in the developer community.
In this ASP.NET post, we describe how to perform insert, update, and delete operations using GridView in ASP.NET with ADO.NET. This post is especially for beginners who want to learn coding step by step.
In this tutorial, we will create a GridView in which we perform insert, update, delete, and edit operations. I want to enable the deletion of records from GridView by clicking on the delete button, but I also want to show an alert message 'Are you sure you want to delete?' to achieve all these tasks using GridView.
To start with GridView operations, we need a database. Therefore, I have created a database table named 'Teachers' with columns: Id, Name, and Salary.
Table Script
CREATE TABLE [dbo].[Teachers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](150) NULL,
[Address] [nvarchar](500) NULL,
[Salary] [decimal](18, 2) NULL,
CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]Step 1-Now Let’s Start,Open Visual studio and add empty website.
Step2-Now add a gridview to the page
Step 3-Right-click on gridview and then click on gridview property and double click on the following event of gridview. We will perform an operation on those events.
Step 4 –Some gridview view event which are very useful.
1) OnRowCancelingEdit: The RowCancelingEdit event occurs when the Cancel button of a row in edit mode is clicked.
2) OnRowEditing: The RowEditing event is fired when a row's Edit button is clicked, but before the GridView control enters edit mode.
3) OnRowUpdating: The RowUpdating event is fired when a row's Update button is clicked, but before the GridView control updates the row.
4) OnRowDeleting: The RowDeleting event is fired when a row's Delete button is clicked, but before the GridView control deletes the row.
5) OnRowCommand: The RowCommand event is fired when a button is clicked in the GridView control.
Now event are created on codebehind of page.Now aspx page should be like this:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Index.aspx.cs" Inherits="Index" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowCommand="GridView1_RowCommand" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
</asp:GridView>
</div>
</form>
</body>
</html>
Code Behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Index : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
}
}Now paste the following code between gridview view tag.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Index.aspx.cs" Inherits="Index" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" DataKeyNames="Id,Name" AutoGenerateColumns="False" ShowFooter="True" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowCommand="GridView1_RowCommand" OnRowUpdating="GridView1_RowUpdating" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="Action">
<EditItemTemplate>
<asp:Button ID="btnUpdate" CommandName="Update" runat="server" ToolTip="Update" Height="30px" Width="60px" Text="Update" />
<asp:Button ID="btnCancel" CommandName="Cancel" runat="server" Text="Cancel" ToolTip="Cancel" Height="30px" Width="60px" />
</EditItemTemplate>
<ItemTemplate>
<asp:Button ID="btnEdit" runat="server" CommandName="Edit" Text="Edit" ToolTip="Edit" Height="30px" Width="60px" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete" Height="30px" Width="60px" OnClientClick="return confirm('Are you sure you want to delete this ?');" />
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnAdd" runat="server" Text="AddNew" CommandName="AddNew" Width="120px" Height="30px" ToolTip="Add new User" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<EditItemTemplate>
<asp:Label ID="lbleditName" runat="server" Text='<%#Eval("Name") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblitemName" runat="server" Text='<%#Eval("Name") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<EditItemTemplate>
<asp:TextBox ID="txtSalary" runat="server" Text='<%#Eval("Salary") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblSalary" runat="server" Text='<%#Eval("Salary") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrSalary" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address">
<EditItemTemplate>
<asp:TextBox ID="txtAddress" runat="server" Text='<%#Eval("Address") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblAddress" runat="server" Text='<%#Eval("Address") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrAddress" runat="server" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</div>
</form>
</body>
</html>
Eval function is used to bind data from database to controls inside DataBound controls like GridView, Repeater, etc.
Now Gridview should look like this
Now paste the below code for operation on the event of gridview.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Index : System.Web.UI.Page
{
//Replace you connection strings
SqlConnection con = new SqlConnection("Data Source=SQLEXPRESS01;Initial Catalog=DemoDataBase;User ID=sa;Password=ap@1234");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDataGridView();
//Check page is loaded first time or it's loaded due to button on page
}
}
public void BindDataGridView()
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from Teachers", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow()); // if record not found then returning a blank table structure
GridView1.DataSource = ds;
GridView1.DataBind();
int columncount = GridView1.Rows[0].Cells.Count;
GridView1.Rows[0].Cells.Clear();
GridView1.Rows[0].Cells.Add(new TableCell());
GridView1.Rows[0].Cells[0].ColumnSpan = columncount;
GridView1.Rows[0].Cells[0].Text = "No Records Found";
}
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1; //after cancel button want go to one index back that's y -1
BindDataGridView();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex; //this open new index that is edit mode
BindDataGridView();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int Id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["Id"].ToString()); //finding Datakeynames from gridview
string Name = GridView1.DataKeys[e.RowIndex].Values["Name"].ToString();
con.Open();
SqlCommand cmd = new SqlCommand("delete from Teachers where Id=" + Id, con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindDataGridView();
Response.Write("<script language=javascript>alert('" + Name + "'+'details deleted successfully');</script>");
}
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtUsrname = (TextBox)GridView1.FooterRow.FindControl("txtftrname");
TextBox txtsalary = (TextBox)GridView1.FooterRow.FindControl("txtftrSalary"); //find textbox control on footer with crrosponding id
TextBox txtadress = (TextBox)GridView1.FooterRow.FindControl("txtftrAddress");
con.Open();
SqlCommand cmd = new SqlCommand("insert into Teachers(Name,Salary,Address) values('" + txtUsrname.Text + "','" + txtsalary.Text + "','" + txtadress.Text + "')", con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindDataGridView();
Response.Write("<script language=javascript>alert('" + txtUsrname.Text + "'+'Details inserted successfully');</script>");
}
else
{
Response.Write("<script language=javascript>alert('" + txtUsrname.Text + "'+' Details not inserted');</script>");
}
}
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int userid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
string Name = GridView1.DataKeys[e.RowIndex].Values["Name"].ToString();
TextBox txtSalary = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSalary");
TextBox txtAddress = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAddress");
con.Open();
SqlCommand cmd = new SqlCommand("update Teachers set Salary='" + txtSalary.Text + "',Address='" + txtAddress.Text + "' where Id=" + userid, con);
cmd.ExecuteNonQuery();
con.Close();
Response.Write("<script language=javascript>alert('" + Name + "'+' Details Updated successfully!');</script>");
GridView1.EditIndex = -1;
BindDataGridView();
}
}IsPostBack is a property of the Asp.Net page that checks whether the page is loaded first time
or it is loaded due to button on page.Now run your code and perform gridview operation.
Page Load:
- It checks whether the page is loaded for the first time or due to a postback. If it's the first time, it calls the BindDataGridView method to populate the GridView with data from the "Teachers" table.
BindDataGridView Method:
- This method retrieves data from the "Teachers" table using a SQL query and populates a DataSet with the results.
- If there are records in the DataSet, it binds the data to the GridView (GridView1).
- If no records are found, it adds a new row to the DataSet to display a message ("No Records Found") in the GridView.
GridView Events:
- GridView1_RowCancelingEdit: Cancels editing mode for the GridView.
- GridView1_RowEditing: Puts the GridView in editing mode for the selected row.
- GridView1_RowDeleting: Deletes a record from the database based on the selected row in the GridView.
- GridView1_RowCommand: Handles commands such as adding new records to the GridView.
- GridView1_RowUpdating: Updates a record in the database based on the edited values in the GridView.
Database Operations:
- Database connections are opened and closed as needed.
- SQL queries are executed to perform CRUD operations on the "Teachers" table.
User Interaction:
- JavaScript alerts are used to provide feedback to the user after certain actions, such as successful deletion or insertion of records.
Above code demonstrates how to create a basic CRUD functionality using ASP.NET Web Forms and interact with a SQL Server database to perform data manipulation operations.
Downlaod Source Code
Reseult:
