OOP design issue - keeping track of many of these things
I am developing a data access layer for a C # / ASP.net application and I have a question regarding parameter handling in sql queries.
At the moment, when the query requires 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 SelectParameters SqlDataSource collection, (3) set the temporary value of the parameter when the method containing query, (4) and sets the parameter value in the SqlDataSource source select event.
I have a list of ~ 20 parameters that are used across all the different queries, and it seems that I can define each time and just specify the method to use (and update it accordingly).
Is there a good way to fix this?
source to share
My favorite way to do this is to set up the object's datasource, and then in your table adapter, you can add queries for all the different queries you want to run, with different parameters that you need for different options. This gives you the ability to ignore the actual parameters and just use the class to call methods and pass the appropriate parameters. You also get the benefit of getting a first-class object or collection of objects returned, rather than worrying about typing each of the return values. This method doesn't work though if the schema you return changes for every request.
This article from Scott Hooke's blog seems to cover what I'm talking about to a much greater extent, but to be honest, I've only skimmed it:
http://weblogs.asp.net/scottgu/archive/2006/01/15/435498.aspx
source to share