Click here to Skip to main content
13,293,894 members (55,115 online)
Click here to Skip to main content
Articles » Web Development » Ajax » General » Downloads


168 bookmarked
Posted 5 Mar 2010

HTTP Push from SQL Server — Comet SQL

, 27 Nov 2012
This article provides an example solution for presenting data in "real-time" from Microsoft SQL Server in an HTML browser. The article presents how to implement Comet functionality in ASP.NET and how to connect Comet with Query Notification from SQL Server.
// Copyright (c) 2010 Daniel Wojciechowski 
// E-mail: at daniel.wojciechowski

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;

namespace HttpPushFromMsSql
    /// <summary>
    /// Message data access layer
    /// </summary>
    public class MessageDal
        static string connString = "Server=(local);Initial Catalog=TestDB;Integrated Security=SSPI";

        // Query for selecting data
        private const string query = @"
SELECT RecId, Text, Time
FROM TestTable";

        // 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 = @"
FROM dbo.TestTable";

        // 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
        // compatibile 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" at
        // And don't look at this: (older - not full list):
        // "Special Considerations When Using Query Notifications" at
        private const string queryForNotification = @"
FROM dbo.TestTable
WHERE RecID > @recId

        //private static ManualResetEvent releasePhaseStartEvent = new ManualResetEvent(false); // used to wait for Query Notif.
        //private static ManualResetEvent releasePhaseEndEvent = new ManualResetEvent(false); // used to wait for Query Notif.

        private static SqlDependency depend = null;
        private static object dependSync = new object();

        // For simplicity CometAsyncHandler.ProcessAllWaitingClients() will be called
        // instead if OnQueryNotification
        //public delegate void OnQueryNotificationEventHandler();
        //public static OnQueryNotificationEventHandler OnQueryNotification = null;

        /// <summary>
        /// Executes simple select and convert result to list
        /// </summary>
        /// <param name="lastRecId">Last record ID</param>
        /// <param name="dateTime">Date and time of last inserted record.
        /// This value is returned only for debugging purposes</param>
        /// <returns></returns>
        public static List<string> GetMessageData(out int lastRecId, out DateTime dateTime)
            List<string> messageList = new List<string>();

            lastRecId = -1;
            dateTime = new DateTime();
            using (SqlConnection conn = new SqlConnection(connString))
            using (SqlCommand cmd = new SqlCommand(query, conn))

                using (SqlDataReader reader = cmd.ExecuteReader())
                    while (reader.Read())
                        lastRecId = (int) reader["RecId"];
                        dateTime = (DateTime) reader["Time"];
                        messageList.Add(reader["Text"] + " (Time of data: " + Tools.ToTimeStringWithMilisecond(dateTime) + ")");

            return messageList;

        /// <summary> This method will wait for changes in Sql Server. </summary>
        /// <returns>false if there is new data,
        /// true if you must wait for new data.</returns>
        public static bool WaitMessageDataAsync(int lastRecId)
            lock (dependSync)
                if (depend != null)
                    return true; // "return" is to prevent creating many dependencies
                                 // "true" means you must wait for result (asynchrounous)

                using (SqlConnection conn = new SqlConnection(connString))

                    // 1. First query, to check if we need to wait for changes
                    using (SqlCommand cmd = new SqlCommand(queryForCheck, conn))
                        object o = cmd.ExecuteScalar();
                        int max;
                        if (o is DBNull)
                            max = -1; // no records
                            max = Convert.ToInt32(o);

                        if (max > lastRecId) // if max > last seen recId
                            Trace.WriteLine("New data available right now!");
                            return false; // No async! New data available right now!

                    // 2. Second query, to run dependency
                    Trace.Write("Creating new dependency... recId: " + lastRecId);

                    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();

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

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

                        Trace.WriteLine("                       ...creating new dependency ABORTED!");

                        return false; // No async! New data available right now!
                    Trace.WriteLine("                       ...creating new dependency end.");
            return true; // true - you must wait for notification

        private static void Depend_OnChangeAsync(object sender, SqlNotificationEventArgs e)
            lock (dependSync)
                SqlDependency sqlDependency = (SqlDependency) sender;
                sqlDependency.OnChange -= Depend_OnChangeAsync;

                depend = null; // current dependecy finished his work - mark it as null to create new

                // For simplicity it is not called as event

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer
Poland Poland
I graduated from the Jagiellonian University. After defence of master thesis (link to presentation) I have started professional programming in C and C++ using OpenGL. After that, my Company gave me a chance to switch to .Net framework and C# and I have betrayed my passion for computer graphics. Now I'm C#, SQL and sometimes ASP.NET programmer.

You may also be interested in...

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171207.1 | Last Updated 27 Nov 2012
Article Copyright 2010 by CoperNick
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid