WCF-SQL Adapter Modernizes BizTalk SQL Adapter Capabilities

In my spare time I have been playing and testing all of the latest released capabilities of BizTalk Server 2009. During some of my time I have been working with the new WCF-SQL adapter that exposes a new WCF binding, SqlBinding, as part of the latest Adapter Pack. If you have ever worked with the BizTalk SQL adapter you know that the wizard in Visual Studio frequently caused problems and unfortunately these shortcomings were never adequately addressed in a service pack or updated release. Usually it was required that you just take a working example of the SQL adapter and then manually modify it to work with your database objects. More often than not you probably just avoided the SQL adapter completely and worked with the database through ADO.NET calls in an orchestration or custom pipeline. Now with the updated Adapter pack the WCF-SQL adapter finally modernizes the SQL adapter experience and gives you a stabile and very functional environment for interacting with SQL via an adapter. This article will highlight some of its capabilities and provide a walkthrough of the new WCF-SQL adapter wizard.

All of my testing has been with the BizTalk 2009 Beta loaded on a Windows Server 2003 box with Visual Studio 2008 (including SP1) installed. I installed the BizTalk Adapter Pack found at https://connect.microsoft.com/content/content.aspx?ContentID=10580&SiteID=218. To add a schema based on the WCF-SQL adapter, open or create a BizTalk project in Visual Studio 2008 and right-click on the project. Clcik on the Add New Item > Add Generated Items as shown below:

Then after the window loads you should see the following window:

Click on "Add Adapter Metadata" and then click "Add". This will open up a general window that is used for several different adapters. After installing the BizTalk Adapter Pack V2, you should see an item listed in the listview called WCF-SQL. You should also see the older SQL item listed as well as any other Adapter Pack or Line-of-Business (LOB) adapters. You will need to enter a SQL server name and database in the combo boxes below the listview. I entered my local SQL Server instance and my BizTalkMgmtDb as seen in the screenshot below:

You do not need to enter a port for this adapter so just leave this one blank and click Next. The following screen shows the form that is loaded next to help you determine which SQL objects to include in the WCF-SQL schema. If you worked with the previously mentioned rudimentary SQL adapter in BizTalk, at this point you would be presented with the option of entering a command text or a single SQL object name for the wizard to use to generate the schema. But the WCF-SQL adapter wizard provides database object browsing and filtering to further simplify the task of schema generation. The following screenshot shows the form when it is initially loaded:

If you merely want to use the database you specified earlier in the wizard, click the configure button and then click OK to generate the default URI of mssql://.//?. Then click Connect to connect to the database specified. This will enable the bottom half of the screen where you can expand the categories of database objects and then find one or more objects to select. It is also possible to select a different database and/or configure additional database connectivity properties via the Configure button’s window. Below is an image of the Configure button’s window with the SSO database details specified:

I have found that it is not possible to specify "(local)" for the SQL instance name as shown above so do not try this. Below is the form changed to allow selecting from several of the SSO databases’s tables:

The "Search in Category" box in the middle of the form provides a way to filter the list of database objects. I have found that it is possible to use wildcards in this box by the percent character "%" but not by the asterisk – "*". Once you have selected objects to generate a schema for, click OK. Visual Studio will generate a schema and a related code class as well.

The designer experience provided by the WCF-SQL adapter wizard is a huge step forward for the creation of BizTalk SQL schemas.