Skip to content

Transactional Process

Note: It is recommended that the Neuron process documentation be reviewed before running this sample. The documentation thoroughly describes each process component individually and describes how to configure each one as well as how to construct a process and associate it with a Neuron Publisher or Subscriber. See the Configuration Notes section at the end of this document for more information.

Overview

This sample demonstrates how to wrap process steps in a transaction. Transactions are used to ensure that, if a process fails to execute, any individual step of that process (that supports transactions or use XA resource compliant transaction resource managers) that had already completed prior to the error occurring is “rolled back” to its original state. Transactions are commonly used in database processes.

For example, a large purchase order may contain many “Order” records, each with corresponding Customer and Product information, which must be entered as separate operations into multiple tables. A purchase order may contain 10 orders to be entered and on the 9th record there is a database error due to corrupt data the process will fail and only part of the purchase order will have been saved making it unusable. The desired result would be to rollback all of the previous records stored if any failure occurred. This can be accomplished using transactions. In Neuron Processes there is a Transaction process component that can be used to wrap other steps and execute them in one atomic transaction.

Process Components Demonstrated:

  • Exception (Try/Catch/Finally)
  • Transaction
  • Code
  • Store Table Query Trace

Solution

This sample consists of two processes. One process will demonstrate using process steps non-transactionally and the other will execute the same steps within a Transaction process step. By demonstrating these two scenarios it will be easy to understand the value of the Transaction process step especially when dealing with processes such as database queries.

The DB Non-Transactional process shown in Figure 1 will be used to demonstrate what happens when database processing is done non-transactionally and errors occur. The DB Transactional process shown in Figure 2 will run the identical steps except the steps executing database storage will run inside a Transaction process step.

Figure 1: DB Non-Transactional process as displayed in the Neuron Process designer.
Figure 2: DB Transactional process as displayed in the Neuron Process designer.

Both processes have an Exception Process Step used as a container for all the process steps.

Note: See the Exception Handling Using Neuron Processes sample for more information regarding the Exception process step.

Within the Try Execution Block there are 3 process steps.

  • The first step is a Code step name “insert new order” which is used to create 3 new database Order records from the incoming message. The records inserted in this step will use only the OrderID node data from the incoming message and use default values for the other fields.
  • The second step is a Store process step named “update order date” which is configured to extract the OrderDate node data and update each of the records that was inserted in the insert new order process step with the date field information.
  • The final step that is within the Try Execution Block is a Store process step named “update order amount” which is configured to update the OrderAmount field. This final Store step will be used topurposely cause an error so that the Transaction step can be demonstrated. All properties for the update order amount step are configured correctly except for the connection string which will cause an error when it is executed.
Note: Figure 3 shows the format of the message used in this sample.
Figure 3: Sample Orders message used in this example. Note the highlighted area. An XPath expression of “Orders/Order” specified in the XPath property of the of the Store and Table Query process steps will select each Order block that is found under the root element of Orders.

In the Catch Execution Block is a single Code process step, “trace exception”. This step will be used to display the error message produced from the execution of the errant update order amount step in the Try Execution Block within the Trace Window. When we run this sample we will use the Test feature of the Neuron Process Designer. Testing in the designer allows you to see each step being executed. Trace messages can be added to the execution using a Trace process step and these messages can be seen in the Process Designer’s Trace Window during testing.

The Finally Execution Block contains 2 process steps.

  • The first step is a Table Query process step named “get orders” which will be used to extract all of the records from the database that were inserted during the execution of the previous storage steps. The get orders step is configured to use the OrderID node fields from the incoming message as parameters to a stored procedure which will return the record with that OrderID value.
  • The last step in the process is a Trace process step which will be used like the Tracestep found in the Catch block. The Trace step is used to display the resulting message from all other previous steps. The message will be displayed in the designer’s Trace Window when using the Neuron Process Designer test feature.
Note: Table Query and Store process steps have several properties that must be configured and the process documentation should be reviewed to gain a full understanding of these properties. As an overview the Store process step named “update order date” in this example has the configured properties displayed in Figures 4 and 5.
Figure 4: Store process properties.
Figure 5: Store parameters collection.
Note: See the Appendix section for details on the database configuration used in this example

Running the Sample

Prerequisites

  1. Database creation – A SQL database is required to run this sample. To create the database and table associated with this sample run the SQL script titled “MessageData Creation Script” found in the Appendix section of this document.
  2. For each process, verify the connection string used in the Store process steps named “update order date” and “update order amount” as well as the Table Query process step named “get orders”. Follow the instructions titled “Verify Connection Strings” found in the Appendix section of this document.
  3. The “Distributed Transaction Coordinator” service must be running. To start the service, run “services.msc” from the Windows “Run” box and start the “Distributed Transaction Coordinator” service if it is not running already.

Open the Sample

To open this sample, see the topic Using the Neuron Samples and select the Transactional Process sample.

Run the Sample

Note: The database table Order must be cleared of all records prior to running the process in the following steps. It must be cleared each time the example is executed.
  1. From the Repository tab in Neuron Explorer copy the test message OrdersTestMessage from the Xml Documents area.
  2. Navigate to the Process Designer by selecting the Processes tab in the left panel of Neuron Explorer.
  3. Select the DB Non-Transactional process from the list on the left of the designer.
  4. On the Process Designer toolbar click the Test button . This will open the Edit Test Message Dialog shown in Figure 6.
Figure 6: Edit Test Message dialog displaying the test Orders message for this example. Red arrow indicates the hyper-link label for editing custom properties as described in step 6.
  1. Paste the test message retrieved in step 1 into the message field of the Edit Test Message dialog as shown in Figure 6.
  2. Within the Edit Test Message dialog, click the Edit Custom Properties link label shown in Figure 6 to bring up the Custom Properties dialog as shown in Figure 7.
Figure 7: CustomProperties dialog displaying the “db.cs” custom property that must be set for the Code process step named “insert new order”. The “db.cs” custom property defines the database connection string used by the Code step to connect to the sample database.
  1. Enter the same connection string used in the Store and Table Query step that was set in the prerequisite for the value for the “db.cs” custom property.
  2. Click the OK button to close the Custom Properties dialog.
  3. Click the OK button on the Edit Test Message dialog. Once the dialog is closed the process will begin execution.
  4. As the process runs each step will be highlight in green as it is executed. In addition, any trace messages will be visible in the Trace Window. As you visually follow the execution not the sequence of steps. When the process completes, the Trace Window should look similar to what is shown in Figure 8.
Figure 8: DB Non-Transactional process test run displaying results and exceptions in the Trace Window.
  1. Check the database using a tool such as SQL Server Management Studio to verify the records were stored in the Order table of the MessageData database. Note that the OrderID and OrderDate fields have the correct values found in the original message. Note that the OrderAmount field does NOT have the correct value. This is because only the Code step and the first Store step found in the Try Execution Block of the process executed without error. The Store step that was configured to update the records with the correct amount failed rendering all of these records invalid. See Figure 9 below.
Figure 9: Order records displayed in SQL Server Management Studio after the test run executed in step 9 above.
Note: The message shown on the Trace Window also indicates the corrupt data as described in step 10. When running a process using the Test feature it is advantageous to use Code and Trace steps that trace information to the Trace Window.

Executing Transactionally

  1. Delete the records in the database.
  2. Select the DB Transactional process from the list on the left of the Process Designer.
  3. Click the Test button on the designer toolbar as described in step 3 under the “Run the Sample” section of this document. This will open the Edit Test Message dialog again. The test message and custom properties should still be set. If not, repeat steps 4-8 under the “Run the Sample” section.
  4. Click the OK button on the Edit Test Message dialog. The process will run and you can follow the execution as described in step 9.
  5. The final screen capture of the Trace Window after executing the transactional version of the process is shown in Figure 10.
Figure 10: DB Transactional process test run displaying results and exceptions in the Trace Window
  1. Check the database table as described in step 10 and note that there are no records stored. The Transaction component rolled back the process and the first 2 database processes were “rolled back”. Also note the final message as displayed in the Trace Window showing no records retrieved.
As an exercise try running these samples processes live using the Neuron Test Client. The sample configuration file has two Parties that can be used to accomplish this. The OrderStorer Party can be used to run the process in the OnPublish mode and the OrderSubscriber can be used to receive the Orders message.

Configuration Notes

All processes with the exception of the Code process step are configured by selecting and setting their properties in the property grid located at the bottom right of the process designer. The Code process step is configured by selecting the “Edit” menu item that is available when right-clicking the Code step in the process designer. See the process documentation for more information.

Figure 11: The Neuron Process Designer displaying the DB Transactional process. Property Grid at the bottom right displaying the properties of Query process step named “get orders”.

Appendix

MessageData Creation Script

 USE [master]
 GO
 CREATE database MessageData
 GO
 USE [MessageData]
 GO
 CREATE table [Order] (OrderID int, OrderDate datetime, OrderAmount   money)
 GO
 CREATE procedure StoreOrderItem (@OrderID int, @OrderDate datetime,   @OrderAmount money)
 AS
 INSERT INTO [Order] (OrderID, OrderDate, OrderAmount) VALUES (@OrderID,   @OrderDate, @OrderAmount)
 GO
 CREATE procedure SelectOrderItem (@OrderID int)
 AS
 SELECT OrderID, OrderDate, OrderAmount FROM [Order] WHERE OrderID=@OrderID
 GO
 CREATE procedure SelectOrderItemXml (@OrderID int)
 AS
 SELECT OrderID, OrderDate, OrderAmount FROM [Order] WHERE OrderID=@OrderID
 FOR XML AUTO, ELEMENTS
 GO 
A copy of the full database script for this sample can be found in the Neuron Samples folder under the installation directory: Samples\Processes, file name: CreateSampleDB.sql

Verify Connection Strings

  1. Open Neuron Explorer and navigate to the Processes section.
  2. Select the DB Non-Transactional process listed in left panel.
  3. Select the Store process step named “update order date”.
  4. In the property grid select the ConnectionString property and click the ellipsis button on the right of the property value.
  5. The Connection Properties dialog will open as shown below in Figure 12
Figure 12: Connection Properties dialog.
  1. Make sure the SQL Server and Database Name is correct then click the Test Connection button to assure that the connection string is correct.
  2. Click the OK button on the Connection Properties dialog to save your changes.
  3. Repeat steps 3- 7 for the Table Query process step named “get orders” and the Store process step named “Update Order Amount”.
    Note: The Database name used in the “Update Order Amount” step is purposefully set to an incorrect value – “MessageDat”. When you test the connection you will be notified that the database connection test failed. This is expected.
  4. Save the changes to the process and the configuration by first clicking the Apply button at the top of the process designer then the Save button on the Neuron Explorer toolbar.
  5. Select the DB Transactional process listed in the left panel.
  6. Repeat steps 3-9 for the DB Transactional process.
Was this article helpful?
Dislike 0
Previous: Testing Processes from Visual Studio
Next: Using For and While Loops