I have been doing some research on already available classes for keeping track of open transactions and connections. For .NET 2.0 Microsoft has released TransactionScope class which really simplifies keeping track of simple, nested and distributed transactions. It is very simple to use and MSDN documentation is pretty good. The only issue I found was that if you keep re-creating connection inside a transaction – the final transaction would be distributed which may result in performance loss.
Luckily one of the members of ADO.NET has countered the problem here:
His class DbConnectionScope keeps track of already open connections and re-uses them within a TransactionScope. Following his lead I came up with two classes:
- XOpenCnTransScope (derived from XOpenCnScope)
These two classes allow me to open connection or connection & transaction scope using one statement. Internally open DbConnection and TransactionScope are re-used until all the nested Scopes have been closed.
Here is an example of open connection with transaction:
using (XOpenCnTransScope scope1 = new XOpenCnTransScope())
public void InsertRecord(int recID)
XCommand cmd = new XCommand();
string sql = "INSERT INTO Db.Sample_XOpenCnTransScope (ID, Name) VALUES (" + customerID + ", '" + "Name_" + recID + " ')";
XCommand object is aware of XOpenCnScope and XOpenCnTransScope (similar of how TransactionScope is aware of DbConnection.Open). So when XCommand executes a query it will use the connection with transaction provided by OpenCnTransScope.
Actually if you create a new XCommand outside of OpenCnScope – it will automatically open default application connection – execute the query and close connection. That saves 4-5 lines of code.