Accessing SQL Data From Processes
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
|Exception (Try/Catch/Finally) Transaction Code Trace||Split with Null Join Store Table Query Xml Query|
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:
|Process Name||Process Steps Used||Description|
|Insert Orders||Store Exception Split with Null Join Trace||Uses the Store process step to insert data into a database using a stored procedure, followed by a second Store process step to update the record with additional information via a SQL UPDATE statement.|
|Table Query – Stored Proc||Table Query Code Trace Cancel||Uses the Table Query process steps to execute a stored procedure to select an order from the database. The order is returned to the test client.|
|Table Query – Text||Table Query Code Trace Cancel||Uses the Table Query process steps to execute a SQL SELECT statement to select an order from the database. The order is returned to the test client.|
|Xml Query – Stored Proc||Table Query Code Trace Cancel||Uses the Xml Query process steps to execute a stored procedure to select an order from the database. The order is returned to the test client.|
|Xml Query – Text||Table Query Code Trace Cancel||Uses the Xml Query process steps to execute a SQL SELECT statement to select all the orders from the database. The orders are returned to the test client.|
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).
<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>
Figure 3: Sample Orders message used with the Insert Orders process. Note the highlighted area. An XPath expression of “Orders/Order” specified in the XPath property of the of the Split process step will select each Order block that is found under the root element of Orders.
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.
Figure 8: Table Query processes as displayed in the Neuron Process designer.
<GetOrder> <OrderID>1235</OrderID> <GetOrder>
Figure 9: Sample GetOrder message used in this process. Note the highlighted area.
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:
Figure 14: Xml Query processes as displayed in the Neuron Process designer.
<GetOrder> <OrderID>1235</OrderID> <GetOrder>
Figure 15: Sample GetOrder message used in this process. Note the highlighted area.
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.
Running the Sample
- 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.
- 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
- From the Data tab in Neuron Explorer copy the test message OrdersTestMessage from the Xml Documents area.
- Navigate to the Process Designer by selecting the Processes tab in the left panel of Neuron Explorer.
- Select the Insert Orders process from the list on the left of the designer.
- On the Process Designer toolbar click the Test button . This will open the Edit Test Message Dialog shown in Figure 20.
- Paste the test message retrieved in step 1 into the message field of the Edit Test Message dialog as shown in Figure 20.
- Click the OK button on the Edit Test Message dialog. Once the dialog is closed the process will begin execution.
- 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.
- 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.
- To test the GetOrders functionality, you will use a Neuron Test Client. In the Neuron ESB Explorer, click Tools->Test Client->1 Test Client.
- Select OrderPublisher as the Party Id and click the Connect button.
- 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.
- Click the Send button on the test client, and then navigate to the Receive tab. You should see a response similar to Figure 24.
- Close the Neuron Test Client.
- 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.
- Select OrderPublisher from the list of parties.
- Click on the Processes tab and then click the Edit Processes link as shown in Figure 25.
- In the Assign Processes dialog box, uncheck the currently configured process and check the process you wish to test next. See Figure 26.
- Click the Close button, then click the Apply button to apply the changes for the party OrderPublisher.
- Save the changes by clicking File->Save.
- 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.
- Repeat steps 6 – 11 to configure the OrderPublisher party to execute the other processes included in this sample.
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.
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
- Open Neuron Explorer and navigate to the Processes section.
- Select the Insert Orders process listed in left panel.
- Select the Store process step named “Insert Order”.
- In the property grid select the ConnectionString property and click the ellipsis button on the right of the property value.
- The Connection Properties dialog will open as shown below in Figure 28
- Make sure the SQL Server and Database Name is correct then click the Test Connection button to assure that the connection string is correct.
- Click the OK button on the Connection Properties dialog to save your changes.
- Repeat steps 3- 7 for the Store process step named “Update Order Amount”.
- 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.
- Repeat steps 3-9 for the Table Query and Xml Query steps in the other four processes.
Clear Order Table Script
DELETE FROM [Order] WHERE 1=1