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.

Cheers.

Technorati: biztalk server 2010