BizTalk Server: Streamlining WCF SQL and Oracle Messaging-Only and Other Patterns

Article
1/17/2024

The Issue

There’s been a few cases on the forums lately where uses have had some issues with WCF SQL Operations and the first thing that comes to mind is the WCF Action.

Please, read the entire article for a fun challenge at the end!

The WCF Action property for SQL Server, Oracle and SAP Operations has been a rather perplexing implementation since the WCF Adapter Pack was introduced with BizTalk Server 2006 R2 and unfortunately, the situation hasn’t changed.

It’s the messaging-only scenario where we face our biggest hurdle since the Action property takes either a single action, TypedProcedure/dbo/FirstOperation for example, or the Action Mapping Xml blob. The Action Mapping blob presents a secondary problem since it relies on the BTS.Operation Context Property which is not set in a messaging-only scenario.

So, we are left with some not-so-desirable choices, create a separate Send Port for each Operation or route the message through an Orchestration just to set the BTS.Operation property. If the Orchestration route is chosen, another more direct option is available, simply set the WCF.Action Context Property to eliminate the need for Administrators to worry about the Action Mapping Xml blob.

Facilitating Development and Deployment

This has lead me to develop a few workarounds of varying depth. For apps we deliver to our customers, we have a custom Pipeline Component that sets the WCF.Action Context Property value for SQL Server, Oracle and SAP Operations. For these bindings, the WCF.Action value can be easily derived directly from the BizTalk Message Type for nearly all cases. The one exception is a less common SAP Operation type for which I have to pull the Schema.

This is very useful as it eliminates any Design time or Admin time issues. The Message Context is unmodified and the Action field is left blank.

But I kept thinking there has to be an easier way, especially for what is likely the most common case, WCF SQL and/or Oracle DB Operations. Here is my solution.

The Generic Action Value

Fortunately, the sqlBinding and oracleDBBinding support what is essentially are generic Action values:

SQL Server: CompositeOperation
Oracle DB: http://Microsoft.LobServices.OracleDB/2007/03/CompositeOperation

By specifying the composite operation Action, the sqlBinding and oracleDBBinding will skip the root element of the Xml message and process each child element in order as if it were a single message.

Happily, the Adapters aren’t too particular about the name or namespace of the root element, just that it’s there.

For the purpose of this solution, the most important behavior of the composite operation Action is the Adapters make no effort to ‘validate’ the Action. The result, no more of these:

Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "MySQLOperation" was not understood.

Every Operation as a Composite Operation

Imagine we have an app with several Operations in SQL Server and Oracle:

FirstOperation – SQL Server
SecondOperation – SQL Server
Table Insert – SQL Server
FIRSTOPERATION – Oracle DB

Each will be called independently in a messaging-only scenario.

In a basic scenario, we would have to do one of four things to accommodate this:

Use an Orchestration to set WCF.Action
Use an Orchestration to get the BTS.Operation Property and maintain the Action Mapping Xml Blob.
Use a custom Pipeline Component to set either of these Context Properties.
Create three separate Send Ports, each with a single Action value.

Unless at Design Time, we treat every operation as a Composite Operation, which is actually really easy to do.

The SQLOperations Schema

The only notable difference between a Composite Operation Message and a single operation Message is that in a Composite Operation, the actual SQL operation message is the child of another user defined root.

This is a single operation Schema. The request message schema is its own root element as generated by the Wizard.

Creating the SQLOperations Schema takes a few simple steps:

Add a new Schema to the Project.
Change the default Root node name to something meaningful.
On the <Schema> node, Import the .xsd’s that define the individual SQL Operations.
Add a Child Record Element under to root.
Set the Data Structure Type of that element to a request message’s root element.
Repeat steps 4 & 5 to add additional Operations.
Add a second root element for the response container.
On <Schema> node, set the Envelope property to Yes.
On the response element, set the Body XPath to the response element.

This is how the SQLOperations message appears with all three Operations added.

If we were using an Orchestration based solution, where the Ports are Specify Later and bound in BizTalk Administrator, we can add all four schemas, SQL Server and Oracle, to the same composite schema. However, we would not be able to use the Message Type to filter at the Send Port.

It is not necessary to add any elements under the response. By setting Envelope=Yes and the Body XPath property, the response message will be debatched, resolved and submitted under its own message type.

This is the Imports list. All three Operations are defined in a single .xsd.

This is how the Response element is selected for the Body XPath.

Configuring the Send Port

In our BizTalk Administrator applications, we have a Two Way Send Port connected to the sample database instance on SQL Server.

On the Transport Properties dialog, we set the Action property to CompositeOperation.

For an Oracle Send Port, the Composite Operation Action value is http://Microsoft.LobServices.OracleDB/2007/03/CompositeOperation.

The easiest way to configure the Send Port is to import one of the generated Binding Files and change the Action property.

For this sample, the SQL Send Port has a Filter set to SQLOperations BizTalk MessageType.

Using the SQLOperations Message

In the most basic case, the messaging would be:

Receive an external message.
Map to the SQL Operation
Call the database
Send the response to its destination

We have a very simple request message to receive.

Which we map to the FirstOperation message in the SQLOperations schema.

<ns0:ExternalFirstRequest xmlns:ns0="http://CompositeOperationsSample.ExternalFirstRequest">
  <FieldData>My External Field Data</FieldData>
</ns0:ExternalFirstRequest>

Dropping this sample message in the pickup folder, we get this output:

<?xml version="1.0" encoding="utf-8"?>
<FirstOperationResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
  <StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/FirstOperation">
      <Data1>A constantValue</Data1>
      <Data2>My External Field Data</Data2>
    </StoredProcedureResultSet0>
  </StoredProcedureResultSet0>
  <ReturnValue>0</ReturnValue>
</FirstOperationResponse>

Here’s it’s important to note that the output message is only the SQL Operation response and does not include the SQLOperationsResponse root element. This is because earlier, we set the SQLOperationsResponse element to the Body XPath of the Envelope. This caused the XmlDisassembler to debatch the response message from the composite envelope.

Benefits

This pattern has a number of benefits to consider when developing a WCF SQL or Oracle app.

An app can be deployed with only 1 One-Way and 1 Two-Way WCF Send Ports for all Operations for with SQL Server or Oracle.
Multiple Orchestrations or Send Ports are no longer necessary.
A messaging-only pattern is easier to deploy and maintain.
The Administrator involvement in WCF SQL messaging is reduced or even eliminated.
WCF SQL messaging is more in-line with all other Adapters.

The “Why Not?” Challenge

Are there any reasons to not use this pattern? Well, that’s a great question. I have struggled for a long time to come up with a scenario or reason why the explicit Action requirement for the sqlBinding or oracleDBBinding is useful or beneficial. Several astute developers at customer sites have raised the same question.

Since there is a one-to-one relationship between the Action and Schema, there’s no redirection opportunity as you might have with a SOAP service. The message and Action are tightly coupled by necessity.

Most significantly, the Adapters are able to correctly process the Operation with the non-specific CompositeOperation Action.

I have created a Forum Post for discussion and answers to this question: Uses for the Action Property with SQL and Oracle DB Adapters

Share via