Pass selected items to query in SQL Server

Question

I have built a C# application where users can select from multiple lists of criteria to develop a set of search criteria to build a query in SQL Server. This involves multiple listboxes (multi items can be selected) combo boxes (only 1 item can be selected) and a even a datagrid. I'm not sure how to capture all the selected items and pass them to parameters into SQL Server. I started to do a massive foreach statement but I don't even know if that's going to work. Does anyone have a suggestion? Thanks.

sjones · Answer

Sorry it took so long. I've been out of town. No. Your approach is not going to work. Because you don't have a known number of parameters to pass you cannot write a SP in the database. The IN clause I was talking about is SQL and goes in the WHERE clause. For example: SELECT * FROM people WHERE name IN ('Jim' 'John' 'Sam') Here's the reason SP's suck when it comes to handling big adhoc query tools. Look at this one: SELECT * FROM people WHERE firstname IN ('Jim' 'John' 'Sam') AND lastname NOT IN ('Smith' 'White' 'Greene') So that would handle a first name multi-select listbox for names to include and another for lastnames to exclude. As you move down your search form you process each Control and build a Where clause for it. Then you tack it onto your big SQL Where clause with an AND or an OR. You should also look into The ANY and ALL keywords in SQL. They come in very handy for this sort of thing. The thing you need to be very careful about is your boolean logic. Draw out your decision tree on paper before you start coding. You need to be sure where to use parens to group your filters together. For example. firstname IN ('Jim' 'Joe') AND lasname NOT IN ('Smith' 'White') OR eyecolor='Blue' This works just like a math equation.

rulec444 · Answer

Okay - I don't have a clue. I've tried to find information on the Where clause and all I can find is references to SQL. Then as for the In clause I can only find references to foreach(XX in XX). The DBA's would like me to pass all paramaters into one stored procedure but maybe it isn't possible because I need to keep all the catagories of types of selected items identified - I was going to try something like this but I can't pass the different types of parameters in the the stored procedure in the SqlHelper class: private void getAllParams() { SqlParameter[] paramM = {new SqlParameter(@MSA SqlDbType.VarChar)}; SqlParameter[] paramS = {new SqlParameter(@State SqlDbType.VarChar)}; SqlParameter[] paramC = {new SqlParameter(@County SqlDbType.VarChar)}; SqlParameter[] paramSp = {new SqlParameter(@SpecialtyName SqlDbType.VarChar)}; SqlParameter[] paramSG = {new SqlParameter(@SpeciatlyGroupName SqlDbType.VarChar)}; foreach(DataRowView msaDRV in lbxMSAs.SelectedItems) { int countM = 0; paramM[countM].Value = (msaDRV[MSA]);//.ToString()//lbxMSAs.SelectedValue; countM++; } foreach(DataRowView stateDRV in lbxState.SelectedItems) { int countS = 0; paramS[countS].Value = (stateDRV[State]); countS++; } foreach(DataRowView countyDRV in lbxCounties.SelectedItems) { int countC = 0; paramC[countC].Value = (countyDRV[County]); countC++; } foreach(DataRowView specialtyDRV in lbxSpecialty.SelectedItems) { int countSp = 0; paramSp[countSp].Value = (specialtyDRV[SpecialtyName]); countSp++; } foreach(DataRowView specialtyGroupDRV in lbxSpecialtyGroups.SelectedItems) { int countSG = 0; paramSG[countSG].Value = (specialtyGroupDRV[SpecialtyGroupName].ToString()); } try { SqlHelper.ExecuteNonQuery(cnIPDAdmin CommandType.StoredProcedure gsp_CreateExtractRequest paramM);// paramS); } catch(Exception e) { MessageBox.Show(e.ToString() + error on CreateExtractRequest sp); } }//end of getAllParams() Is this approach going to even work? There are just so many variables in the selections I don't know how to capture that. Thanks again!!

rulec444 · Answer

Great thanks! I'll start working on this - I'm not even familiar with the IN() clause so I'm going to have to learn about that first. I appreciate your feedback...you've been really helpful to me before so I'm confident you're heading me in the right direction. I played around with the foreach loop yesterday and didn't get very far. Thanks.

sjones · Answer

You need to roll up your sleeves and do some coding for this one. I am guessing that each control contains possible filters for a Where a clause. So you need to process each control seperately building a clause. When you are done you build the actual Where clause but concatenating the clauses you built with AND. Each control is handled differently. For a single select listbox you would use field = SelectedItem. For multi-select you would spin the the SelectedItems collection building an IN() clause like field IN (x x x x) and so on. This is not difficult you just need to get all your parenthesis commas ANDs and other pieces in the right place. Build the Where clause on piece at a time and be generous with the Console.WriteLine()'s during development. Watch closely as each piece is being built. I had to do this on a web app 2 years ago that took 15 different filters from a page most of them multi-select with multiple inclusive and exclusive combinations and range values. The final SQL statement ended up being bigger than 10KB most of the time.