My Favorite Database Wrapper for C#

As a guy who started programming back in the days of DAO, I have nothing but love for ADO.NET. But let’s face it, you can still end up writing pretty lousy code with it. Not everybody’s fully aware of the best uses of the ADO.NET. One of the most common problems I keep stumbling onto in other peoples code is the poor use of ADO.NET Connection Pooling feature.

Somewhere, down the line Microsoft probably started noticing that their cool new data access model wasn’t being fully utilized. The birth of the Microsoft Enterprise Library was probably due to this.

As cool as the Enterprise Library is, I’ve found it to be an over kill for most of my projects. In fact one of my favorite projects featured on CodeProject, was because Enterprise Library’s Offline Application Block was so clunky (and very limited).

Around the same time that I worked on on the SCOAB (Smart Client Offline Application Block), I also wrote a ADO.NET wrapper similar to the Enterprise Library. It pretty much follows the public interface of the Enterprise Library, but it’s a very tiny wrapper compared to the enormous DAAB (Data Access Application Block).

You can download the .cs file here:Ā AbstractDatabase.zip (1.88 kb)

This contains a generic abstract base class called AbstractDatabase. You can extend this class to use it with any type of relational database type. If you take a look at my SQLite Membership Provider, you’ll find this class derived to be used with SQLite ADO.NET Wrapper. I have simply called that class Database, and it’s code looks something like this:

public class Database : AbstractDatabase<SQLiteConnection, SQLiteCommand, SQLiteDataAdapter>
{
    protected override string  GetConnectionString()
    {
        return string.Format(ConfigurationManager.ConnectionStrings["BlogEngine"].ConnectionString,
                            HttpContext.Current.Server.MapPath("~/App_Data"));
    }
}

As you see, the only member you need to override is the GetConnectionString() method. This brings up the question as to why I made this abstract. Why not read the connection string value from the web.config? Well, I’ve done this because I use this class on both web and desktop projects. And in some cases, especially with WinForms applications, the database is SQLite and resides in the application directory. When that happens, I can always hard code the database file name.

Here’s a example of the the Database class’s usage (taken from the SQLiteBlogProvier class).

using (Database db = new Database())
    using (DbTransaction txn = db.BeginTransaction())
    {
        // SQLite doesn't support TRUNCATE
        DbCommand cmd = db.GetSqlStringCommand("DELETE FROM be_Settings"); 
        db.ExecuteNonQuery(cmd, txn);

        cmd = db.GetSqlStringCommand(
                    "INSERT INTO be_Settings (SettingName, SettingValue) VALUES (@name, @value)");
        db.AddInParameter(cmd, "@name", DbType.String, "");
        db.AddInParameter(cmd, "@value", DbType.String, "");
        
        foreach (string key in settings.Keys)
        {
            cmd.Parameters[0].Value = key;
            cmd.Parameters[1].Value = settings[key];
            cmd.ExecuteNonQuery();
        }
        txn.Commit();
    }

A quick rundown of the public interface of AbstractDatabase class:

void AddInParameter(System.Data.Common.DbCommand, string, System.Data.DbType, object)
void AddInParameter(System.Data.Common.DbCommand, string, System.Data.DbType, int, object)
DbTransaction BeginTransaction()
void Dispose()
DataSet ExecuteDataSet(System.Data.Common.DbCommand)
int ExecuteNonQuery(System.Data.Common.DbCommand)
ExecuteNonQuery(System.Data.Common.DbCommand, System.Data.Common.DbTransaction)
DbReader ExecuteReader(System.Data.Common.DbCommand)
ExecuteReader(System.Data.Common.DbCommand, System.Data.CommandBehavior)
T ExecuteScalar<T>(System.Data.Common.DbCommand, T)
abstract string GetConnectionString()
DbCommand GetSqlStringCommand(string)
DbCommand GetSqlStringCommand(string, params object[])
DbCommand GetStoredProcedureCommand(string)
Connection { get; }

Update (June 20, 2008): I have updated this class with a minor bug fix and have added support for other parmeter types including Output and InputOuput parameters (Thanks Yordan).

Update (Oct 20, 2008): This wrapper has been updated and made a part of the Codoxide Common Library

14 thoughts on “My Favorite Database Wrapper for C#

  1. Hi this post rocks!!!

    You should put a donate paypal link to it ; )

    How to get a dataset from stored procedure using the AbstractDatabase wrapper:
    Obviously I am doing something wrong :

    public bool Meta_Data_Get ( ref string msg, ref DataSet ds, ref string dbObjectName , ref bool visible , ref string tableName )
    {
    try
    {
    using (Database db = new Database ( ))
    using (DbTransaction txn = db.BeginTransaction ( ))
    { //comm — so existing users are overwritten !!!

               string storedProcedureName; 
               DbCommand cmd; 
               storedProcedureName = &quot;Meta_Data_Get&quot;; 
    
               cmd = db.GetStoredProcedureCommand ( storedProcedureName ); 
    
               db.AddInParameter ( cmd, &quot;@Name&quot;, DbType.String, dbObjectName ); 
               db.AddInParameter ( cmd, &quot;@Visible&quot;, DbType.Boolean, visible ); 
               db.AddInParameter ( cmd, &quot;@Deftable&quot;, DbType.String, tableName ); 
    
               Utils.Debugger.WriteIf ( &quot;My stored procedure text is &quot; + cmd.CommandText ); 
    
    
    
               cmd.Transaction = txn; 
               ds = db.ExecuteDataSet ( cmd ); 
    
    
               msg = &quot;The data updated successfully !&quot;; 
               return true; 
               } //eof using 
           } //eof try 
       catch (Exception e) 
           { 
           Utils.Debugger.WriteIf ( &quot;The following Exception occured : 
    

    " + e.Message );
    msg = "An error in the application occurred. Report to advanced support error_code: mm1 " + e.Message;
    return false;
    } //eof catch

    Like

  2. public DbParameter AddInOutParameter ( DbCommand cmd, string paramName, DbType paramType, int size, object value )
    {
    DbParameter param = cmd.CreateParameter ( );
    param.DbType = paramType;
    param.ParameterName = paramName;
    param.Size = size;
    param.Value = value;
    param.Direction = ParameterDirection.Output;
    cmd.Parameters.Add ( param );
    return param;
    }

        public DbParameter AddInOutParameter ( DbCommand cmd, string paramName, DbType paramType, object value ) 
            { 
            DbParameter param = cmd.CreateParameter ( ); 
            param.DbType = paramType; 
            param.ParameterName = paramName; 
            param.Value = value; 
            param.Direction = ParameterDirection.Output; 
            cmd.Parameters.Add ( param ); 
            return param; 
            } 
    

    Like

  3. Yordan,
    Hey, I'm really sorry I couldn't reply to your comment. BlogEngine has apparently stopped sending me comment notifications. I need to find out why.

    Yes, I was intending on including a method for adding an output parameter as well. Somehow, have missed it. I'll update as soon as my laptop comes back from repair… which should be any day now.

    Thanks for the PayPal idea. I really want to post bit more code before I considered a step like that šŸ™‚
    Don't want to seem too gready šŸ˜€

    Like

  4. I tried using the AbstractDatabase class for an Oracle connection, but I get the following error.

    Error 1 The type 'Oracle.DataAccess.Client.OracleCommand' cannot be used as type parameter 'COMMAND_TYPE' in the generic type or method 'SERP.core.Providers.AbstractDatabase<CONNECTION_TYPE,COMMAND_TYPE,ADAPTER_TYPE>'. There is no implicit reference conversion from 'Oracle.DataAccess.Client.OracleCommand' to 'System.Data.Common.DbCommand'. C:Documents and SettingsdhawsMy DocumentsVisual Studio 2008ProjectsSERPSERPProvidersOracleProviderDatabase.cs 13 18 SERP.core

    The code looks like this:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Configuration;
    using Oracle.DataAccess.Client;

    namespace App.core.Providers.OracleProvider
    {
    /// <summary>Wrapper for Oracle Database interations</summary>
    public class Database : AbstractDatabase<OracleConnection, OracleCommand, OracleDataAdapter>
    {

    }

    I tried using your BlogEngine implementation as an example. I am very new to C#, but would like to implement a database access methodology that is db platform independent; however, Oracle is one of the providers I would like to use.

    Like

  5. Hi dkhaws7,

    I'm not sure what Oracle.DataAccess.Client.OracleCommand is. But the following code will work. I have to admit that I haven't tried this with Oracle and have almost no experience with any flavor of Oracle. But as long as the database client library conforms to ADO.NET specification, the wrapper will work. System.Data.OracleClient which is bundled with .NET framework does conform to it.

    using System.Data.OracleClient;

    namespace Codoxide.Data.Samples
    {
    class Program
    {
    class OracleDb : Database<OracleConnection, OracleCommand, OracleDataAdapter>
    {
    ..

    Like

  6. Thanks, that cleared the errors. I got Oracle.DataAccess.Client from another example. It worked for the example, but did not work for your implementation. Thank you again.

    Like

  7. Excellent post. The AbstractDatabase class works nicely… but aren't you missing methods for 'committing' and 'rolling back' transactions?

    Like

    1. The Commit and Rollback are available through the DbTransaction (returned in db.BeginTransaction()). You can see a code sample in the second snippet in the post.

      Like

  8. does this class work with MS SQL server ?

    i can not seem to get pasted the first line :
    public class Database : AbstractDatabase<SQLiteConnection, SQLiteCommand,…. }

    I have no idea what SQLite is and what parameters are required to get this working for MS SQL!!
    can you provide an example using MS SQL

    THANKS

    LEE

    Like

    1. Lee,
      It sure does work with MS SQL Server.
      SQLite is a embedded SQL database. All you need to do to get this working with SQL Server, is to replace SQLiteXX bits with SqlXX. Which means that you'll replace:
      SQLiteConnection with SqlConnection
      SQLiteCommand with SqlCommand
      SQLiteDataAdapter with SqlDataAdapter

      Like

  9. For Oracle.DataAccess.Client, BindByName was set to false by default, but your wrapper class does not allow resetting the property. So it is not working when using parameters.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.