Earlier I wrote about how to use one SQL Port for multiple operations within a BizTalk Application / Orchestration. Now I would like to address another issue I often see handled in a manner I think is not the most appropriate. The SQL Adapter is a great tool, but may be a little too Black Box for many developers to understand the proper uses of. Perhaps the most common is how to handle multiple calls to a SQL Service (be it stored procedure or updategram).

Often the scenario looks like this: A message arrives with a repeating element in it and each element must be inserted into a database. Like many things in BizTalk there are actually several ways to tackle this problem and at least one doesn't even involve Orchestration, but usually the solution I see looks something like this.

Usually the loop iterates over an xpath expression extracting each message and assigning it to another that will then be sent to the SQL Port in question. This does work and does sort of do what the author intended. There are, however, a few potentially serious issues with this. For one there is not transactional integrity between the sends to the SQL Adapter. The last one could fail and the others would already have been sent and committed to the database. You could add compensation logic, but that sort of just piles on more mess on an existing mess.

What many do not realize is that a request can be made much simpler. The schema for the SQL send will probably look something like this:

-

With a instance like:

<ns0:GetDataRequest xmlns:ns0="http://nova/biztalk/sql">

<ns0:ReadSecurityIntervalData LastTime="1143003000" SecurityId="1" TimeFrameId="2" />

</ns0:GetDataRequest>

By default the generated schema does not have the Min Occurs or Max Occurs properties set for an request message (it does however set them to zero and unbounded for response messages). If you simply set these as the response elements are set (zero and unbounded) you can send multiple requests to the SQL Adapter at a single time.

<ns0:GetDataRequest xmlns:ns0="http://nova/biztalk/sql">

<ns0:ReadSecurityIntervalData LastTime="1143003000" SecurityId="1" TimeFrameId="2" />

<ns0:ReadSecurityIntervalData LastTime="1143006000" SecurityId="1" TimeFrameId="2" />

<ns0:ReadSecurityIntervalData LastTime="1143009000" SecurityId="1" TimeFrameId="2" />

<ns0:ReadSecurityIntervalData LastTime="1143012000" SecurityId="1" TimeFrameId="2" />

</ns0:GetDataRequest>

This not only saves creating an indeterminate number of messages, it ensures that they are all sent to the SQL Server in one atomic transaction (regardless of the transaction type in the Orchestration, which can't use an Atomic Scope with Send-Receive ports in any case) and as a single batch which reduces the number of trips to the message box and increases the throughput of a solution. The SQL statements will all be performed in the same DTC transaction in an all or nothing fashion which may be more in line with the business requirement in the first place.

This can be extended even further and combined with my original posting about SQL Server Adapter Port (Ab)Use. Not only can you call multiple operations through a single port, you can combine them through a single request and all in one operation. Suppose you had a series of stored procedures that all needed to be called and succeed or all rollback in a single transaction. Most people don't know that this is even possible with the SQL Adapter. Not only is it possible it's also pretty easy. This time you're request element (the root node you used for all your SQL Wizard-ry) will be made to use types defined for the different operations. You'll want to use a map to create your new request message and fill in all the elements that make up the operations you wish to call. This will result in a message like the following.

<ns0:SqlRequest xmlns:ns0="http://nova/biztalk/sql">

<ns0:ReadSecurityIntervalData LastTime="1143003000" SecurityId="1" TimeFrameId="2" />

<ns0:GetLastTrade TimeFrameId="2" />

</ns0:SqlRequest>

As can be seen here the request message contains multiple SQL Operations in a single message. I have gotten this to work in calling multiple stored procedures of different types, but so far I'm only getting the result of the first query. I will post on that solution after I find it.