CRUD Operation in ASP.Net GridView using DataBase
CRUD ie. Create,read,update and delete in gridview with SQL Server database
backend in ASP.Net.The GridView has a
CommandField column which will display the command buttons i.e. Edit, Update,
Cancel and Delete. Below the GridView there’s a Form which will allow us to
insert data to the SQL Server database table.
Database:
Source code:
<%@ Page Language="C#"
AutoEventWireup="true" CodeBehind="CrudNew.aspx.cs"
Inherits="rememberme.CrudNew" %>
<!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" Width = "750px"
AutoGenerateColumns = "false"
Font-Names = "Arial"
Font-Size = "11pt"
AlternatingRowStyle-BackColor = "#A1DCF2"
HeaderStyle-BackColor = "#006699" HeaderStyle-ForeColor="White"
AllowPaging ="true" ShowFooter
= "false"
OnPageIndexChanging = "OnPaging"
onrowediting="Editrow"
onrowupdating="Updaterow" onrowcancelingedit="Cancelrow"
PageSize = "10" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
>
<FooterStyle BackColor="White"
ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True"
ForeColor="white"/>
<PagerStyle BackColor="White" ForeColor="#000066"
HorizontalAlign="Left" />
<RowStyle BackColor="#A1DCF2"/>
<SelectedRowStyle BackColor="#669999"
Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
<Columns>
<asp:TemplateField ItemStyle-Width = "100px"
Visible="false" HeaderText =
"SL No">
<ItemTemplate>
<asp:Label ID="lblid" runat="server"
Text='<%# Eval("id")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtid" Width = "40px"
MaxLength = "5"
runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="Label1" runat="server"
Text="SI.No"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width = "150px" HeaderText = "Dept">
<ItemTemplate>
<asp:Label ID="lbldept" runat="server"
Text='<%# Eval("dept")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtdept" runat="server"
Text='<%# Eval("dept")%>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True"
ItemStyle-Width = "150px" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkRemove" runat="server"
CommandArgument = '<%# Eval("id")%>'
OnClientClick = "return confirm('Do you want to delete?')"
Text = "Delete" OnClick =
"Deleterow"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<AlternatingRowStyle
BackColor="white" />
</asp:GridView>
</div>
</form>
</body>
</html>
C# Code:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace rememberme
{
public
partial class CrudNew : System.Web.UI.Page
{
string constr =
ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}
private void BindData()
{
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand cmd2 = new SqlCommand("select * from dept", con);
{
con.Open();
SqlDataAdapter da = new
SqlDataAdapter(cmd2);
DataSet ds = new DataSet();
da.Fill(ds);
using (SqlDataReader sdr =
cmd2.ExecuteReader())
{
if (sdr.HasRows)
{
GridView1.Visible =
true;
GridView1.DataSource
= ds;
GridView1.DataBind();
con.Close();
}
else
{
}
}
}
}
}
protected void Deleterow(object sender, EventArgs e)
{
int Id = int.Parse((sender as LinkButton).CommandArgument);
LinkButton lnkRemove = (LinkButton)sender;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("delete from dept where
id=@id", con);
cmd.Parameters.AddWithValue("@id", Id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
BindData();
}
protected void GrdvwSaldetails_SelectedIndexChanged(object sender,
EventArgs e)
{
}
protected void Editrow(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}
protected void Updaterow(object sender, GridViewUpdateEventArgs e)
{
string Id = ((Label)GridView1.Rows[e.RowIndex]
.FindControl("lblid")).Text;
string dept = ((TextBox)GridView1.Rows[e.RowIndex]
.FindControl("txtdept")).Text;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new
SqlCommand("update dept set dept=@dept where id =@id ", con);
cmd.Parameters.AddWithValue("@dept", dept);
cmd.Parameters.AddWithValue("@id", Id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
GridView1.EditIndex = -1;
GridView1.DataBind();
BindData();
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
BindData();
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void Cancelrow(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs
e)
{
}
}
}
Output:
Figure(1) GridView displays all records.
Figure(2) When the Edit Button is clicked, the GridView’s OnRowEditing event handler is
triggered. EditIndex of
the GridView is updated with the Row Index of the GridView Row to be
edited. Update Button is clicked, the GridView’s OnRowUpdating event
handler is triggered. If user select cancel button, EditIndex is set to -1 and the GridView is
populated with data.
Figure(3) After clicking update button,the Gridview will affect the row depend on User's change.
Figure(4) When the Delete Button is clicked, the GridView’s OnRowDeleting event handler is triggered.
Figure(5)Finally, One Row deleted successfully.
