OOP design question - keeping track of many similar things

Question

I'm designing a Data Access layer for an C#/ASP.net application and I have a question regarding the treatment of parameters in sql queries.

At the moment, when a query needs a dynamically set parameter for the Where clause I have to (1) define a variable to hold the value, (2) add a new QueryStringParameter to the SqlDataSource's SelectParameters collection, (3) set the temporary value of the parameter when the method containing the query is called, (4) and set the value of the parameter on the Selecting event of the SqlDataSource.

I have a list of ~20 parameters that are used throughout the different queries and it seems as though I should be able to define each once and simply tell the method which to use (and update it's value appropriately).

Is there a good way to set this up?

Answer 1

My favorite way to do this is to set up an object data source and then in your table adapter you can add queries for all of the different queries you want to perform with the different parameters you need for your various selects. This gives you the ability to ignore the actual parameters and just use the class to call the methods and pass the appropriate parameters. You also get the benefit that you are returned a first class object, or collection of objects instead of having to worry about typecasting each of the returned values. This method does not work well though if the schema you're returning modifies for each query.

This article from Scott Gu's blog seems to cover what I'm talking about in much better depth, but honestly I only skimmed it:

http://weblogs.asp.net/scottgu/archive/2006/01/15/435498.aspx