Lotushints

Lately, I’ve started noticing a pattern in data layer of one of our projects at work. The pattern looks like this:

SqlConnection connection = null;
SqlTransacton transaction = null;
try {
    connection = new SqlConnection(connectionString);
	connection.Open();
	transaction = connection.BeginTransaction("transactionName");
	// execute database queries and do mapping and stuff
}
catch (Exception) {
	if (null != transaction) {
		transaction.Rollback();
	}
	throw;
}
finally {
	if (null != connection) {
		connection.Close();
	}
}

This repeats itself in just about every data layer method. Lines and lines of useless, repeating code for which I am also to take a lot of blame. So I thought: “There must be a better way than this.”

And there is. I created this method in data layer base class:

public T ExecuteDbCommand<T>(string connectionString, Func<SqlConnection, SqlTransaction, T> action)
{
	SqlConnection connection = null;
	SqlTransaction transaction = null;
	try
	{
		connection = new SqlConnection(connectionString); 
		connection.Open(); 
		transaction = connection.BeginTransaction("transactionName");
		return action(connection, transaction);
	}
	catch (Exception)
	{
		if (null != transaction)
		{
			transaction.Rollback();
		}
		throw;
	}
	finally
	{
		if (null != connection)
		{
			connection.Close();
		}
	}
}

This enables me to now change every data layer method to look like this:

return ExecuteDbCommand<ReturnClass>((connection, transaction) =>
{
	var command = connection.CreateCommand();
	command.Transaction = transaction;
	// do database stuff and port result to ReturnClass
	// return ported result
	// commit transaction if needed (on update or insert statement)
});

This solution has a small issue though. If you are doing insert or update, you might not want to return anything. As you cannot return void, just define returning type to be object and return null. I am prepared to live with this.