BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns

In this ninth and final article in a series on BizTalk Server 2006 R2 and WCF integration, we take a look at how BizTalk Server uses the recently released BizTalk Adapter Pack. We will look at four scenarios for the Microsoft BizTalk Adapter 3.0 for Oracle Database:

• Inserting multiple records at one time
• Calling Oracle stored procedures that make use of both weak ref cursors and strong ref cursors
• Build a solution which polls an Oracle table
• Call an Oracle adapter service using code from a BizTalk orchestration

---

Scenario #1 – Inserting Multiple Oracle Table Records

---

In this first scenario, a BizTalk orchestration will insert many records into an Oracle table. First, a new BizTalk project was created in Visual Studio.NET. Next, that project was right-clicked and the Add Generated Items menu was selected. From there, the Consume Adapter Service template was chosen, and the BizTalk Adapter Pack LOB browser was launched.

Next, all the necessary Oracle connection details were added, including the data source name (from the Oracle tnsnames.ora file), and the Oracle username and password. Once a connection was established, the database artifacts are visible and browsable. For this scenario, the “Insert” operation was chosen on both the “Orders” table and the “OrderItems” table.

This wizard is identical to the one launched from any other Visual Studio.NET project except for one subtle difference. At the bottom of the wizard launched for a BizTalk project is a checkbox labeled “Generate unique schema type.” This would be used if one was generating metadata for multiple operations with the same underlying complex type (e.g. “Orders”) and needed to avoid type collisions.

Once the wizard completed, three new files were added to the project. Two of the files were schemas which contained the data entity for inserting into “Orders” and “OrderItems” respectively. The third file was a binding file which contained the preconfigured send port details. During this demonstration, the binding file only contained a SOAP action for one entity, so the wizard was actually used twice in order to generate a unique binding file for each entity.

Next, the orchestration was constructed. This orchestration inserts a single order into the “Orders” table and multiple line items into the “OrderItems” table. First, the orchestration messages were defined using the schema types generated by the adapter wizard.

Next, a (multi-part) map was created in order to instantiate the Order and OrderItems messages.

The next part of the orchestration flow is responsible for sending the “insert” request to the appropriate database tables. Notice that the operation name on the logical ports is “Insert.”

After this BizTalk project was deployed, the auto-generated binding files were added to the BizTalk application. These new send ports used the WCF-Custom BizTalk adapter and each had a single SOAP action with the name of “Insert.”

The port needed to have the Oracle credentials manually configured because this information was not stored in the auto-generated bindings (for security reasons).

After starting the application and sending in a trigger message, the orchestration successfully inserted a single record into the “Orders” table and multiple records into the “OrderItems” table. In a real-world scenario, the orchestration would be built using transactions and compensation to ensure that a failure in inserting order items resulted in a rollback of the insert into the “Orders” table.

---

Scenario #2 – Consuming Oracle Stored Procedures

---

This next scenario looks at how to consume Oracle stored procedures that return their result sets as either strongly or weakly typed ref cursors.

After right-clicking the BizTalk project in Visual Studio.NET, and selecting the Add Generated Items and then Consume Adapter Service, the LOB browser was once again launched. This time, the “procedures” artifacts were selected from the Oracle table.

The wizard produced both a schema file containing all the parameters for the procedures, and a single binding file that had the send port details needed by both procedures. The schema file had nodes for the weak ref cursor input and output, and the strong ref cursor input and output. Notice below how the weak ref cursor result set only returns “GenRecordRow” and “GenRecordColumns.” Instead of a loosely typed result set, one could use the strongly typed ref cursor (“OITEM” below) to return an unambiguous set of records.

Next, an orchestration was built which had messages for the input and output values needed by the stored procedures.

Once again, a (multi-part) map was built to instantiate both stored procedure input messages at the same time.

The orchestration flow then sent messages to the request/response ports which had operation names matching the stored procedure names.

Once the project was deployed, the auto-generated send port bindings were imported into the BizTalk application. Notice below that the SOAP action values match the stored procedure names, and the names on the orchestration port operations.

The result of the weakly typed stored procedure call is an XML message that looked like this.

While usable, this type of message can be a challenge to work with. On the other hand, the Oracle procedure making use of a strong ref cursor sends back a payload that looked like this.

---

Scenario #3 – Working with Polling-based Solutions

---

One core use case of the BizTalk database adapters is polling tables for changes which in turn trigger events in the message bus. The Microsoft BizTalk Adapter 3.0 for Oracle Database has strong support for this scenario.

While configuring the connection to the Oracle database in the BizTalk Adapter Pack LOB browser, it is necessary to enter a value in the “Polling Statement” field on the “Binding Properties” tab. This value is used to generate a schema based on the result set.

One could also choose to set the “PollingId” on the “URI Properties” tab if a unique namespace was desired. After connection details were complete, a connection to the database was made. Instead of a contract type of “Client (outbound operations”, the value was switched to “Service (inbound operations).” This is because BizTalk is now a service host versus a service consumer. Notice that a single operation, POLLINGSTMT was available.

The schema that was generated by this wizard had a strongly typed result set.

A simple orchestration was created to take in the polled records and send the results to disk. After this solution was deployed, a brand new receive port and location. While the wizard produced a template binding file, the receive port in that binding file was request/response, whereas only a one-way receive location was needed.

The new one-way receive location used the WCF-Custom BizTalk adapter. The oracleDBBinding was chosen on the “Binding” tab of the configuration and the “Polling statement” field was populated for this binding.

After binding the orchestration to the ports, and turning on the receive location, a set of orders from the database table were retrieved and sent to the file system.

Notice that the result set is batched in a single file. In the real world, two changes to this demonstration would be necessary. First, a “post poll statement” would be added to the receive location. This statement may be an Oracle function that takes the previously polled records and copies them to an archive table. This prevents subsequent polling operations from pulling duplicate records. The second change needed to make this demonstration more “enterprise ready” would be to split the inbound message from the adapter into individual messages representing distinct orders. This could happen in a pipeline by creating an envelope schema, or within an orchestration.

---

Scenario #4 – Calling the Oracle Adapter Directly From An Orchestration

---

The final scenario explains how to call the Microsoft BizTalk Adapter 3.0 for Oracle Database from BizTalk Server without using a WCF adapter. This situation may arise if simple scalar values are needed by the orchestration, or a typed class is desired. Instead of incurring the cost of the BizTalk infrastructure and MessageBox communication path, a call can be made directly to the Oracle adapter. The tradeoff is that platform services such as retries, ordered delivery and such are unavailable, but that may be acceptable in certain use cases.

First, a new .NET class library was created. This library will be called by the BizTalk orchestration and contain all the details necessary to communicate with the Oracle adapter directly. References were added to Microsoft.Adapters.OracleDB, Microsoft.ServiceModel.Channels, System.ServiceModel and the service library created in the eighth article of this series. That service library contained the auto-generated bits necessary to describe and communicate with the Oracle tables.

Instead of trying to reference the WCF configuration file from the BizTalk configuration file (btsntsvc.exe.config), a binding was defined in the code itself. In the operation below, an OracleDBBinding was created and EndpointAddress object was configured to point to the pre-defined Oracle data source. Next, the client class from the aforementioned service library was used to communicate with the “Customers” table. In this case, the “MEMBERSHIPSTATUS” is the only column requested for the customer record with the specified customer ID. Finally, that membership status value is returned to the caller.

Once this .NET project was strong-named, compiled and GAC-ed, a reference to this project was added in the Visual Studio.NET BizTalk project. A variable was added to an existing orchestration. This variable pointed to the “CustomerQueryProxy” type created above.

Next, an orchestration Expression shape was added, and the single call to “GetCustomerStatus” was configured.

Executing this orchestration resulted in a call being made directly to the adapter and the single “status” value being returned. If one were to do this scenario in a real-world project, it is vital to design a viable way to store the database username and password in a secure location and look that value up at runtime in the code. Embedding the database credentials in the code (as seen above) is not only a maintenance nightmare, but also foolishly unsecure.

---

Summary

---

In this article, we looked at how to consume the BizTalk Adapter Pack’s Oracle adapter from a BizTalk project. These demonstrations showed how simple it is to connect to an existing Oracle database, generate metadata, and consume Oracle artifacts.

Throughout this nine-part series, we have looked at how BizTalk Server 2006 R2 consumes WCF services that implement a variety of operation patterns, security configurations, attachment designations and transaction settings. We also reviewed how BizTalk itself exposes WCF services that support multiple operation patterns, security configurations, attachment designations and transaction settings. Finally, we look a long look at the BizTalk Adapter Pack and how this new piece of software from Microsoft enables broad consumption of LOB data through a common framework. I hope that you enjoyed this series and learned as much reading it as I did writing it.

Now that the entire series is complete, keep an eye out on my blog for all the source code.

Questions, comments or corrections?  Go ahead and leave a comment on my blog post about this article.

You can read more about BizTalk, SOA and enterprise architecture on my blog at https://seroter.wordpress.com.