Using SQL Service Broker in NET applications

I've been doing some work with SQL Service Broker lately and I thought I could speak about my experience with it. I was interested in the possibility of using NServiceBus or any other popular implementation of a Enterprise Service Bus in .NET (MassTransit, RhinoServiceBus) with an underlying transport different from Msmq and I thought SQL Service Broker could be a good candidate for the replacement.

This implementation exercise shouldn't be a very complicated  task taking into account the great deal of extensibility of a framework like NServiceBus (you just need to implement an interface called ITransport to start using your own transport layer along with the bus), but unfortunately up to this point of time there is no API implementation for Service Broker in .NET framework so things get a bit trickier. Actually, while dealing with the implementation of a custom API for Service Broker I've hit some limitations/design decisions which, in my opinion, make SQL Broker not the best option for the transport layer of an Enterprise Service Bus framework.

Distributed Transactions

I don't want to get into the details about how SQL Service Broker works, but if you have ever used it, you know that messages are retrieved from queues using the TSQL instruction RECEIVE which is a not blocking call but you can combine it with the WAITFOR command for the blocking effect so you avoid polling the queue for dealing with new coming messages. The main problem with WAITFOR instruction is that it sets a transaction savepoint which makes the current transaction not suitable for enlistment; putting it in other words the message handling from the queue cannot be used in the context of a distributed transaction.

So what's the big deal? Actually, I read in msdn forums that SQL broker team wants to discourage the use of distributed transactions because of their performance hit. Besides, you might argue that you can get by without DT keeping all the work related to the message handling in the same database and so there is no need for enlistment. I'm afraid this covers some scenarios but it falls short in others, you have to bear in mind that:

• Not every transactional resource is a SQL Server instance, it doesn't even need to be a SQL engine at all
• Even if all your transactional resources are SQL Server instance because of scalability you might have to keep queues in a different instance from where your mainstream/business data is.
• Regarding security you might have to use different sql user accounts to handle messages and handle application data

For all these scenarios distributed transactions are needed to provide reliable message handling and avoid situations like repeated or lost messages...Can you imagine a bank system where messages that increase/decrease account balance could be handled twice or not handled at all??

There is an alternative provided by SQL Broker that could help to get around this limitation (apart from polling)  called External Activation which in few words is sort of a SQL trigger where custom logic can be placed to be executed upon messages arrival to a queue. Then the trick is to use a second queue to store notifications of new coming messages so the consumer application reads and waits from this event queue out of transaction and when a new message arrives it doesn't need to use WAITFOR to go to the main queue to receive the real message within a transaction. I think that this overcomplicates things as you have to handle two queues for what you could do with one and you have to make sure that both queues stay in sync so for example you don't face situations where there are messages outstanding in the main queue and available consumers to handle them but the messages don't get served because the event queue is empty.

Poison messages handling

An aspect that has to be always taken into account while developing a messaging solutions is the poison messages handling and the replayable message strategy. A valid strategy would be that, on the event of error during message handling, retry up to n times and after it goes over the limit the message can be sent to a dead/error message queue.

But what do we do when there is an error while handling the message, for example an external subsystem needed for processing the message is down for some time? A straight forward answer would be to rollback the current transaction so all inconsistent business work would be undone and the current message would come back to the queue so it could be handled again later when maybe the subsystem would come back (you can keep counters at message level for the retries to identify poison messages).

Well this strategy clashes with the way SQL Service Broker deals with poison messages: A queue is disabled upon 5 consecutive times transaction rollbacks when RECEIVE is used... Once the queue is disabled it cannot be used for processing until an user with administrator right enables it back. This automatic poison message detection it's not message bound, it can't be disabled and the number of consecutive times can't even be changed!

To sum up, on one hand we should always try to commit the transaction to avoid this behavior to be triggered but on the other hand we don't want to leave our business data in a inconsistent state, which leads us to split up the transaction in two: one for the message and the other for the business logic; pretty much we are back to square one with the risk of repeated/lost messages...

There are a couple of ways around that behavior though, one of them is another sort of a trigger where custom logic can be placed to be executed upon queue disabled event. The other one is to follow the strategy explained here, which in few words consists of creating a transaction savepoint (bye bye distributed transaction again) before receive is executed and rollback from that transaction savepoint in case of error, so technically the receive statement is not rollbacked.

Wrapping up

Despite the issues above, I think that this has been a good experience as I have got to know better SQL Broker and I've had the chance to dig a bit into NServiceBus internals and understand a bit more how it works under the hood.

The general idea that I get of SQL Broker is that it can be a good solution in an applications where the message handling business logic is included in the database, but for .NET applications I'd rather go for MSMQ queues which, on top of providing an API implementation in .NET framework, don't suffer from the problems that I've talked about. 

I'm no expert in SQL Broker though, so if I'm missing something, don't hesitate to say it, I'll appreciate it!

Moving this blog to a custom domain

I’ve moved this Blogger blog to the custom domain that you can see in your navigation bar, the former blogspot url will keep redirecting to the new domain though.

You may already know that a new domain means a new PageRank, and as I have already noticed in the traffic figures of this site, a poorer PageRank :) (you can read about this subject here).

So my advice is that if you have a blog engine subdomain url  (blogger, wordpress, etc) and you are thinking of going for your own custom domain, the sooner the better!

ASP.NET Custom Button Controls Source Code

I’ve uploaded the source code of the Confirmation Button control here and the Multiple Validation Group Button control here. Besides, for the former I have fixed a bug regarding ASP.NET validation and I have extended the original javascript file to support confirmation popups on html link clicks, and for the latter I have created a separate library project with the custom controls and their associated javascript embedded.

Any feedback will be welcomed!

ASP.NET Multiple Validation Group Button

In ASP.NET validation controls, the Validation Group allows to break down the set of page validation controls into different units that can be executed independently from each other. Then, a single validation group can be specified at button level so a particular action is glued to a validation controls subset. There are some situations though, where you might be interested in binding a button to multiple validation groups, one of these situations is described in this feedback entry at Microsoft Connect site.

That was back in 2005 when the Validation Group concept was a brand new upcoming feature in .NET 2.0, which by that time was still in beta version. Well, despite the fact that guys from Microsoft seemed to be receptive to the suggestion, it's 2009, ASP.NET is in it 3.5 version and we all know that this feature hasn't quite made it :(. The workaround suggested is to call Page.Validate(group) in server-side but then it means that we'd need to ditch client-side validation which I'm not very happy about.

So let's pick up this suggestion and create a new asp.net button control that includes a coma delimited validation group list property.

public class MultipleValidationGroupButton: Button

{

  /// <summary>

  /// Gets or sets the validation group list

  /// comma delimited.

  /// </summary>

  /// <value>The validation group list.</value>

  /// <remarks>

  /// Empty string represents validation controls that

  /// don't define validation group property

  /// </remarks>

  public string ValidationGroupList

  { 

      get 

      { 

          return ViewState["ValidationGroupList"] as string; 

      }

      set 

      { 

          ViewState["ValidationGroupList"] = value; 

      }

  }

  protected override void OnPreRender(EventArgs e)

  {

      if (!String.IsNullOrEmpty(this.ValidationGroupList))

      {

          this.CausesValidation = false;

          string script = String.Format("ValidateGroups('{0}','{1}');", 

              this.ClientID, this.ValidationGroupList);

          if (Page.IsAsyncPostBack())

          {

              ScriptManager.RegisterStartupScript(Page, typeof(Page), this.ClientID, script, true);

          }

          else

          {

              Page.ClientScript.RegisterStartupScript(typeof(Page), this.ClientID, script, true);

          }

      }

      base.OnPreRender(e);

  }

  /// <summary>

  /// Validates the groups.

  /// </summary>

  public void ValidateGroups()

  {

      if (!String.IsNullOrEmpty(this.ValidationGroupList))

      {

          string[] list = this.ValidationGroupList.Split(',');

          foreach (string item in list)

          {

              Page.Validate(item);

          }

      }

  }

}

The server side implementation is pretty straightforward: we check if the ValidationGroupList propety has been set and if so we disable the regular ASP.NET validation mechanism (CausesValidation=false) and we register the call to the script that will actually do the job (For the client-side registration I use the Page.IsAsyncPostBack partial method already implemented while creating my jQuery ConfirmationButton). The purpose of ValidateGroups method is to remove the need of hardcoding the validation groups in code behind to enforce server side validation, so instead of doing this:

protected void Button_Click(object sender, EventArgs e)

{

   Page.Validate("Group1");

   Page.Validate("Group2");

   Page.Validate("Group3"); 

   if (Page.IsValid)

   { 

       //Button Actions

   }

}

We don’t need to scatter our validation groups in both markup and code-behind because we can do this:

protected void Button_Click(object sender, EventArgs e)

{

   this.Button.ValidateGroups();

   if (Page.IsValid)

   { 

       //Button Actions

   }

}

And to end with, server-side wise, this is how we define our button in ASP.NET markup language.

<cc:MultipleValidationGroupButton ID="btn" runat="server" 
        ValidationGroupList="Group1,Group2" onclick="Button_Click" Text="Click" />

Now it's time to code the javascript function that performs validation on button click. For the implementation, I've used some of the syntactic sugar that jQuery offers but as I'm not using a great deal of things of it you could potentially rewrite this function with plain javascript without much complexity, although I must say that after you start using jQuery (if you haven't yet), you will never want to go back to plain javascript again!

function ValidateGroups(buttonId, validationGroupList) {

    //Bind the logic to the specified button with jQuery

    $("#" + buttonId).click(function(e) {

        var list = validationGroupList.split(',');

        //Loop through the entire set of page validators

        $.each(Page_Validators, function() {

        if ((this.validationGroup && ExistsGroup(list, this.validationGroup)) 

            ||(!this.validationGroup && ExistsGroup(list, ''))) {

                ValidatorValidate(this, this.validationGroup);

                Page_IsValid = Page_IsValid && this.isvalid;

            }

        });

        //Reflect validation in ValidationSummary's if there are any in the page

        $.each(list, function() {

            ValidationSummaryOnSubmit(this);

        });

        //If Page_IsValid is false the execution flow will be interrupted

        return Page_IsValid;

    });

}

function ExistsGroup(list, group) {

    var found = false;

    for (i = 0; i < list.length; i++) {

        if (list[i] == group) {

            found = true;

            break;

        }

    }

    return found;

}

And that’s it, the implementation is ready but before we wrap it up, there are a few things that are worth mentioning: The second clause in the if statement is there to support the scenario where we would potentially want to combine “ungrouped” with “grouped” validation controls and in that situation we’d define our control like this:

<cc:MultipleValidationGroupButton ID="btn" runat="server" 
        ValidationGroupList=",Group1" onclick="Button_Click" Text="Click" />

Finally, you may have noticed that I've used extensively the ASP.NET validation client-side API (ValidatorValidate, Page_Validators, etc), if you want to have more details about this API I recommend you using our friend Reflector to get acquainted to this library (System.Web assembly, Resources folder, WebUIValidation.js resource), that helped me a lot.

Hope this helps!

UPDATE: I've uploaded the code for this post here.
I've created a library project with ASP.NET custom controls for Button, ImageButton and LinkButton; the associated javascript code is embedded in that project (apart from jQuery js file).

ASP.NET Confirmation Button using JQuery

Quite often in business applications the use of confirmation modal popups is required in order to get confirmation from the user of a specific action he’s going to carry out. In web environment the “gorgeous” built-in javascript confirm function is a valid option, but we are living in the XXI century and we can do much better! Particularly with JQuery and the SimpleModal plug-in we can achieve something like this with very little effort:

So, let's get started! First of all we’ll download jQuery js file, SimpleModal js file and css file and add references to them in our master page:

<script src="/Sample/Scripts/jquery-1.2.6.js" type="text/javascript"></script>
<script src="/Sample/Scripts/SimpleModal/jquery.simplemodal.js" type="text/javascript"></script>
<link type='text/css' href='/Sample/Scripts/SimpleModal/css/confirm.css' rel='stylesheet' />

Then we can create the actual html that will be used as modal popup. The html will be reused in any call to the jQuery plugin so we’ll put it in the master page anywhere within the body tag.

<div id='confirm' style='display: none'>

    <div class='header'>

        <asp:Literal ID="ConfirmLiteral" runat="server" 

            Text="<%$ Resources:Global, Confirm %>"/>

    </div>

    <p class='message'></p>

    <div class='buttons'>

        <div class='no modalClose'>

            <asp:Literal ID="NoLiteral" runat="server" 

                Text="<%$ Resources:Global, No %>"/>

            </div>

            <div class='yes'>

                <asp:Literal ID="YesLiteral" runat="server" 

                    Text="<%$ Resources:Global, Yes %>"/>

            </div>

    </div>

</div>

If you don’t need to localize the popup, of course you can get rid of the resources and the Literal controls. Now we can create the javascript function that uses the plugin to show the modal popup.

function confirm(message, callbackYes) {

   $('#confirm').modal({

        close:false, 

        overlayId:'confirmModalOverlay',

        containerId:'confirmModalContainer', 

        onShow: function (dialog) {

            dialog.data.find('.message').append(message);

            // if the user clicks "yes"

            dialog.data.find('.yes').click(function () {

                // call the callback

                if ($.isFunction(callbackYes)) {

                    callbackYes.apply();

                }  

                // close the dialog

                $.modal.close();

            });

We already have the means to show the popup but we still need to trigger it on button clicked.

var Page_IsValid = true;

function SetConfirmation(buttonId, message)

{

    $("#" + buttonId).click(function(e) {

        //We keep the original click event so

        //it can be executed as a callback

        var target = $(e.target);

        //We prevent the form from posting back

        e.preventDefault();

        if(Page_IsValid)

        {

            confirm(message,function() {target.click();});

        }

    });

}

The SimpleModal plugin provides asynchronous and non-blocking functionally (not like the builtin javascript confirm) and that means that we need to prevent the original button’s click event from happening (e.preventDefault();) but at the same time we need to keep it as a callback function to be used in case the user confirms the action to be executed (e.target.click();).

If we you are using ASP.NET validators you might want to show the confirmation pop up only the page is valid (Page_IsValid), there is no point of asking for confirmation if the action cannot be carried out anyways! The variable initialization outside the function guarantees that our function won’t break if there is no ASP.NET validator in the page.

Well, client-side wise we are done, here it goes a usage sample.

SetConfirmation('<%= myButton.ClientID %>','Are you sure of this?');

Let’s focus on server-side now, is there anything we can do? Well, we can create a asp.net control that hides the javascript event subscription to the consumer, so dropping this line in the Page/UserControl markup would be enough to have the confirmation button functionality all in a single place.

<sample:ConfirmButton ID="actionButton" runat="server" Text="<%$ Resources:Global, Action %>"

 OnClick="actionButton_Click" Message="<%$ Resources:Global, ConfirmationMessage %>" />

Here goes the implementation of the ConfirmButton control

public class ConfirmButton : Button

{

    public string Message

    {

        get;

        set;

    }

    protected override void OnPreRender(EventArgs e)

    {

        base.OnPreRender(e);

        if (!String.IsNullOrEmpty(this.Message))

        {

            string script = String.Format("SetConfirmation('{0}','{1}');", this.ClientID, this.Message);

            if (Page.IsAsyncPostBack())

            {

                ScriptManager.RegisterStartupScript(Page, typeof(Page), this.ClientID, script, true);

            }

            else

            {

                Page.ClientScript.RegisterStartupScript(type, this.ClientID, script , true);

            }

        }

    }

}

A few things to point out in the implementation: the javascript call is only rendered if a message text has been assigned to the control, which means that we could potentially use the ConfirmButton as our regular button and provide the confirmation message only when needed.  The second thing is that we could extract a helper method from the logic in the OnPreRender method, so we could use this helper method to set confirmations programmatically upon any sort of button control (Button, ImageButton, LinkButton). Finally, note that the ScriptManager has to be used to register the javascript in the page when the control is rendered as a part of an UpdatePanel partial postback. The following page extension method is defined to identify those scenarios.

public static class PageExtensions

{

    public static bool IsAsyncPostBack(this Page page)

    {

        var result = false;

        var scriptManager = ScriptManager.GetCurrent(page);

        if(scriptManager != null)

        {

            result = scriptManager.IsInAsyncPostBack;

        }

        return result;

    }

}

And that's it, I hope this helps!

UPDATE: You can find the code of this post here
Besides, I've added some code to enable confirmation popup on link click and I've fixed a bug regarding validation.

UPDATE #2:I've updated the javascript code in order to fix an issue that I ran into when using the confirmation buttons with Update Panels (The javascript event handler binder was getting executed on every partial post back which was causing some issues with the popup callback. Check out the implementation for more details, all the changes I did are documented in the js file)