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:
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:
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”
Change Address (URI) with appropriate server- and database name.
Filter added for message type: “http://schemas.microsoft.com/Sql/2008/05/Procedures#ADD_EMP_DETAILSResponse”
Next step is to enable the receive location and start sendports. Copy message to folder, message looks like this:
With response:
In database you will see newly added record.
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.
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:
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:
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”
Change Address (URI) with appropriate server- and database name.
Filter added for message type: “http://schemas.microsoft.com/Sql/2008/05/Procedures#ADD_EMP_DETAILSResponse”
Next step is to enable the receive location and start sendports. Copy message to folder, message looks like this:
With response:
In database you will see newly added record.
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.
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.
ReplyDeletehttp://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
More Useful lInks:
ReplyDeletehttp://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