Migrating to WCF SQL Adapter from SQL Adapter (Sending)
We will now look at the differences and similarities of the old SQL Adapter vs. the new WCF SQL Adapter when it comes to sending from BizTalk; both doing one-way and solicit-response communication with the SQL Server.
Sending using the old Adapter
When doing an Insert on a SQL table using the old Adapter all we have to do is send an Updategram to the SQL Adapter and set the connection string.
On the one-way SQL Send Port we are required to set both the connection string and a namespace, even though this namespace is not used for anything!! Try it yourself, the namespace do not have to match the namespace of the updategram received, it will work nonetheless. It doesn’t make sense that we have to supply a one-way Port with a namespace, it should only be required for the Solicit-Response Port when a response message requires the Adapter to set both a root element and a namespace!
1 Table Insert
And the updategram XML needed to populate a table (SendOneTable) with 2 rows, 4 columns would be:
Notice that the namespace doesn’t match the namespace specified on the Adapter configuration of the Send Port at all, and the Adapter couldn’t care less!
Multiple Tables Insert
For inserting rows in several tables in the same transaction, we can use the following XML:
Notice that all we did was adding another record that conforms with the other table’s name and column(s). Also notice that the new table does not reside in the default dbo Schema, but in a custom Schema (NewSchema). Even though there is a bug in the Visual Studio Schema generation tool for the SQL Adapter that do not allow us to generate Schemas for table not residing in the dbo Schema, the Adapter can update them!
Calling a Stored Procedure one-way
When calling a stored Procedure named: UpdateTable with 1 parameter (textValue), the following XML is used (this time notice, that both the namespace and root element can be whatever we choose):
Selecting Data using a Solicit-Response Port
Using the SQL Adapter we are not able to select directly using SQL commands, if we want data back in BizTalk. To use the Solicit-Response Port with the SQL Adapter we need to call a Stored Procedure (the SP is required to return XML (FOR XML clause).
By calling a Stored Procedure named GetTable with no input parameters, which returns a table in an XML structure:
And setting up a Solicit Response Port with the following configuration:
We will get the following XML back:
Notice that the root element and namespace was chosen by us at the Send Port level.
Sending using the new WCF Adapter.
When using the new Adapter, the syntax for inserting directly into tables are somewhat different than it was using the old Adapter:
This new syntax is used both for one-way operations, solicit-response and multi-table syntax.
This means that a direct migration from the old Adapter is not possible, Schemas and maps will need to be re-generated.
When it comes to calling Stored Procedures, the syntax are closer to the old Adapter, but there are still differences: