Multi Parameter Search

[cfps.Christian]

I'm working on a program where I have a decent sized database with
almost every single value able to be searched for (there would
actually be a need for it). To make this easier on myself I've coded
views so that they're all in one central location and I don't have to
fully navigate the db structure to search. What I'm trying to
determine is a good preferably dynamic way to code my searches.

I have a potential of lets say 20 columns and any number from 1 to 20
can be search at any time (not necessarily any order but I'd like to
plan ahead). I'm trying to avoid writing individual SQL strings or
even passing in string of column/value since this even has a potential
of breaking.

Essentially I would love to have an object that directly represents
the column name and the datatype for use with intellisense. Again I
can't code for every column since they can easily change.

How have people done this in the past/present?

 

[Marc Gravell]

First, I doubt I'd use a view ;-p
In some older code, I use a template class (that has properties for
every search option) for this purpose (including dynamic properties
for extensible values [name/value pairs]), and a horribly complicated
DAL method to generate parameterised SQL from this - but it is very
complicated and took me some time to get right. If I was writing this
anew (today), I'd use LINQ.

There was a good discussion of dynamic LINQ queries (for searching)
here:

http://groups.google.co.uk/group/mi...read/thread/76353240ec0652c3/4e2cc66c3d39ee9f

Marc

 

[cfps.Christian]

From skimming through it LINQ kinda went over my head a little, but
before I rack my brain trying to learn a new way of doing something
I'm planning on having a data access layer with a web service on top
of that. I'm thinking that I need a somehow strongly typed method
that will accept an infinite number of parameters and the parameters
themselves are strongly typed as well.

Also I'd be curious as to why not use a view. I'm not very familiar
with SQL managment objects (I can query and create a database just
fine but Views/Stored Procedures/Functions still leave a lot of room
for me to learn).

 

[DeveloperX]

From skimming through it LINQ kinda went over my head a little, but
before I rack my brain trying to learn a new way of doing something
I'm planning on having a data access layer with a web service on top
of that.  I'm thinking that I need a somehow strongly typed method
that will accept an infinite number of parameters and the parameters
themselves are strongly typed as well.

Also I'd be curious as to why not use a view.  I'm not very familiar
with SQL managment objects (I can query and create a database just
fine but Views/Stored Procedures/Functions still leave a lot of room
for me to learn).

It depends on how complex your queries are going to be imo. Here I'll
try to query people and run through some of the possible issues:

Create a person class.
Create a QueryPerson class.
Create an Operator enum (equals, greater than, like, not equal, etc).
QueryPerson would look a bit like Person but where person might have
properties for age, name, surname, QueryPerson would have methods like
AddAge(int pAge, Operator pOperator)

You need to ask yourself if you need to query on multiple values or
with varying operators. If not you can just pass the dal an empty
person with just the queried fields set. Back to the complex one
though.

So you might pass:

name = John
age > 16
age < 60
name = Jane
country = UK
surname like 'L%'

The DAL then needs to group and process the above. Something like.

where name in ('John', 'Jane') and age > 16 and age < 60 and country =
UK and surname like 'L%'

Note it spotted there were two names. This is the reason for AddAge,
so we can add multiples. It put them in an in clause. You should be
able to get away with IN and AND clauses and of course NOT. OR cases
get more complex.

You can then query your view. and have the DAL generate Person objects
to return to what ever is doing the work. Or possibly add them to a
cache and inform the consumer they're there with a set of keys. You
can prevent two processes having two different copies of the same
object (person) that way.

If you don't want to do all that yourself, you could investigate
something like nHibernate which is an ORM framework. I've not really
used it, but it sort of does the same thing as above (but not really)
and manages the generation of all the objects based on the db schema
you have. It's a bit involved so go have a look at their web site.

If you can use framework 3 I'd still go with LINQ. It's less complex
than it first appears. I don't know why Marc wouldn't use a view.
There's an old mantra that all access should be through an SP or a
view and never direct to the table (which I'm not really a huge
subscriber to) but if you've got a bunch of normalised tables which
expand out to a single view then go for it

Finally I wouldn't worry about generating your own SQL. LINQ does it,
nHibernate does it. I suppose there's a risk of sql injection attacks,
I'd have to think about that...

 

[cfps.Christian]

I think you kinda hit on my idea. I'm coding in 2.0 since I actually
have a copy of that in hand and don't have the money to buy VS08 yet
(or probably ever).

The only other thing that I could think of and it would be very
involved is to do what VS does automatically when creating a dataset.
By that I mean strip out the columns and their names and somehow parse
them into an enum or object that can be tacked on as a parameter. For
instance to build a big query you would just pass in the object with
the column and based upon the column it knows the datatype.

If nothing else I would love to create a class that can automatically
write its own methods for querying the data and be done with it.

 

[Marc Gravell]

I don't know why Marc wouldn't use a view.
Simple; unnecessary joins and complexity. The view approach relies on
the query optimiser hopefully stripping things back out of the view
and arriving at something efficient>. If you are going to SELECT all
the columns in the view, then I guess it doesn't matter a whole
bunch... but like you, I don't buy into mantras blindly ;-p

 

[Marc Gravell]

 For instance to build a big query you would just pass in the object with

the column and based upon the column it knows the datatype.

If nothing else I would love to create a class that can automatically
write its own methods for querying the data and be done with it.

I've done both of these, but trust me: they aren't trivial. If you
have the choice, go with something that already exists; nHibernate or
LINQ. When I last did this, nHibernate wasn't an option and LINQ
wasn't RTM. If I was doing it again, I reckon I'd have used LINQ - or
perhaps written a bespoke LINQ provider (I was doing xml queries which
LINQ doesn't really enjoy).

Marc