CODEWITHSUNDEEP
c#sql-serversqldependency
Mou
Mou

Reputation: 16292

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

Upvotes: 1

Views: 420

Answers (2)

dyatchenko
dyatchenko

Reputation: 2343

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.

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46213

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.

Upvotes: 1

Related Questions