SQL dependency and data refering in SQL

Question

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

I am not sure I fully understand your question. Do you want the TOP queries to be sorted: SELECT TOP 1 ActivityDate FROM [bba-reman] ORDER BY ActivityDate DESC — Alex
– Alex, Commented Apr 21, 2015 at 19:43
yes if i submit that query to sql dependency then does it work? can notify the change ? — Mou
– Mou, Commented Apr 21, 2015 at 19:46
Yes, you can submit that query to SqlDependency and it should work. — Alex
– Alex, Commented Apr 21, 2015 at 19:49

Dan Guzman · Accepted Answer · 2015-04-22 02:14:21Z

1

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.

answered Apr 22, 2015 at 2:14

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:28:17Z

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.

Collectives™ on Stack Overflow

SQL dependency and data refering in SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related