search design feedback

[=?Utf-8?B?cm9kY2hhcg==?=]

hi all,
i have a search control for Northwind Employees so they can search on just
about anything regarding Northwind Customers. The search control dynamically
builds the sql statement in the data layer based on employee search criteria
and sends it off as a parameterized query to sql server.

Here's a quick example of how it works:
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM Customers" + Environment.NewLine);

if (searchControlFilter == "ByCity")
{ sb.Append("WHERE City Like @city + "%"); }
if (searchControlFilter == "ByCountry")
{ sb.Append("WHERE Country Like @country + "%"); }

So, when users come up with another search idea we just add to the logic. So
the code, if you can imagine, can get complex.

This product is stable and has been in production for several years so this
logic is pretty baked in. I'm mainly asking for low impact improvements for
better code manageability (It'd be nice too if the search could be more
generic as well). But I also would like to hear, in general, other ways this
could have been done (newer technologies like linq).

This a MS Visual Studio 2005 ASP.Net(C#) product with a MS SQL Server 2005
backend. We are utilitizing AJAX and JQuery.

thanks,
rodchar

[Marc Gravell]

I recall wroting some similar logic a year-or-so ago; re being generic
- anything that gets the mappings (sql columns to logical properties)
from some metadata store (for example, attributes or an xml file)
would be an option; as would support for more than just LIKE @foo +
'%', and the ability to perform multiple filters at once including
nesting of conditions "(x or y) and z".

Re technology; an obvious candidate here would be ORMs such as LINQ-to-
SQL. By creating a data-context, it is fairly trivial to write a
search screen:

IQueryable<Customerquery = ctx.Customers;

if(!string.IsNullOrEmpty(customerName)) query =
query.Where(x=>x.Name == customerName);
if(!string.IsNullOrEmpty(city)) query = query.Where(x=>x.City ==
city);
//...etc
List<CustomerresultsPage = query.Take(100).ToList();

which will give you the first 100 results where all the chosen options
matched. Note that much more complex queries are possible.

Marc

[=?Utf-8?B?cm9kY2hhcg==?=]

would anyone else like to expand with article examples, more insight, is
there a way to use interfaces in this case (with the exception of linq)?

"rodchar" wrote:

hi all,
i have a search control for Northwind Employees so they can search on just
about anything regarding Northwind Customers. The search control dynamically
builds the sql statement in the data layer based on employee search criteria
and sends it off as a parameterized query to sql server.

Here's a quick example of how it works:
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM Customers" + Environment.NewLine);

if (searchControlFilter == "ByCity")
{ sb.Append("WHERE City Like @city + "%"); }
if (searchControlFilter == "ByCountry")
{ sb.Append("WHERE Country Like @country + "%"); }

So, when users come up with another search idea we just add to the logic. So
the code, if you can imagine, can get complex.

This product is stable and has been in production for several years so this
logic is pretty baked in. I'm mainly asking for low impact improvements for
better code manageability (It'd be nice too if the search could be more
generic as well). But I also would like to hear, in general, other ways this
could have been done (newer technologies like linq).

This a MS Visual Studio 2005 ASP.Net(C#) product with a MS SQL Server 2005
backend. We are utilitizing AJAX and JQuery.

thanks,
rodchar