Click here to Skip to main content
11,644,982 members (59,806 online)
Click here to Skip to main content
Articles » Web Development » Ajax » General » Downloads
Add your own
alternative version

HTTP Push from SQL Server — Comet SQL

, 27 Nov 2012 CPOL 84K 3.3K 164
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 gmail.com 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 = @"
SELECT MAX(RecId)
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
        // http://msdn.microsoft.com/en-us/library/ms181122.aspx
        // And don't look at this: (older - not full 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
";


        //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))
            {
                conn.Open();

                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))
                {
                    conn.Open();

                    // 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
                        else
                            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);
                    Tools.EnableNotifications(connString);

                    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;
                    reader.Close();
                    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
                CometAsyncHandler.ProcessAllWaitingClients(); 
            }
        }
    }
}

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.

License

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

Share

About the Author

CoperNick
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...

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