One of the features that make BizTalk a great tool to work with is Adapters. Although they can take a while to learn the Adapter in BizTalk Server is the gateway to other systems you integrates in a BizTalk solution and is thus vital to BizTalk solutions.

Like many features in BizTalk there is great beginning documentation for the SQL Adapter, but shortly after that it sort of drops off and it can be difficult to find more advanced information.

One of the common complaints I see on the USENET groups I take part it (Microsoft.public.biztalk.*) is having to create multiple SQL Ports to call different stored procedures or updategrams on the same database. The Add Generated Items wizard for the SQL Adapter certainly contributes to this and it also does a few things I don't particularly care for.

For one it creates a new orchestration containing the multipart message types and the port that you can use for calling the Adapter. This in itself is not that bad (see my post about multipart messages in Orchestrations to see why). But most of us either call more than one SQL Service or are adding it to an existing orchestration (or not using orchestration at all). I don't like this new orchestration just to hold two multipart message types and a port that take very little time to create manually and in the location and manner you prefer. Worse still the orchestration is in the same Visual Studio Project as the schema which violates one of my guidelines for solution structure in BizTalk (see post)

Secondly it gives some terrible names to the Port, Orchestration, and schema file/ type. You should always change those names because they are meaningless by default (doing so, however, will break the orchestration that the wizard created for you since it will no longer find the types it wants).

This is the downside to wizards. To be more useful in the majority of cases they are less so in the minority of cases.

After generating the artifacts for your SQL use you will be left with a schema that looks like the following.

As you can see the schema has two root elements in it, one for your request and the other for your response.

The real issue comes when it is time to bind your orchestration ports. If you used the wizard and call four different stored procedures on the same database by default you will have four different send ports because each requires a namespace and root element of the returned message. This is actually where we can make a few changes to avoid having to create a separate port for each SQL call (even across orchestrations within the application). By this I mean we can share a port that connects to one database to call whatever operations on it we like. And it's not nearly as much work as I thought it would be.

Steps

Step 1

First when creating your schemas using the wizard be sure to use the same namespace every time. This really makes sense since you're doing the same thing, calling a database, in the same application. This is important because BizTalk identifies message types as a combination of namespace and root node name (for more info see: How BizTalk Identifies Types).

Next also in the wizard name your root response element the same such as SqlResponse. The Adapter actually puts whatever SQL Server returns under this node so it will not interfere with your returned data (you can skip this step if you like, but it might save you some headache down the road).

Step 2

Now comes the tricky part, by naming all of your response elements the same you're about to break BizTalk, so you need to combine them all into one schema. Either create a new schema for them (using the same namespace again and same root node name) or pick one of your existing ones to build off of. I've never found a friendly way to do this moving in Visual Studio so I just fire up TextPad and do it manually. Put each of these child elements under the root node you've designated as your Response Document Root Element (from the wizard in the previous step). After you put them there specify that they are Nilable and set their Min Occurs to zero. Don't worry this is the worst part and it's still not that bad. Also note if you were already doing things with these messages before consolidation (as I was when I wrote this article) you may see your orchestrations no longer able to identify the message types you just moved. If this happens it generally is resolved by closing Visual Studio and reloading the project.

Now in your Orchestration (if you're using one) just add operations to the one SQL Port you have. This can be done in the Orchestration View under Types -> Port Types. Technically I think you can get away without doing this, but it's still a good idea. It conveys intent and it allows for better message routing.

That's it, now you have one SQL Port that can be used for an unlimited number of operations in your BizTalk Application. If you want to be even bolder you can make this port public and allow other applications to use it, but I generally shy away from this for dependency reasons.