Puzzle - How would you do this?

Comments

# William said on March 25, 2005 10:21 PM:

Here's what I have in my framework:

1) On each form field that will accept a query parameter I put in the tag field a string that denotes that it's a search field, the column name, and the search type (like, =, >=, <=). Eg. "search:csstyle:like"

2) When the search button is pressed a routine is called that looks at all the fields on the form, checking for "search" tags. Matches get pushed into a strongly typed collection called QueryParms.

3) The QueryParms collection gets passed to the proper routine in the data repository where ultimately a static routine in the QueryParms called BuildWhereClause looks at the parms, matches the search field to the proper data column, creates a query parameter, stuffs the search value into the new parameter, and adds the appropriate text to the where clause string respecting the conditional type (like, =, etc).

4) Finally the WhereClause gets added to the select command text that sits in a adapter component and the dataset gets filled.

The good part of the above is it definitely solves the problem I most often need solved: allowing a user to specify search parameters in a easy to use fashion (step 2 is built into a base that I inherit from and step three is just a call in the repository's retrieval routine). And of course I built it so I understand the limitations as well as how to enhance. But it's very limited and makes me appreciate what I read about ORM's. My main beef with ORM's is they typically eschew DataSets and until .Net '05 is a reality I prefer working with DataSets when binding in forms.

Oh, the SQL would look something like this:

select * from cust where LastName like @LastName and FirstName like @FirstName and DoB = @DoB order by LastName, FirstName;

I know that I'm ignoring a potential gotcha with the date. One could easily create two time parameters whenever they see an '=' combined with a date (e.g. where DoB >= @DoB and Dob < @DoBPlus1Day).

I look forward to seeing your solution. I'm not much of a framework guy, I just do enough to make writing my apps less painful.

# William said on March 26, 2005 2:02 AM:

Hi Ken:

Basically for each group of search values, you'd do something like

SELECT *
FROM TableName
WHERE (FirstName = @FirstName or @FirstName IS NULL) AND (LastName = @LastName or @LastName IS NULL) and you repeat. This way you aren't using any nondeterministic functions and you can index the search columns and squeeze some extra performance out of it. I'm in the middle of posting a whole example which I'll have up shortly!

# William said on March 26, 2005 10:51 AM:

Hey ADD boy it's long past shortly. Kim d@mmit let him put his pants back on and post the answer. I am patiently waiting to see how this would be done because not being a Db guy I have no clue how you would do this the most efficiant way.

# William said on March 28, 2005 3:54 PM:

We do as Ken suggested above... works just fine.