Patrick.Roeper posted on Thursday, March 20, 2008
The past couple of days I have been hit pretty hard with sorting out bugs from using the Transactional attribute on my dataportal methods. Everything was due to instantiating more than one database connection within the lifetime of a transaction. I've been trying to work out an elegant solution that is reusable and leverages features of the framework/C# so I would like to hear suggestions on how to improve what I have come up with already.
The place where everything starting is when I was making my accounting system more tolerant to failure. Example: When a user receives a payment in the system, I need to save the payment, create a new journal entry, update any credits that were applied, create any credits if the payment was an over payment, update all invoice balances to reflect the payment, ... the list continues. My way of handling this was to make an InvoicePaymentPoster command object who's responsibility was to make sure each of these items happened. I wrapped the DataPortal_Execute with a [Transaction] so that if any part of the entire process failed, nothing would ever be committed to the database.
Each one of these different objects I am working with from within DataPortal_Execute is a root object of its own, so during a fetch/update in any of these objects I am instantiating a new SqlConnection object which is where everything hit the fan. I read some posts on the forums and I saw Rocky mention the LocalContext object and how it could be used to share objects in the AppDomain. The next challenge was to come up with something that would fix my problems now, be elegant, efficient, and be something I could possible put into my codegen templates. So here is what I came up with:
public static class Database
{
public static string Accounting
{
get { return ConfigurationManager.ConnectionStrings["Accounting"].ConnectionString; }
}
private static bool NewConnection(string connectionString, out SqlConnection cn)
{
if (ApplicationContext.LocalContext.Contains(connectionString))
{
cn = (SqlConnection)ApplicationContext.LocalContext[connectionString];
return false;
}
else
{
cn = new SqlConnection(connectionString);
return true;
}
}
public delegate void CommandHandler(SqlConnection cn);
public static void CallCommand(string connectionString, CommandHandler commandMethod)
{
SqlConnection cn;
if (NewConnection(connectionString, out cn))
{
using (cn)
{
cn.Open();
ApplicationContext.LocalContext.Add(connectionString, cn);
try { commandMethod(cn); }
finally { ApplicationContext.LocalContext.Remove(connectionString); }
}
}
else
commandMethod(cn);
}
}
This makes all of my root level DataPortal_XYZ methods look something like...
[Transactional(TransactionalTypes.TransactionScope)]
protected override void DataPortal_Update()
{
Database.CallCommand(Database.Accounting, CommandUpdate);
}
private void CommandUpdate(SqlConnection cn)
{
if (base.IsDirty)
ExecuteUpdate(cn, null);
//update child object(s)
UpdateChildren(cn);
}
Everything is working in my application now; database connections are being shared across the multiple root objects, the code is repeatable and easy to implement for anyone after me, and I can put this into my codegen templates. Also, when it comes to fetching objects, I had to deal with the extra filter criteria parameter so I did the following:
public delegate void CommandHandlerWithCriteria<T>(SqlConnection cn, T criteria);
public static void CallCommand<T>(string connectionString, CommandHandlerWithCriteria<T> commandMethod, T criteria)
{
SqlConnection cn;
if (NewConnection(connectionString, out cn))
{
using (cn)
{
cn.Open();
ApplicationContext.LocalContext.Add(connectionString, cn);
try { commandMethod(cn, criteria); }
finally { ApplicationContext.LocalContext.Remove(connectionString); }
}
}
else
commandMethod(cn, criteria);
}
And the root implementation looks like....
private void DataPortal_Fetch(Criteria criteria)
{
Database.CallCommand(Database.Accounting, ExecuteFetch, criteria);
}
private void ExecuteFetch(SqlConnection cn, Criteria criteria)
{
using (SqlCommand cm = cn.CreateCommand())
{
....
So I am only a junior developer (been out of college for all of 2 months now
![Big Smile [:D]](/emoticons/emotion-2.gif)
) and I am sure there is some code smell I haven't gotten a wiff of. I would appreciate any insight to potential problems this implementation could cause as well as suggestions on ways to make it better (e.g. standards I am not adhering to). When I make the jump to .NET 3.5 I plan on using the Action<T> since it supports multiple parameters in .NET 3.5; that would allow me to drop my delegates and make the code a little cleaner.
Thanks!
