Hi Folks,

I wanted to see if I could use the WCF-Custom Adapter to match my scenario below:

Scenario:

I want to call a stored procedure with multiple arguments without using orchestrations and one of the parameters of the stored procedure is the actual BizTalk Message as XML SQL data type.

Consider a scenario where you do not want the overhead of generating metadata, creating an orchestration, deploying the orchestration, and executing the operation. For such a basic scenario, where the stored procedure takes a single parameter, you do not need to create an orchestration. Rather, you can configure a WCF-Custom or WCF-SQL send port to directly invoke the stored procedure. This topic demonstrates how to perform these tasks using the BizTalk Server Administration console.

Prerequisites:

WCF LOB Adapter SDK SP2

and the following

Microsoft BizTalk Adapter for SQL Server:

Ok, so either way, lets continue!

I managed to do it with this template in the WCF-Custom properties, however I found some limitations.

Here is a template that works, notice I HARD coded the Stage, Status and Id, call then constants for now.

<SetWorkflowRecord xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
<Message><bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/></Message>
<Stage>TWO</Stage>
<Status>SUC</Status>
<Id>968819</Id>
</SetWorkflowRecord>

How the above template will probably not be cool, who hard codes values into send ports….

However this template is pretty cool, we can actually use WCF to call a proc directly with multiple parameters, as long as the other parameters besides the Message is constants. Note, you can generate a default template using the Consume Adapter Service BizTalk Project Add-In, and then generate an instance.

I while back I developed a custom SQL adapter that can call a SQL procedure directly and match the scenario above, you know what I love about the custom adapter, is that it uses a STREAM and not a String to send the XML data to the stored procedure. So for large files, I can imagine a Out Of Memory Exception. Also the custom SQL adapter can parse Constants and promoted properties as parameters to a stored procedure, and that is very powerful stuff. We need this power in WCF-Custom or WCF-SQL adapters! I mean it. This allows us to avoid orchestrations and updategrams etc.

However, here is my wish list, and I hope a Microsoft BizTalk Developer finds this wish list:

I wish for a WCF-Custom adapter, where you can reference promoted properties in the WCF template properties. This would match what I can do with the custom SQL adapter we built. Where I can send XML, and promoted property values from BizTalk directly to a WCF adapter, without the need for developing complex WCF Chanel Model or WCF Service Model tools. I also wish that the Message is streams to the SQL server procedure.

So, perhaps in the near future, this type of template will work in WCF-Custom. Where the template has access to the BizTalk Property Schema. Notice below we no longer use encoding type of string to the message, will be faster, of course this sort of template will never work now, but nice to have one day? It could be an add in for the Consume Adapter Service BizTalk Project Add-In where you can drag promoted property fields into the template. This should be possible, since the following assembly has direct access to the message and all properties:

System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

<SetWorkflowRecord xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
<Message><bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007"/></Message>
<Stage><StageParam xmlns="http://MMIT.Workflow.Common.Schemas.WorkflowRecordPropertySchema" encoding="string"/></Stage>
<Status><StatusParam xmlns="http://MMIT.Workflow.Common.Schemas.WorkflowRecordPropertySchema" encoding="string"/></Status>
<Id><IdParam xmlns="http://MMIT.Workflow.Common.Schemas.WorkflowRecordPropertySchema" encoding="string"/></Id>
</SetWorkflowRecord>

StageParam, StatusParam and IdParam are promoted properties of the message going into the adapter, and this should be possible, the WCF architecture can support this, since they already have the fixed property schema for:

bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007

I am excited about WCF, but it can be much more powerful with BizTalk if it can hook into Promoted Properties and not encode the message as a string, but default it to a stream.

This will allow for some awesome low latency BizTalk Patterns…. I understand that we need to keep promoted properties under 10 or so, which is the case, but sometime you want to access promoted properties for database level operations, thus avoiding XPath on them again in the proc to save time if you already have it promoted.

I have migrated all send ports to WCF, so my solution is still simple, all I do is change the stored procedure to accept one parameter and then in the SQL proc, we XPATH the stage, status and ID values, which should be fine, however it will be slightly slower as in the stored procedure I run a SQL Message.Query to get the stage, status and ID :), I will do some performance test against both ways, WCF and the Custom Adapter. However if the WCF is slightly slower, I will probabyl stick with so that in the long run we are WCF compliant!

One thing I like very much is the Transaction Isolation Level and the fact we can use Read Committed 🙂 Also, I prefer Custom as it is more flexible than the WCF-SQL 🙂

Below are screenshots for setting up the adapter:

Click Configure

Then add your SQL URI and stored procedure name:

Then change the binding to SQLBinding, to do this, you must install the following:

WCF LOB Adapter SDK SP2
Microsoft BizTalk Adapter for SQL Server:

, eitheway, you should now be able to choose the SQLBinding.

Then you can create your template on the messages tab, NOTE I change the transaction isolation level here, as I want READPAST hints.

That is all there is to it. If you having trouble making the template, just use Visual Studio to do it for you, then copy past the instance:

Just add a generated item to a existring BizTalk project and choose Consume WCF Service:

Since you NOT using orchestrationsm, you do not need to save the generated items 🙂 🙂

Once you got an instance, that is the template to copy paste into the Template section for the messages.

Hope this helps thos of you who want high performance writes to SQL Server.

 

This is the best option to use, a long while back I had a custom SQL adapter developed for this as posted in the blogs.

Cheers

• Uncategorized