transaction is a very common operation in our daily lives as well as in the computing world. For example, when you try to deposit a check issued by your boss, the bank first needs to debit the stated amount from your boss’s account before the money is credited into your account. If your boss does not have sufficient funds in his account, the money will not be credited into your account. Similarly, after debiting the money from your boss’s account, if the bank is not able to credit the money into your account for some reason (perhaps you cancelled the account), the amount will be credited back to your boss’s account. As you can see, for this transaction to be successful, these two operations must complete, or else any one of the operations needs to be rolled back (reversed).

In this article, I will show you how transactions are implemented in Windows Workflow. By using the transactions support in Windows Workflow, you can drastically reduce the overhead needed to write custom code to implement transactional behavior.

What You Need

For this article, you need the following components: ?Visual Studio 2005 ?Microsoft Pre-Release Software Microsoft .NET Framework 3.0–Release Candidate ?Microsoft Visual Studio 2005 Extensions for Windows Workflow Foundation Release Candidate 5

To implement workflow transactions, you need to install the SqlPersistenceService database as outlined in my previous article, “Execute a Long-Running Workflow Using Persistence in Windows WF.”

Author’s Note: For the 3.0 release of the .NET Framework, the path to the SQL scripts is: C:WINDOWSMicrosoft.NETFrameworkv3.0Windows Workflow FoundationSQLEN.

Preparing the Database
In this article, I will create a simple application, using Windows Workflow, to show how funds can be transferred from one account to another and how to use transactions to ensure that the transfer is performed correctly.

The first step to creating the application is to create the database to store the bank account information.

In Visual Studio 2005, go to Server Explorer (View | Server Explorer). Right-click on Data Connections and select Create New SQL Server Database? (see Figure 1).

Figure 1. Create a new SQL Server database. This will be used to store the bank account information for the sample application.

?

Figure 2. Give the new database a name. I chose BankDatabase.

In the text box for the Server name enter “.SQLEXPRESS” (assuming you have SQL Server 2005 Express edition installed locally on your development machine). Name the database BankDatabase (see Figure 2).

The database will now be created and will appear under the Data Connections item in Server Explorer. Now I want to add a new Table to the database. Expand the new database name, right-click on Tables (see Figure 3), and select Add New Table.

Figure 3. Add a new table to a database.

?

Figure 4. Define the fields in the table.

Define the fields for your new table as shown in Figure 4. Click the Save icon and name the table Accounts.

Now that the table is defined, I want to populate it with some data. Right-click on the Accounts table name in Server Explorer and select Show Table Data (see Figure 5).

Figure 5. View the content in the table.

?

Figure 6. Populate the table with two records.

Populate the Accounts table with the data shown in Figure 6. The first column represents two bank customers while the second column is their respective bank balances.

Creating the Application
I’ll now use Windows Workflow to create a new Sequential Workflow Console Application. This will allow me to demonstrate how transactions are implemented in Windows Workflow. Name the project TransactionWorkflow (see Figure 7).

Figure 7. Create a new Sequential Workflow Console Application in Windows Workflow.

?

Figure 8. Design the workflow by adding the activities in proper sequence.

Designing the Workflow
Populate Workflow1.vb with the activities shown in Figure 8. These include a TransactionScope and three Code activities. You must add them to the form in the top-down sequence shown in Figure 8. Set the (Name) properties of the three Code activities as “DebitAccount,” “CreditAccount,” and “TransactionCompleted,” respectively.

Double-click on each Code activity to switch to the code-behind of the workflow. This will add and activate the event handler for each Code activity for you automatically.

The code-behind of each Code activity should look like this:

Public class Workflow1    Inherits SequentialWorkflowActivity    Private Sub DebitAccount_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)    End Sub    Private Sub CreditAccount_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)    End Sub    Private Sub TransactionCompleted_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)    End SubEnd Class

As you can guess from the workflow, in this application you will debit an amount from one account and then credit the same amount into the other account. If any one of these events fails, the entire transaction rolls back.

Coding the Workflow
Now that you have designed the workflow, it’s time to write the code to wire up all the activities. But first, add a connection string of the database to use in the Settings tab of the project property page. To do this, right-click on project name in Solution Explorer and select Properties (see Figure 9).

Name the setting “ConnectionString” and set its value as “Data Source=.SQLEXPRESS;Initial Catalog=BankDatabase;Integrated Security=True;Pooling=False”.

Figure 9. Add a new connection string as an application setting.

In Module1.vb, add the boldface portion of the following code snippet so that the workflow application can make use of the SqlWorkflowPersistenceService service:

        Shared Sub Main()            Using workflowRuntime As New WorkflowRuntime()                AddHandler workflowRuntime.WorkflowCompleted, _                AddressOf OnWorkflowCompleted                AddHandler workflowRuntime.WorkflowTerminated, _                AddressOf OnWorkflowTerminated                workflowRuntime.AddService( _                  New SqlWorkflowPersistenceService( _                  "Initial Catalog=SqlPersistenceService;" & _                  "Data Source=.SQLEXPRESS;Integrated " & _                  "Security=SSPI;"))                Dim workflowInstance As WorkflowInstance                workflowInstance = _                   workflowRuntime.CreateWorkflow(GetType(Workflow1))                workflowInstance.Start()                WaitHandle.WaitOne()                Console.ReadLine()            End Using        End Sub

Back in the code-behind of Workflow1.vb, code DebitAccount as follows:

    Private Sub DebitAccount_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)        Dim conn As New SqlConnection(My.Settings.ConnectionString)        Dim command As New SqlCommand("UPDATE Accounts SET Balance = Balance - 100 WHERE UserID='12345'", conn)        command.Connection.Open()        command.ExecuteNonQuery()        Console.WriteLine("---Account debited from 12345---")        conn.Close()    End Sub

Here, you debit $100 from account 12345.

Next, you will credit the $100 into account 54321. This is accomplished by the Code activity CreditAccount:

    Private Sub CreditAccount_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)        Dim conn As New SqlConnection(My.Settings.ConnectionString)        Dim command As New SqlCommand("UPDATE Accounts SET Balance = Balance + 100 WHERE UserID='54321'", conn)        command.Connection.Open()        command.ExecuteNonQuery()        Console.WriteLine("---Account credited into 54321---")        conn.Close()    End Sub

The last Code activity (TransactionCompleted) simply prints out the balance of both accounts for 12345 and 54321:

    Private Sub TransactionCompleted_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)        Dim conn As New SqlConnection(My.Settings.ConnectionString)        Dim command As New SqlCommand("SELECT Balance FROM Accounts WHERE UserID='12345'", conn)        command.Connection.Open()        Dim balance As Double = CDbl(command.ExecuteScalar())        Console.WriteLine("---Balance in account 12345 is $" & balance)        command.CommandText = "SELECT Balance FROM Accounts WHERE UserID='54321'"        balance = CDbl(command.ExecuteScalar())        Console.WriteLine("---Balance in account 54321 is $" & balance)        conn.Close()    End Sub

Testing the Workflow
Time to test the workflow! Press F5 in Visual Studio 2005 to debug the workflow. If everything is coded properly, you should see the output shown in Figure 10.

As expected, $100 is debited from account 12345 and credited into account 54321. Hence the balance of account 12345 is $400 and $200 for account 54321.

What happens if there is an error in one of the code activities? Let’s assume that DebitAccount works correctly but there is a runtime error in CreditAccount. To see what happens, make the following changes to the SQL statement in CreditAccount (see the boldface type):

    Private Sub CreditAccount_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)        Dim conn As New SqlConnection(My.Settings.ConnectionString)        Dim command As New SqlCommand("UPDATE Act SET Balance = Balance + 100 WHERE UserID='54321'", conn)        command.Connection.Open()        command.ExecuteNonQuery()        Console.WriteLine("---Account credited into 54321---")        conn.Close()    End Sub

Instead of updating the Accounts table, I have changed it to Act, which will result in a runtime error (the table name cannot be found in the database). Press F5 to start the workflow. Visual Studio 2005 will proceed to debit $100 from account 12345, and then when it is time to credit account 54321, it halts due to a runtime error. So, what is the balance of both accounts now? After the debit process, the balance of account 12345 should be $300, but since the crediting part is not performed, the balance of account 12345 would be rolled back to its original balance of $400. This can be verified by checking the value of the Accounts table in Visual Studio 2005.

Hence, activities inside a TransactionScope activity automatically participate in a transaction.

Throwing Exceptions
In the last section, you saw how two activities engaged in a transaction. In the example above, as long as one of the code activities fails to perform correctly, the transaction rolls back. But how do you explicitly define conditions that will initiate a rollback? For example, if account 12345 has a balance of $0, it is clearly not possible to debit the account and hence the transaction must be rolled back.

To show you how this can be done in Windows workflow, make the following changes to your workflow.

• Add an IfElse activity after DebitAccount (and before CreditAccount).
• Remove the right branch of the IfElse activity.
• Add a Throw activity into the left branch of the IfElse activity.

Figure 11 outlines the above steps.

Figure 10. Output of the workflow is shown.

?

Figure 11. Modifying the workflow to include an IfElse activity.

Select throwActivity and click the “?” button next to the FaultType property in the Properties window. In the Type name text box, enter System.Exception and click OK (see Figure 12). Basically, the Throw activity models the throwing of an exception.

You also need to configure IfElseBranchActivity. Select the activity and set the properties as shown in Figure 13.

Figure 12. Select a .NET Type for throwing an exception.

?

Figure 13. Configure the properties of the ifElseBranchActivity activity.

The InsuffifientFunds condition (subroutine) will determine if there is a need to throw an exception. In this case, if the balance for account 12345 is less then 0 after the debiting, a throw activity must be invoked. This is accomplished by the following code:

    Private Sub InsufficientFunds( _       ByVal sender As System.Object, _       ByVal e As System.Workflow.Activities.ConditionalEventArgs)        Dim conn As New SqlConnection(My.Settings.ConnectionString)        Dim command As New SqlCommand("SELECT Balance FROM Accounts WHERE UserID='12345'", conn)        command.Connection.Open()        Dim balance As Double = CDbl(command.ExecuteScalar())        If balance < 0 Then            e.Result = True        Else            e.Result = False        End If        conn.Close()    End Sub

At the bottom of Workflow1.vb, click the third tab to switch to the Fault Handler view (see Figure 14).

Drag and drop a FaultHandler activity into the position labeled "Drop FaultHandlerActivity Here." Then, drop a Code activity into faultHandlerActivity1 and name it "ExceptionHandler". Figure 15 outlines the steps.

Figure 14. Switch to the Fault Handler view for the workflow by selecting the third tab.

?

Figure 15. Modify the Fault Handler view of the workflow.

Set the FaultType property of faultHandlerActivity1 to "System.Exception" (you need to click on the "?" button to select the System.Exception type; same as the previous step).

Double-click on ExceptionHandler to switch to its code-behind. Code the following:

    Private Sub ExceptionHandler_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)        Console.WriteLine("---Rolling back the transaction...---")    End Sub

With that completed, you've now successfully modified the application to throw an exception and roll back the transaction when the account to be debited has insufficient funds.

Testing the Application
Press F5 to test the application. Each time you run the application, $100 will be debited from account 12345 and credited into account 54321. Run the application a few times until the balance of account 12345 becomes 0. When its balance hits negative after the debiting process, you will see the output shown in Figure 16. This is caused by the Throw activity and the FaultHandlers that you just added.

Figure 16. Examine the output of the workflow to see how the insufficient funds condition is handled.

?

Figure 17. The modified workflow includes a new TransactionScope and Throw activities.

If you checked the database after this, you will see that the balance of account 12345 remains at $0 and no additional amount of money is credited into account 54321.

Compensation
So far, all the exceptions in this workflow happen within a transaction. What happens if you realize after the completion of a transaction that you need to roll it back? This process is known as compensation. Continuing with our earlier example, funds may have been successfully transferred from account 12345 to account 54321. But let's assume that later we learned that the account numbers given were not correct. In this case, two accounts must be rolled back to their previous states.

To illustrate compensation, let's modify the existing project.

Replace the existing TransactionScope activity with the CompensatableTransactionScope activity. Also, add a Throw activity to the workflow and place it outside compensatableTransactionScope. Figure 17 shows the new workflow.

Here, I am simply going to perform the transaction and then use a Throw activity to invoke an exception (you can always add your own logic to determine if an exception must be invoked; for now I am just hard coding it).

Right-click on compensatableTransactionScope and select View Compensation Handler. Figure 18 shows that upon selecting the new view, you will see the new compensationHandlerActivity activity.

Figure 18. View the Compensation Handler.

?

Figure 19. Drag and drop a Code activity into the compensationHandlerActivity activity.

Drag and drop a Code activity into compensationHandlerActivity and name it Compensate (see Figure 19).

Double-click on Compensate and code the following:

    Private Sub Compensate_ExecuteCode( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs)               Dim conn As New SqlConnection(My.Settings.ConnectionString)        Dim command As New SqlCommand("UPDATE Accounts SET Balance = Balance + 100 WHERE UserID='12345'", conn)        command.Connection.Open()        command.ExecuteNonQuery()        command.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE UserID='54321'"        command.ExecuteNonQuery()        Console.WriteLine("The transaction has been compensated")        conn.Close()    End Sub

Essentially, this activity will manually rollback (reverse) the transaction. That is, it will credit $100 into the account 12345 and debit $100 from 54321.

In the Fault Handler view of the workflow, add a Compensate activity to faultHandlerActivity1 (see Figure 20) and set its TargetActivityName property to "compensatableTransactionScopeActivity1."

Figure 20. Add the Compensate activity to faultHandlerActivity1.

?

Figure 21. Examining the output of the workflow.

That's it! When you press F5 to test the application, you should now observe the output as shown in Figure 21.

To finish, verify that the balances for both accounts are the same before and after the workflow is executed.

In this article, you have seen a working sample of how transactions are implemented in Windows Workflow. In addition, you have also seen how you can explicitly cause an exception by using a Throw activity. For transactions that have been completed, you need to use compensation to rollback the transactions.

But you can easily how a Workflow can be an elegant and visual interface to transaction-oriented applications.