One of the greatest difficulties I found when I started working with BizTalk 2004 was the lack of documentation about the SQL Adapter. In this article, I'm going to demonstrate how we can use this adapter in an Orchestration of BizTalk.
The Example
To build this example, we're going to use the Northwind database. We're going to simulate a hypothetical situation where we receive an XML message as a file, containing the order number, a customer ID, and the date of the order. In the orchestration, we will use SQL Server to search the additional information about the customer, using the SQL Adapter.
Creating the Project
We'll start this article by creating a new BizTalk Server project in Visual Studio. In the Visual Studio .NET menu, select the "New Project" option, and for the type of project, select "BizTalk Projects". Select the template "Empty BizTalk Project" and create a project named OrderManager.
Creating the Schemas
Now that we have the project, we're going to create two maps that we'll use in the project, one for the input message and one for the output message.
Right-click on the project in the Solution Explorer, and select the "Add New Item" option, then select the "Schema" item. Create a schema named "IncompleteOrder".
Click on the root element of the schema and change the property "NodeName" to "Order". After that, right-click on this node and select the "Insert Schema Node" option. Inside this option, select "Child Field Element". Change the NodeName property of this new node to OrderId. Repeat this operation to create two additional elements named "OrderDate" and "CustomerId". Your schema should look like this:
Now, we're going to create the schema with the complete information of the order. Right-click on the project in the Solution Explorer and select the "Add New Item" option. Then, select the item "Schema" and name it "CompleteOrder".
When the schema shows up, rename the "Root" element to "CompleteOrder". After that, create the child elements: "OrderId", "OrderDate", "CustomerID", "CustomerName", "CustomerAddress", "CustomerCity", "CustomerCountry". The complete schema can be viewed in the image below:
Creating the test messages
In order to test our solution, we need to create some test messages. BizTalk Server is capable of creating these messages for us. Right-click on the "IncompleteOrder.xsd" schema in the Solution Explorer and select the "Properties" option. In the Properties, select the property "Output Instance Filename". In this field, type the value "C:\IncompleteOrder.XML". Click OK to close the window. Right-click on the schema file again in Solution Explorer and select the "Generate Instance" option. Open Windows Explorer and check if the file was created in the indicated place.
If you check the file created in Visual Studio, you'll see that the values generated are random. We're going to modify these values to use some valid information. Replace the OrderId field to the number 1. In the OrderDate field, type the value "2005-03-01", and in the field "CustomerId", the value "ALFKI". The XML file should have a similar structure as the file below:
Save the file, we'll need it later.
Creating the structure to have access to the database
In order to use the SQL Server resources, we'll need to create a Stored Procedure capable of returning the data from the customer that we will place in the order.
Open the Enterprise Manager of SQL Server and select the Northwind database. Select the "Stored Procedures" applet, and right-click it, select the option "New Procedure". A new procedure should be created as follows:CREATE PROCEDURE proc_GetCustomer
(@CustomerId char(5))
AS
SELECT * FROM Customers
WHERE CustomerId=@CustomerId FOR XML AUTO, XMLDATA
GO
Don't forget to include the XMLDATA parameter in the end of the procedure, this will generate the necessary information for the SQL adapter. This parameter will be removed later.
Adding the structure to call SQL Server
Now that we've created all the necessary structures for the solution, we're going to create the structure to call SQL Server. For this, we will create a new item generated from the Solution Explorer. Right-click on the project in the Solution Explorer and select the "Add Generated Items" option. In the list of items, select the Add Adapter option and click on the Open button. The following screen will show up:
In this screen, select the adapter of type "SQL" and click Next (the other options can stay with default values, unless the database of your BizTalk server is in a remote server).
In the first screen, click on the Set button and provifde the information to connect to your SQL Server instance. Select "Northwind" as the initial catalog. When the connection string is set, click Next.
Th next screen shows information about the schemas that will be used to transport the information to and from SQL. In the Target Namespace option, type "http://nwtraders". In the Port-Type, select "Send Port", since we're going to send a request to SQL Server and receive a response. In the property "Request root element name", type "InCustomer", and in the property "Response root element name", type "OutCustomer".
Click on the Next button. In the screen "Statement type information", select the option "Stored Procedure". Click Next. In the combo box for selecting the procedure, select proc_GetCustomer. The stored procedure parameters will show up. Click on the first parameter (near the check box... do not check the check box, just click near it until the prompt appears) to enter the parameter information. Type "ANTON", that is a valid customer ID. Click on the Generate button and you will see that the script used to execute the procedure will show up in the bottom of the screen.
This information will be used by the SQL adapter to generate the initial schema, they are not used later in the project. Click on the Next button, and in the next screen, click the Finish button. A new schema and a new orchestration will be created in your project.
The created schema (SQLService) contains the request and response information for the stored procedure. The orchestration contains some types (port type) used to call the SQL adapter.
Updating the orchestration
Right-click on the orchestration in Solution Explorer and select the "Rename" option. Rename it to "ProcessOrder.odx".
Open the orchestration file and click on the white area in the orchestration designer (between the red and green indicators that indicate the end and beginning of the process). Check the property windows, and change the TypeName property from Orchestration_1 to ProcessOrderOrch.
Creating the necessary messages
In order to use our messages within the orchestration, it's necessary to create message variables. To do this, we'll need the Orchestration View window. Click on the "View" menu, select "Other Windows", and select the "Orchestration View" window.
In the orchestration view, right-click on "Messages" folder and select the New Message option. In the identification, type "msgIncompleteOrder" and select the "OrderManager.IncompleOrder" and its schema (the schema is available in the schemas item).
Create three additional messages with the following identifiers/schemas:
Identifier
Type
msgCompleteOrder
OrderManager.CompleteOrder
msgGetCustomerReq
OrderManager.procedureRequest (no item multipart messages)
msgGetCustomerResp
OrderManager.procedureResponse (no item multipart messages)
Creating orchestration elements
Now, we will create the elements used in the orchestration. In the toolbox, search for the "Scope" shape and drag it to just below the green indicator in the designer area. Rename the scope shape to "Do Updates". Change its transaction type to "None".
Now, drag a "Receive" shape from the toolbox to the area inside the scope shape. In the shape properties, set the properties below:
Name - Receive Incomplete Order
Message - msgIncompleteOrder
Activate - True
Just below the receive shape, drag a "Construct Message" shape. Use the following properties:
Name - Create SQL Request
Messages Constructed - msgGetCustomerReq
Now, drag a "Transform" shape inside the empty area inside the "Construct Message" shape. Select the "Input Messages" property and click the (...) button. A new window will open up with the mapping options. In the "Fully Qualified Map Name" box, type "ProcessOrder.IncompleteOrder_To_SQLRequest". Click on the "Source" option and select the "msgIncompleOrder" as the source message. Click on "Destination" and select the "msgGetCustomerReq" as the destination message. Your "Transform Configuration" screen should look like the one below. When you finish, click OK
In the map editor, drag the CustomerID field from the IncompleteOrder schema to the CustomerID field on the GetCustomerReq schema. Note that the destination schema represents the parameters used to call the stored procedure.
Save the map and go back to the orchestration file. Change the name of the "Transform" shape to "Create Request".
Now, we'll create a "Send" shape that will send our request to the SQL Server Adapter. Drag a new "Send" shape just below the construct message shape and use the following properties:
Name - Send SQL Request
Message - msgGetCustomerReq
After the Send Shape, create a new Receive Shape with the following properties:
Name - Receive SQL Resp
Message - msgGetCustomerResp
After the Receive shape, drag a new construct message with the following properties:
Name - Construct Response
Messages Constructed - msgCompleteOrder
Drag a new "Transform" shape to our newly created "Construct" shape. Select the property "Input Messages" and click on the (...) button. The transform configuration window will show up again. In the fully qualified name field, type ProcessOrder.SQL_To_CompleteOrder. In the Source option, select the "msgGetCustomerRep" and the "msgIncompleteOrder" (that is, two messages as source). Select the "Destination" option and select the "msgGetCustomerReq" option
Click OK and in the map editor, create a map with the necessary links
Close and save the map, and go back to the orchestration. Change the name of the "Transform" shape to "Create Response".
Now, we'll create a new "Send" shape that will send the final response to our customer. Create a new "Send" shape below the Construct Message shape, with the following properties:
Name - Send Response
Message - msgCompleteOrder
Comments