Advanced Basics: Handling Transactions Between .NET Components

Handling Transactions Between .NET Components

Download the code for this article: Basics0205.exe (88KB)

This month I'll dig into a question from a reader about transactions that applies to both Web and Windows®-based applications. I'm happy to report that this is the first month I was able to use the shipping bits of Visual Studio® .NET and the Microsoft® .NET Framework. No more beta for a while!
Q** I read your column in the February 2002 issue of MSDN® Magazine regarding COM+, DCOM, and MSMQ serialization in .NET. You said that if a component is performing transactions on a single database and you expect that you'll always be going against only one database, then you don't necessarily need COM+ to implement those transactions; instead, you can implement them with ADO.NET. This seems to be a big change in philosophy.
      Could you give me more information about how I can handle transactions between .NET components even though I am dealing with the same database? If I pass on the connection string and keep a connection open, doesn't that incur overhead as well?
A** This is a great question. Instead of providing a quick answer, I'll dive into this a bit with a sample app that I created. After I walk through the sample, I will address your question on performance.
      First, let's deal with the issue of creating transactions with ADO.NET. I created a sample application that inserts from one to four order lines into the Order Details table in the sample Northwind database. The application is designed to either commit all inserts or roll them back if an error occurs for any reason. The application uses SQL Server™ and the SQLClient data provider. You can do essentially the same thing using the OLE DB provider as long as your database supports transactions.
      Instead of just relying on the sample transaction code in the documentation that explains how ADO.NET transactions work, my sample application uses a simple multilayer approach. All of the database code is in a separate components class while the front end is in a Windows form in a separate project.
      The database component contains one class, which is named DBStuffADONET (see Figure 1). Notice that a private variable contains the connection string. This is fine when the component does not need to be used with different databases. However, the component also has an overloaded constructor that takes a new connection string that can be specified when you instantiate the class.
      After creating the connection string variable, I also create private variables for the SqlConnection and the SqlTransaction instances. More on this later.
      The class uses the RunSQLWithDataSet function that I showed in the April 2002 issue. This function does not participate in the transactions; instead, it returns a transactional DataSet. In this example, I am using static SQL instead of stored procedures to keep things simple.
      Now let's take a look at the functions that participate in the transactions and a few more that don't. The OpenConnectionTrans routine creates a new SqlConnection and then opens it. Then it creates a database transaction, BeginTransaction, and sets the TransactionCurrent variable to reference the transaction, as you can see here:

  Public Sub OpenConnectionTrans()
  

      ConnectionCurrent = New SqlConnection(sConnectionString)
  

      ConnectionCurrent.Open()
  

      TransactionCurrent = ConnectionCurrent.BeginTransaction()
  

  End Sub
  

If you need a connection and do not need transaction support, then you can call OpenConnection:

  Public Sub OpenConnection()
  

      ConnectionCurrent = New SqlConnection(sConnectionString)
  

      ConnectionCurrent.Open()
  

  End Sub
  

When it's time to commit the transaction, the following function is called:

  Public Sub CommitTransaction()
  

      TransactionCurrent.Commit()
  

  End Sub
  

If you need to roll back the transaction, then call this function:

  Public Sub RollbackTransaction()
  

      TransactionCurrent.Rollback()
  

  End Sub
  

      When you have finished working with a connection, you should close it. The following function closes the connection and removes the reference to the connection object:

  Public Sub CloseConnection()
  

      If ConnectionCurrent Is Nothing Then
  

          Exit Sub
  

      End If
  

      If ConnectionCurrent.State = ConnectionState.Open Then
  

          ConnectionCurrent.Close()
  

          ConnectionCurrent = Nothing
  

      End If
  

  End Sub
  

      Now, what happens when you need to execute an insert or update that is part of a transaction? That's what the RunSQLNonQuery function is for. When you call it, this function creates a new instance of the SqlCommand class, then sets the Connection property to the current connection (ConnectionCurrent) and the Transaction property to the current transaction (TransactionCurrent). Then the SQL statement is executed with the ExecuteNonQuery method, which has low overhead because it does not return any data (see Figure 2).
      The last function in the class is RunSQLScalar, which executes a SQL statement and returns only a single piece of data. This function is useful when you need to grab one piece of data, such as the Unit Price for a product.
      If you study this class for a minute, you can see that it is stateful. You must instantiate the class, open a connection with a transaction, then execute the code that you want to be part of the transaction. Then you have to commit or roll back the transaction, and finally, you have to close the connection. This is not really a big deal since you could instantiate the class, do the work, then close the connection in either a Windows-based or Web application. My sample app uses a Windows-based front end and simply instantiates the class for the duration of the application's running time.
      Figure 3 shows the interface for the Windows form. The interface is simple. You select an order from the list at the top, then click the arrow buttons to add detail rows. Figure 3 shows the form with two detail rows. This sample has the quantity and discount fields dummied in at 1 and .15, respectively, for testing purposes.

Figure 3 ADO.NET Transaction Form

      After you have entered the line items, you click the Insert—ADO.NET Trx button in order to add the new entries to the Order Details table of your database.
      Now, let's look at the form construction. The Orders list (cboOrders) and the Products list (cboProducts) are comboboxes that provide the list of orders and products. Here's a bit of trivia that may save you some time. When I built the form, I hid all of the data entry controls and turned on each row as needed. I set the cboProducts. Visible property to False and tried to turn it on when I turned on a row of controls. For some reason, this generated a runtime error every time. So, I wondered what would happen if I put cboProducts in a Panel control. I placed a Panel on the form (pnlProduct), placed cboProducts in it, set the panel's size to match the cboProducts, and set its location and visibility instead of setting them for cboProducts. This worked fine. Go figure. Figure 4 shows the form in design view.

Figure 4 Design-time Transaction Form

      The combobox control sitting just below the Orders combobox control is cboProducts. It is dynamically placed on top of the first textbox in a row (txtProductName_1, for instance) when the user is entering data in that one row.
      You can explore the rest of the interface by downloading the sample code at the link at the top of this article. Let's take a look at the code that deals with transactions in the client. When the user clicks the Insert—ADO.NET Trx button to insert the order details, the cmdInsertADONet_Click event executes.
      The first three lines of the event declare variables:

  Dim sSQL As String
  

  Dim sStatus As String = ""
  

  Dim sOrderID As String
  

At this point, the CloseConnection method is called. If there is no connection, an error does not occur, but if there is a connection, the connection is closed:

  oDB.CloseConnection()
  

Next, a connection is opened and a transaction started:

  oDB.OpenConnectionTrans()
  

The next few lines of code are straightforward; they simply set controls, make sure that a product name has been entered, and obtain the current order:

  lblMessage.Text = ""
  

  If txtProductName_1.Text <> "" Then
  

  sOrderID = cboOrders.SelectedValue
  

  If sOrderID = "" Then
  

  lblMessage.Text = "You must select an order to add line items to"
  

  Exit Sub
  

  End If
  

The next several lines insert order details into the database by calling the InsertOrderDetail function. Each of these lines is called in the following format, where the values from the form are passed into the function:

  'Insert 1st order
  

  sStatus = InsertOrderDetail(sOrderID, _
  

      txtProductID_1.Text, _
  

      txtUnitPrice_1.Text, _
  

      txtQuantity_1.Text, _
  

      txtDiscount_1.Text)
  

      The only things that change for subsequent calls are that sStatus is checked to make sure it does not contain an error message and that the txtProductID control is not empty. If these criteria are met, the call to InsertOrderDetail is made:

  'Insert 2nd order
  

  If sStatus = "" And txtProductID_2.Text <> "" Then
  

  sStatus = InsertOrderDetail(sOrderID, _
  

      txtProductID_2.Text, _
  

      txtUnitPrice_2.Text, _
  

      txtQuantity_2.Text, _
  

      txtDiscount_2.Text)
  

  End If
  

I have omitted the calls for the third and fourth lines as they are identical to the last one, except they reference different controls.
      After the last call to InsertOrderDetail, you can clean up the transaction. If sStatus contains anything, an error occurred and the transaction is rolled back by calling the RollbackTransaction method. Error messages will also be displayed in the lblMessage control, as you can see here:

  'Cleanup and rollback / commit
  

  If sStatus <> "" Then
  

  lblMessage.Text = _
  

      "Rolled back transaction due to error " _
  

      on row " _
  

      & " — " & sStatus
  

  oDB.RollbackTransaction()
  

  Exit Sub
  

  End If
  

      Finally, if no errors occurred, then the CommitTransaction method is called to complete the transaction. LblMessage is updated to reflect a successful write to the database, and the connection is closed:

  oDB.CommitTransaction()
  

  lblMessage.Text = "Line items inserted ok"
  

  oDB.CloseConnection()
  

  End If
  

      The InsertOrderDetail function is quite simple. The function header looks like this:

  Function InsertOrderDetail(ByVal sOrderID As String, _
  

      ByVal sProductID As String, ByVal sUnitPrice As String, _
  

      ByVal sQuantity As String, ByVal sDiscount As String) _
  

      As String
  

Next, two variables are created:

  Dim sSQL As String
  

  

  Dim sInsertStatus As String
  

Then the SQL statement is created using the parameters passed in:

  sSQL = "INSERT INTO [Order Details] " _
  

      "(OrderID, ProductID, UnitPrice, Quantity," _ 
  

      "Discount)  " 
  

  sSQL &= "VALUES(" & sOrderID & "," & sProductID & "," _
  

      & sUnitPrice & ","
  

  sSQL &= sQuantity & "," & sDiscount & ")"
  

Finally, a Try/Catch block contains the call to RunSQLNonQuery, which actually executes the SQL. Notice that an exception is generated if sInsertStatus is set to any non-blank value. This allows the code to trap all error conditions in the Catch block:

      Try
  

          sInsertStatus = oDB.RunSQLNonQuery(sSQL)
  

          If sInsertStatus <> "" Then
  

              Throw New System.Exception(sInsertStatus)
  

          End If
  

      Catch exc As Exception
  

          Return exc.Message
  

      End Try
  

  End Function
  

That's it. Pretty simple. You can see that ADO.NET does allow you to code transactions pretty easily as long as your database supports them.
      The second part of this month's question deals with performance. Of course, keeping a connection open can cause performance issues. As shown in this example, you can still use ADO.NET to handle transactions and it's up to you how long you keep the connection open. ADO.NET also supports connection pooling, so opening and closing connections should add very little overhead, as long as you use the same connection string.

Send questions and comments for Ken to basics@microsoft.com.Ken Spencer works for 32X Tech (https://www.32X.com). 32X provides training, software development, and consulting services on Microsoft Technologies.

From the May 2002 issue of MSDN Magazine