SQL dependency and data refering in SQL

问题:

when we work with sql dependency then we need to always refer a sql like below one SELECT ActivityDate FROM [bba-reman].MyLog

i just like to know if i write the above sql this way then does it work

SELECT TOP 1 ActivityDate FROM [bba-reman].MyLog OR

SELECT TOP 5 ActivityDate FROM [bba-reman].MyLog

i am looking for suggestion and guidance.

private void RegisterNotification()
{
    string tmpdata = "";
    System.Data.SqlClient.SqlDependency.Stop(connectionString);
    System.Data.SqlClient.SqlDependency.Start(connectionString);

    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = "SELECT ActivityDate FROM [bba-reman].MyLog";
            dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler(OnDataChange);

            SqlDataReader dr = cmd.ExecuteReader();
            {
                while (dr.Read())
                {
                    if (dr[0] != DBNull.Value)
                    {
                        tmpdata = dr[0].ToString();
                    }
                }
            }

            dr.Dispose();
            cmd.Dispose();
        }
    }
    finally
    {
        //SqlDependency.Stop(connStr);
    }
}

回答1:

The SqlDependency class has a lot of restrictions as well as the memory leak problems. An absence of the TOP instruction is the one of them. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Filtering incoming messages helps you implement desirable behavior. Hope this help.

回答2:

According to the SQL Server Books Online (https://msdn.microsoft.com/en-us/library/t9x04ed2.aspx), one of the restrictions for using QueryNotifications is that the statement must not use a TOP expression. SqlDependency is just a higher level implementation of QueryNotifications that takes care of the Service Broker plumbing.