Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

WCF-SQL Polling and the ESB Portal

Dan Field

With the ESB Toolkit, BizTalk provides an excellent framework for handling exceptions that occur throughout the ESB. There are many built in facilities that are as simple as checking off a box to route failed messages to the portal, and within orchestrations you can easily build ESB Exception messages in catch blocks and route them to the portal as well.

However, these only apply if a message actually makes it to a pipeline or orchestration. For WCF SQL Polling receive locations, it’s possible that no message will ever make it to the pipeline – for example, if the procedure causes an exception to occur (perhaps by a developer intentionally using THROW or RAISERROR), the adapter will write the exception to the event log without providing a message for any pipeline or orchestration processing. Checking “suspend message on failure” doesn’t offer any help, since there is no actual message to suspend. If you have effective monitoring software, such as AIMS, SCOM, or BizTalk 360, you can configure it to alert on such errors/warnings in the event log. If you own the procedure in question, it may be possible to refactor it so that it returns an error message rather than throwing an exception – but this isn’t always possible either (or may involve heavier refactoring of developed work!). However, it’s also possible to route such exceptions to the event log using a custom WCF Endpoint Behavior.

First, the implementation of IErrorHandler, where the meat of the work is done:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.ServiceModel.Channels;
using System.ServiceModel.Dispatcher;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using System.Xml.Linq;

namespace BT.Common.WCFBehaviors
{
    public class SqlExceptionHandler : IErrorHandler
    {
        // we'll take these in as parameters since there's no port to look them up from
        public string ApplicationName;
        public string PortName;
        public bool IsReceive;

        public SqlExceptionHandler(string app, string port, bool isReceive)
        {
            ApplicationName = app;
            PortName = port;
            IsReceive = isReceive;
        }

        // this method gets called after the exception is handled for further processing
        // error is guaranteed to not be null
        public bool HandleError(Exception error)
        {
            try
            {
                // need to create a new transaction to be able to open a connection to the SQL Exceptions Database
                // this block of code could also be replaced with a call to the web service
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    using (SqlConnection conn = new SqlConnection("Server=.; Database=EsbExceptionDb;Trusted_Connection=True"))
                    {
                        conn.Open();
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                            cmd.CommandText = "dbo.usp_insert_fault";

                            cmd.Parameters.Add("@FaultID", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
                            cmd.Parameters.Add("@NativeMessageID", SqlDbType.VarChar).Value = Guid.NewGuid().ToString("d"); // this column expects unique values - we don't have a message to insert, just putting in a GUID
                            cmd.Parameters.Add("@ActivityID", SqlDbType.VarChar).Value = "";
                            cmd.Parameters.Add("@Application", SqlDbType.VarChar).Value = ApplicationName;
                            cmd.Parameters.Add("@Description", SqlDbType.VarChar).Value = "SqlException";
                            cmd.Parameters.Add("@ErrorType", SqlDbType.VarChar).Value = "SqlException";
                            cmd.Parameters.Add("@FailureCategory", SqlDbType.VarChar).Value = "Adapter";
                            cmd.Parameters.Add("@FaultCode", SqlDbType.Int).Value = -1;
                            cmd.Parameters.Add("@FaultDescription", SqlDbType.VarChar).Value = error.ToString().Left(4096); // extension method as in http://stackoverflow.com/questions/7574606/left-function-in-c-sharp
                            cmd.Parameters.Add("@Scope", SqlDbType.VarChar).Value = "Adapter";
                            cmd.Parameters.Add("@ServiceInstanceId", SqlDbType.VarChar).Value = "";
                            cmd.Parameters.Add("@ServiceName", SqlDbType.VarChar).Value = PortName;
                            cmd.Parameters.Add("@MachineName", SqlDbType.VarChar).Value = Environment.MachineName;
                            cmd.Parameters.Add("@ExceptionMessage", SqlDbType.VarChar).Value = error.Message;
                            cmd.Parameters.Add("@ExceptionType", SqlDbType.VarChar).Value = error.GetType().Name;
                            cmd.Parameters.Add("@ExceptionSource", SqlDbType.VarChar).Value = "Adapter";
                            cmd.Parameters.Add("@ExceptionTargetSite", SqlDbType.VarChar).Value = error.TargetSite.ToString();
                            cmd.Parameters.Add("@ExceptionStackTrace", SqlDbType.VarChar).Value = error.StackTrace.Left(4096); // same extension method
                            if (error.InnerException != null)
                            {
                                cmd.Parameters.Add("@InnerExceptionMessage", SqlDbType.VarChar).Value = error.InnerException.ToString().Left(4096); // same extension method
                            }
                            else
                            {
                                cmd.Parameters.Add("@InnerExceptionMessage", SqlDbType.VarChar).Value = "";
                            }

                            cmd.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = DateTime.UtcNow;
                            cmd.Parameters.Add("@FaultSeverity", SqlDbType.Int).Value = 2; // "Error"
                            cmd.Parameters.Add("@FaultGenerator", SqlDbType.VarChar).Value = IsReceive ? "Messaging.ReceiveLocation" : "Messaging.SendPort";

                            cmd.ExecuteNonQuery();
                        }
                    }
                    ts.Complete();
                }
            }
            catch (Exception ex)
            {
                // Log an error about the exception
            }

            return true;
        }

        // this method will not get called here because there adapter doesn't have a message to send to BizTalk.  We'll just ignore it.
        public void ProvideFault(Exception error, System.ServiceModel.Channels.MessageVersion version, ref System.ServiceModel.Channels.Message fault)
        {
            return;
        }
    }
}

The other two classes to implement the behavior are basically boilerplate, with the addition of the parameters to determine whether this is a Receive Location or Send Port, the application name to use, and the Port name to use. Normally, the ESB Toolkit components look these up based on the message context – but here we have no message context to use! Here’s the implementation of IEndpointBehavior:

using System;
using System.ServiceModel.Channels;
using System.ServiceModel.Dispatcher;
using System.ServiceModel.Description;

namespace BT.Common.WCFBehaviors
{
    public class SqlExceptionBehavior : IEndpointBehavior
    {
        string _app;
        string _port;
        bool _isrecv;

        public SqlExceptionBehavior(string app, string port, bool isrecv)
        {
            _app = app;
            _port = port;
            _isrecv = isrecv;
        }
        public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters)
        {
            return;
        }

        public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
        {
            return;
        }

        public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher)
        {
            SqlExceptionHandler handler = new SqlExceptionHandler(_app, _port, _isrecv);
            endpointDispatcher.ChannelDispatcher.ErrorHandlers.Add(handler);
        }

        public void Validate(ServiceEndpoint endpoint)
        {
            return;
        }
    }
}

And lastly, the implementation of BehaviorExtensionElement:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.ServiceModel.Configuration;
using System.Text;
using System.Threading.Tasks;

namespace BT.Common.WCFBehaviors
{
    class SqlExceptionBehaviorElement : BehaviorExtensionElement
    {
        public override Type BehaviorType
        {
            get { return typeof(SqlExceptionBehavior); }
        }

        protected override object CreateBehavior()
        {
            // pass the properties to SqlExceptionBehavior
            return new SqlExceptionBehavior(ApplicationName, PortName, IsReceive);
        }

        ConfigurationPropertyCollection _properties;
        [ConfigurationProperty("ApplicationName")]
        public string ApplicationName
        {
            get { return (string)base["ApplicationName"]; }
            set { base["ApplicationName"] = value; }
        }

        [ConfigurationProperty("PortName")]
        public string PortName
        {
            get { return (string)base["PortName"]; }
            set { base["PortName"] = value; }
        }

        [ConfigurationProperty("IsReceive")]
        public bool IsReceive
        {
            get { return (bool)base["IsReceive"]; }
            set { base["IsReceive"] = value; }
        }

        // set up properties and defaults
        protected override ConfigurationPropertyCollection Properties
        {
            get
            {
                if (this._properties == null)
                {
                    _properties = new ConfigurationPropertyCollection();
                    _properties.Add(new ConfigurationProperty("ApplicationName", typeof(string), "JJill.BT.Common"));
                    _properties.Add(new ConfigurationProperty("PortName", typeof(string), "Unknown"));
                    _properties.Add(new ConfigurationProperty("IsReceive", typeof(bool), true));
                }
                return _properties;
            }
        }

    }
}

Sign the assembly, build it, GAC it. Then, to make the behavior available in BizTalk, you can import the following configuration file (no need to edit machine.config, since this is for in process WCF adapters, not IIS!):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <extensions>
      <behaviorExtensions>
        <add name="sqlExceptionHandler" type="BT.Common.WCFBehaviors.SqlExceptionBehaviorElement, BT.Common.WCFBehaviors, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1f2c9024358fb718"/> <!-- replace with the strong name of your assembly - public key token will vary -->
      </behaviorExtensions>
    </extensions>
  </system.serviceModel>
</configuration>

Save this file and import it using the Admin Console under the correct receive/send host for the adapter (for example, the receive host for WCF-Custom; screen cap taken after importing, so now it shows the behaviorExtensions elements):
import_wcf_bindings

Once you’ve restarted the host instances (and the admin console after GACing the assembly), you can add the behavior to a receive location or send port:

AddExtension

And it can be configured like so:

extensionconfig

And now these exceptions will be routed to the ESB Exception Database (and through that to the ESB Portal).  Other logging or alerting could be done as well, either from the behavior or from the portal alerts.  Enjoy!

Related Articles

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\