2

I've tried setting up an SQL Dependency to fire on a "Count Rows" query (written in C#, SQL Server 2008 Express), but after the original subscription SQLNotificationType goes, the event handler never seems to want to fire again (despite rows being added, and I've checked the SQL and it's returning the expected value...).

My code is below. Any thoughts are much appreciated!

EDIT: The project that this code is in is a WPF program. I have this particular code stored in a separate class, which my WPF program creates an instance of in an 'Initialized' event handler. I then have a method in this class that basically calls ConnectToDatabase() first, and then calls SetupSQLDependency().

EDIT 2: As a side note, this program is a WPF which I was hoping to distribute to a few users. The goal was to have the WPF update with certain information whenever new rows were added to a database. I thought that this would be the best way to go about it, as opposed to always querying the database.

        private void ConnectToDatabase()
        {
        //This method is the first to be called, and is the entry 
        // point into my SQL database code.

            databaseConnection = new SqlConnection(connectionString);

            // Setup command used in SqlDependecy 
            SqlCommand tempCmd = new SqlCommand();
            tempCmd.Connection = databaseConnection;
            tempCmd.CommandText = "SELECT COUNT(ID) FROM [Example].[dbo].[ExampleTable]";
            sqlCmd = tempCmd;

            try
            { databaseConnection.Open(); }
            catch (Exception e)
            { writeDebug(e.ToString()); }
        }        

        private void SetupSQLDependency()
        {
            SqlDependency.Stop(connectionString);
            SqlDependency.Start(connectionString);

            sqlCmd.Notification = null;

            // create new dependency for SqlCommand
            SqlDependency sqlDep = new SqlDependency(sqlCmd);
            sqlDep.OnChange += new OnChangeEventHandler(sqlDep_OnChange);

            SqlDataReader reader = sqlCmd.ExecuteReader();
        }

        private void sqlDep_OnChange(object sender, SqlNotificationEventArgs e)
        {
            // FROM: http://msdn.microsoft.com/en-us/a52dhwx7.aspx

            if (e.Type == SqlNotificationType.Change)
            {
            //++++++ THIS IS THE BLOCK THAT IS NEVER TRIGGERED ++++++//
                // Have to remove this as it only work's once
                SqlDependency sqlDep = sender as SqlDependency;
                sqlDep.OnChange -= sqlDep_OnChange;

                // Resetup Dependecy
                SetupSQLDependency();
            }
            else if (e.Type == SqlNotificationType.Subscribe)
            {
                double te = 12; // Used this just to test a break... code is useless
            }
        }
9
  • Can you elaborate on where this code exists on how it is called? For example if this code exists in a Page and is called in the OnLoad event, then you have to remember that your page class is only alive for a split second while the page is being rendered and then a whole new class is instantiated for each request. So your event subscription, and the sqlCmd variable instance are not kept around (and theoeretically are causing mem leaks). Commented Jan 26, 2012 at 20:21
  • OK, for some reason I thought you were doing this in ASP.NET. So does your SqlNotificationType.Change fire once and only once, or never at all? Commented Jan 26, 2012 at 20:27
  • Ah, yea a plain 'ol WPF. The only time it fire's is when: e.Type == SqlNotificationType.Subscribe, but never again.. Commented Jan 26, 2012 at 20:29
  • Is this a static class? Is it possible the connection and/or the command are being disposed somewhere? Commented Jan 26, 2012 at 20:31
  • You've mentioned SQL Express, which jumped out at me as I swear it does not support the Broker Service you require for the SQLDependencies, but I am having real trouble finding a definitive statement of such on MSDN. I'll keep digging, but I'd recommend it as something to investigate. We have almost identical code to yours running on SQL Server 2008 Developer and Standard editions, we did not try Express because of this somewhat elusive statement regarding the Express edition... Commented Jan 26, 2012 at 20:32

1 Answer 1

2

I believe the problem here is the COUNT. Refer to the MSDN documentation for Supported SELECT Statements for more info:

The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). [...]

Sign up to request clarification or add additional context in comments.

1 Comment

you're correct - I realized that a little while ago but forgot to update this. Thanks for that. I ended up just re-querying the DB every few seconds....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.