Quick and Efficient Database Library

In recent projects the need to continually re-write database access routines came up over and over.  I got tired of redoing the same code and so I wrote these quick database functions and extensions.  They are fully unit-tested and I'll include those at the end.

I think they could be made slightly more efficient using lambda expressions to handle the try-catch block, but that is a future enhancement that I might make.

For now, here's the first function: ExecuteScalar.  The power of these functions is that they are not tied to a specific database type such as SqlServer.  The use the generic System.Data interfaces for all external objects and even when they throw an exception they throw the generic DbException (from which exceptions such as SqlException inherit).  This allows you to theoretically connect and execute commands on a wider range of relational database types (though I have not tested it against MySql, Oracle, etc).

The function parameters are quite simple and straight-forward.  Pass in a connection, the name of the stored procedure (or the inline sql if you wish).  Optional arguments include a parameter list, an error message to include if an exception is thrown, and if you wish to trap any exceptions you can.

Code Snippet

29. public object ExecuteScalar(IDbConnection dbConnection, string storedProcedureName, IEnumerable<IDataParameter> parameters = null,
30. string errorMessage = "", bool throwException = true)
31. {
32.   object result = null;
33.   try
34.   {
35.     using (IDbCommand dbCommand = dbConnection.CreateCommand())
36.     {
37.       SetupDbCommand(dbConnection, dbCommand, storedProcedureName, parameters);
38.       dbCommand.Connection.Open();
39.       result = dbCommand.ExecuteScalar();
40.       dbCommand.Connection.CleanupConnection();
41.     }
42.   }
43.   catch (DbException ex)
44.   {
45.     dbConnection.CleanupConnection(); 
46.     if (throwException)
47.       throw;
48.   }
49.  
50.   return result;
51. }

This works great if all you are expected to be returned is a row count or a single value, but you need more if you expect an actual data-set. In that situation we have:

Code Snippet

141. public T ExecuteQuery<T>(IDbConnection dbConnection, string storedProcedureName, Func<IDataReader, T> translateFunction, IEnumerable<IDataParameter> parameters = null, string errorMessage = "", bool throwException = true) where T : class, new()
142. {
143.   if (translateFunction == null) throw new ArgumentNullException("translateFunction");
144.  
145.   try
146.   {
147.     using (IDbCommand dbCommand = dbConnection.CreateCommand())
148.     {
149.       SetupDbCommand(dbConnection, dbCommand, storedProcedureName, parameters);
150.       dbConnection.Open();
151.       using (IDataReader dbReader = dbCommand.ExecuteReader())
152.       {
153.         var result = new T();
154.         if (dbReader != null)
155.           result = translateFunction.Invoke(dbReader);
156.  
157.         dbCommand.Connection.CleanupConnection();
158.  
159.         return result;
160.       }
161.     }
162.   }
163.   catch (DbException ex)
164.   {
165.     dbConnection.CleanupConnection();
166.  
167.     if (throwException)
168.       throw;
169.   }
170.  
171.   return null;
172. }

This function takes a data reader and passes it to a custom translation function you pass in.  The translation function is responsible for retrieving the data from the reader and creating the object or objects you wish to return.  In most cases I have used this by creating a static translate function on the class itself.  I have also created a variant of this function to handle returning an IEnumerable<> of the object type.

Finally, one key component these functions don't handle is transaction management.  For that, for a unit of work, you'd need to wrap the calls in your own transaction statements complete with commit and rollback handling.

Enjoy and as usual I welcome and encourage feedback!