Geeks With Blogs
Martin Abbott not just .net

I started having a think about how you could make BizTalk Server 2009 RESTful as I had been asked to give it some thought.

Whilst thinking about it and wondering how I could let any subscription know the message it was subscribing to was supposed to be GET, DELETE, PUT or whatever I started creating some basic plumbing to give me something to test.

To this end I started taking a look at the new WCF SQL Adapter in the BizTalk Adapter Pack 2.0. This new adapter is built using the WCF LOB Adapter SDK and is the way we are now supposed to have a conversation with SQL Server when using either BizTalk Server 2009 or any other client instance that wants to interact using a service.

As I wanted things to be nice and basic I thought I’d check how you go about just selecting data from a database to start with. When you chose to add a generated item from the project context menu you now need to choose Consume Adapter Service. So, Step 1 is no longer the dreaded Generate Schemas.

Once you’ve clicked Add you get a nice UI that you use to define all the bits and pieces you need to interact with your WCF LOB application. Firstly you choose the binding, so in our case as we’re talking to SQL Server this is sqlBinding.
There is a big friendly Configure button that brings up another slightly more familiar UI. I say more familiar because this is the sort of UI you get from the old days of the adapter framework that allows you to configure the adapter. There are 3 tabs, Security, URI Properties, and Binding Properties. Since I’m just doing some testing I left Security and Binding Properties unchanged so simply configured my database and server in the URI Properties. Remember we’re doing WCF here so the database is exposed as an endpoint URI using a specific binding scheme.
It is noteworthy to mention that in the Binding Properties there are options for setting things like XmlStoredProcedureRootNodeName which seems familiar to the old school Generate Schemas functionality, but I didn’t set this. The reason this is noteworthy will become clear!
Once set, we can examine the URI generated and once happy can click another big friendly button that says Connect on it.
We then go through selecting what operations and functions we want to expose via our WCF service. Clicking “/” gives a list of standard .NET operations:
Clicking “Procedures” shows the stored procedures available:
Clicking on an individual table gives the CRUD operations on that table:
When any operation is highlighted, clicking on the Properties button helpfully displays the WSDL for that operation. This is extremely useful if you remember that these new WCF adapters, whilst packaged under the BizTalk banner can be used for any client as long as the binding is registered. Being able to get at the WSDL means we’re able to start building clients whilst other work carries on.
In the usual Windows way, we Add and Remove operations as required. One thing to note is the contract type Client implies that we’ll be calling the service from a client, i.e., exposing the operations we wish to make available. There is also the option of a contract type of Service. In this case the adapter is acting as the client rather than the service (which is a bit confusing I think) and you are presented with a different set of operations around polling for information.
As I want to test how you get data from a database, I chose to investigate the generic .NET ExecuteReader, a stored procedure call that takes an Id and returns a matching book record from my simply library database, and using a SELECT statement directly against the table.
After completing the UI and continuing you get a bunch of schemas added to the project and also a binding file. The binding file is a usual binding file that you can simply import in to your BizTalk application so taking the headache out of configuring the WCF port. The binding file contains a bunch of mappings from WCF Actions to port Operations, so much in the same was as working with normal WCF or web services, you need to make sure that the configured port operation in an orchestration matches the relevant operation in the binding file.
All my test orchestrations were basically the same and involved taking in a file, transforming to the relevant outgoing message expected by the service operation and then simply dumping the response out to another file.
Again, the transformations were all pretty similar using String Concatenate functoids to provide the relevant mapping from an incoming book Id. In the case of the ExecuteReader operation this involved constructing a SELECT statement with the Id (note the incoming schema of the input message in the diagram below) whilst the stored procedure operation simply exposes the procedures parameters that you match up, which in my case was simply the book Id.
The interesting one is when you work with the direct CRUD operation. Here you have to provide the columns required in the response and the query.
For the Columns I simply had a functoid that passed out “*” as I wanted all columns, but you can comma separate as required. For the Query you create a statement minus the SELECT <columnlist> part.
So pretty simple. It is worth noting that you have to provide a value for Columns as shown by the minOccurs in the generated schema. I think this must have changed from earlier versions as Richard Seroter mentions in his article that this defaults to “*”. You can get away without providing a Query however.
So once all this is built, deployed and set up I simply dumped a message in my file location and awaited the output from my operations. My input message only had an Id whilst all other elements were empty (see schema on map above).
The response from the CRUD operation is shown below:
This is great as it returns all the data in a very consumable way.
Now what about the stored procedure:
and ExecuteReader:
Going back to the Binding Properties that I chose not to set, what is truly interesting about this is in neither case was there a need to specify “FOR XML” when constructing the SELECT query, either in the ExecuteReader or stored procedure. If you think about the old interactions with SQL it used to be painful to have to add FOR XML to your stored procedures, generate the schemas and then remove the FOR XML. Well, no more! However, it is not quite as simple as all that because the generated schema for the response has two XML <any> elements (one to contain the schema and the other to contain the result) so it is not just a simple case of mapping from one message format to another, but it is hardly onerous to deal with once it is understood what is returned.
I’m going to have a longer play with this, and I have noticed that when you do non-SELECT type operations it uses the standard diffgram type approach used before which is pretty familiar and friendly to use.
All in all though I have to say I am impressed with this new adapter, and I’ve only been playing with it for a little while.
Posted on Wednesday, June 3, 2009 6:32 PM BizTalk , WCF , SOA | Back to top


Comments on this post: BizTalk Server 2009 and WCF SQL Adapter

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Hi,

Very good article.
If you want the response of the stored procedure as strongly typed, and not just an array of DataSet, you can generate schemas for the procedures under the Strongly-Typed Procedures node.
Left by bizlearner on Jun 08, 2009 5:17 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Hi Bizlearner,

Many thanks for the comment, exactly what I found out shortly after posting (typically!), and good info to know.

I love this new adapter, makes interacting with SQL Server so much more enjoyable!
Left by Martin Abbott on Jun 08, 2009 5:46 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Very good source of information. I was looking for WCF SQL Adapter details.
Left by Pankaj on Jun 13, 2009 5:17 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Hi, I've some question:

What if I want to start my orchestration with no message on Input (I want to use my orchestration as a batch, with ports scheduled - which will start the process) and will get data from SQL by Wcf-Sql Adapter? I've built it, but nothing happens on port enabled. Is there something wrong?

Thanx
Left by Yonathan Masovich on Jul 09, 2009 6:43 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Hi Yonathan,

I think what you're after is not using the adapter as a client but rather as a service.

Richard Seroter wrote a great article showing some more complex examples using the adapter, so check that out:

http://www.packtpub.com/article/soa-capabilities-in-bizTalk-wcf

There is a section on polling data and a section on using SQL Notification.

I think that should see you through.
Left by Martin Abbott on Jul 09, 2009 9:02 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Hi Yonathan,

Further to that, I'd certainly recommend Richard Seroter's book from which the article is taken, I've written a review of it here.
Left by Martin Abbott on Jul 09, 2009 9:04 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Your posts helped me very much!

Thank you!
Left by Yonathan Masovich on Jul 30, 2009 4:55 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Hi again,

In Sql adapter, there was an option to define more than one receive location on the URI, it was something like SQLServerName/Something/1
SQLServerName/Something/2 etc. It was useful when I want to get data to different orchestrations from one table.
Is there a way to define such a thing (and how?) in Wcf-Sql adapter? I didn't find anything useful in the net.
Thnx b4head :)
Left by Yonathan Masovich on Sep 13, 2009 2:36 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
I've found an answer. Didn't check if it works yet, but it looks like something I need - In URI of binding there's InboundId property.
Left by Yonathan Masovich on Sep 14, 2009 3:06 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
I am trying to use WCF adapter with Biztalk 2009. I have created bindingfile based on the steps given in this article.

But problem is that Physical send port is not subscribing the message. i.e. i am not able to send the request to database to save the data.

Could you please help me in resolving the problem.
Left by Rahul Mahajan on Feb 19, 2010 4:05 AM

# re: BizTalk Server 2009 and WCF SQL Adapter
Requesting Gravatar...
Hi Rahul,

Not really sure what the problem may be, but referring to the article, I am struck by this comment...

"The binding file contains a bunch of mappings from WCF Actions to port Operations, so much in the same was as working with normal WCF or web services, you need to make sure that the configured port operation in an orchestration matches the relevant operation in the binding file"

Have you ensured that you have set the port operation to be the same as that in the binding file?

If you have, could you provide a little more information?

Thanks,

Martin
Left by Martin Abbott on Feb 23, 2010 1:02 PM

Your comment:
 (will show your gravatar)


Copyright © martinabbott | Powered by: GeeksWithBlogs.net