.NET Performance Blog

April 10, 2006

Connection/Transaction scope

Filed under: XLib Framework — Eric P @ 12:57 pm

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:

http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx

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:

  • XOpenCnScope
  • 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())

{

InsertRecord(1001);
InsertRecord(1002);

scope1.Complete();

}

public void InsertRecord(int recID)

{

XCommand cmd = new XCommand();

string sql = "INSERT INTO Db.Sample_XOpenCnTransScope (ID, Name) VALUES (" + customerID + ", '" + "Name_" + recID + " ')";

cmd.ExecuteSql(sql);

}
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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: