BizTalk Server: WCF-SQL Adapter Table Operations

Article
1/17/2024

Introduction

The WCF based SQL Adapter can be used to connect BizTalk applications to SQL Server. WCF-SQL Adapter is part of BizTalk Server Adapter Pack 2010/2013. To work with the adapter you will need to install the BizTalk Server Adapter Pack (Adapter Pack is available through BizTalk Server 2010/2013 medium e.g. DVD/ISO). Once installed you can start making use of its features. The WCF-SQL adapter has more features on-board then what is referred to old SQL adapter, which is the out-of the box SQL adapter. In this article focuses on usage of the WCF-SQL adapter for the following table operations: INSERT, UPDATE, DELETE and SELECT.

Note: This article is derived from the following blog posts from the author: WCF-SQL Adapter Table Operations and BizTalk Adapter Pack 2010 WCF-Custom with sqlBinding Table Operations through Messaging solution.

WCF-SQL Adapter

The WCF-SQL adapter is a part of the BizTalk adapter Pack that including system and data adapters SAP, Siebel, Oracle databases, and Oracle eBusiness Suite. In older BizTalk versions (2006 and below) the adapters were built on top of a commonly defined BizTalk Adapter Framework. This framework prescribed interfaces and APIs for adapter developers in order to elicit a common look and feel for the users of the adapters. With BizTalk Server 2006 R2 a new framework was introduced: WCF LOB Adapter SDK. This new framework can be considered as an evolution of the BizTalk Adapter Framework, yet now it is based on WCF technologies.

WCF-SQL Features

There is a huge difference in features between the out-of the box SQL Adapter and the WCF-SQL Adapter. In the table below these differences will be quite obvious.

Feature	SQL Adapter	WCF-SQL Adapter
Execute create-read-update-delete statements on tables and views; execute stored procedures and generic T-SQL statements	Partial (send operations only support stored procedures and updategrams)	Yes
Database polling via FOR XML	Yes	Yes
Database polling via traditional tabular results	No	Yes
Proactive database push via SQL Query Notification	No	Yes
Expansive adapter configuration which impacts connection management and transaction behavior	No	Yes
Support for composite transactions which allow aggregation of operations across tables or procedures into a single atomic transaction	No	Yes
Rich metadata browsing and retrieval for finding and selecting database operations	No	Yes
Support for the latest data types (e.g. XML) and SQL Server 2008, 2008 R2, 2012 platform	No	Yes
Reusable outside of BizTalk applications by WCF or basic HTTP clients	No	Yes
Adapter extension and configuration through out of the box WCF components or custom WCF behaviors	No	Yes
Dynamic WSDL generation which always reflects current state of the system instead of fixed contract which always requires explicit updates	No	Yes

Adding the WCF-SQL Explicitly

The SQL adapter can be used in BizTalk either as a WCF-Custom port or a WCF-SQL port. If you want to use the SQL adapter through a WCF-Custom port, you do not need to add the WCF-Custom port to the BizTalk Server Administration console, because the WCF-Custom port is added to the BizTalk Server Administration console by default. However, if you want to use the SQL adapter through a WCF-SQL port, you must first add the WCF-SQL adapter to the BizTalk Server Administration console.

To add the WCF-SQL Adapter you need to start the BizTalk Server Administration Console and then perform the following steps:

In the console tree, expand the BizTalk Group, expand Platform Settings, and then click Adapters.
Right-click Adapters, point to New, and click Adapter.

Figure 1. Add new adapter in Platform Settings Adapters.
In the Adapter Properties dialog box, specify a name for the adapter and from the Adapter list, select WCF-SQL.

Figure 2. Specifying name and adapter.
Click Ok.

Adapter Metadata Wizard

The Adapter Metadata wizard is supplied through WCF LOB Adapter SDK and necessary to generate schema’s through Visual Studio .NET. This tool enables you to browse through the SQL objects like table, views and stored-procedures. You can generate schemas based on the operations you want to perform on the database.

Table Operations

To perform operations on SQL Server table using the WCF-SQL adapter with BizTalk Server, you must perform a set of design-time and run-time tasks using Visual Studio and BizTalk Server Administration console respectively. These high level tasks for a message based solution are:

Create BizTalk project, and generate schema
Create messages instances
Build and deploy the BizTalk project
Configure the application

Create BizTalk project and generate schema

First step will be that you will need to create the XML schemas for your table operation(s). With the WCF LOB Adapter SDK (SP2) installed, you will have the metadata browser built into Visual Studio. NET at your disposal. You can access this from a BizTalk project by right-clicking the project, choosing Add then Generated Items and selecting Consume Adapter Service.

Figure 3. Add Generated Items - Consume Adapter Service template (click to enlarge).

This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing you will need to do here is choose the sqlBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style.

Figure 4. Consume Adapter choose Binding and configure URI (click to enlarge).

Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories. The contract type can be Client (Outbound operations) as seen below in the screenshot to perform operations on SQL objects or Service (Inbound operations) to perform polling operations.

Figure 5. Select contract type and category (click to enlarge).

On the root "/" character a range of untyped generic functions are displayed, which can be exploited. Click on tables and you will see a number of Tables, select Table (screen below the Employee table has been chosen). Note that other SQL objects can be accessed like procedures and views.

Figure 6. Choose SQL Object: Table Employee (click to enlarge).

Click the Delete, Insert, Select and Update statement in available categories and operations. Click the Add button to add the operations.

Figure 7. Select operations (click to enlarge).

After clicking the OK button at the bottom, schema (and a single binding file) is added to your associated BizTalk project in Visual Studio.NET.

Figure 8. TableOperations BizTalk Project View.

The binding can be used later, when solution is deployed and ports have to be configured for SQL Server (i.e. Send Port).

Create messages instances

The next step is to generate messages from generated schemas, which can be routed from Receive location in Receive Port to a send port with WCF-SQL Adapter (messaging based solution) or an orchestration (orchestration based solution). Generating the first message is easy, as you can right click on for instance TableOperation.dbo.Employee.xsd (see screenshot above) and select Generate Instance and an instance of top element is created (i.e. delete).

Figure 9. Schema Table Operation.

You will get an instance of message as depicted below.

Figure 10. Schema Instance Delete Message.

By opening up your TableOperation.dbo.<object>.xsd in file-editor you can move the next operation to top, so you can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with… and then select XML Editor) to move elements around. In end you will have four xml instance for TableOperations, save to file as xml (type all files, encoding UTF-8!).

Build and deploy the BizTalk project

Only schemas are required for a messaging only scenario with WCF-SQL Adapter. Therefore after assigning a strong name to your project(s) and an application name to it can be build and deployed to the BizTalk runtime. In the administration console you can further configure the application and start it.

Configure the application

After your solution has been deployed it is time to configure the ports. FILE ports for instance are easy and the focus here will be configuring the WCF-SQL Send Port. Through BizTalk Management Console one can browse to the application and right click the application and select Import Bindings … and navigate to file called WcfSendPort_SqlAdapterBindings_Custom.bindinginfo.xml, select and click Open. You will notice that a Send Port will be created automatically for you.

The next steps are setting filters on WCF-Send Port and File Send Port (see picture below). Note that the operation names are not required as the Send port is not bound to a logical port in an orchestration. If you do have an orchestration than operation names are required and have to be same as operation names of the logical send ports. If these are not similar you will run into an issue described in this post Why does the Adapter say ‘Action is not understood’ even though I am using the binding file generated by the ‘Consume Adapter Service’ wizard?. In the end your message based solution can be something that is depicted in the picture below:

Figure 11. BizTalk Operations Message Solution Overview (click to enlarge).

Wrap up

This article shows how to perform table operations using the WCF-SQL adapter from the BizTalk Adapter Pack. This is not the only capability of the WCF-SQL Adapter. You can also use the WCF-SQL ADapter to perform operations through SQL Views, Stored-Procedure, Polling and so on (see WCF-SQL). The adapter can even be used to connect to SQL Azure and perform operations. The article demonstrated an approach for a messaging based solution for performing table operations in SQL Server. The WCF-SQL Adapter can also be used in a BizTalk solution that makes use of orchestrations.

Call for action

In case you want to try or find out yourself how to work with the WCF-SQL Adapter you can find a sample for this kind of solution can be found on code gallery: BizTalk Table Operations - Messaging. Besides this sample there are a few others on the code gallery concerning other operations on SQL objects:

You can also find samples on the Microsoft Download Center:

Share via