SQLDependency OnChangeEvent not firing

Question

I have the following code to execute a SqlCommand against a database View:

public IEnumerable<PickNote> GetData()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BTNInternalData_LiveEntities"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT [PICKINGROUTEID],[CUSTOMER],[SALESNAME]
           FROM [dbo].[PickScreenData] WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, [ACTIVATIONDATETIME])) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) or [EXPEDITIONSTATUS] = 3", connection))
                {
                    // Make sure the command object does not already have
                    // a notification object associated with it.
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        var data = reader.Cast<IDataRecord>();

                        return data.Select(x => new PickNote
                        {
                            pickingRouteId = x["PICKINGROUTEID"].ToString()
                        }).ToList();
                    }
                }
            }
        }

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        // do stuff
    }

However, the dependency_OnChange method only gets called at the start of my application and doesn't do so again no matter if the data in my View changes. I've debugged SqlNotificationEventArgs and the Info is Invalid but I'm not sure why as the command query runs fine

Edit

I've changed the query so that it queries the table directly but SqlNotificationEventArgs.Info still says Invalid. Here is the new code:

public IEnumerable<PickNote> GetData()
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AXLive"].ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand(@"
                SELECT PICKINGROUTEID, EXPEDITIONSTATUS
                FROM         [dbo].[WMSPICKINGROUTE]
                WHERE     (EXPEDITIONSTATUS <> 20) 
                AND (DATEADD(dd, 0, DATEDIFF(dd, 0, [ACTIVATIONDATETIME])) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
                    OR [EXPEDITIONSTATUS] = 3)", connection))
            {
                // Make sure the command object does not already have
                // a notification object associated with it.
                command.Notification = null;

                var dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    var data = reader.Cast<IDataRecord>();

                    return data.Select(x => new PickNote
                    {
                        pickingRouteId = x["PICKINGROUTEID"].ToString()
                    }).ToList();
                }
            }
        }
    }

WMSPICKINGROUTE is the table where my view was getting the data from before.

score 1 · Accepted Answer · answered Nov 14 '13 at 16:30

1

According to this MSDN page, you cannot use a SqlDependency against a select statement that references a view. That seems to be the problem. Rewrite your query to hit tables and it should work.

answered Nov 14 '13 at 16:30

Sven Grosen

5,616
3
30
52

I've changed it so that it queries the table directly but it still says invalid in `SqlNotificationEventArgs.Info` – CallumVass Nov 15 '13 at 08:25
@BiffBaffBoff I don't have much personal experience with notifications, but I'd suggest starting with a very simple select statement with no where criteria and see if that behaves as you'd expect. Make sure you are meeting all the other criteria referenced on that MSDN pae. – Sven Grosen Nov 15 '13 at 13:33
That's what I did, I took out the Date stuff from the WHERE clause and it worked. @ledbutter – CallumVass Nov 15 '13 at 14:03
Ah, that's what I was alluding to: the calculated date stuff in your where clause. Sure seems like this dependency stuff has limited use. – Sven Grosen Nov 15 '13 at 14:04
Yeah it seems quite limited but its still very good when its impossible to use other means :) – CallumVass Nov 15 '13 at 14:17

SQLDependency OnChangeEvent not firing

Edit

1 Answers1