Accessing SQL Data From Processes

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

There are two ways to integrate with SQL Server in Neuron. The first way is to use one of the two adapters included with Neuron - SQL Adapter or Odbc Adapter. The second way to integrate with SQL Server is to use one of three process steps - Store, Table Query and XML Query. This sample demonstrates how to integrate with SQL Server using these three process steps.

There are several scenarios that call for integrating with SQL Server in a process. One common scenario is when you want to query data from a database to create a respond to a Web service call. Another common scenario is to perform multiple database operations within the scope of a transaction.

For example, a large purchase order may contain many "Order" records. Each of these Order records needs to be inserted into a database individually. When designing a Neuron solution for this problem, you could use the Split process step to create a new message for each Order record and publish each message to the bus. Then a subscribing party using the SQL Adapter will receive each individual message and perform the insert. But what if you have to perform all the inserts within a single transaction? Then you will need to use the Transaction process step to wrap the Split, Store and other process steps in one atomic transaction.

Process Components Demonstrated:

Solution

This sample consists of five processes. Each process will demonstrate using one of the different methods for integrating with SQL server with a process step:

Insert Orders Process

The Insert Orders process shown in Figure 1 will be used to demonstrate two different ways to use the Store process step. First, a Split process step is used to extract individual Order messages from the original order batch. Each of these messages will flow through the steps inside the Split execution block (see Note below). The first Store process step, "Insert Order" calls a stored procedure to insert the records into SQL Server. The second Store process step, "Update Order Amount" executes a SQL UPDATE statement to update the order amount for the previously written record. The Split and Store process steps are executed within a Transaction execution block (see Note below). This ensures that if one of the individual records fails to be written to the database, the entire batch of orders will be rolled back. When this happens, the original message containing the entire batch of orders will be logged to the failure database and the exception information will be written to the event log. The entire Insert Orders process is enclosed inside an Exception step (see Figure 2). When an exception is throws, the process jumps to the Catch execution block, where the exception information is traced and then processed by the Exception Handing process (see Note below).

Note: For more information on the Split Process step, see the samples named Splitting Messages with Join or Splitting Messages with Null Join.

Note: For more information on the Transaction Process shape, see the Transactional Process sample.

Note: Each of the processes used in this sample utilize the same exception handling logic. For more information on Exception Handling, see the Exception Handling process sample.

<Orders>
     <Order>
          <OrderID>1234</OrderID>
          <OrderDate>4/22/09</OrderDate>
          <OrderAmount>100.00</OrderAmount>
     </Order>
     <Order>
          <OrderID>1235</OrderID>
          <OrderDate>4/22/09</OrderDate>
          <OrderAmount>110.00</OrderAmount>
     </Order>
     <Order>
          <OrderID>1236</OrderID>
          <OrderDate>4/22/09</OrderDate>
          <OrderAmount>120.00</OrderAmount>
     </Order>
<Orders>

Note: The Store process step has several properties that must be configured and the process documentation should be reviewed to gain a full understanding of these properties. The following figures display the various configured properties of the Store process steps used in this process.

Insert Order (Store Step)

Update Order Amount (Store Step)

Table Query Processes

The Table Query process step allows you to invoke a stored procedure or execute a SQL SELECT statement. Either one of these options supports the use of parameters. When using parameters, you can 1) provide a default value for the parameter, 2) set the parameter value based on data within the current message, 3) set the value based on a message header, or 4) set the value based on a message property. When setting the parameter value based on the message data, you can set an XPATH expression to the section of the message being processed to use for setting parameters. Then, within the Parameters dialog you would set the SelectionValue relative to the results returned by the XPATH expression. See Figures 10 - 13 for more details about the properties of the Table Query step.

<GetOrder>
     <OrderID>1235</OrderID>
<GetOrder>

Note: The Table Query process step has several properties that must be configured and the process documentation should be reviewed to gain a full understanding of these properties. The following figures display the various configured properties of the process steps used in this example.

Table Query (Using a Stored Procedure)

This process uses the Table Query step to invoke a stored procedure. The properties for the Table Query step are shown in Figures 10 & 11.

Table Query (using a SQL SELECT Statement)

This process uses the Table Query step to invoke a SQL SELECT statement. The properties for the Table Query step are shown in Figures 12 & 13.

Xml Query Processes

The Xml Query process step is very similar to the Table Query step. The biggest difference between Xml Query and Table Query is that Xml Query takes advantage of the "FOR XML" clause that signals to SQL Server to return the result set as XML. When using a command type of "Text", the SELECT statement needs to end with the "FOR XML" clause. When using a stored procedure, the SELECT statement used in the procedure to return data also must end with the "FOR XML" clause. See Figures 16 - 19 for more details about the properties of the Xml Query step.

For more information about the "FOR XML" clause in SQL Server, see:

http://msdn.microsoft.com/en-us/library/ms178107.aspx

<GetOrder>
     <OrderID>1235</OrderID>
<GetOrder>

Note: The Xml Query process step has several properties that must be configured and the process documentation should be reviewed to gain a full understanding of these properties. The following figures display the various configured properties of the process steps used in this example.

Xml Query (Using a Stored Procedure)

This process uses the Xml Query step to invoke a stored procedure. The properties for the Xml Query step are shown in Figures 16 & 17.

Xml Query (Using a SQL SELECT Statement)

This process uses the Xml Query step to invoke a SQL SELECT statement. The properties for the Xml Query step are shown in Figures 18 & 19.

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, Table Query and Xml Query process steps. Follow the instructions titled "Verify Connection Strings" found in the Appendix section of this document.

Open the Sample

To open this sample, see the topic Using the Neuron Samples and select the Accessing SQL Data From Processes sample.

Run the Sample

Note: The database table Order must be cleared of all records prior to running the Insert Orders process in the following steps. It must be cleared each time the example is executed. To delete the records in the Order table, run the SQL script titled "Clear Order Table Script" found in the Appendix.

1. From the Data 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 Insert Orders 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 20.



Figure 20: Edit Test Message dialog displaying the test Orders message for this example.

5. Paste the test message retrieved in step 1 into the message field of the Edit Test Message dialog as shown in Figure 20.
6. Click the OK button on the Edit Test Message dialog. Once the dialog is closed the process will begin execution.
7. 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 21.



Figure 21: Insert Order process test run displaying results and exceptions in the Trace Window.

8. 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. See Figure 22 below.



Figure 22: Order records displayed in SQL Server Management Studio after the test run executed in step 6 above.

Get Order(s)

1. To test the GetOrders functionality, you will use a Neuron Test Client. In the Neuron ESB Explorer, click Tools->Test Client->1 Test Client.
2. Select OrderPublisher as the Party Id and click the Connect button.
3. Click on the Send tab. Enter the message text as shown in Figure 23. Make sure you set the Semantic to Request. If you want to copy the message data, go to the Data tab in Neuron Explorer and copy the test message GetOrderTestMessage from the Xml Documents area.



Figure 23: Neuron Test Client configured to send a GetOrders Request message to the Party OrderPublisher and topic Orders.

4. Click the Send button on the test client, and then navigate to the Receive tab. You should see a response similar to Figure 24.



Figure 24: Response received after sending a GetOrders Request message to the Party OrderPublisher and topic Orders. To see the message formatted properly, click Message->Format XML.

5. Close the Neuron Test Client.
6. The party OrderPublisher is initially configured to execute the Table Query - Stored Proc process. To test the other three processes, go to the Messaging tab in Neuron Explorer and click on Publishers.
7. Select OrderPublisher from the list of parties.
8. Click on the Processes tab and then click the Edit Processes link as shown in Figure 25.



Figure 25: The Processes tab for the party OrderPublisher.

9. In the Assign Processes dialog box, uncheck the currently configured process and check the process you wish to test next. See Figure 26.



Figure 26: The Assign Processes dialog for the party OrderPublisher.

10. Click the Close button, then click the Apply button to apply the changes for the party OrderPublisher.
11. Save the changes by clicking File->Save.
12. Repeat steps 1 - 5 to test the selected process.

Note: If you did not close the test client, you should disconnect and reconnect the OrderPublisher party in the test client to make sure the new process has loaded.

13. Repeat steps 6 - 11 to configure the OrderPublisher party to execute the other processes included in this sample.

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" option from the short cut menu that is available when right-clicking the Code step in the process designer. See the process documentation for more information.

Figure 27: The Neuron Process Designer displaying the Insert Orders process. Property Grid at the bottom right displaying the properties of Query process step named "Update Order Amount".

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 Insert Orders process listed in left panel.
3. Select the Store process step named "Insert Order".
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 28: Connection Properties dialog.

6. Make sure the SQL Server and Database Name is correct then click the Test Connection button to assure that the connection string is correct.
7. Click the OK button on the Connection Properties dialog to save your changes.
8. Repeat steps 3- 7 for the Store process step named "Update Order Amount".
9. 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.
10. Repeat steps 3-9 for the Table Query and Xml Query steps in the other four processes.

Clear Order Table Script

USE [MessageData]

GO

DELETE FROM [Order] WHERE 1=1

GO