Removing XML namespaces – revisit

by Richard | Jun 26, 2008 | BizTalk Community Blogs via Syndication

I have a old post on removing XML namespace from outgoing messages using XSLT in a map on the send port. Removing XML namespace is usually a late requirement that shows up during integration tests with for example legacy systems that has problems reading XML and only finds XML namespaces messy and confusing and wants it removed.

The post recently got commented by Jeff Lynch (one of the codebetter.com bloggers) asking me why I just didn’t create a schema without any XML namespace in it to represent the outgoing schema (see figure below) and then map to that in the send port.

Say for example that we have incoming messages like the one below with namespaces.

<div><span style="color: #0000FF; "><</span><span style="color: #800000; ">ns0:BlahRoot </span><span style="color: #FF0000; ">xmlns:ns0</span><span style="color: #0000FF; ">="http://Sample.BlahIncomingSchema"</span><span style="color: #0000FF; ">></span><span style="color: #000000; ">
  </span><span style="color: #0000FF; "><</span><span style="color: #800000; ">BlahNode</span><span style="color: #0000FF; ">></span><span style="color: #000000; ">Test Value</span><span style="color: #0000FF; "></</span><span style="color: #800000; ">BlahNode</span><span style="color: #0000FF; ">></span><span style="color: #000000; ">
</span><span style="color: #0000FF; "></</span><span style="color: #800000; ">ns0:BlahRoot</span><span style="color: #0000FF; ">></span></div>

We’ve then defined a schema without namespace and map to that and get the following result.

<div><span style="color: #0000FF; "><</span><span style="color: #800000; ">BlahRoot</span><span style="color: #0000FF; ">></span><span style="color: #000000; ">
  </span><span style="color: #0000FF; "><</span><span style="color: #800000; ">BlahNode</span><span style="color: #0000FF; ">></span><span style="color: #000000; ">Test Value</span><span style="color: #0000FF; "></</span><span style="color: #800000; ">BlahNode</span><span style="color: #0000FF; ">></span><span style="color: #000000; ">
</span><span style="color: #0000FF; "></</span><span style="color: #800000; ">BlahRoot</span><span style="color: #0000FF; ">></span></div>

This method is of course much cleaner then any previous and it’s also more conceptually correct as the schema actually represents the contact between BizTalk and the receiving system (the contract is a message without namespace in it, not one with that we then remove).

The only problem with this kind of approach is that as BizTalk recognized the message type using a combination between root node and XML namespace we can’t have another schema with the BlahRoot root node without a defined XML namespace. Even if those two schemas would look totally different in structure and be two different message types BizTalk would be able to see the difference (to BizTalk they both be #BlahRoot message types).

Thanks Jeff for pointing this out to me.

Speaking at KNUG – Karlstad .NET User Group

by Richard | May 9, 2008 | BizTalk Community Blogs via Syndication

Yesterday I presented the MasterData Management using BizTalk 2006 R2 talk (I’ll soon have a post out with the presentation in English) I recently held at Developer Summit at the local .NET user group in Karlstad (KNUG).

KNUG is a new .NET user group that I actually helped start a couple of months ago. This meeting was the second meeting for the group. The meeting was attended by about 20 persons and we had two presentations on the agenda. Besides my own Thomas Heder showed the group some LINQ and how he and his colleagues uses LINQPad to develop and test there queries.

We also discussed future subjects, possible speakers and moving information on the group over to a Community Server driven site.

Does anyone have any experience on Community Server and how the feature set matches those need for running a user group (managing users, blogs, email lists, calendar etc)?

Writing BizTalk context properties to a message from a WCF service using behaviors

by Richard | Apr 24, 2008 | BizTalk Community Blogs via Syndication

The new WCF adapter in BizTalk 2006 R2 offers a lot of new possibilities. One of those is to write data to the BizTalk Message context properties directly from an exposed WCF Service. A practical use of this technic could be to write the username from the Windows credentials of the calling client into the context of the BizTalk message. This could be useful as this information is encrypted in messages that are received via the WCF adapter and isn’t possible to read when inside BizTalk. I’ll try and demonstrate the technique in this post.

If you have used the SOAP adapter before you might know that all you had to do was to turn on Windows based security for the exposed SOAP service and the username was automatically promoted to the context of the incoming BizTalk message. That username could then be used for routing, tracking which user called the service or using the value in plain text when communicating further to other connected systems. However using the WCF adapter this is not true anymore – when using the new WCF Message Security model the username and password is encrypted in the message and once the message is received by BizTalk it’s to late to read it. Basically we have to read the username in the actual service and write it into our own context property (that doesn’t get encrypted).

One way of achieving this is to read the username in the service and then to add it to the WCF Message Headers. All WCF message headers will by default be written to a the BizTalk Message context property called InboundHeaders (in the http://schemas.microsoft.com/BizTalk/2006/1/Adapters/WCF-properties namespace). First we’ll create an EndpointBehavior that will use a MessageInspector to add the username to the message header. Finally we create BehaviorExtensionElement so we can use a WCF Custom Binding in BizTalk and configure it to add our new behavior.

Creating the new EndpointBehavior

To create the configurable behavior we’ll need the three classes we mentioned above.

1. A class that implements the IDispatchMessageInspector interface to handle to reading and writing to the actual message.

2. A class that implements the IEndpointBehavior interface to define what kind of endpoint we’re creating and what it should do.

3. A class that implements the BehaviorExtensionElement abstract class to create the behavior and make it configurable.

   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.ServiceModel;
   using System.ServiceModel.Channels;
   using System.ServiceModel.Dispatcher;
   using System.ServiceModel.Description;
   using System.ServiceModel.Configuration;

   namespace CustomWCFProperties.Behavior
   {
   ///

   
   /// PromoteUserNameMessageInspector implements IDispatchMessageInspector and adds the name from the WindowsIdentity to a WCF header called WindowsUserName in the http://CustomWCFProperties.Schema namespace. BeforeSendReply only returns as we’re not interested in handling the response.
   ///
   public class PromoteUserNameMessageInspector : IDispatchMessageInspector
   {
   #region IDispatchMessageInspector Members

       </span><span style="color: #0000ff">public</span><span style="color: #000000"> </span><span style="color: #0000ff">object</span><span style="color: #000000"> AfterReceiveRequest(</span><span style="color: #0000ff">ref</span><span style="color: #000000"> System.ServiceModel.Channels.Message request, System.ServiceModel.IClientChannel channel, System.ServiceModel.InstanceContext instanceContext)
       {
           </span><span style="color: #0000ff">string</span><span style="color: #000000"> windowsUserName </span><span style="color: #000000">=</span><span style="color: #000000"> ServiceSecurityContext.Current.WindowsIdentity.Name;
           request.Headers.Add(MessageHeader.CreateHeader(</span><span style="color: #000000">"</span><span style="color: #000000">WindowsUserName</span><span style="color: #000000">"</span><span style="color: #000000">, </span><span style="color: #000000">"</span><span style="color: #000000">http://CustomWCFProperties.Schema</span><span style="color: #000000">"</span><span style="color: #000000">, windowsUserName));
           </span><span style="color: #0000ff">return</span><span style="color: #000000"> </span><span style="color: #0000ff">null</span><span style="color: #000000">;
       }
   
       </span><span style="color: #0000ff">public</span><span style="color: #000000"> </span><span style="color: #0000ff">void</span><span style="color: #000000"> BeforeSendReply(</span><span style="color: #0000ff">ref</span><span style="color: #000000"> Message reply, </span><span style="color: #0000ff">object</span><span style="color: #000000"> correlationState)
       {
           </span><span style="color: #0000ff">return</span><span style="color: #000000">;
       }
   
       </span><span style="color: #0000ff">#endregion</span><span style="color: #000000">
   }
   
   </span><span style="color: #808080">///</span><span style="color: #008000"> </span><span style="color: #808080"><summary></span><span style="color: #008000">
   </span><span style="color: #808080">///</span><span style="color: #008000"> PromoteUserNameBehavior implements IEndpointBehavior and adds a message inspector to the dispatch behavior. Doesn't use any binding parameters, doesn't validate any configuration etc and can't be used in a client (only in a service).
   </span><span style="color: #808080">///</span><span style="color: #008000"> </span><span style="color: #808080"></summary></span><span style="color: #808080">
   

   public class PromoteUserNameBehavior : IEndpointBehavior
   {
   #region IEndpointBehavior Members

       </span><span style="color: #0000ff">public</span><span style="color: #000000"> </span><span style="color: #0000ff">void</span><span style="color: #000000"> AddBindingParameters(ServiceEndpoint endpoint, System.ServiceModel.Channels.BindingParameterCollection bindingParameters)
       {
           </span><span style="color: #0000ff">return</span><span style="color: #000000">;
       }
   
       </span><span style="color: #0000ff">public</span><span style="color: #000000"> </span><span style="color: #0000ff">void</span><span style="color: #000000"> ApplyClientBehavior(ServiceEndpoint endpoint, System.ServiceModel.Dispatcher.ClientRuntime clientRuntime)
       {
           </span><span style="color: #0000ff">throw</span><span style="color: #000000"> </span><span style="color: #0000ff">new</span><span style="color: #000000"> Exception(</span><span style="color: #000000">"</span><span style="color: #000000">The method or operation is not implemented.</span><span style="color: #000000">"</span><span style="color: #000000">);
       }
   
       </span><span style="color: #0000ff">public</span><span style="color: #000000"> </span><span style="color: #0000ff">void</span><span style="color: #000000"> ApplyDispatchBehavior(ServiceEndpoint endpoint, System.ServiceModel.Dispatcher.EndpointDispatcher endpointDispatcher)
       {
           endpointDispatcher.DispatchRuntime.MessageInspectors.Add(</span><span style="color: #0000ff">new</span><span style="color: #000000"> PromoteUserNameMessageInspector());
       }
   
       </span><span style="color: #0000ff">public</span><span style="color: #000000"> </span><span style="color: #0000ff">void</span><span style="color: #000000"> Validate(ServiceEndpoint endpoint)
       {
           </span><span style="color: #0000ff">return</span><span style="color: #000000">;
       }
   
       </span><span style="color: #0000ff">#endregion</span><span style="color: #000000">
   }
   
   </span><span style="color: #808080">///</span><span style="color: #008000"> </span><span style="color: #808080"><summary></span><span style="color: #008000">
   </span><span style="color: #808080">///</span><span style="color: #008000"> Defines the behavior.
   </span><span style="color: #808080">///</span><span style="color: #008000"> </span><span style="color: #808080"></summary></span><span style="color: #808080">
   

   public class PromoteUserNameBehaviorElement : BehaviorExtensionElement
   {
   protected override object CreateBehavior()
   {
   return new PromoteUserNameBehavior();
   }

       </span><span style="color: #0000ff">public</span><span style="color: #000000"> </span><span style="color: #0000ff">override</span><span style="color: #000000"> Type BehaviorType
       {
           </span><span style="color: #0000ff">get</span><span style="color: #000000"> { </span><span style="color: #0000ff">return</span><span style="color: #000000"> </span><span style="color: #0000ff">typeof</span><span style="color: #000000">(PromoteUserNameBehavior); }
       }
   }
   

   }

Finally we have to sign the assembly using a strong key and add it to the GAC.

Configure the machine.config

As we need BizTalk and the WCF adapter to pick up the need behavior and make it possible to configure our receive port we need to to add the behavior element to the machine.config.aspx). The easiest way of doing this is to use the new WCF Service Configuration Editor tool and point to the machine.config file.

After the dll been added and the machine.config file has been saved the the line below should have been added to the element (that is if you use the same strong name key as in the sample project I’ve linked here).

<div><span style="color: #0000ff"><</span><span style="color: #800000">add </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="addCustomWCFProperties"</span><span style="color: #ff0000"> type</span><span style="color: #0000ff">="CustomWCFProperties.Behavior.PromoteUserNameBehaviorElement, AddCustomWCFPropertiesBehavior, Version=1.0.0.0, Culture=neutral, PublicKeyToken=705e34637fdffc54"</span><span style="color: #ff0000"> </span><span style="color: #0000ff">/></span></div>

Create the BizTalk Receive Port and Receive Location

Next thing to do is to start the BizTalk WCF Service Publishing Wizard. Choose to publish a service endpoint and make sure you enable metadata and create a receive location. In this example we’ll next choose to “Publish schemas as WCF service” and then define our service by naming service operations and so on.

When you then browse to the URL you choose to publish your service to you’ll see the nice example of how to instance the service you just defined.

If we then send a request message to service (you’ll find a client as part of the attached solution here) and inspect the message and its context properties in BizTalk we’ll see that the username of the calling client is nowhere to be found.

Configure a WCF-Custom binding and adding a Endpoint Behavior

To add the username to the message context we’ll need to add our newly created behavior to our service. We’ll do this by switch the service over to use a WCF-Custom binding to enable configuration. We then need to add the URL in the address field, define the binding type to a wsHttpBinding and to add our addCustomeWCFProperties behavior to the list of endpoint behaviors.

>
> [](../assets/2008/04/windowslivewriterwritingbiztalkcontextpropertiestoamessag-72f4note-2.gif) NOTE: there is a limitation in the BizTalk WCF implementation in that you can’t create the WCF-Custom receive location that uses a HTTP in-process based binding (like the wsHttpBinding used in a WCF-Custom endpoint is) first and then use the WCF Publishing Wizard to only publish a metadata endpoint.
>
>

>
> [Richard Seroter writes about it here](http://www.topxml.com/code/cod-72_10213_biztalk-and-wcf-part-v-publishing-operations-patterns.aspx) and I found the same thing to be true.
>
>

>
> _”This error doesn’t have to do with mixing MEX endpoints and “regular” endpoints in the same IIS web site, but rather, creating MEX endpoints for in-process HTTP bindings seems to trigger this. **Note that an IIS-hosted MEX endpoint CAN be created for IIS-hosted HTTP endpoints, but not for in-process hosted HTTP endpoints.”**_
>
>

>
> If you however choose a different binding that Http or (as in this case) publishes the metadata first and then switches over to a custom binding you’re ok.
>
>

If we then post another message to the service and inspect the message we’ll see that the behavior actually added a header and that it’s part of our BizTalk context properties. The adapter is also smart enough to know that this header isn’t part of the original headers and therefore stores in it’s own field within the context properties (you’ll find as part of the InboundHeaders block as well).

One problem remains – the actual value of the user is nested inside a XML node and the property isn’t promoted.

Extract and promote the value

To extract and promote the value we use an old fashion pipeline component using the following code in the execute method (the complete project is part of the downloadable sample project).

<div><span style="color: #000000">        </span><span style="color: #0000ff">public</span><span style="color: #000000"> IBaseMessage Execute(IPipelineContext pc, IBaseMessage inmsg)
        {
            StringReader reader </span><span style="color: #000000">=</span><span style="color: #000000"> </span><span style="color: #0000ff">new</span><span style="color: #000000"> StringReader(inmsg.Context.Read(</span><span style="color: #000000">"</span><span style="color: #000000">WindowsUserName</span><span style="color: #000000">"</span><span style="color: #000000">, </span><span style="color: #000000">"</span><span style="color: #000000">http://CustomWCFProperties.Schema</span><span style="color: #000000">"</span><span style="color: #000000">).ToString());

            </span><span style="color: #0000ff">if</span><span style="color: #000000"> (reader </span><span style="color: #000000">!=</span><span style="color: #000000"> </span><span style="color: #0000ff">null</span><span style="color: #000000">)
            {
                XPathDocument document </span><span style="color: #000000">=</span><span style="color: #000000"> </span><span style="color: #0000ff">new</span><span style="color: #000000"> XPathDocument(reader);
                XPathNavigator navigator </span><span style="color: #000000">=</span><span style="color: #000000"> document.CreateNavigator();
                </span><span style="color: #0000ff">string</span><span style="color: #000000"> value </span><span style="color: #000000">=</span><span style="color: #000000"> navigator.SelectSingleNode(</span><span style="color: #000000">"</span><span style="color: #000000">/</span><span style="color: #000000">"</span><span style="color: #000000">).Value;
                inmsg.Context.Promote(</span><span style="color: #000000">"</span><span style="color: #000000">WindowsUserName</span><span style="color: #000000">"</span><span style="color: #000000">, </span><span style="color: #000000">"</span><span style="color: #000000">http://CustomWCFProperties.Schema</span><span style="color: #000000">"</span><span style="color: #000000">, value);
            }

            </span><span style="color: #0000ff">return</span><span style="color: #000000"> inmsg;
        }</span></div>

All the component does is reading the XML node the value exists inside and then it reads the actual value. Finally it writes the value back and promotes it. To be able to promote the value we also have to have a Property Schema deployed with a corresponding property name and namespace (WindowsUser and http://CustomeWCFProperties.Schema in this case).

The end results looks something like this.

The username is extracted and promoted and available for example for tracking or to for example use in a routing scenario.

This technique could of course be used for all kinds of scenarios where you like to add information to the context properties and could potentially replace a lot of the classic scenarios for custom pipelines.

All kind of comments are of course more than welcome!

Download the sample solution here.

Random thoughts on SQL Server Adapter and T-SQL Stored Procedures for BizTalk 2006

by Richard | Feb 18, 2008 | BizTalk Community Blogs via Syndication

Writing Stored Procedures is an art of its own. As all of you know it’s very different from writing ordinary code and presents its own kind of problems and issues when it comes to performance, builds, version control, testing etc. This post will try and highlight a few points that I find important when it comes to Stored Procedures, and the especially in a BizTalk and integration related scenario.

Consider the simplified procedure below, I’ll use is an example for much of the discussion in the rest of the post. Basically the procedure inserts or updates (based on if the optional EmployeeID exists from before or not) employee information to the Employee table of a database named “TestDB”.

<span class="kwrd"></span>








<div><span style="color: #0000ff">Use</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TestDB</span><span style="color: #ff0000">]</span><span style="color: #000000">
</span><span style="color: #0000ff">Go</span><span style="color: #000000"> 
</span><span style="color: #0000ff">If</span><span style="color: #000000"> </span><span style="color: #808080">Exists</span><span style="color: #000000">(</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #808080">*</span><span style="color: #000000"> </span><span style="color: #0000ff">From</span><span style="color: #000000"> dbo.sysobjects </span><span style="color: #0000ff">Where</span><span style="color: #000000"> id </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff00ff">object_id</span><span style="color: #000000">(N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">AddEmployee</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #808080">And</span><span style="color: #000000"> </span><span style="color: #ff00ff">OBJECTPROPERTY</span><span style="color: #000000">(id, N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">IsProcedure</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">) </span><span style="color: #0000ff">Drop</span><span style="color: #000000"> </span><span style="color: #0000ff">Procedure</span><span style="color: #000000"> AddEmployeeGo 
</span><span style="color: #0000ff">Create</span><span style="color: #000000"> </span><span style="color: #0000ff">Procedure</span><span style="color: #000000"> AddEmployee    
    </span><span style="color: #008000">@EmployeeId</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">Int</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">,    
    </span><span style="color: #008000">@LastName</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">Varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">30</span><span style="color: #000000">),    
    </span><span style="color: #008000">@FirstName</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">Varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">30</span><span style="color: #000000">),    
    </span><span style="color: #008000">@MiddleName</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">Varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">30</span><span style="color: #000000">)
</span><span style="color: #0000ff">As</span><span style="color: #000000"> 
</span><span style="color: #0000ff">Begin</span><span style="color: #000000">    
    </span><span style="color: #0000ff">Declare</span><span style="color: #000000"> </span><span style="color: #008000">@currentEmployeeId</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">int</span><span style="color: #000000">    
    </span><span style="color: #0000ff">Declare</span><span style="color: #000000"> </span><span style="color: #008000">@currentAction</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">tinyint</span><span style="color: #000000">    
    </span><span style="color: #008080">--</span><span style="color: #008080"> Check based on the EmployeeId if the employee exists from before    </span><span style="color: #008080">
</span><span style="color: #000000">    </span><span style="color: #0000ff">If</span><span style="color: #000000"> </span><span style="color: #808080">Not</span><span style="color: #000000"> </span><span style="color: #808080">Exists</span><span style="color: #000000">(</span><span style="color: #0000ff">Select</span><span style="color: #000000"> EmployeeId </span><span style="color: #0000ff">From</span><span style="color: #000000"> Employee </span><span style="color: #0000ff">Where</span><span style="color: #000000"> EmployeeId </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@EmployeeId</span><span style="color: #000000">)    
        </span><span style="color: #0000ff">Begin</span><span style="color: #000000">        
            </span><span style="color: #008080">--</span><span style="color: #008080"> Inserts new employee        </span><span style="color: #008080">
</span><span style="color: #000000">            </span><span style="color: #0000ff">Insert</span><span style="color: #000000"> </span><span style="color: #0000ff">Into</span><span style="color: #000000"> Employee (LastName,FirstName,MiddleName)         
            </span><span style="color: #0000ff">Values</span><span style="color: #000000"> (</span><span style="color: #008000">@LastName</span><span style="color: #000000">, </span><span style="color: #008000">@FirstName</span><span style="color: #000000">, </span><span style="color: #008000">@MiddleName</span><span style="color: #000000">)        
            </span><span style="color: #0000ff">Set</span><span style="color: #000000"> </span><span style="color: #008000">@currentEmployeeId</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff00ff">Scope_Identity</span><span style="color: #000000">()        
            </span><span style="color: #0000ff">Set</span><span style="color: #000000"> </span><span style="color: #008000">@currentAction</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">    
        </span><span style="color: #0000ff">End</span><span style="color: #000000">    
    </span><span style="color: #0000ff">Else</span><span style="color: #000000">    
        </span><span style="color: #0000ff">Begin</span><span style="color: #000000">        
            </span><span style="color: #008080">--</span><span style="color: #008080"> Updates employee        </span><span style="color: #008080">
</span><span style="color: #000000">            </span><span style="color: #0000ff">Update</span><span style="color: #000000"> Employee </span><span style="color: #0000ff">Set</span><span style="color: #000000"> LastName </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@LastName</span><span style="color: #000000">,        
            FirstName </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@FirstName</span><span style="color: #000000"> </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> EmployeeId </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@EmployeeId</span><span style="color: #000000">        
            </span><span style="color: #0000ff">Set</span><span style="color: #000000"> </span><span style="color: #008000">@currentEmployeeId</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@EmployeeId</span><span style="color: #000000">        
            </span><span style="color: #0000ff">Set</span><span style="color: #000000"> </span><span style="color: #008000">@currentAction</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">    
        </span><span style="color: #0000ff">End</span><span style="color: #000000">      

    </span><span style="color: #0000ff">Select</span><span style="color: #000000"> </span><span style="color: #008000">@currentEmployeeId</span><span style="color: #000000"> </span><span style="color: #0000ff">As</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">@EmployeeId</span><span style="color: #ff0000">]</span><span style="color: #000000">, </span><span style="color: #008000">@currentAction</span><span style="color: #000000">     
    </span><span style="color: #0000ff">As</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">@ProcedureAction</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #0000ff">For</span><span style="color: #000000"> Xml Path (</span><span style="color: #ff0000">'</span><span style="color: #ff0000">Response</span><span style="color: #ff0000">'</span><span style="color: #000000">)
</span><span style="color: #0000ff">End</span><span style="color: #000000">
</span><span style="color: #0000ff">Go</span></div>

Builds and version control

I always try to have the complete database version controlled. One solution to this that some people advocate is to have a central development database. I have however always found it to be a lot of hassle with overwriting each others changes and so on and always try to make it possible for each developer to build a local version of the database for development. It should also be possible to always check out the latest version and run it to build all the database objects – no matter what you have from before. This is of course a bit tricky when it comes to the actual tables as you might have data and so on (in my opinion it shouldn’t be the case during development but that’s another post discussing test data for the database etc).

When it however comes to Stored Procedure as in this post it’s easy – drop the old one if it exists and create the new one as in the example.

<div><span style="color: #0000ff">If</span><span style="color: #000000"> </span><span style="color: #808080">Exists</span><span style="color: #000000">(</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #808080">*</span><span style="color: #000000"> </span><span style="color: #0000ff">From</span><span style="color: #000000"> dbo.sysobjectsWhere id </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff00ff">object_id</span><span style="color: #000000">(N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">AddEmployee</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #808080">And</span><span style="color: #000000"> </span><span style="color: #ff00ff">OBJECTPROPERTY</span><span style="color: #000000">(id, N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">IsProcedure</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">) </span><span style="color: #0000ff">Drop</span><span style="color: #000000"> </span><span style="color: #0000ff">Procedure</span><span style="color: #000000"> AddEmployee
</span><span style="color: #0000ff">Go</span><span style="color: #000000">
</span><span style="color: #0000ff">Create</span><span style="color: #000000"> </span><span style="color: #0000ff">Procedure</span><span style="color: #000000"> AddEmployee </span><span style="color: #008000">@EmployeeId</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">Int</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">, ...</span></div>

If found this article by Jeff Atwood over at Coding Horror (and the articles by K. Scott Allen it links to) very good on how to think about version control database objects.

Idempotent procedures

In complex integration solution it’s a common problem with multiple messages – sending systems might send the same message twice for a number of reasons and one will end up with a shaky system if that causes an error in the integration. I’ve also seen solutions to this problem where a check is done against the database (say from a BizTalk orchestration) and based on that result a decision is made to call an update or an insert procedure. In my opinion that just unnecessary complexity and database communication. I believe one should try and handle whether to update or insert inside the procedure as long as that’s possible and effective.

I in the AddEmployee Stored Procedure example might receive a EmployeeID as an input parameter and based on a check inside the procedure if the employee exists from before with that id a post is either updated or inserted.

Response message

I generally always want two things in return from a Store Procedure like the one in the AddEmployee example (In a select based procedure that would of course be very different as we like all the data in return).

1. The id that been added or changed

2. What action that’s been performed (insert or update)

What action that was performed is a good idea to return as it’s quite usual to use this in for example BAM tracking or other logging and so on (we might want know how many employees that was added versus updated for example).

Using the Xml Path syntax for generating the Xml response (thanks again Nick for a great post) from the procedure has made it even simpler to actually skip using the Add Generated Item option in BizTalk. All you have think about is to set name of the child node the request root node to the same name as the procedure (in this case AddEmployee). Also remember that the type should be set up as a Multi-Part Message Type in the orchestrations you use it from and that the Receive Pipeline you choose in the Send Port must use a Xml Disassembler component (XmlReceive pipeline will do fine unless you have your custom pipeline for some reason).

Error handling

Some people might find it strange that I don’t have any error handling in my Stored Procedures but in most cases an error in the procedure will cause an exception in the port communication with the database and that’s fine. If I’d like to handle that error I’ll handle that either in the orchestration or using other mechanisms in BizTalk like Failed Message Routing etc. If I can’t handle the error in the procedure I don’t see a reason to catch it.

Security

I’ve seen to many cases where developers actually used the user and password login option available on the database adapter and port. Even if the credentials are safe in the SSO database there is a risk you’ll end up in a mess with login data spread all over the BizTalk administration. One should always use try to use Windows NT Integrated Security!

Even more common than the above is to have a user that part of the administrators group or similar to hit the database – don’t! I usually try an set up one Host to run as in all the communication with a specific database (Could also be a Host for that database and that BizTalk Application so that different applications have their own Host). I’ll then give that specific user the least privileges needed in the specific database.

In the AddEmployee and TestDB example I’d create a new Group in the AD called for example “BizTalk TestDB Users”. I’d then create a User in the AD called “BizTalk TestDB User”. It’s based on this I’d then create a Host in BizTalk referencing the above created Group.

Finally we’ll create a Host Instance and make a Send Handler of it on the SQL Server Adapter.

Now we can set the least required privileges on the “BizTalk TestDB Users” in Sql Server as that the Group the User belongs that’ll hit the database. In this case this means granting the Group to the standard BizTalk required privileges for a Host besides granting it Execute right on the AddEmployee Stored Procedure.

Something I missed? How do you handle your database communication and security in your BizTalk implementations?