WCF-SQL Adapter Stored Procedure

Thursday, August 25, 2011
post by Steefjan-wiggers


In this post I would like to show a different way to insert a record inside a SQL Table using a stored-procedure with WCF-SQL Adapter. Last year I wrote a post on WCF-SQL Table Operations, which was/is very useful for a lot BizTalk pro’s. For BizTalk 2010 you can find Adapter Pack Samples through BizTalk Server Development Center. On that page you can find a sample called SQL Adapter –> Using the adapter with BizTalk Server –> Invoking Stored Procedures. If you download this sample you have to run executable and unzip content in a folder. Then in that folder read the Readme.
Following through the readme file you will come to a point that you have to open the solution with Visual Studio and you will see the orchestration displayed below:
image
This is very basic (not really useful using an orchestration) and through messaging one can accomplished just the same. I did deploy this sample, but without the orchestration (I deleted it). I only needed the schema’s. I named the application StoredProcedure. Schema used to call stored procedure is displayed below:
image
After deploying the sample I imported the binding file accompanied with this sample called BINDINGS.xml. Receive and send ports will be created and only thing I had to do is adding filters to send ports, change database name and credentials.
Filter added for message type: “http://schemas.microsoft.com/Sql/2008/05/Procedures#ADD_EMP_DETAILS”
image
Change Address (URI) with appropriate server- and database name.
image
Filter added for message type: “http://schemas.microsoft.com/Sql/2008/05/Procedures#ADD_EMP_DETAILSResponse”
image
Next step is to enable the receive location and start sendports. Copy message to folder, message looks like this:
image
With response:
image
In database you will see newly added record.
image
As you can see through messaging one can call stored procedure with WCF-SQL Adapter. With maps inside port one can map incoming message and response message to desired format. I will delve into this next time, with some alternative ways.

2 comments:

  1. You can do that through using BizTalk Adapter Pack 2010 and besides reading my post you can find more sample through Microsoft BizTalk Adapter Pack 2010 Samples.

    http://msdn.microsoft.com/en-us/biztalk/gg491395

    I also advice to look at documentation

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=1325

    and a post on how to install adapter pack in BTS-2010

    http://soa-thoughts.blogspot.com/2010/11/installing-biztalk-adapter-pack-2010-on.html

    ReplyDelete
  2. More Useful lInks:

    http://geekswithblogs.net/bosuch/archive/2010/10/19/biztalk---simple-wcf-tutorial-for-using-a-stored-procedure.aspx

    and

    Using For- xml queries
    http://social.technet.microsoft.com/wiki/contents/articles/3480.aspx

    ReplyDelete

Post Your Comment...