BizTalk 2004: SQL Adapter and SQL Ports: User Interface: Review

by | Mar 14, 2006 | BizTalk Community Blogs via Syndication

BizTalk 2004: SQL Adapter and SQL Ports: User Interface: Review


I’m considering only working with stored procedures, not SQL commands nor Updategrams. Generating the SQL port artifacts with “Add Adapter Wizard” is out of scope this article.


Two-root schemas


The SQL ports use the Two-root schemas, one root for Request and one for Response. We’ve got one root empty if we use the stored procedure without input parameters.


Why? The one reason I can see is the logical grouping two schemas working with one port. From one side it logically links those schemas; from other side it seriously complicates the issue. Now we have to use the multi-part message types, have to manually prepare them after the schema generating procedure. Such preparing includes copying the multi-part message types and the special port types, using these messages types, to our orchestration. In most cases we should rename this schema and copy it to other project. It is not a straightforward process.


Why there is not used the standard Request-Response object model with two separate schemas? In this case we should manually create in the orchestrations the ports working with such schemas. There are not the multi-part message types, not all error prone steps around.


Anyway, the SQL ports use the Two-root schemas.


One-way and two-way ports


The stored procedures always return the data. In the simplest case it is the integer value (by default 0 means Success).  That means all SQL ports (working with stored procedures) should be two-way ports.


Let see, is it true or false.


Send ports

We can create one-way and two-way SQL Send ports.






(Pict. 1. One-Way Send Port Properties window)



 



(Pict. 2. Solicit-Response (Two-Way) Send Port Properties window)



 



As you can see the only difference is the two-way port can use different pipelines for send and receive ways when the one-way port use one pipeline for both directions.


Receive ports

For the SQL Receive ports we can create only one-way ports.


Note

Undercover we always work with two-way SQL ports (ports which work with stored procedures) no matter how they are called in BizTalk.


SQL Send ports


When we click the “secret” button [] in the URI field of the Send Port Properties window (Transport tab) we’ve got the “SQL Transport Properties” window.



(Pict. “SQL Transport Properties” window of the Send port)


SQL Receive ports


When we click the “secret” button [] in the “URI” field of the “Receive Port Properties” window (Transport tab) we’ve got the “SQL Transport Properties” window.



(Pict. 1 “SQL Transport Properties” window of the Receive port)



 



After configuring the “Connection String” parameter we’ve got the value of the “URI” field. It is compounded from a protocol name – “SQL://”, a server name – “localhost”, and a database name – “Court_Prod”.


There is another “secret” button [] in the “SQL Command” field.



 




(Pict. “Import Information from a generated schema” window)


Does the “Project” drop-down field hold the project names? No. It holds the names of the deployed assemblies and the public BizTalk assemblies like Microsoft.BizTalk.GlobalPropertySchemas or Microsoft.BizTalk.DefaultPipelines.


We should choose the schema. It should be the schema generated by “Add Adapter Wizard”.


The generating schemas for SQL port is out of scope this article, but the name of this wizard is the other naming odd of the BizTalk. This wizard does not add adapter. We are adding adapters from BizTalk Administrative Console. This wizard generates the objects (artifacts) for the SQL Port. In BizTalk 2006 it is called “Add Adapter Metadata Wizard” but anyway it adds metadata not for adapter but for the port. The adapter metadata should be the “default” data for all ports this adapter type not for the specific port.


After this we have the modified “SQL Transport Properties” window.



(Pict. 2 “SQL Transport Properties” window of the Receive port)


There are three more parameters: “Document Root Element Name”, Document Target Namespace”, and “SQL Command”.


Take in mind these three fields are Editable, you can change it by mistake. Why it so? If these fields fill up automatically why we need to change them by hand?