Blogging BizTalk

Lets Discuss BizTalk

BizTalk – SQL Integration : Part 1: WCF CUSTOM Adapter


There are many  adapters using which we can integrate BizTalk with SQL server, for example ‘SQL’, ‘WCF-SQL’,’WCF-CUSTOM’.  In this example I am going to focus on how we can leverage WCF Custom adapter to consume data from a SQL DB.
For this example I will be consuming data from a SQL Database table using a stored proc. The data is being pooled from a table columns which is defined to be of the xml data type.

Note: For this scenario using SQL Adapter will not work because of an known issue with xml/varchar(MAX) data types.

This is how my SQL table looks like:

Database

The Stored procedure

StoresProc

The stored proc is designed to accept parameters and it will return a result based on that. Hence we will be required to consume this stored proc on a two way send port side .

First of we will be required to create the contract for calling the stored procedure. Here is how you can do that: Use the “Add Generated Items” option provided by Visual Studio to generate it. We are going to pick consume Adapter Service here. This is going to give you a binding file and schema files. This binding file you can use to import later to create your send port. After you pick consume Adapter Service you are going to be presented with few windows:

COnsumeWhatStep0

AddAdapter_1

AddAdapter_2

Under the category you are presented with “Procedures” & “Strongly Types Procedures”. You can pick any of them however the first category will generate the schema with node of any type and the later will generate schema a specific data type, in our example it will be string type. Please see the images below.

For Procedure Category Schema definition will be following:

AnyType

For Strongly Typed  Procedures the schema definition will be following

Typedschema

Notice the difference in the data type of the node selected. Based on the type of schema generated you will have to handle accordingly. From usage perspective choosing the strongly typed stored  procedure is more convenient.

The Add Generated Item is also going to give you the option of “Add Adapter Metadata”. Using this will result in generation of an orchestration which will have the port type already defined within with request and response message set to stored procedure Request and Response. You wont get the port binding file thou.

AutoGeneratedOrchestration

Next step would be to create the orchestration which will invoke the  stored procedure. This is how my orchestration looks like. Since my stored procedure is expecting an input parameter I am providing this via an another message which is set to be the activation subscription of this orchestration.I am using another a transform to pass on this value to the input request of the stored procedure request schema. Further I have published the orchestration as service to save myself from creating few extra ports :-).

Orchestration

 Once you are done deploying your BizTalk solution. You will be required to import the binding file which was generated for us by BizTalk. That will result in creation of a send port. Change the SOAP Action header to following :Procedure/dbo/YourSPName  in case of typed you will be using  :TypedProcedure/dbo/YourSPName

SendPort and Binding

Bind your orchestration and start start all the artifacts.

Trigger it and you will notice that the data fetched from SQL comes wrapped under CDATA. this is a unique scenario where the response for the untyped schema will be following Notice your actual payload comes wrapped under <![CDATA…

.

<GetXMLDataResponse xmlns=”http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo”><GetXMLDataResult><DataSet xmlns=”http://schemas.datacontract.org/2004/07/System.Data”><xs:schema id=”NewDataSet” xmlns:xs=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”><xs:element msdata:IsDataSet=”true” name=”NewDataSet”><xs:complexType><xs:sequence><xs:element minOccurs=”0″ maxOccurs=”unbounded” name=”NewTable”><xs:complexType><xs:sequence><xs:element minOccurs=”0″ name=”id” type=”xs:int”/><xs:element minOccurs=”0″ name=”XMLData” type=”xs:string”/></xs:sequence></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:schema><diffgr:diffgram xmlns:diffgr=”urn:schemas-microsoft-com:xml-diffgram-v1″><NewDataSet xmlns=””><NewTable><id>1</id><XMLData><![CDATA[YOUR ACTUAL XML PAYLOAD]]></XMLData></NewTable></NewDataSet></diffgr:diffgram></DataSet></GetXMLDataResult><ReturnValue>0</ReturnValue></GetXMLDataResponse>

The response of the typed will not have CDATA section.

Now since we intend to extract the actual xml payload for our further processing we will have to extract it using XPATH (IN case of untyped) or XPATH/Message parameter(In case of typed).

Here is the example of XPATH we are goign to use in case of typed and untyped message:

Untyped:

StrPayload = xpath(MessageOP, “string(/*[local-name()=’GetXMLDataResponse’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo’%5D/*%5Blocal-name()=’GetXMLDataResult&#8217; and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo’%5D/*%5Blocal-name()=’DataSet&#8217; and namespace-uri()=’http://schemas.datacontract.org/2004/07/System.Data’%5D/*%5Blocal-name()=’diffgram&#8217; and namespace-uri()=’urn:schemas-microsoft-com:xml-diffgram-v1′]/*[local-name()=’NewDataSet’ and namespace-uri()=”]/*[local-name()=’NewTable’ and namespace-uri()=”]/*[local-name()=’XMLData’ and namespace-uri()=”])”);

Typed:

StrPayload = xpath(msgSQLResponse, “string(/*[local-name()=’GetXMLDataResponse’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’%5D/*%5Blocal-name()=’StoredProcedureResultSet0&#8242; and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’%5D/*%5Blocal-name()=’StoredProcedureResultSet0&#8242; and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetXMLData’%5D/*%5Blocal-name()=’XMLData&#8217; and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetXMLData’%5D)”);

Note:In the case of typed we can also fetch the payload using expression like msgSQLResponse.xmlload.

For the final response coming out of the orchestration I have used a message of   type Microsoft.XLANGs.BaseTypes.Any which will allow me to plug any kind of XML response in the body. Once done I invoke the service and get the stored procedure response in the form of my Orchestration response. This is how it is going to look when you invoke the orchestration using a web service client.

SOAPCALL

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

D Goins Insperience

Technological outformation for this day and age

[INACTIVE BLOG] Connected Thoughts - Thiago Almeida

Connected systems and the occasional picture

Uri Katsir's Blog

BizTalk , BizTalk RFID and .NET

prashantbiztalkblogs

My BizTalk Experiences

BizTalk Server Tutorial

BizTalk Server Concepts and Common Errors

Extremely Talented Monkeys

A Technical Blog by Ed Jones: Azure, .NET, BizTalk, WCF, and SQL Server

On All Things Web

Discussing web development without limits

Vikas Bhardwaj's Blog

Articles about BizTalk Server and .NET C#

Cloud develop

a blog about (cloud) development... because I'm a nerd

Hooking Stuffs Together

My learning logs from day to day work experience about Integration platform using Microsoft technologies.

Connected Pawns

Mainly BizTalk & Little Chess

jhelatissimo

a blogger in the process.

MS Innovations Blog

Tips, Tricks, and Workarounds for BizTalk and other Microsoft technologies

srirambiztalks

Katradhu Kaialavu,Kalladhadhu Ulagalavu!

Vijay Microsoft Technical

BizTalk, WCF, ESB ToolKit, Windows Azure

Mind Over Messaging

Musings on BizTalk, Azure, and Enterprise Integration

%d bloggers like this: