dynamically build sql statement based on selection criteria of checkboxes

I have an .aspx page which has Products grouped into various categories in a SQL Server 2005 database.

The user should be able to select from any combination. For example, selecting 'Beige' and 'Black' from the COLOR category and 'Tapestry' from the STYLES category should yield only Products with Beige+Tapestry as well as Black+Tapestry.

I am thinking about having a WHERE clause which checks to see: if any of the COLOR checkboxes are checked then build a string with those checkboxes' values into a 'WhereClauseColor' string variable, using WhereCluaseColor += checkbox values;

if any of the STYLES checkboxes are checked then using another variable called WhereClauseStyles--just like in the Color category.

Finally, build a sql string called WhereClause: if WhereClauseColor != "" then WhereClause += AND 'color' field in db CONTAINS the WhereClauseColor;

similar thing for the Styles.

But I am having no luck. May be I need an entirely different approach. Too bad the client wants the products to be Checkboxes to allow multiple selection under each category.

WhereClause = "SELECT * FROM [xxxx] WHERE 0 = 1";
/////COLORS
// if (Request.Form["color_gold"] != null) { WhereClause += " OR  xxx.color = 'gold'"; }//// OLD CODE. . 08/04/11
if (Request.Form["color_spice"] != null) { WhereClau开发者_如何学Pythonse += " OR  CONTAINS (xxxx.color,'spice')"; }/// ## NEW CODE: NOTE CONTAINS. . 08/04/11

Have you tried to build the sort of SQL select query you require in SSMS first to get the correct syntax so that when you start to build the dynamic query you at least now what your aiming for? This is how I would look at doing this.

You might also be able to do it all in SQL with temporary tables where the first query take the first selection of check boxes (passed as params to a stored proc, and the next set of check boxes are processed in the next section of your stored proc.

I don't exactly know why you're using Request.Form. Something like if (chkSpice.Checked) would be much more readable.

If you have to keep your SQL in code, and can't use a stored procedure, then this is one way you could do it. You're not accepting user input and passing it in SQL, so there's no SQL injection risk.

If you know what your styles and colors will be named in the database, a SQL IN operator would be much more efficient.

string styles = String.Empty;
string colors = String.Empty;

if (chkTapestry.Checked)
    styles = "'Tapestry'";
else if (chkRug.Checked)
    styles += ",'Rug'";


if (chkBlack.Checked)
    colors = "'Black'";
else if (chkBeige.Checked)
    colors += ",'Beige'";

string sql = "SELECT * FROM [X]";

if (styles.Length > 0 && colors.Length > 0)
    sql += String.Format(" WHERE [Style] IN ({0}) AND [Color] IN ({1})", styles, colors);
else if (styles.Length > 0)
    sql += String.Format(" WHERE [Style] IN ({0})", styles);
else if (colors.Length > 0)
    sql += String.Format(" WHERE [Color] IN ({0})", colors);

The above is a rather manual approach to assemble styles and colors, and uses regrettable hard-coding of style and color names. A better design might be:

1. Select all colors from the database. Store them in a DataTable.
2. DataBind the colors DataTable to a CheckBoxList control to display the colors on the page.
3. Select all styles from the database. Store them in another DataTable.
4. DataBind the styles DataTable to a CheckBoxList control to display the styles on the page.

Then, on PostBack:

1. Reload your colors DataTable with values from the database. Accept the small risk that the list of colors has changed since your page was rendered, or store the DataTable in a Session variable across post backs to mitigate the risk.
2. Reload your styles DataTable.
3. Iterate through your colors CheckBoxList items in a for loop. For each item that is selected, use the for-loop index to retrieve that same row index from the DataTable. Get the color name from the DataRow, and add it to your list of colors to use in the Color IN (...) part of your WHERE clause. This ensures you are using a value from your database and not a value that could be tampered with by a user.
4. Iterate through your styles CheckBoxList items in a for loop, doing the same thing as the colors.
5. Assemble your WHERE clause as I did in the example code.
6. Execute the query on your database and process the result set.

Example Code:

protected DataTable dtColors
{
    get
    {
        if (Session["fabrics-dtColors"] == null)
            Session["fabrics-dtColors"] = FetchDataTable("SELECT DISTINCT Color FROM X ORDER BY Color");
        return (DataTable)Session["fabrics-dtColors"]; 
    }
}

protected DataTable dtStyles
{
    get
    {
        if (Session["fabrics-dtStyles"] == null)
            Session["fabrics-dtStyles"] = FetchDataTable("SELECT DISTINCT Style FROM X ORDER BY Style");
        return (DataTable)Session["fabrics-dtStyles"]; 
    }
}

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        cblColors.DataSource = dtColors;
        cblColors.DataBind();

        cblStyles.DataSource = dtStyles;
        cblStyles.DataBind();
    }
}

protected void btnSearch_OnClick(object sender, EventArgs e)
{
    string colors = String.Empty;
    string styles = String.Empty;
    string sql = "SELECT * FROM [X]";

    if (cblColors.SelectedIndex > -1)
    {
        for (int i = 0; i < cblColors.Items.Count; i++)
        {
            if (cblColors.Items[i].Selected)
            {
                colors += String.Format("'{0}',", dtColors.Rows[i][0]);
            }
        }

        colors = colors.TrimEnd(',');
    }

    if (cblStyles.SelectedIndex > -1)
    {
        for (int i = 0; i < cblStyles.Items.Count; i++)
        {
            if (cblStyles.Items[i].Selected)
            {
                styles += String.Format("'{0}',", dtStyles.Rows[i][0]);
            }
        }

        styles = styles.TrimEnd(',');
    }

    if (styles.Length > 0 && colors.Length > 0)
        sql += String.Format(" WHERE [Style] IN ({0}) AND [Color] IN ({1})", styles, colors);
    else if (styles.Length > 0)
        sql += String.Format(" WHERE [Style] IN ({0})", styles);
    else if (colors.Length > 0)
        sql += String.Format(" WHERE [Color] IN ({0})", colors);

    GetSearchResults(sql);
}

I've been working on a library called QBuilder to make this kind of dynamic sql generation a bit easier. It may be of some use to you.

http://qbuilder.codeplex.com/

private List<string> arrColors = new List<String>();
private List<string> arrStyles = new List<String>();   

if (Request.Form["color_spice"] != null) { 
  arrColors.Add("spice"); }/// ## NEW CODE: NOTE CONTAINS. . 08/04/11

if (Request.Form["color_gold"] != null) { arrColors.Add("gold"); }

string sql = "select * from ado_products_fabrics where [color] in('";
       sql += string.Join("','", this.arrColors.ToArray());
       sql += "')";
       sql += " AND [style] in('";
       sql += string.Join("','", this.arrStyles.ToArray());
       sql += "')";

WhereClause = sql;/

Build tables in your application code from your various selections: SelectedColors, Selected Styles, ... . Then pass them as parameters to a stored procedure that uses them in the WHERE clause:

where ( Color in (select Color from SelectedColors) or
     not exists ( select Color from SelectedColors ) ) and ...

I assume that if no selection is made for a given category, e.g. print scale, that you want to accept all values.

This will let you have multiple selections and prevent SQL injection attacks.