I suppose that this blog is probably a bit late to the game being that it is 2018 now, but still thought this may be useful to share with the community.
The Problem - WCF SQL and NULLs
If you've been using BizTalk for awhile, you know that Microsoft has provided an adapter pack for connecting easily to various common databases and applications. Included in this pack among others is the WCF SQL Adapter which allows XML messages to be sent to a port using this adapter which is used to query tables and run stored procedures as needed. So the "issue" at hand here is how NULLs were treated in the past and how they are now treated. Don't get me wrong here, the way NULLs are treated post 2010 is correct: a blank tag that's present is interpreted as a blank value/empty string, and the tag not being present or having the xsi:nil attribute present is interpreted as a NULL value. However in 2010 (at least before the Adapter Pack's CU2), the adapter treated any blank but present tags the same as not present or xsi:nil: these were all pushed into the database as a NULL value.
In any mappings used to process incoming messages and send them on to a WCF SQL port, if a field type is not a string (i.e. INT, UNIQUEIDENTIFIER, etc.), but is blank because it can be a NULL in the stored proc call or table, the mappings in 2010 would convert this to a NULL, no harm no foul. However post-2010, this blank mapping would result in attempting to convert an empty string to an INT or GUID value: which of course causes conversion errors likely never encountered before:
<ns0:FaultEnvelope xmlns:ns0="http://schemas.microsoft.biztalk.practices.esb.com/exceptionhandling">
<Description>An error occurred while processing the message, refer to the details section for more information
Message ID: {28A3F15C-E2B8-41AF-9201-86E2D3EE038C}
Instance ID: {C2FFEBB3-B14D-4E19-BEAF-0C4315D78A02}
Error Description: System.FormatException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
--- End of inner exception stack trace ---
...
</Description>
</ns0:FaultEnvelope>
Depending on your mapping structure, this could cause any upgrade from 2010 to be a huge undertaking, causing rewriting of mappings that hit the WCF SQL Adapter. Of course this is "rewriting it to be correct," yes, but projects and timeframes don't always allow for this. So how can we avoid remapping everything in the interim (or in the permanent if this is the desired behavior in your scenario)?
The Solution Is In the Name!
So this is the WCF SQL Adapter. As you know, WCF provides many extension and customization points in the form of behaviors. So what that means for us is that we can setup a behavior to attach to the WCF SQL Adapter Send Port that massages the messages to make them go out as they did in days of old in 2010. Since this is a Send Port, we're on the "client-side" of WCF here, so we need to create an Endpoint Behavior that injects a Client Message Inspector in the WCF stack.
Inspector Code
What the client inspector needs to do here is relatively simple: take in the XML message coming in, and add the xsi:nil attribute to any tag with an empty value and no internal XML to force it to behave as a NULL when sent to the database. Keep in mind however that WCF Messages are consumable only once and immutable, so we need to make a copy of the incoming message, manipulate it, and return the XML in a new message object. Below is the code to accomplish just that (note that to make things a bit more useful in my circumstances, I have a property that stores field name prefixes that we need to operate on due to naming standards so we only impact INT, GUID, and DATETIME types. Depending on your situation, this may not be necessary: if this property is empty, we operate on all fields).
using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Dispatcher;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Xml;
using System.Xml.Linq;
namespace Phidiax.WCF.SQLAdapterPriorNullBehavior
{
class SQLAdapterPriorNullClientInspector : IClientMessageInspector
{
private List ValidPrefixes { get; set; }
public SQLAdapterPriorNullClientInspector(List<string> validPrefixes)
{
ValidPrefixes = validPrefixes;
}
public object BeforeSendRequest(ref Message request, IClientChannel channel)
{
var timer = System.Diagnostics.Stopwatch.StartNew();
if (request.IsEmpty)
return null;
var newMsg = request.CreateBufferedCopy(int.MaxValue);
var copy = newMsg.CreateMessage();
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms);
copy.WriteMessage(xw);
xw.Flush();
ms.Position = 0;
xw.Close();
xw = null;
var xr = XDocument.Load(ms);
foreach (var n in xr.Root.Descendants().Where(e => e.Value == string.Empty && e.Descendants().Count()==0))
{
bool bDoReplacement = ValidPrefixes.Count == 0;
foreach (string s in ValidPrefixes)
bDoReplacement = bDoReplacement || n.Name.LocalName.StartsWith(s, StringComparison.CurrentCulture);
if (bDoReplacement) n.SetAttributeValue(XName.Get("nil", "http://www.w3.org/2001/XMLSchema-instance"), "true");
}
ms.Position = 0;
xw = XmlWriter.Create(ms);
xr.WriteTo(xw);
xw.Flush();
ms.Position = 0;
XmlDictionaryReader xdr = XmlDictionaryReader.CreateTextReader(ms, new XmlDictionaryReaderQuotas());
request = Message.CreateMessage(xdr, int.MaxValue, copy.Version);
timer.Stop();
System.Diagnostics.Trace.TraceInformation("{0} ms", timer.ElapsedMilliseconds);
return null;
}
public void AfterReceiveReply(ref Message reply, object correlationState)
{
}
}
}
Behavior and Behavior Element Code
Once we have the client inspector code created, we need to make it available easily in the WCF framework to BizTalk (or any client side WCF implementation). This is accomplished by creating an Endpoint Behavior and an Endpoint Behavior Element: the element is the type accessible to the WCF configuration setup which properly inserts the behavior in the stack, which in turn properly inserts the inspector in the stack.
Behavior:
using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Channels;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;
using System.Text;
using System.Threading.Tasks;
namespace Phidiax.WCF.SQLAdapterPriorNullBehavior
{
public class SQLAdapterPriorNullBehavior : System.ServiceModel.Description.IEndpointBehavior
{
private List<string> ValidPrefixes { get; set; }
public SQLAdapterPriorNullBehavior(string validPrefixes="")
{
ValidPrefixes = validPrefixes.Split(',').ToList();
}
public void Validate(ServiceEndpoint endpoint)
{
}
public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters)
{
}
public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher)
{
}
public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
{
clientRuntime.ClientMessageInspectors.Add(new SQLAdapterPriorNullClientInspector(ValidPrefixes));
}
}
}
Behavior Element:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
namespace Phidiax.WCF.SQLAdapterPriorNullBehavior
{
public class SQLAdapterPriorNullBehaviorElement : System.ServiceModel.Configuration.BehaviorExtensionElement
{
public override Type BehaviorType
{
get { return typeof(SQLAdapterPriorNullBehavior); }
}
protected override object CreateBehavior()
{
return new SQLAdapterPriorNullBehavior(ValidPrefixes);
}
[ConfigurationProperty("validPrefixes")]
public string ValidPrefixes
{
get { return (string)base["validPrefixes"]; }
set { base["validPrefixes"] = value; }
}
}
}
Configuration in BizTalk
To make the above behavior available to BizTalk, the code must be built and installed in the Global Assembly Cache, and included in the 32-bit and 64-bit machine.config files which contain all references to globally available WCF Extensions (using the strong name reference). By default, you can find these at C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config and C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config respectively:
<system.serviceModel>
<extensions>
<behaviorExtensions>
...
<add type="Phidiax.WCF.SQLAdapterPriorNullBehavior.SQLAdapterPriorNullBehaviorElement, Phidiax.WCF.SQLAdapterPriorNullBehavior, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38d1e9eb5c5fc3ed" name="SQLAdapterPriorNullBehavior" />
</behaviorExtensions>
</extensions>
</system.serviceModel>
This will make the behavior available in the BizTalk Admin Console when configuring the Send Port. Select the Send Port and click Configure, and navigate to the Behavior tab. Right-click the EndpointBehavior item:
Select Add extension, and scroll to find and select the new item:
Now, configure any public properties of the behavior as made public on the behavior element created above:
Now this WCF SQL Adapter Send Port with the behavior configured will operate just like it did in 2010, eliminating the immediate need to update mapping, and leave one less impediment in the way of upgrading BizTalk!
2009-2017 Phidiax, LLC - All Rights Reserved