Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

WCF-SQL adapter and simple select statement

leave a comment »

In order to use WCF-SQL adapter you need to install two additional component :

  • ASDK_x86
  • AdapterPack_x86

Both of those can be found on BizTalk 2010 installation CD. Once you do it, you need to register new adapter in BizTalk adapter tab (simply go there and add new adapter by pointing WCF-SQL). After that you are ready to start using new adapter.

Here I would try to go thru simple example how to use that adapter to perform simply “select” statement and by this retrieve one record from DB and put it (after simple transformation) into BizTalk MsgBox.

First go to Visual Studio and create new project. Once project will be created go and add two new schemas and map:

1. Schema1 = “resp.xsd”

image

This schema will be used to map returned data by our select statement. Please notice two important things here:

  • Entire schema need to be “qualified” one
  • You need to set-up namespace (we will use it during configuration of WCF-SQL adapter). In my case I’m using “http://hempel.com/cw02” for all my artifacts

2. Schema2 = “common.xsd”

image

again:

  • mark elements as “qualified”
  • remember of setting up proper namespace

3. Map = “ConverttoCommon.map”

image

which is used to translate Schema1 into Schema2.

In addition to above you need a database on SQL and table of the structure like the one shown bellow:

image

Once you do this, then compile project and deploy it to BizTalk. Inside BizTalk go to receive port and create one of “one-way” type. After that add new location and:

image

then click on “Configure”: Set-up connection string to SQL and click on Binding:

image

set-up binding in the way that:

  • XmlStoredProcedureRootNodeName = root node of you SQL schema (this will be added by the adapter during retrival of data)
  • XmlStoredProcedureRootNodeNameSpace = “http://hempel.com/cw02”
  • InboundOperationType = “XmlPoling” <- this is importent as this defines the way how the data will be returned by the adapter
  • PolledDataAvailableStatment = “select count(1) from source” <- this will let the adapter to know if there is something to retrieve
  • PollingStatement =
   1:  select 
   2:  1 as tag, NULL as parent,
   3:  'http://hempel.com/cw02' as "cust:Source!1!xmlns:cust", 
   4:   Id as "cust:Source!1!cust:Id!element",
   5:   Name as "cust:Source!1!cust:Name!element",
   6:   Attr01 as "cust:Source!1!cust:Attr01!element",
   7:   Attr02 as "cust:Source!1!cust:Attr02!element",
   8:   Attr03 as "cust:Source!1!cust:Attr03!element"
   9:  from source for xml explicit
 Unfortunately this is the way how you need to make SQL statement. If you don’t go for this then you will get improper namespace for root elements returned by SQL which will prevent further processing of the message (you will be unable to map it to our internal SQL response message schema (schema1))

Done.

Next go to port definition and add “Inbound map”:

image

Click OK, and add also send port (for example file one) and set-up it in the way that it will subscribe for our common message:

image

Completed! Now if you put some data into SQL table, then you should get a file in output folder (if you use file send adapter in sent port).

Advertisements

Written by Mariusz Gorzoch

9 March 2011 at 15:21

Posted in BizTalk 2010

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 bloggers like this: