AboutCode.net - Data Access Syntax Using Anonymous Methods

Thoughts on Software by Andrew Davey

Monday, August 27, 2007

Data Access Syntax Using Anonymous Methods

I have so far managed to avoid needing to use any fancy ORM tools. However, this doesn't mean I like writing all the standard ADO.NET code by hand. Using C# 2.0 anonymous methods I am able to write code like the following. (I'm not sure all of this is truly original work; if you have already done this then sweet! I just want to share the ideas with everyone.)

Customer c = With.Database<Customer>(delegate(Database db)
{
    return db.ExecuteReader<Customer>(
        "select Id, FirstName, Surname from Customer where Id = @id", // The SQL to execute
        delegate(IDbCommand cmd) // This anonymous function is called before executing so we can add params.
        {
            db.AddParameter(cmd, "@id", DbType.Int32, id);
        },
        delegate(IDataReader reader) // The IDataReader returned from ExecuteReader is passed here.
        {
            if (reader.Read())
                return new Customer(
                    reader.GetInt32(0),
                    reader.GetString(1),
                    reader.GetString(2));
            else
                return null;
        });
});

My Database class manages the creation of a connection and optionally a transaction. It then exposes methods to invoke SQL commands (Reader, Scalar and NonQuery). A transaction can be automatically provided by calling With.DatabaseInTransaction( ... ) instead. This then wraps the inputted action in a try-catch-finally block, commit and rolling back in the usual places.

("With" is a static class that provides convenient access to the Database object)

public static T DatabaseInTransaction<T>(Function<Database, T> function)
{
    using (Database db = new Database())
    {
        db.BeginTransaction();
        try
        {
            T result = function(db);
            db.CommitTransaction();
            return result;
        }
        catch
        {
            db.RollbackTransaction();
            throw;
        }
    }
}

There are generic and non-generic versions of the functions, depending on if we want to return a value.

For example, here is the non-generic ExecuteReader method from Database:

public void ExecuteReader(string sql, Action<IDbCommand> addParameters, Action<IDataReader> action)
{
Debug.Assert(sql != null, "sql cannot be null.");
Debug.Assert(action != null, "action cannot be null.");

    using (IDbCommand cmd = CreateCommand(sql))
    {
        if (addParameters != null)
        {
            addParameters(cmd);
        }
        using (IDataReader reader = cmd.ExecuteReader())
        {
            action(reader);
        }
    }
}

The IDbCommand is created using the following method. Notice that we also handle assigning the transaction if we're in one.

IDbCommand CreateCommand(string sql)
{
Debug.Assert(_connection != null && _connection.State == ConnectionState.Open);

    IDbCommand cmd = _connection.CreateCommand();
    cmd.CommandText = sql;
    cmd.CommandType = CommandType.Text;
    if (_transaction != null)
    {
        cmd.Transaction = _transaction;
    }
    return cmd;
}

The Database class implements IDisposable, thus allowing the "using" syntax in the With class methods. In Dispose() I close the connection, if the transaction is still open I rollback first.

I anyone wants the full Database and With classes drop me a line. I'm looking forward to C# 3.0 since the improved type inference and lambda syntax will slim down the amount of keyboard time even more!

Monday, August 27, 2007 9:41:09 AM (GMT Standard Time, UTC+00:00)

Tuesday, August 28, 2007 1:50:55 PM (GMT Standard Time, UTC+00:00)

I do something very similar. I don't implement the transactional code as I haven't had much use for it.

I think there is a common pattern here that many people will "invent" on their own but ultimately will look very similar.

The hard part is getting my fellow programmers up to speed on it. The calling syntax is ugly and I too am looking forward to C# 3.0 for the very same reasons you mentioned.

Chris Weber | webercjAT NOSPAMgmail dot com

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):