Multiple Checkbox combinations with gridview

Question

I have a client who wants filtering done on their application. One page has 8 filters and the other has 19. The filters can be combined. Basically 2^nth. Not a problem at all until it gets a high number such as 19! That would be... 524288 different combinations? Maybe I am not thinking this correctly.

So, what is a way I can have this done without creating that much code. My thinking was originally done as: 1. Create the number of check boxes needed. 2. Use logic (case,if, else, etc.) on the checked changed event to give the sql command for the desired gridview look and pass it.

But this is an insane amount of coding and there must be a more simple way than putting flags or checking if it is checked or not on every combination.

I have it working for the 5 combinations they originally wanted but 19? I just can't think of a way to do it without lots of code. Maybe I am exhausted and there is a simple answer.

To clarify, lets go to the 8 possible combinations. Lets say they have a client who eats 8 different foods. 1. Eggs 2. Bacon 3. Ham 4. Toast 5. Bagel 6. OJ 7. Milk 8. Apples

So the filtering could be: Eggs Or Eggs and bacon Or Eggs and bacon and ham Or Eggs and bacon and ham and Toast

Or Bacon Or Bacon and ham Or Bacon and ham and Toast

Or Ham Or Ham and toast

Etc. etc. etc. The combinations go on.

I don't know how to do this without checked if each combination is selected or not. I have it working for a smaller amount of filtering, but when the number gets large, well, the permutation just explodes to the insane amount.

EDIT --- THIS IS HOW I GOT IT TO WORK FOR ANYONE ELSE IN THIS SITUATION

protected void Page_Load(object sender, EventArgs e)
{
    bindFilter();
}

protected void chkBoxFilters_SelectedIndexChanged(object sender, EventArgs e)
{
    this.bindFilter();
}
private void bindFilter()
{
    string constr = ConfigurationManager.ConnectionStrings["YOUR CONNECTION STRING HERE"].ConnectionString;
    string query =
       "YOUR SQL QUERY HERE";

    string condition = string.Empty;
    foreach (ListItem item in chkBoxFilters.Items)
    {
        condition += item.Selected ? string.Format("'{0}',", item.Value) : string.Empty;
    }

    if (!string.IsNullOrEmpty(condition))
    {
        condition = string.Format(" WHERE COLUMN_NAME IN ({0})", condition.Substring(0, condition.Length - 1));
    }

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query + condition))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                cmd.Connection = con;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GRIDVIEWID.DataSource = dt;
                    GRIDVIEWID.DataBind();
                }
            }
        }
    }
}

}

And, of course, make a checkbox list with autopostback set to true. This worked for me, if anyone else in the future runs into something similar. If you have more than one column you are looking for, then add an OR [ColumnName] IN ({0}) afterwards and it works as well. You can have this filter multiple tables as well. Sweet and simple.

Answer 1

I'm not sure if this will help or not, but I've had to handle advanced search pages before and went this route...

Build a page full of Checkboxes, get each Checkbox's Checked property and pass it to a SQL stored procedure (perhaps with a SqlDataSource control and ControlParameter that looks like this:

CREATE PROC usp_web_search
    @eggs       bit     = NULL,
    @bacon      bit     = NULL,
    @ham        bit     = NULL,
    @toast      bit     = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      * 
    FROM        myTable a
    WHERE       (@eggs IS NULL OR (a.Eggs = @eggs))
                AND (@bacon IS NULL OR (a.Bacon = @bacon))
                AND (@ham IS NULL OR (a.Ham = @ham))
                AND (@toast IS NULL OR (a.Toast = @toast));
END;

You can make the WHERE logic more complicated, if necessary, but this handles the variety of combinations. Any of the parameters could be NULL (they'll be ignored in the query), 0 (not checked; will be part of query), or 1 (checked; will be part of query).

Hope that's helpful.