sql - search refinement in WebMatrix -

i starting build web page refine search results. code below works pretty well, , if add 1 of query strings (ie, ?beds=4, returns correct results. if, however, specify both query strings (ie, ?beds=4&sleeps=8, returns results matching either (all propertys 4 beds (regardless of sleeps) , propertys 8 sleeps (regardless of beds), , not both. need sort of , statement, results match beds , sleeps?

@{ layout = "~/_sitelayout.cshtml"; page.title = "search";  string searchtext = request.unvalidated["searchtext"];  var searchterms = searchtext.split('"').select((element, index) => index % 2 == 0 ? element.split(new[] { ' ' }, stringsplitoptions.removeemptyentries) : new string[] { element }).selectmany(element => element).tolist();  (int i=0; i<searchterms.count; i++) {     if (searchterms[i].toupper() == "the" || searchterms[i].toupper() == "and" || searchterms[i].toupper() == "as" || searchterms[i].toupper() == "an" || searchterms[i].toupper() == "but" || searchterms[i].toupper() == "or" || searchterms[i].toupper() == "of" || searchterms[i].toupper() == "if" || searchterms[i].toupper() == "is" || searchterms[i].toupper() == "in" || searchterms[i].toupper() == "it" || searchterms[i].toupper() == "by" || searchterms[i].toupper() == "to" || searchterms[i].toupper() == "for" || searchterms[i].length <= 1 || string.isnullorwhitespace(searchterms[i]))         {         searchterms.removeat(i);         i--;  //decrements 'i' if element removed because indexes after 1 drop one. ensures no indexes skipped.         } }    var db = database.open("stayinflorida"); string searchquerystring = ""; int termcount = searchterms.count; string[] searchtermsarray = searchterms.toarray();  searchquerystring = "select * propertyinfo numbedrooms = ";  (int i=0; i<termcount; i++) {     if (i != 0)         {         searchquerystring += "or numbedrooms = "; //ensures not appended first term. alternatively, of course, can use "and", depending on how want results returned, want "or".         }  searchquerystring += "@" + + " "; }  searchquerystring += "union "; searchquerystring += "select * propertyinfo numsleeps = ";  (int i=0; i<termcount; i++) {     if (i != 0)         {         searchquerystring += "or numsleeps = ";         }  searchquerystring += "@" + + " "; }  searchquerystring += "union "; searchquerystring += "select * propertyinfo numbathrooms = ";  (int i=0; i<termcount; i++) {     if (i != 0)         {         searchquerystring += "or numbathrooms = ";         }  searchquerystring += "@" + + " "; }  searchquerystring += "order anytermyouwishtoorderby desc";  if (searchtermsarray.length > 0) //prevents server-side error if searchterm list empty when converted searchtermsarray     {     var queryresults = db.query(searchquerystring, searchtermsarray);     } } 

caveat:

i hope answer helps approach, logically, solution extend functionality include number of variables not know until runtime, must use query database. understand not searching on strings instead numbers, or possibly other kinds of data, gathered form on previous page, once of data need target page (using query strings may not suitable), logistics should same, we'll start there. if, in end, find having trouble getting unknown number of variables form target page, can address that, also, assume have gathered necessary data on page queries database.

for example assume user types string of text must split on spaces, extract words from, , store each in list.

firstly, (for example) retrieve value in single query string <form> using get on previous page. this, know how do.

string searchtext = request.unvalidated["searchtext"]; 

note have retrieve value unvalidated, because if user type angle brackets in search term, throw server-side error. okay, though, because going careful untrusted data.

next can check whole search text make sure isn't empty or white space so:

if (!string.isnullorwhitespace(searchtext)) 

within branch can begin execute of functionality need make work.

you can replace simple characters want ignore (we'll use apostrophe , comma).

searchtext = searchtext.replace("'", "").replace(",", ""); 

now, next line little complex , i'm not sure explain correctly (i'm still new linq), so, suffice splits string on spaces (taking care of things double-spaces , treating text wrapped in quotes single item) , storing them in list (this part may outside scope of question , if so, apologize, in case need use this...).

var searchterms = searchtext.split('"').select((element, index) => index % 2 == 0 ? element.split(new[] { ' ' }, stringsplitoptions.removeemptyentries) : new string[] { element }).selectmany(element => element).tolist(); 

now, if choose, can omit terms list want ignore (i use simple common words in example, as, single character items, , of course, empty, null, or white space items):

for (int i=0; i<searchterms.count; i++) {     if (searchterms[i].toupper() == "the" || searchterms[i].toupper() == "and" || searchterms[i].toupper() == "as" || searchterms[i].toupper() == "an" || searchterms[i].toupper() == "but" || searchterms[i].toupper() == "or" || searchterms[i].toupper() == "of" || searchterms[i].toupper() == "if" || searchterms[i].toupper() == "is" || searchterms[i].toupper() == "in" || searchterms[i].toupper() == "it" || searchterms[i].toupper() == "by" || searchterms[i].toupper() == "to" || searchterms[i].toupper() == "for" || searchterms[i].length <= 1 || string.isnullorwhitespace(searchterms[i]))     {         searchterms.removeat(i);         i--;  //decrements 'i' if element removed because indexes after 1 drop one. ensures no indexes skipped.     } } 

next, declare basic variables need:

var db = database.open("stayinflorida"); string searchquerystring = ""; int termcount = searchterms.count; string[] searchtermsarray = searchterms.toarray(); 

now here of logic comes play handle compiling sql query string when don't know how many variables test columns against (for example assume 3 database columns exist: numbedrooms, numsleeps, , numbathrooms).

searchquerystring = "select * propertyinfo numbedrooms = ";  (int i=0; i<termcount; i++) {     if (i != 0)     {         searchquerystring += "or numbedrooms = "; //ensures not appended first term. alternatively, of course, can use "and", depending on how want results returned, want "or".     }      searchquerystring += "@" + + " "; }  searchquerystring += "union "; searchquerystring += "select * propertyinfo numsleeps = ";  (int i=0; i<termcount; i++) {     if (i != 0)     {         searchquerystring += "or numsleeps = ";     }      searchquerystring += "@" + + " "; }  searchquerystring += "union "; searchquerystring += "select * propertyinfo numbathrooms = ";  (int i=0; i<termcount; i++) {     if (i != 0)     {         searchquerystring += "or numbathrooms = ";     }      searchquerystring += "@" + + " "; }  searchquerystring += "order anytermyouwishtoorderby desc"; //you can order whatever term need, and, always, can use "asc" instead of "desc" 

now that part on have left make sure pass appropriate amount of arguments db.query() method, , since have array copy of our list, can use it.

if (searchtermsarray.length > 0) //prevents server-side error if searchterm list empty when converted searchtermsarray {     var queryresults = db.query(searchquerystring, searchtermsarray); 

thankfully db.query() method makes simple accepting array of values second argument, fills in parameters in query, if adding multiple arguments after first.

lastly, display results (you know part, sake of completion, show example anyway)

foreach (var row in queryresults) {     <div>number of bedrooms: @row.numbedrooms</div><br/>     <div>number of sleeps: @row.numsleeps</div><br/>     <div>number of bathrooms: @row.numbathrooms</div><br/><hr/><br/> } } // <-- don't forget close `if (searchtermsarray.length > 0)` branch. } // <-- don't forget close `if (!string.isnullorwhitespace(searchtext))` branch. 

i know 100% of logic here may not fit scenario, gives enough can modify needs. also, if have questions, i'll happy further if can.