a better way for user searches

[Dan Holmes]

i have a table of products and there is a screen to search for them. I
am stuck as to how to distinguish that the user doesn't want to include
that criteria with that criteria is blank.

Here is a sample:

public List<ProductInfo> List(IVS.Framework.Identity id, ProductInfo
productInfo1, ProductInfo productInfo2)
{
List<ProductInfo> returnData = null;
StringBuilder sql = new StringBuilder();

....snipped stuff

if (!string.IsNullOrEmpty(productInfo1.ProductID))
sql.AppendFormat(" AND ProductID LIKE '{0}%'", productInfo1.ProductID);

if (!string.IsNullOrEmpty(productInfo1.CategoryID))
sql.AppendFormat(" AND CategoryID LIKE '{0}%'",
productInfo1.CategoryID);
else if (productInfo1.CategoryID == null)
sql.Append(" AND CategoryID IS NULL");

in the case of a productid it can never be null but a category could.
How have you guys done this? The above will always look for categoryid
of null even when i only want to search by productid or i have to
include a category too.

dan

 

[Marc Gravell]

First off: *never* concatenate user input; use parameters instead...
i.e. "AND ProductID LIKE @ProductID"
(and add a parameter named @ProductID with Value =
productInfo1.ProductID + "%".

However; back to the question ;-p Various options:
* A checkbox next to each that checks itself automatically when a
value is entered, but can be checked manually for searching blank
* Radios for value / blank: (o) [Textbox] (o) (blank) - defaults to
value obviously (and selecting (blank) would disable the Textbox)
* Drop-down list of available options, with (blank) at the top?
* A more dynamic search UI - i.e. user adds criteria by selecting a
property (from a list) *and* the value - and can and multiple rows -
i.e.

[Name] = Fred
[Order Ref] =
[Foo] = bar
[Select option] = {value}

(the bottom option adds a new row; the Order Ref is searching for a
blank)
This approach also: handles large numbers of searchable fields without
needing a massive search form; allows for more complex "(this and
that) or (theother)" searches; makes it easier to fit operator options
(equals/starts-with/less-than/more-than etc) without making the UI too
ugly (OK; still ugly, but not too much so).

Of course, the alternative would be to go all "google", with a single
text-box and simply parse the tokens - i.e.:
Name=Fred and Order Ref = "" and Foo=bar
(or something)

Marc

 

[Dan Holmes]

First off: *never* concatenate user input; use parameters instead...
i.e. "AND ProductID LIKE @ProductID"
(and add a parameter named @ProductID with Value =
productInfo1.ProductID + "%".

thanks i missed that in this one. My other code does that but i looked
right over it here.

However; back to the question ;-p Various options:
* A checkbox next to each that checks itself automatically when a
value is entered, but can be checked manually for searching blank
* Radios for value / blank: (o) [Textbox] (o) (blank) - defaults to
value obviously (and selecting (blank) would disable the Textbox)
* Drop-down list of available options, with (blank) at the top?
* A more dynamic search UI - i.e. user adds criteria by selecting a
property (from a list) *and* the value - and can and multiple rows -
i.e.

[Name] = Fred
[Order Ref] =
[Foo] = bar
[Select option] = {value}

(the bottom option adds a new row; the Order Ref is searching for a
blank)
This approach also: handles large numbers of searchable fields without
needing a massive search form; allows for more complex "(this and
that) or (theother)" searches; makes it easier to fit operator options
(equals/starts-with/less-than/more-than etc) without making the UI too
ugly (OK; still ugly, but not too much so).

Of course, the alternative would be to go all "google", with a single
text-box and simply parse the tokens - i.e.:
Name=Fred and Order Ref = "" and Foo=bar
(or something)

Marc

what data structure you use for holding the search criteria with this
idea? I guess i could create one...perhaps something like

class Criteria
{
public string columnname;
public string value;
public Operation operator;
}
Enum Operation
{
Equals
, GreaterThan
, LessThan
...
}

 

[Marc Gravell]

perhaps something like
Depending on your actual requirements, yes; I have used something very
similar... probably not public fields, though ;-p

Marc

 

[Marc Gravell]

Actually - seeing the later posts, I'd agree that Expression is a
better way to encapsulate this...

Plus (as has already been pointed out) it would allow the system to
write the SQL for you.

For more info on building the query, see:
http://groups.google.com/group/micr...read/thread/76353240ec0652c3/4e2cc66c3d39ee9f

Marc