Tuesday, September 5

Generic Business layer

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.


  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     {
  193     }
  194     private static int GenericInsert(string storedProc,
  195     params SqlParameters[] parameters)
  196     {
  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.

Do you remember the discussion we had about using the "using" clause on my other blog?

I think it is very good practice to move the burden of cleanup on the resource objects themselves rather than the client who uses the resource by having the resource implement the IDisposable method. All the client does is limit the scope of the resource by using the "using" clause. When the resource goes out of scope, the appropriate cleanup is automatically done. This is a first class design pattern and "best practice."
Edit to previous comment: I meant to say, "IDisposable interface", not "IDisposable method".
Yes, your blog was very helpful in revising for the C# exam. I have started using "using() {}" every where now.

I haven't actually implemented a IDisposable interface for any custom resource as yet.

Where have you used it?
Post a Comment

Subscribe to Post Comments [Atom]

<< Home

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]