BizTalk and WCF: Part VII, About the BizTalk Adapter Pack

This 7^th article in a series on BizTalk Server 2006 R2 and Windows Communication Foundation (WCF) integration takes a look at the newly released BizTalk Adapter Pack and highlights a standard data query use case.

---

What is the BizTalk Adapter Pack?

---

While the LOB (“Line of Business”) adapters included with BizTalk Server 2006 R1 had a common metadata retrieval process, and relatively similar adapter configurations, there were challenges in using these adapters. The developer had to understand some underlying connection details, there were a limited set of features and configuration options available, and arguably most importantly, these adapters were BizTalk specific. That is, if you had a need to connect to a non-Microsoft system for a simple data query, and wanted to use auto-generated bits, you had no choice but to hook up a BizTalk process that utilized the LOB adapters.

The BizTalk Adapter Pack is an offering from Microsoft which takes LOB systems and exposes them out via WCF services. These services can be consumed through a variety of means including the WCF service model, WCF channel model, BizTalk model, and in some cases, ADO.NET. Regardless of the access scheme, the adapter connects to the target LOB system in the same fashion. The adapters make use of the WCF LOB Adapter SDK which provides developers a common way to build system adapters that can be accessed by a variety of consumers.

In this present edition of the Adapter Pack, there are adapters for Oracle databases, Siebel systems and SAP. Each adapter has an associated custom WCF binding, which is used to connect to each system type. Each adapter uses bits from their respective source companies for connectivity. That is, the Oracle adapter uses the Oracle Data Provider for .NET 2.0 (ODP.NET), the Siebel adapters uses the Siebel COM Data Control library (sstcha.dll) and the BusinessObjectsInterfaces.dll library (included in the Siebel Web Client components), and the SAP adapter uses the SAP Unicode RFC SDK Library (librfc32u.dll).

As for consumer access support, the following table outlines the out-of-the-box capabilities included with each adapter:

Adapter	BizTalk Int.	Channel Model	Service Model	.NET Framework Provider
Oracle	x	x	x
Siebel	x	x	x	x
SAP	x	x	x	x

A brief description of each:

• BizTalk applications – This means that you can use the adapter to generate the necessary BizTalk artifacts (schema, binding) for interacting with Oracle data. Notice that unlike the previous BizTalk Oracle adapter, there is no orchestration generated.
• WCF service model – This is the most developer-friendly way to interface with WCF services. A proxy class is used to call service operations alongside strongly typed .NET classes as parameters.
• WCF channel model – If more granular control of the communication channel or message payload is needed, the Oracle adapter is perfectly compatible with the WCF channel model. Using a channel model, the developer can choose what to include in the message, and also has the ability to use the adapter’s support for streaming of Oracle large object types.
• .NET Framework Data Providers – This provider allows developers to query either Siebel or SAP systems using an ADO.NET client (or SQL SSIS). For the Siebel provider, one can either perform SELECT queries on business components, or EXEC queries on business services. On the SAP side, one can perform SELECT or EXEC statements on the SAP system. You’d use this capability to import data from these LOB systems, vs. loading pushing into them.

The Adapter Pack documentation mentions IIS-hosted web services as an additional means of access. Note however, that enabling standard web service access to LOB systems requires building a custom web service wrapper around the Adapter Pack generated bits. This scenario will be demonstrated in the next article, but note that this capability is not a out-of-the-box feature.

Microsoft BizTalk Adapter 3.0 for Oracle Database

This article, and the two subsequent articles, will focus on the Oracle Adapter (formally called the Microsoft BizTalk Adapter 3.0 for Oracle Database) included in the Adapter Pack. Many of the experiences demonstrated for the Oracle adapter will map directly to capabilities of the Siebel and SAP adapters, but naturally the Oracle adapter exposes its own unique functionality.

Using the Oracle Adapter, a developer can execute operations on database tables and views, call stored procedures, and execute functions. The Oracle Adapter in the Adapter Pack offers significantly more capabilities than the classic LOB BizTalk adapter. Some key improvements include:

• Use of ODP.NET for database access vs. Oracle ODBC driver
• Exposure of additional configuration settings around connection pooling and caching
• Support for in or out REF CURSORS
• Data streaming support
• Support for Oracle large object (LOB) data types such as binary large objects and binary files
• The remaining portion of this article will demonstrate how to perform simple Oracle database queries using either a code client or BizTalk client.

---

Scenario #1 – Performing a data query via code

---

So how does one execute an Oracle data query in code using the BizTalk Adapter Pack? First off, the Oracle adapter requires adding an entry into the tnsnames.ora file that is part of the Oracle client installation. Think of this as similar to setting up the System DSN in previous versions of the Oracle adapter.

Next, within Visual Studio.NET 2005, a project was created add “Add Adapter Service Reference” was chosen from the project “References” folder.

Next, a wizard is shown where one can connect to, and browse, an Oracle repository. The first step was to choose which adapter binding to apply. This demonstration used the OracleDBBinding.

Next, the appropriate security settings were applied. Note that these credentials are purely for connecting to, and browsing, the Oracle repositories. This data is not persisted when artifacts are created for the chosen Oracle database. Also, be aware that the credentials appear to be case sensitive as errors were encountered later on unless the user name was entered in all capital letters.

Next, the name of tnsnames.ora file entry was applied as the “DataSourceName.” In this way, there are no machine-specific settings stored on the adapter itself.

Once that data was entered, and the “Connect” button clicked, a list of all the databases on the Oracle server were returned. Below, notice that the “BIZTALK_DEV” database was chosen.

One can browse all of the artifacts in the database and choose which actions to perform. In the diagram below, notice that the “Customers” table was chosen, and only the “select” operation was added to the operations exposed by the adapter.

After the desired operation was chosen, and the wizard completed, two files were added to the .NET project. First, an “app.config” file which contained the OracleDBBinding configuration details. Next was the “OracleDBBindingClient.cs” file which holds the service contract, proxy class, and strongly typed parameters.

The application configuration file, shown below, contains a number of settings for managing timeouts, performance and caching. Also note that the endpoint address maps to the tnsnames.ora file entry. There is no direct reference to the underlying Oracle database server.

Using the WCF Service Model, it’s quite simple to query the Oracle database. After creating an instance of the auto-generated proxy class (using the application configuration endpoint name as a parameter), one has to set the valid credentials to access the database. The “select” operation accepts a list of columns to return as the first parameter, and any filter criteria (the “where” clause) as its second parameter.

---

Scenario #2 – Performing a data query via BizTalk

---

How about executing an Oracle data query from a BizTalk application using the BizTalk Adapter Pack? The Visual Studio.NET BizTalk project does not have a “Add Adapter Service Reference” menu option, but rather, the developer must choose to “Add Generated Items.” There, the “Consume Adapter Service” option will be available.

The wizard screen that follows is identical to the experience highlighted in the previous scenario. Regardless of the Oracle consumer, metadata is browsed in the same fashion. Once again, the “select” operation on the Customers table was chosen.

In the BizTalk project, two new artifacts are added. First, there is the “OracleDBBindingSchema.xsd” which is an XSD representation of the strongly typed parameters. Secondly, there is a custom binding file that contains all the details for the WCF-Custom adapter in BizTalk.

Within the BizTalk project, messages were created that pointed to the auto-generated types in the OracleDBBindingSchema.xsd. The “Select” type was chosen for the request message, and the “SelectResponse” message was chosen for the response message.

A map was created to populate the “FILTER” value with a specific customer ID (based on the input value).

The final orchestration is below. After a query is received into the orchestration, the “Select” message is created via a map, and then that message is sent to a new request-response port. Notice that unlike the classic Oracle (ODBC) adapter, there was no orchestration generated automatically. So, there was no “port type” that had to be referenced here. Also be aware that the name of the operation on the port is relevant. The standard name, Operation_1, would cause a problem. Unless the WCF.Action value is set in code, or the send port default configuration changed, then BizTalk won’t know which operation to call on the WCF Oracle adapter. Notice that the operation name on the port below is called “Select.”

Next, after deploying the BizTalk project, the auto-generated binding file was imported into the BizTalk application.

On the first page of the WCF-Custom send port configuration, notice the SOAP Action section. Here is where the WCF.Action name matters. If the orchestration above had kept “Operation_1” as its operation name on the port, and other steps weren’t taken (such as setting WCF.Action manually, or changing this text box to only contain a single action line), then the correct operation on the Oracle adapter would not get called.

On the “Binding” tab of the adapter configuration, notice that the new OracleDBBinding was selected.

Finally, on the Credentials tab, see that the account used to access the database was configured here.

After starting and enlisting all the application bits, and a query message sent in, the orchestration sent out the response from the Oracle adapter. Sure enough, the “SelectResponse” message contains the record from the database.

---

Summary

---

In this article, we’ve looked at the new BizTalk Adapter Pack and how to use the Oracle adapter to query a database table from either .NET code or a BizTalk project.  In the next two articles, I’ll demonstrate key use cases for using the Adapter Pack with code or BizTalk.

Once this entire BizTalk+WCF series is complete, I will make the entire source code available.

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.