multiple types of queries and stored procedures

[Steve]

I'm using MySql and ADO.net

I find myself in a situation where I need to execute many different types of
queries. Basically they are all getting the same type of data, but with
different WHERE clauses and different levels of required data.

For example, one query "GetAllTest" will return ALL the test records in a
table and all of the fields.
Another query might be "GetRecentTests" which should only get the results
from the last 10 days and I don't need to get all the fields, just a subset.

I'm using a DataReader for performance reasons and my Dal layer is
populating business entities with the results from the DataReader.

My questions are:

1) What is the best practice for executing different types of queries with
different filter criteria? My Sprocs are pretty long and to make a handful
of them that vary only slightly (IE: WHERE _type LIKE 'Single', WHERE type
LIKE 'Single' AND ID > 100, etc) and all share the same joins and selects
seems like an incredible amount of redundancy.

I've thought about using straight SQL rather than sprocs, then I could have
a query builder class that could build the queries for me based on filter
criteria. I don't want to go away from sprocs if I don't have to, they are
so much more maintainable ;0)

2) Business entity population - I currently populate my business entities
in the body of my DAL method (IE: GetAllTests) - this poses the same problem
as #1, I would be repeating this code over and over for every DAL method
that is responsible for getting results with slightly different filters.
Seems like a ton of wasted code.

I thought about creating an ObjectBuilder class that would take a DataReader
and populate my objects for me, I could then use this in my various DAL
methods. Is there another way? How do you all do this?


These 2 problems must be something that many of you deal with on a regular
basis. Do you have any ideas for me?

I just though of one more, I could use a VIEW to keep all my joined results,
then I would query against the VIEW with my filters, that would save me some
redundancy in the sprocs I guess... but that VIEW would get pretty big as
time goes on.

I'm stuck, I need some hints, pointers, tips, clues, etc ;0)

Thanks for any help,
Steve

 

[Matt Noonan]

1) What is the best practice for executing different types of
queries with different filter criteria? My Sprocs are pretty long
and to make a handful of them that vary only slightly (IE: WHERE
_type LIKE 'Single', WHERE type LIKE 'Single' AND ID > 100, etc) and
all share the same joins and selects seems like an incredible amount
of redundancy.
I've thought about using straight SQL rather than sprocs, then I
could have a query builder class that could build the queries for me
based on filter criteria. I don't want to go away from sprocs if I
don't have to, they are so much more maintainable ;0)

Well now, that's an argument starter, but let's just say I disagree...

You could use an O/RM that has a built-in db-independent query language.
That way you add the different variations in your business logic layer and
the O/RM will generate the queries that you need. No stored procs are
necessary, although you can certainly use them if you wish.

dOOdads has always worked well for me with MySQL:
http://www.mygenerationsoftware.com/portal/dOOdads/CUsage/tabid/53/Default.aspx

2) Business entity population - I currently populate my business
entities in the body of my DAL method (IE: GetAllTests) - this poses
the same problem as #1, I would be repeating this code over and over
for every DAL method that is responsible for getting results with
slightly different filters. Seems like a ton of wasted code.

You can avoid repetitive code for some of it, but each variation is going to
require some level of uniqueness. I still believe using an O/RM approach
will help keep the repetitive code down to a minimum.

I just though of one more, I could use a VIEW to keep all my joined
results, then I would query against the VIEW with my filters, that
would save me some redundancy in the sprocs I guess... but that VIEW
would get pretty big as time goes on.

Views are always helpful for joining information together into a single
resultset, but you don't want them to become unweildy. Maybe you could build
multiple views, depending on your needs?