Gridview Selection with Checkboxes

We all use Gridview controls to show tabular data. That's what they are designed to do. But what if you want to just display a table of data and allow the user to select one (or many) rows so that the system can associate those rows with something else.

For example, let's consider a shopping cart. You browse a site and see lots of products. You might want to buy some milk, but there are so many different types of milk. The site could simply list the milk types in a Gridview with a checkbox in each row. Then you can check the checkbox of the product you want, so that the system can associate each of those milk selections with your cart.

The process we need to do is:

1. Build the Gridview
2. Capture the event that indicates the selections have been made
3. Loop through each row of the Gridview and
   • Check if the checkbox is selected or not
   • Either associate or disassociate the item

So, let's create the Gridview.

<asp:GridView ID="Gridview1" runat="server" 
        AutoGenerateColumns="False" DataKeyNames="ID" 
        DataSourceID="sqlDatasource1">
        <Columns>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="selectall" ClientIDMode="static" runat="server" />
                </HeaderTemplate>
                <ItemTemplate>
                    <asp:CheckBox ID="IsAssociated" ClientIDMode="static"  runat="server" Checked=&#039;<%# Bind("IsAssociated") %>&#039; />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="SomeField" HeaderText="SomeField" 
                InsertVisible="False" ReadOnly="True" SortExpression="SomeField">
            </asp:BoundField>
            <asp:BoundField DataField="SomeOtherField" HeaderText="SomeOtherField" SortExpression="SomeOtherField">
            </asp:BoundField>
        </Columns>
    </asp:GridView>

Now, we have a button called btnAssociate and clicking that will execute our codebehind. I will show you the codebehind and comment each of the lines to explain what is going.

protected void btnAssociate_Click(object sender, EventArgs e)
        {
        	// this connection string is drawn from your web.config
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            // create the connection to use later
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            // create the command object to use later
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            int rowcount = 0;
            // looping through each Gridview row
            foreach (GridViewRow row in gvCategoryExpectations.Rows)
            {
            	// Is the row full of data that has come from the database? (excluding header, footers etc
                if (row.RowType == DataControlRowType.DataRow)
<div style="display: none"><a href='Bestbettafish.com ' title='fish for aquarium'>fish for aquarium</a></div>                {
                	//  set up the checkbox and get the checked property
                    CheckBox cb = (CheckBox)row.Cells[0].FindControl("IsMember");
                    if (cb.Checked)
                    {
                    	//  Yes, it&#039;s checked
                    	
                    	//  Now we find controls on the page that have the data we want to use in the Stored Procedure
                    	//	to make the association or disassociation
                        Label lbl = (Label)fvExpectation.FindControl("AControlThatHasAParameterValue");
                        int ID = Convert.ToInt32(gvCategoryExpectations.DataKeys[rowcount].Value);
                        //  Call the method that will execute against the database
                        AssociateItem(cmd, ID, Convert.ToInt32(lbl.Text));
                    }
                    else
                    {
                    	//  Nope, it&#039;s not selected. But we still want to do something - we are disassociating the item
                        Label lbl = (Label)fvExpectation.FindControl("AControlThatHasAParameterValue");
                        int ID = Convert.ToInt32(gvCategoryExpectations.DataKeys[rowcount].Value);
                        DisAssociateItem(cmd, ID, Convert.ToInt32(lbl.Text));
                    }
                }
                rowcount++;
            }
        }

Make sense? Hopefully you are still with me. Now, let's create the method that does the database calls.

protected void AssociateItem(SqlCommand cmd, int SomeID, int SomeOtherID)
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "proc_procedure_to_associate";
            //  we need to clear the parameter list because we are calling it repeatedly while we loop through the Gridview rows
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SomeID", SomeID);
            cmd.Parameters.AddWithValue("@SomeOtherID", SomeOtherID);
            cmd.ExecuteNonQuery();
        }

        protected void DisAssociateItem(SqlCommand cmd, int CategoryID, int ExpectationID)
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "proc_procedure_to_disassociate";
            //  we need to clear the parameter list because we are calling it repeatedly while we loop through the Gridview rows
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SomeID", SomeID);
            cmd.Parameters.AddWithValue("@SomeOtherID", SomeOtherID);
            cmd.ExecuteNonQuery();
        }

And that's it. Now our Gridview is writing selected items back to the database. And also, if the item is deselected, it's REMOVED from the database. There are some things that I haven't touched on like "Select All" functionality. Maybe someone could give some comments on how to enhance this a little more.

Til next time ...