ASP, C# & SQL complex parameter/query examples please

I am building a webpage that has a few controls on it such as CheckBoxList and Listbox (with multi selection enabled). These controls will be linked to a sql database table, one for colors and one for sizes. What is the best way to design the query, primarily the 'where' statement to filter the shirts listed in a gridview that match the selected sizes and colors made by the user. For example, if the user checks red and blue from the colorsCheckBoxList and medium and large from the sizesListbox, then the gridview will display only shirts that are red and are in medium and large as well as shirts in blue that are in medium and large. I've done most of the work, I just can't figure the best way to design the 'where' cla开发者_如何学JAVAuse. I can create it easily if I allow only one selection per control but I'd prefer to allow multi-selection.

This is an .aspx page with 'code behind' the page (C#). I've read using parameters is the best method for clean code and security but I'd like to hear what others think.

Please let me know any further details you may need and I greatly appreciate any time spent regarding this question.

Yes always use SQL Parameters to remove the risk of SQL Injection. It's hard to tell what you need with out seeing a database schema. Here is what I would do in your case.

SELECT shirtid, shirtname
FROM shirts
WHERE colorID IN (REDSHIRTID, BLUESHIRTID)

You would put your IDs from your selectbox as REDSHIRTID and BLUESHIRTID.

You can use Table-valued or XML parameters to pass in mutliple IDs for the size and color. I would opt in favor of Table-valued parameters, personally.

The selection will vary depending on the size and color multiple or single selection for both.You can workout with stored procedure.(Also it will be faster) While passing the parameter to the sp you can pass the selections for both as string with character seperation of comma e,g for size 'red,blue,green' if selected this three.Same for color. In sp you can first seperate the parameter by splitting ',', and querying as

//create sp name

//pass parameter color and size

//begin sp

//write for splitting paramter code maybe in for statement then

   SELECT col1, col2, col3 from shirts
   WHERE size IN (separated param list)  AND
   color IN (separated param list of colors)

// end sp