My Initial reading(All I know so far) on WCF-SQL Adapter with BizTalk 2010

In this article, I am going to briefly describe about the various benefits of using the new WCF-SQL Adapter, released as part of the BizTalk Adapter Pack 2010.

I have been working on SQL Adapter with the BizTalk 2004/2006 versions & all I used to use is generating the SQL schema from the stored procedures for the DML operations (SELECT, INSERT, UPDATE & DELETE) .I have been hit with few issues during my personal experience with SQL Adapter. Few of them,

·         The schema generated using SQL adapter is not very flexible.Means, I can’t promote or distinguish fields. Factors include, the adapter generates them as either attributes or record nodes. I need to tweak the schema manually, to convert them into elements & then I can either promote or distinguish.

I am not going to compare the SQL Adapter with WCF-SQL, just for the above mentioned issues.

But I am going to describe the features & benefits of WCF-SQL over SQL Adapter.

WCF-SQL Adapter Features

·         SQL Server DB can be exposed as WCF service to external clients – exchange of SOAP messages for request-response.

·         Support for performing DML operations directly on to the SQL tables. The schema generated will have the same number of fields as the table & u have the option to generate separate schema for separate DML operation. These schemas make it very simple and easier for mapping SQL to/from other message formats.

·         Support for strongly typed stored procedure. In the SQL Adapter, the stored procedure should return the result set as XML AUTO, XMLDATA .otherwise, we will have pain in generating the schema. With WCF-SQL adapter, we can generate schemas from stored procedures without FOR XML. Also, the schema generated will have the list of fields which are returned in the result set. For example,

Select ID, NAME, DESC, AMNT from Table where ID = @ID, inside the stored procedure.

Then, the schema generated will have the fields as ID, NAME, DESC, AMNT.This is lot more easily not to do mapping & message construction within BizTalk orchestration.

Also, we can generate schema with <Any> field instead of the specific field names. This is the difference between strongly typed stored procedures & stored procedures.

·         WCF-SQL adapter also provides backward support for the stored procedure with FOR XML AUTO.

·          The CLR type ADO.NET functions like ExecuteNonQuery, ExecuteReader & ExecuteScalar are also supported by WCF-SQL adapter. These functions become useful for situations like INSERT, UPDATE, and DELETE where you won’t expect any result set back, but an integer return value & if you want to return single value from SQL (value from 1^st row 1^st column).Another advantage of using these functions is, executing multiple SQL statements.

·         Composite operations – means we can execute related set of operations together on different database tables (SELECT is not supported). This means you can perform separate insert & delete operations together with the composite message. All we need to do is to construct a composite schema with all the operation schemas referenced.

WCF-SQL adapter is not just whatever I have listed above. It has much more feature sets & options for developers such as polling based on specific conditions, SQL-WCF adapter using WCF-Service & channel model and so on. The Binding for the WCF-SQL adapter is been made easy (binding file generated along with the schema), which can be imported to configure physical send/receive ports.

I will try to cover up and advanced version of this article, with elaborating more on the WCF-SQL adapter, with some hands on samples. Thanks for reading & happy Biztalking!!!