Introduction: In this article I am going to explain with example How to bind, delete items and delete multiple selected items/ records/ rows from Grid View in asp.net using both C# and VB.Net languages.
Description: Basically you will learn the following through this article.
Implementation: Let’s create a sample web application to understand the concept practically.
 |
| Click on the image to enlarge |
- How to bind GridView from Sql server database table.
- How to delete items/records/rows from grid view.
- How to show Checkbox with each row/record in GridView
- How to delete multiple records from GridView by selecting multiple items using CheckBox in grid view.
- How to implement the Confirmation before Deleting gridview records
- How to implement Paging in GridView
Implementation: Let’s create a sample web application to understand the concept practically.
Source Code:
- In the design page (.aspx) place GridView control and set it as:
<div>
<fieldset style="width:415px;">
<legend>Bind,Delete,Multiple delete example in gridview</legend>
<asp:GridView ID="grdEmp" runat="server" AllowSorting="True" ShowFooter="true"
DataKeyNames="Emp_Id" CssClass="rowHover" RowStyle-CssClass="rowHover"
AutoGenerateColumns="False"
EmptyDataText="No records found"
AllowPaging="True" onrowdeleting="grdEmp_RowDeleting"
onpageindexchanging="grdEmp_PageIndexChanging"
PageSize="10"
CellPadding="4" ForeColor="#333333"
GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField HeaderText="Emp Name" DataField="EmpName" />
<asp:BoundField HeaderText="Age" DataField="Age" />
<asp:BoundField HeaderText="Salary" DataField="Salary" />
<asp:BoundField HeaderText="Address" DataField="Address" />
<asp:TemplateField HeaderText="Delete" HeaderStyle-HorizontalAlign="Center"ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:ImageButton ID="imgDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png" OnClientClick="return confirm('Are you sure you want to delete selected record ?')" ToolTip="Delete"/>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Multiple Delete" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:CheckBox ID="chkDel" runat="server" />
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnDelete" runat="server" Text="Multiple Delete"
OnClientClick="return confirm('Are you sure you want to delete selected records?')"
onclick="btnDelete_Click" />
</FooterTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#ffffff" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle CssClass="rowHover" BackColor="#F7F6F3" ForeColor="#333333"></RowStyle>
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333"/>
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
<EmptyDataRowStyle Width = "410px" ForeColor="Red" Font-Bold="true"
HorizontalAlign = "Center"/>
</asp:GridView>
</fieldset>
</div>
Note: Create a folder in the root directory and name it “Images” and place the “delete image icon” in this folder. You can search on google for the term “delete png icons”.
- Now Create a Database in Sql server e.g. “Emp_DB” and create a Table with the columns and the Data type as shown below and name it “Emp_Tb”.
Column Name
|
Data Type
|
Emp_Id
|
Int( Primary Key. So set Is Identity=True)
|
EmpName
|
varchar(100)
|
Age
|
int
|
Salary
|
int
|
Address
|
varchar(500)
|
- Create a stored procedure to fetch the employee records and bind gridview
CREATE PROCEDURE BindEmpGrid_Sp
AS
BEGIN
SELECT * FROM Emp_Tb
END
- Create a stored procedure to delete the employee records
CREATE PROCEDURE DeleteEmpRecord_Sp
@EmpId INT
AS
BEGIN
DELETE FROM Emp_Tb WHERE Emp_Id=@EmpId
END
- In the web.con fig file create the connection string to connect the asp.net web application with the Sql server database.
<connectionStrings>
<add name="con" connectionString="Data Source=Lalit;Initial Catalog=Emp_DB;Integrated Security=True"/>
</connectionStrings>
Note: Replace the Data Source and Initial catalog (i.e. Database Name) as per your application.
C#.Net Code to bind,delete and multiple deletion using checkbox from gridview
- In the code behind file (.aspx.cs) write the code as:
First include the required namespaces and then write the below mentioned code:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (!Page.IsPostBack)
{
BindEmpGrid();
}
}
#region "Bind GridView"
private void BindEmpGrid()
{
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
adp = new SqlDataAdapter("BindEmpGrid_Sp", con);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
grdEmp.DataSource = dt;
grdEmp.DataBind();
}
else
{
grdEmp.DataSource = null;
grdEmp.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
dt.Clear();
dt.Dispose();
adp.Dispose();
con.Close();
}
}
#endregion
#region "GridView Paging"
protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdEmp.PageIndex = e.NewPageIndex;
BindEmpGrid();
}
#endregion
#region "Deletion in gridview"
protected void grdEmp_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlCommand cmd = new SqlCommand();
try
{
//get EmpId from DatakeyNames from gridview
int empId = Convert.ToInt32(grdEmp.DataKeys[e.RowIndex].Value);
cmd = new SqlCommand("DeleteEmpRecord_Sp", con);
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
BindEmpGrid();
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(),Guid.NewGuid().ToString(), "alert('Record has been deleted successfully');", true);
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
cmd.Dispose();
con.Close();
}
}
#endregion
#region "To delete multiple record"
protected void btnDelete_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
try
{
//Loop through all the rows in gridview
foreach (GridViewRow grv in grdEmp.Rows)
{
//Finiding checkbox control in gridview for particular row
CheckBox chk = (CheckBox)grv.FindControl("chkDel");
if (chk.Checked)
{
//get EmpId from DatakeyNames from gridview
int empid = Convert.ToInt32(grdEmp.DataKeys[grv.RowIndex].Value);
cmd = new SqlCommand("DeleteEmpRecord_Sp", con);
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empid;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
grdEmp.EditIndex = -1;
BindEmpGrid();
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(),Guid.NewGuid().ToString(), "alert('Selected Records has been deleted successfully');", true);
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
cmd.Dispose();
con.Close();
}
}
#endregion
No comments:
Post a Comment