sql - Search refinement for WebMatrix site -

i've been asking few questions on site, , have answers lots of little bits, trying piece whole thing together.

i have page shows properties have in database. need find way refine results though, example, show properties have 4 bedrooms etc. problem is, don't know how many variables might have in search term yet. here's example.

1. a user may want see properties have 4 bedrooms
2. a user may want see properties have 4 bedrooms , 3 bathrooms , sleeps 8 people etc.

i have decided pull variables querystring, ?numbedrooms=4&?numbathrooms=3

here's code have far, doesn't work:

@{ var db = database.open("stayinflorida");  ienumerable<dynamic> queryresults;  int numbedrooms = request.querystring["numbedrooms"].asint(); int numbathrooms = request.querystring["numbathrooms"].asint(); int sleeps = 0;  list<int> arglist = new list<int>();  if (request.querystring["sleeps"].isint()) { sleeps = request.querystring["sleeps"].asint(); }  int numofarguments = 0;  string selectquerystring = "select * propertyinfo ";  if (numbedrooms != 0) { arglist.add(numbedrooms); selectquerystring += "where numbedrooms = @0 ";  numofarguments++; //increment numofarguments 1 }  if (numbathrooms != 0) { arglist.add(numbathrooms); if (numofarguments == 0) {     selectquerystring += "where numbathrooms = @0 "; } else {     selectquerystring += "and numbathrooms = @" + numofarguments + " "; } numofarguments++; }  if (sleeps != 0) { arglist.add(sleeps); if (numofarguments == 0) {     selectquerystring += "where sleeps = @0 "; } else {     selectquerystring += "and sleeps = @" + numofarguments + " "; } numofarguments++; }  selectquerystring += "order numbedrooms desc"; //adjust order clause how see fit.  int[] argarray = arglist.toarray();  if (argarray.length > 0) { queryresults = db.query(selectquerystring, argarray); //stores dynamic list 'queryresults' can later iterated through 'foreach' list , written page razor. } }    <!--results start-->  @foreach(var row in queryresults){ <div class="container">                  <h4><a href="/property.cshtml?propertyid=@row.propertyid">@row.propertyname</a></h4>                 <h5>bedrooms: @row.numbedrooms bathrooms: @row.numbathrooms sleeps: @row.numsleeps</h5>   </div> } <!--results finish--> 

so clarify, need find way check see if there querystring entry each search query, , if there is, append sql statement see results match?

pleeeeeease help, i've been stuck on long time.

many thanks

mainly, seems need "user-interface" page.

within page, have several form input elements (radio buttons, text boxes, checkboxes, etc.), if left blank, ignored, if not, detect , append accordingly sql syntax need sql string.

for instance let's had form on first (user-input) page (let's call page1.cshtml):

<!--[html]--> <form action="~/page2.cshtml" method="get">     <select name="numbedrooms">         <option value="0">please select</option>         <option value="1">1</option>         <option value="2">2</option>         <option value="3">3</option>         <option value="4">4</option>     </select>     <select name="numbathrooms">         <option value="0">please select</option>         <option value="1">1</option>         <option value="2">2</option>         <option value="3">3</option>         <option value="4">4</option>     </select>     <input type="text" maxlength="3" name="sleeps" /> </form> 

of course, viable options, , type of input fields on form totally maleable specific scenario, idea, i'm sure.

next, in part of page2.cshtml file begins construct sql string:

/*[c#]*/ var db = database.open("stayinflorida"); int numbedrooms = request.querystring["numbedrooms"].asint(); int numbathrooms = request.querystring["numbathrooms"].asint(); int sleeps = 0; list<int> arglist = new list<int>();  if (request.querystring["sleeps"].isint()) {     sleeps = request.querystring["sleeps"].asint(); }  int numofarguments = 0;  string selectquerystring = "select * propertyinfo "; 

notice string, if nothing else appended it, work fine , query entries in database, without syntax error (sql doesn't mind if there space @ end. know, because make life easier down road). however, now, can start checking see what info passed page previous, , append sql string accordingly.

also, , perhaps easiest way, query database while passing appropriate number of unknown-until-runtime parameters, pass array db.query method. so, each parameter test valid, added list , later converted array (we use list first, because c# not allow define array unknown size, list allow add values go).

if (numbedrooms != 0) {     arglist.add(numbedrooms);     selectquerystring += "where numbedrooms = @0 ";      numofarguments++; //increment numofarguments 1 }  if (numbathrooms != "0") {     arglist.add(numbathrooms);     if (numofarguments == 0)     {         selectquerystring += "where numbathrooms = @0 ";     }     else     {         selectquerystring += "and numbathrooms = @" + numofarguments + " ";     }     numofarguments++; }  if (sleeps != 0) {     arglist.add(sleeps);     if (numofarguments == 0)     {         selectquerystring += "where sleeps = @0 ";     }     else     {         selectquerystring += "and sleeps = @" + numofarguments + " ";     }     numofarguments++; }  selectquerystring += "order numbedrooms desc"; //adjust order clause how see fit. 

keep in mind depending on how data stored in database (nvarchar, int, etc.) may have use casting within sql string, parsing in c#, or adjust way write selectquerystring, go, won't go further detail on that, because data types/conversions out of scope of question.

next convert list of arguments array.

int[] argarray = arglist.toarray(); 

lastly, have query database. however, need make sure array sent list not empty (which caused list being empty when converted it). assure you, server-side error if pass empty array second argument db.query() method.

if (argarray.length > 0) {     var queryresults = db.query(selectquerystring, argarray); //stores dynamic list 'queryresults' can later iterated through 'foreach' list , written page razor.      //put "foreach (var row in queryresults)" loop here. } 

and that's it. thing is, there lot of things may have critique in order working specific data types (as coming form page, stored within database).

hopefully will, @ least, step closer achieving goal. let me know if need more help, have more questions, or (despite best efforts) spot syntax or other error.

happy coding, gavin!