Add Checkboxes to ASP.NET GridView Control

 
In this article, I am going to demonstrate how to add checkbox columns to the grid view and delete selected (checked) items in from the database.  I am not going to cover any basics in this article. If you are new to using grid view or if you would like know more about grid view, I would recommend you to reach following tutorials:

Using GridView in ASP.NET & C# —PART 1
Using GridView in ASP.NET & C# —PART 2

Overview:
I am going to create a simple Product table with some records. When page is initially loaded, we retrieve products from Production.Product table and bind them to the grid. We display some product attributes such as Product ID, Product Number, Product Name, and List Price.  User can select one or more products and delete them from the database.  We also give them an option of CheckALL & UnCheckAll features to help him in check/uncheck all items in the grid. I am going to enable built-in GridView paging, but not going to explain about it. If you are not aware of this gridview functionality, you could refer to Default Paging in ASP.NET GridView Control

Database Connection

Create a new ASP.NET Web Application project. Open web.config and add following entry under connectionStrings element. I have created a “TEST” database & testuser for my example.

<add name="Sql" connectionString="Data Source={SERVER_NAME};
Initial Catalog=TEST; User=testuser; Password=testuser;" 
providerName="System.Data.SqlClient"/> 

Page Design

Create a new ASP.NET web page. Rename Default.aspx to DeleteProductsView.aspx and update any references.  We don’t want a fancy grid, so just applying some basic formatting.  Page is enabled by setting AllowPaging flag to true and setting the page size.  When user clicks one of the pager buttons is clicked, PageIndexChanging event is raised. We have to implement this event handler. Since it occurs before the GridView control handles the paging operation, we need to set the new page index to the grid and rebind the data as it is a postback.

<asp:gridview id="gvProducts"        
PageSize="5" AllowPaging="True"  
autogeneratecolumns="False"
BorderColor="White" BorderStyle="Ridge"
CellSpacing="1" CellPadding="3" GridLines="None" 
BackColor="White" BorderWidth="2px"
emptydatatext="No data available."               
runat="server" DataKeyNames="ProductID"
OnPageIndexChanging="gvProducts_PageIndexChanging">

protected void gvProducts_PageIndexChanging(object sender, 
                                            GridViewPageEventArgs e)
{
    gvProducts.PageIndex = e.NewPageIndex;
    BindData();
}

We are not allowing user to perform any updates. So we render ProductID Product Number, Product Name & Price in BoundColumns.  

<asp:BoundField DataField="ProductID" HeaderText="Product ID">
    <HeaderStyle HorizontalAlign="Left"  Width="150px"/>
</asp:BoundField>
<asp:BoundField DataField="ProductNumber" HeaderText="Product Number">
    <HeaderStyle HorizontalAlign="Left"  Width="150px"/>
</asp:BoundField>
 <asp:TemplateField headertext="Product Name">
    <HeaderStyle HorizontalAlign="Left" Width="200px" />
    <ItemStyle HorizontalAlign="Left" Width="200px"/>
    <ItemTemplate> 
        <asp:Label id="lblProductName" 
               text='<%#Eval("ProductName")%>'runat="server"/>  
    </ItemTemplate>
</asp:TemplateField>
 <asp:TemplateField headertext="Price">
    <HeaderStyle HorizontalAlign="Right" Width="80" />
    <ItemStyle HorizontalAlign="Right" Width="80" />
     <ItemTemplate> <%#Eval("ListPrice","{0:c}")%></ItemTemplate>
</asp:TemplateField>

A checkbox control is added to the grid rendered in a TemplateField as shown below. User would select this checkbox and hits delete to delete a product.

<asp:TemplateField>
    <ItemTemplate>
        <asp:CheckBox ID="cbSelProduct" runat="server"/>
    </ItemTemplate>
</asp:TemplateField>

In order to support the three actions (Delete, CheckAll, UnCheckAll) mentioned in the overview, added 3 link buttons to the page.

<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="Delete"/>
<asp:Button ID="btnCheckAll" runat="server" Text="Check All" 
                                    OnClick="CheckAll" />
<asp:Button ID="btnUnCheckAll" runat="server" Text="UnCheck All" 
                                    OnClick="UnCheckAll" />

Added a message label to notify user about the details of product he has deleted.

<i><asp:Label ID="lblMessage" runat="server"></asp:Label></i>

Since we set the AllowPaging property of the GridView control to true, the GridView control automatically adds user interface (UI) controls for paging. We can customize the UI for paging by adding a PagerTemplate template. If you combine everything together, you get your final design.

<asp:gridview id="gvProducts"
PageSize="5" AllowPaging="True"  
autogeneratecolumns="False"
BorderColor="White" BorderStyle="Ridge"
CellSpacing="1" CellPadding="3" GridLines="None" 
BackColor="White" BorderWidth="2px"
emptydatatext="No data available." 
runat="server" DataKeyNames="ProductID"
OnPageIndexChanging="gvProducts_PageIndexChanging">
<Columns>
    <asp:TemplateField>
    <ItemTemplate>
        <asp:CheckBox ID="cbSelProduct" runat="server"/>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="ProductID" HeaderText="Product ID"
                    ReadOnly="true">
    <HeaderStyle HorizontalAlign="Left"  Width="150px"/>
    </asp:BoundField>
    <asp:BoundField DataField="ProductNumber" HeaderText="Product Number"
                    ReadOnly="true">
    <HeaderStyle HorizontalAlign="Left"  Width="150px"/>
    </asp:BoundField>
    <asp:TemplateField headertext="Product Name">
    <HeaderStyle HorizontalAlign="Left" Width="200px" />
    <ItemStyle HorizontalAlign="Left" Width="200px"/>
    <ItemTemplate> 
        <asp:Label id="lblProductName" 
                text='<%#Eval("ProductName")%>'runat="server"/>
    </ItemTemplate>                    
    </asp:TemplateField>
    <asp:TemplateField headertext="Price">
    <HeaderStyle HorizontalAlign="Right" Width="80" />
    <ItemStyle HorizontalAlign="Right" Width="80" />
     <ItemTemplate> <%#Eval("ListPrice","{0:c}")%></ItemTemplate>
    </asp:TemplateField>
</Columns>
<PagerTemplate>
    <table>
    <tr>   
  <td><asp:LinkButton ID="FirstButton" CommandName="Page"
        CommandArgument="First" Text="First" RunAt="server"/></td>
  <td><asp:LinkButton ID="PrevButton"  CommandName="Page" 
        CommandArgument="Prev"  Text="Previous"  RunAt="server"/></td>
  <td><asp:LinkButton ID="NextButton"  CommandName="Page" 
        CommandArgument="Next"  Text="Next"  RunAt="server"/></td>
  <td><asp:LinkButton ID="LastButton"  CommandName="Page" 
        CommandArgument="Last"  Text="Last" RunAt="server"/></td>
    </tr>
</table>
</PagerTemplate>
</asp:gridview>
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="Delete"/>
&nbsp;<asp:Button ID="btnCheckAll" runat="server" Text="Check All"
                                             OnClick="CheckAll" />
&nbsp;<asp:Button ID="btnUnCheckAll" runat="server" Text="UnCheck All"
                                              OnClick="UnCheckAll" />
<br />
<i><asp:Label ID="lblMessage" runat="server"></asp:Label></i>

Source Code:
When page is first requested, Page_Load event is one of the events that get fired on the server side. If the load is not because of any web control event, we fetch products from database and will save it in the Viewstate for subsequent page refreshes.

protected void Page_Load(object sender, EventArgs e)
{
    if(!Page.IsPostBack)
        BindData();                
}
private void BindData()
{
    //Bind the grid view
    gvProducts.DataSource = RetrieveProducts();
    gvProducts.DataBind();
}
private DataSet RetrieveProducts()
{
    //if products is available in the viewstate, use that 
    //instead of fetching again from the database
    if (ViewState["Products"] != null)
        return (DataSet)ViewState["Products"];
    //fetch the connection string from web.config
    string connString = 
        ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    //SQL statement to fetch entries from products
    string sql = @"Select P.ProductID, P.Name as ProductName, 
            P.ProductNumber, ListPrice from tblProduct P";
    DataSet dsProducts = new DataSet();
    //Open SQL Connection
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        //Initialize command object
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            //Fill the result set
            adapter.Fill(dsProducts);
        }
    }
    ViewState["Products"] = dsProducts;
    return dsProducts;
}

Select the second and third product and click on “Delete” button.  “Delete” event handler is called. In this method we iterate through all the rows and will check if the checkbox is checked. If yes, we add the data key (Product ID) to the list of products to be deleted. Once we run through all the records, we delete them from the database in single execution. We make a list of deleted products information and will display it to the user.

protected void Delete(object sender, EventArgs e)
{
    Hashtable productList = new Hashtable();
    foreach (GridViewRow row in gvProducts.Rows)
    {
        // Get the checkbox control that contain the user selection.
        CheckBox cbSelProducts = (CheckBox)row.FindControl("cbSelProduct");
        if (cbSelProducts != null && cbSelProducts.Checked)
        {
            Label lblProductName = (Label)row.FindControl("lblProductName");
            /// Get the product id of the selected product
         productList.Add(
                gvProducts.DataKeys[row.RowIndex].Value.ToString(),
                lblProductName.Text);
        }
    }
    if (productList.Count > 0)
        DeleteProducts(productList);
    BindData();
}
private void DeleteProducts(Hashtable productIDList)
{
    string productIDs = ToDelimString(productIDList, ",");
    //fetch the connection string from web.config
    string connString = 
        ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    //SQL statement to delete from products
    string sql = String.Format(@"Delete from tblProduct 
                            where ProductID in ({0})",
                                    productIDs);
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        //Initialize command object
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
    lblMessage.Text = "Following products were deleted:";
    foreach (string key in productIDList.Keys)
    {
        lblMessage.Text += String.Format("<br/> ProductID: {0};
                      ProductName: {1}", key, productIDList[key]);
    }
    ViewState["Products"] = null;
    BindData();
}
public static string ToDelimString(Hashtable input, string delimiter)
{
    return string.Join(delimiter, (from string name in input.Keys 
                            select name).ToArray());
}

If you want to select all products at once, click on “CheckALL”. If you want to unselect all, click on “UnCheck All”. We iterate through all the grid rows and will set the checked property of the checkbox to true or false.

protected void CheckAll(object sender, EventArgs e)
{            
    foreach (GridViewRow row in gvProducts.Rows)
    {
        // Get the checkbox control that contain the user selection. 
        CheckBox cbSelProducts = (CheckBox)row.FindControl("cbSelProduct");
        if (cbSelProducts != null)
            cbSelProducts.Checked = true;
    }            
}
protected void UnCheckAll(object sender, EventArgs e)
{
    foreach (GridViewRow row in gvProducts.Rows)
    {
        // Get the checkbox control that contain the user selection. 
        CheckBox cbSelProducts = (CheckBox)row.FindControl("cbSelProduct");
        if (cbSelProducts != null)
            cbSelProducts.Checked = false;
    }
}

SQL Statements:

I have created a tblProduct table and loaded it with some test data. If you would like to use the same table, you could use below scripts.

CREATE TABLE [dbo].[tblProduct](
 [ProductID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](255) NOT NULL,
 [ProductNumber] [varchar](255) NOT NULL,
 [ListPrice] [numeric](9, 2) NOT NULL,
 CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED 
(
 [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, 
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

DECLARE @COUNTER INT, @productname VARCHAR(255), 
 @productnumber VARCHAR(255), @listprice DECIMAL
SET @COUNTER = 0
SET @productname = 'product name '
SET @productnumber = 'product number '
SET @listprice = 500
WHILE(@COUNTER <= 20)
BEGIN
 SET @listprice= @listprice + 100
 INSERT INTO [dbo].[tblProduct]
     VALUES
           (@productname + convert(VARCHAR, @COUNTER)
           ,@productnumber + convert(VARCHAR, @COUNTER)
           ,@listprice)
 SET @COUNTER = @COUNTER + 1           
END

Be Sociable, Share!

•