HTTP Push from SQL Server — Comet SQL

• Download source code - 45 KB

Contents

• Introduction
• Background
• From Comet Idea to Implementation in ASP.NET
• Long Polling in ASP.NET
• Query Notification
• Results

Introduction

This article presents how to implement the functionality for presenting the so-called real-time data using ASP.NET and SQL Server. This functionality is realized by implementing Comet functionality in ASP.NET and connecting this with Query Notification from SQL Server.

The code presented can also be used when adding a web interface to a legacy system based on SQL Server without changing the legacy system.

The presented code can also be used instead of periodical data refresh in existing web interfaces which use an ASP.NET AJAX Timer. After adaptation of my solution, data in the web browser will be refreshed only just after the data is updated in SQL Server. With no artificial delay. This is why I called it real-time data (not to be confused with Real-time computing).

Background

I wanted to implement a SQL Server initiated message to an Internet browser, but I wanted to do this without Java applets, without Flash, and without a Comet-dedicated server. Only JavaScript with AJAX. I also decided not to use advanced Comet message parsing on the client side. My Comet message contains only an event with simple information: "something was changed". I will later explain why I do it like this.

This article is not about periodical (for example, every 10 seconds) refresh of data from SQL Server or the page in a browser. In this case, the user will see updated information with a maximum delay of 10 seconds. This article is about cases when a compromise between small refresh time and server resources (CPU, RAM, network) can not be achieved. This article is about cases when a periodic refresh delay is too big, and you can not decrease this time because you will break down your server.

From Comet Idea to Implementation in ASP.NET

Comet Idea

Some ideas behind Comet and Comet implementation called Long Polling are described on Wikipedia. I can only add some UML diagram to visualize the idea.

and the same using a loop:

Long-lived HTTP Request

In the previous images, you can see that the Internet browser is notified by the WWW server. Using only pure HTTP without extensions (as those proposed in HTML 5 or similar), it is not simple. Internet browsers were not designed for receiving notifications from a server. We must use some workaround to get this notification.

One of the possibilities is to make some HTTP request and wait for a response. The server will not return a response until some event is raised.

When the client receives a response ("Notification" arrow), it means that the event was raised. This event is our Comet message.

This long-lived HTTP request to the server can be very long, maybe even infinite. Implementing this without introducing some timeout is impractical: think about fighting with all possible timeouts and with a "dead" request on the server because of a broken connection. Let us set some limits on how long the request can be...

Long Polling

To prevent network timeouts and other problems with infinite requests, instead of infinite waiting, we can wait long but not too long. In the case of a request waiting too long, a special response is generated telling the client that there is no notification and that it must request for a notification again.

This pair of "TOO LONG" response and "Request again" is repeated in a loop periodically. It prevents timeouts, and we can call it "Comet Keep-alive". The number of keep-alives, of course, can be 0 when an event comes before the first "TOO LONG" message.

Implementation of Long Polling Comet in ASP.NET

Parsing Comet messages and then using DOM manipulation on the page can not only be hard for JavaScript beginners (like me), it also moves some logic (logic of how the page looks like) from ASP.NET to JavaScript. I recognized it as undesirable because in this case, to change or add some content to the Comet message, you must also change how the Comet JavaScript will parse this message. Because of that reason, I decided to use Comet only for a simple notification that something has changed. After receiving this message, the client will refresh data by making a postback to the current ASPX page (see the "Refresh" and "Page" arrows).

Long Polling in ASP.NET

Why not an ASP.NET AJAX Timer

ASP.NET AJAX Timer can be used for periodic page refresh (or partial page refresh). Instead of this, I decided to use asp:Timer to make the long-lived requests described and shown in the Long Polling paragraph. It worked fine and simple until I wanted to stop watching real-time refreshing and click some button. The postback sent by the button click was blocked. It was queued and executed after the long-lived "tick" from asp:Timer. Aborting the current postback (using the technique described in the "Canceling an Asynchronous Postback" article on MSDN) does not work as I needed: it "has no effect on what's going on in the server". See the "Canceling Server Tasks with ASP.NET AJAX" article on MSDN for details, or take a look at the "Cancelling Async Postback" thread on the ASP.net forum. If we use Session, our postback will be queued and executed after (still running) the cancelled postback!

OK, no more bad solutions. In the next paragraphs, I will show my implementation, describing arrow by arrow from the sequence diagram.

Page request - first arrow

The three first arrows are simple. The user enters the URL or clicks a link, and the page is generated with a GridView filled with data using DataBind(), and this page is returned to the client - nothing unusual.

private void RefreshData()
{
    // . . . 

    int lastRecId;
    List<string> data = MessageDal.GetMessageData(out lastRecId, ...);

    // . . .
    
    Session["LastRecId"] = lastRecId;
    GridView1.DataSource = data;
    GridView1.DataBind();
}

"HTTP Request for notification" arrow

The arrow described as "HTTP Request for Notification" is implemented in JavaScript using AJAX with jQuery. Using jQuery was much simpler for me (JavaScript beginner) than using XMLHttpRequest directly or using the Microsoft AJAX library.

When the page is loaded, the longPolling() function is called.

$(document).ready(function(){
    longPolling(); // Start the initial request 
});

The longPolling() function makes an AJAX request to CometAsyncHandler.ashx, IHttpAsyncHandler, which simulates some kind of a page, that is calculated as a very long time. This time is specified in seconds in the request parameter (i.e., waitTime=60).

function longPolling()
{
$.ajax({
    type: "GET",
    url: "CometAsyncHandler.ashx?waitTime=60", // one minute
    cache: false,
    success: function(data){ 
        isPolling--;
        if(data == "NEWDATAISAVAILABLE")
            RefreshData(); // this function is generated by
                           // using RegisterFunctionToPostBack()
        else if( data == "TOOLONG-DOITAGAIN" )
            setTimeout("longPolling()", 0 );
        else
            addLongPollingError("error",
                "Error on server side. Received data: \"" +
                data + " \"");
    },
    error: function(XMLHttpRequest, textStatus, errorThrown){
        isPolling--;
        addLongPollingError("error",
            textStatus + " (" + errorThrown + ")");
    }
});
}

This request is handled on the server side by the CometAsyncHandler class derived from IHttpAsyncHandler. On the ASP.NET server side, we check if there is new data. If we have new data, then an HTTP response is immediately generated with information: "NEWDATAISAVAILABLE". If there is no new data, then we register to receive Query Notifications (implemented in WaitMessageDataAsync()) and just wait for new data. (How the registration is made will be explained later.)

public class CometAsyncHandler : IHttpAsyncHandler, IReadOnlySessionState
{
    public static List<CometAsyncResult> AllWaitingClients =
        new List<CometAsyncResult>();
    public static object AllWaitingClientsSync = new object();
    private static bool threadForTimeoutsWorking = false;

    // . . .
    // . . .
    // . . .
    
    public IAsyncResult BeginProcessRequest(HttpContext context,
        AsyncCallback cb, object extraData)
    {
        context.Response.ContentType = "text/plain";

        // Get wait time from request
        int waitTime;
        ParseRequest(context.Request, out waitTime);

        // Get last seen record ID from Session
        int lastRecId = (int)context.Session["LastRecId"];

        // . . .

        CometAsyncResult result = new CometAsyncResult(
        context, cb, waitTime, lastRecId);
        lock (AllWaitingClientsSync)
        {
            // register to Query Notification or complete
            // request synchronously in case if there is
            // already new data:
            if (!MessageDal.WaitMessageDataAsync(lastRecId))
            {
                // if not waiting (there is new data)
                // result is to be completed synchronously
                result.IsCompleted = true;
                result.CompletedSynchronously = true;
                result.Result = true; // new data is available
                WriteResponseToClient(result);
                return result;
            }
            else
            {
                // asynchronous (normal case):
                AllWaitingClients.Add(result);

                if (AllWaitingClients.Count == 1)
                    StartClientTimeouter();
            }
        }
        return result;
    }
    
    // . . .
    // . . .
    // . . .
}

"TOO LONG" response

To prevent very long waiting (or infinite waiting), we create a "while" thread that checks all waiting (not responded) clients whether they are waiting too long. If a given client is waiting too long, it is removed from the list and the Callback() associated with the client is called. This callback is the AsyncCallback cb parameter from the BeginProcessRequest() method.

Following is a part of StartClientTimeouter() (modified for presentation and contains only the main idea):

while( AllWaitingClients.Count > 0)
{
    // Call Callback() to all timeouted requests and
    // remove from list.
    lock (AllWaitingClientsSync)
    {
        DateTime now = DateTime.Now;
        AllWaitingClients.RemoveAll(
            delegate(CometAsyncResult asyncResult)
                {
                    if (asyncResult.StartTime.Add(asyncResult.WaitTime) < now)
                    {
                        asyncResult.Result = false; // timeout

                        asyncResult.Callback(asyncResult);
                        return true; // true for remove from list
                    }

                    return false; // not remove (because not timed out)
                });
    }

    // This sleep causes that some timeouted clients are removed with delay
    // Example: if timeout=60s, sleep=1s then timeouted client can be removed after 60,7s.
    // In some cases this can be considered as bug. TODO: Change it to WaitOne() and
    // calculate proper sleep time.
    Thread.Sleep(1000); 
}

After calling Callback() (which is the same as the AsyncCallback cb parameter from the BeginProcessRequest() method), the EndProcessRequest() method is called by th ASP.NET framework. In this method, we have a chance to finish generating the HTTP response.

public void EndProcessRequest(IAsyncResult result)
{
    WriteResponseToClient((CometAsyncResult) result);
}

public void WriteResponseToClient(
    CometAsyncResult cometAsyncResult)
{
    if (cometAsyncResult.Result)
        cometAsyncResult.Context.Response.Write(
            "NEWDATAISAVAILABLE");
    else
        cometAsyncResult.Context.Response.Write(
            "TOOLONG-DOITAGAIN"); // timeout - client must make request again
}

So to each timed out client (time out thread sets its result to false), a "TOOLONG-DOITAGAIN" response is returned. This response is handled by the JavaScript code fragment that made the AJAX/Comet request.

// . . . part of <a href="#longPollingFunction%22">longPolling()</a> function
else if( data == "TOOLONG-DOITAGAIN" )
    setTimeout("longPolling()", 0 );
// <a href="#longPollingFunction%22">. . .</a>

"Request again" arrow

The code above will cause that, after the "too long" message, the current function will be called again. This will cause the client to make the "HTTP Request for notification" once again.

"Notification" arrow

When a Query Notification comes from SQL Server to the ASP.NET server (see bold arrow), the ProcessAllWaitingClients() method is called. This method will iterate through the waiting clients list, setting the Result fields to true and calling the callback (passed earlier as a parameter to the BeginProcessRequest() method).

public static void ProcessAllWaitingClients()
{
    // . . . 
    foreach (CometAsyncResult asyncResult in AllWaitingClients)
    {
        asyncResult.Result = true; // New data available
        asyncResult.Callback(asyncResult);
    }
    AllWaitingClients.Clear();
    // . . .
}

The callback will execute EndProcessRequest() in the same way as in the case of the timed out thread. The difference lies in the fact that Result is set to true in this case. So during HTTP response generation, "NEWDATAISAVAILABLE" is written.

This response is handled by the same JavaScript code fragment that made the AJAX/Comet request.

// . . . part of <a href="#longPollingFunction%22">longPolling()</a> function
if(data == "NEWDATAISAVAILABLE")
    RefreshData(); // this function is generated
                   // by using RegisterFunctionToPostBack()
// <a href="#longPollingFunction%22">. . .</a>

In this case, the longPolling() function is not executed again, so the long polling loop is not stopped. Instead of complicated data, we only have information about new data.

Page Refresh

After receiving the Comet message, we make a partial AJAX refresh by sending a postback to asp:UpdatePanel (UpdatePanel1).

function RefreshData()
{
    __doPostBack('UpdatePanel1','')
}

This function is generated by the RegisterFunctionToPostBack() method.

// I decided to generate JavaScript Refresh() function, but you can
// write it by yourself and include in "LongPolling.js"
//
// Thanks to:
// http://geekswithblogs.net/mnf/articles/102574.aspx
// http://www.xefteri.com/articles/show.cfm?id=18 How postback works in ASP.NET
// and thanks to Dave Ward hint for calling __doPostBack("UpdatePanel1","") ,
public bool RegisterFunctionToPostBack(string sFunctionName, Control ctrl)
{ 
    // call the postback function with the right ID
    // __doPostBack('" + UniqueIDWithDollars(ctrl) + @"','');
    string js = "    function " + sFunctionName + @"()
            {
            " + ctrl.Page.ClientScript.GetPostBackEventReference(ctrl, "") + @"

            }";
    ctrl.Page.ClientScript.RegisterStartupScript(this.GetType(), sFunctionName, js, true);
    return true;
}

So instead of writing a parser for the Comet message in JavaScript and making DOM operations on the page, we just trigger the ASP.NET engine for a partial refresh of the page.

Query Notification

In this part of the article, I will try to show how to trigger Comet events using a Query Notification from SQL Server.

SqlDependency Class

To receive "Query Notifications", we can use the SqlDependency class. In the MSDN documentation of SqlDependency, you can read that you need to associate a SqlDependency object to the SqlCommand object and subscribe to the OnChange event. Then you must guess, that after these steps, you must execute this command. When executing the command, you will get some data. The OnChange event is raised when data from the command changes.

Table

In our case, we are interested in new rows from the table TestTable. Obviously, notifications can be received about any kind of update.

CREATE TABLE [dbo].[TestTable](
    [RecId] [int] IDENTITY(1,1) NOT NULL,
    [Text] [nvarchar](400) NULL,
    [Time] [datetime] NOT NULL CONSTRAINT [DF_TestTable_Time]  DEFAULT (getdate()),

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ( [RecId] ASC )
        WITH (
            PAD_INDEX  = OFF,
            STATISTICS_NORECOMPUTE  = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS  = ON,
            ALLOW_PAGE_LOCKS  = ON)
        ON [PRIMARY]
) ON [PRIMARY]

We can insert data in this table using a simple INSERT.

INSERT INTO TestTable (Text)
VALUES(N'Hello World!')

Step 1 - Check if we need to wait for changes

When the ASP.NET server is asked by the browser for notification about new data, the ASP.NET server checks if there is new data. If so, the browser will receive notification about new data without using a "Query Notification".

In our case, we are watching only insertions, so the query is very simple. We just check MAX(RecId).

// Query for making decision whether data was changed or we must wait.
// In our case we are interested in new records so we select MAX.
private const string queryForCheck = @"
SELECT MAX(RecId)
FROM dbo.TestTable";

// . . .

// 1. First query, to check if we need to wait for changes
using (SqlCommand cmd = new SqlCommand(queryForCheck, conn))
{
    int max = Convert.ToInt32(cmd.ExecuteScalar());
    if (max > lastRecId) // if max > last seen recId
        return false; // No async! New data available right now!
}

Step 2 - Run dependency

When there is no new data, we create and setup a new SqlDependency, associate it with a SqlCommand, and execute the command.

// This query follows rules of creating query for "Query Notification"
// and is filtered by record ID, because (in this case) we expect only
// "INSERT" of new records. We are not observing old records. To be
// compatible with Query Notification we must use schema name ("dbo"

// in our case) before table! For other compatibility issues you must
// search MSDN for "Creating a Query for Notification" or go to
// http://msdn.microsoft.com/en-us/library/ms181122.aspx
// And don't look at this: (old and incomplete list):
// "Special Considerations When Using Query Notifications" at
// http://msdn.microsoft.com/en-us/library/aewzkxxh%28VS.80%29.aspx
private const string queryForNotification = @"
SELECT RecId
FROM dbo.TestTable
WHERE RecID > @recId";

// . . .

// 2. Second query, to run dependency
SqlDataReader reader;
using (SqlCommand qnCmd = new SqlCommand(queryForNotification, conn))
{
    qnCmd.Parameters.AddWithValue("@recId", lastRecId);

    // Setup dependency which will be used to wait for changes
    depend = new SqlDependency(qnCmd);
    depend.OnChange += Depend_OnChangeAsync; // calback 1

    // Execute second query to run dependency (Query Notif.),
    // and to get content of our table data, that will be used
    // by Query Notification as a starting point for observing
    // changes.
    reader = qnCmd.ExecuteReader();
}

Step 3 - Handle rare cases

When executing a command to receive notification, it can be too late. Just before execution, data can be changed (inserted in our case) and we will not receive a notification. To prevent inserting new data between "Step 1" and "Step 2", you can put them in a transaction. This will block insertion of new data. I prefer to avoid blocking table in transaction in this case, because we can simply check if new data was inserted between those two steps.

// 3. Make sure that nothing has changed between point 1. and point 2.
//    (just before firing query notification)
bool newData = reader.HasRows;
reader.Close();
if (newData)
{
    // very rare case - data changed before
    // firing Query Notif. (SqlDependency)
    // TODO: test this case by making some Sleep() between 1. and 2.

    // We have new data and we decide not to receive notification:
    depend.OnChange -= Depend_OnChangeAsync;
    depend = null;

    return false; // No async! New data available right now!
}

Receive notification

When there is no new data, we successfully register to "Query Notification". When somebody or something inserts data to TestTable, then our Depend_OnChangeAsync() will be called. This method will call our ProcessAllWaitingClients() (discussed previously) which will deliver notification to clients.

Results

Precise time measurement is not important here. The most important thing is that time is not related to the polling interval, because there is no polling interval (as in a constant polling solution). If you buy a faster server, you will be faster. But let's make a time measurement just for fun.

Click here or here to enlarge. Press Esc key to stop GIF animation. 

Time measurement is started using a SQL INSERT (DEFAULT (getdate()) with an accuracy of 3.33 milliseconds). After that, a query notification is fired, and ASP.NET is notified. Then, a Comet notification is sent to the browser. The browser makes a refresh call. The server receives the refresh call. On the server side, time is measured again. This time, DateTime.Now (with an accuracy of 15 milliseconds) is used to measure time. The time measured is usually from 15 to 60 milliseconds on localhost (Intel Core 2 Duo 2.13GHz, 3 GB RAM).

Why did I do the measurement on localhost? Because I'm not interested in the network speed, I am only interested in the speed of my code. If you wish to calculate the speed on your network, add some "ping" or "pong" speed to each arrow from the diagram.