Code Luck

On my current project I have discovered a nice way to write generic database access code in the business layer.
Most database access operations are

a. returning DataTable of results (for binding with
objectdatasource & other controls)

b. Inserting/updating data

c. returning a single value (execute scalar)

The approach I have been following is to reduce the number of places where I write database code (code reuse) so as to make it maintainable and make necessary changes in a few places instead in all of the business layer.

e.g

  148 public class DBHandler

  149 {

  150     private static DataTable GenericGetDataTable(string storedProc,

  151     params SqlParameters[] parameters)

  152     {

  153         DataTable retTable = null;

  154         string connStr = Configuration.AppSettings["ConnectionString"];

  155         using (SqlConnection conn = new SqlConnection(connstr))

  156         {

  157             conn.open();

  158             SqlCommand cmd = new SqlCommand(storedProc, conn);

  159             foreach (SqlParameter para in parameters)

  160             {

  161             cmd.Parameters.Add(para);

  162             }

  163             cmd.CommandType = CommandType.StoredProcedure;

  164             .

  165             .

  166             .

  167             .

  168             conn.close();

  169         }

  170         return retTable;

  171     }

  172     public static DataTable GetBooks( int publicationID, string authorName)

  173     {

  174         SqlParameter pubID = new SqlParameter("@pubID", publicationID);

  175         SqlParameter authName = new SqlParameter("@authName", authorName);

  176         return GenericGetDataTable("sp_getAllBooks", pubID, authName);

  177     }

  178     public static DataTable GetPapers(int journalID, int year)

  179     {

  180         SqlParameter jourID = new SqlParameter("@journalID", journalID);

  181         SqlParameter jYear = new SqlParameter("@year", year);

  182         return GenericGetDataTable("sp_getAllPublications", jourID, jYear);

  183     }

  184     .

  185     .

  186     .

  187     .

  188     // similarly for INSERT & Execute Scalar methods

  189     private static object GenericExecuteScalar(string storedProc,

  190     params SqlParameters[] parameters)

  191     {

  192 

  193     }

  194     private static int GenericInsert(string storedProc,

  195     params SqlParameters[] parameters)

  196     {

  197 

  198     }

  199 }

Here we use the params SqlParameters[] parameters to accept variable number of arguments in turn enabling us to write generic methods.
This way we minimize the places where we write database access code. I think I achieved a lot of code reuse by this aproach.

One would say that why not have ONE generic public method in DBHandler (for each operation) that accepts a storedProc & parameters. The only argument I have against this approach is that, with this approach my "page behinds" (controllers) will be clouded by names of stored procedures and SqlParameters statements. I think this would cause more maintainance depending on all the places from where it is called. I think it wouldn't be completely MVC complaint.

In this approach, only the changes need to made to the DBHandler class and specific methods if needed.

I have also realised from reading some posts from microsoft gurus that they are prominently using "using () { }" in their database access operations in order to dispose of the connection objects.