SQLDependency_OnChange-Event fires only one single Time

Question

I'm working with SQLDependency to notify me if there is a change in the Database. After Program Start-Up it works just fine. When I make a first change the Event fires. Wohoo... that's great. But if I made a second change the event doesn't fire again. I've searched all the web I think but haven't found anything about THIS Problem. Only found problems where the OnChange-Event fires in a Loop. Can anyone help me?

Here a little code piece:

private void GetStates()     {         if (!DoesUserHavePermission())             return;          SqlDependency.Stop(con);         SqlDependency.Start(con);          using (SqlConnection cn = new SqlConnection(con))         {             using (SqlCommand cmd = cn.CreateCommand())             {                 cmd.CommandType = CommandType.Text;                 cmd.CommandText = "SELECT Bla, Bla2, ..FROM dbo.[BLA3]"                  cmd.Notification = null;                 cmd.Dispose();                  SqlDependency dep = new SqlDependency(cmd);                 dep.OnChange += new OnChangeEventHandler(dep_OnChange);                  cn.Open();                  using (SqlDataReader dr = cmd.ExecuteReader())                 {                     state.Clear(); //In this Case "state" is a List<string>                     while (dr.Read())                     {                         state.Add(dr.GetString(0) + "|" + dr.GetInt32(3));                     }                     dr.Dispose();                     dr.Close();                 }                                 }         }     } 

my OnChange-Event looks like this:

private void dep_OnChange(object sender, SqlNotificationEventArgs e)     {         SqlDependency dep = sender as SqlDependency;         dep.OnChange -= this.dep_OnChange;          using (SqlConnection cn = new SqlConnection(con))         {             using (SqlCommand cmd = cn.CreateCommand())             {                 cmd.CommandType = CommandType.Text;                 cmd.CommandText = "SELECT Bla, Bla2, ..FROM dbo.[BLA3]";                  cmd.Notification = null;                  if (e.Type == SqlNotificationType.Change)                 {                     if (cn.State != ConnectionState.Open)                     {                         cn.Open();                     }                      using (SqlDataReader dr = cmd.ExecuteReader())                     {                         state.Clear(); // Clear and Refill the stringlist "state"                         while (dr.Read())                         {                             state.Add(dr.GetString(0) + "|" + dr.GetInt32(3));                         }                     }                 }                 cn.Close();             }         }         this.GetStates(); //to go ahead and wait for a new change     } 

Where is the problem?

Answer 1

I was running into this issue as well. You need to create a new SqlDependency entity (after unsubscribing the existing one from the OnChange event) and then run a new ExecuteReader command. I got the idea from this post:

http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events

This usually makes sense, as once you have been notified of a change you will normally want to re-query the data.

Answer 2

After changes happened to the database at the first time, you have to execute the command again and re-subscribe to the event.

The following code is working for me.

class Program {     static string connectionString = "Server=.;Database=test_sql_dependency;Integrated Security=True;";      static void Main(string[] args)     {         // 1. create database          // 2. enable service broker by executing this sql command on the database.         // alter database test_sql_dependency set enable_broker          // 3. start sql dependency, for some sql server connection string or with queue if you want.          //var queueName = "myFirstQueue";         //SqlDependency.Start(connectionString, queueName);         SqlDependency.Start(connectionString);          // complete the rest of the steps in seperate method to be able to call it again when you need to          // re-subscribe to the event again, becuase by default it will be executed only one time          RegisterSqlDependency();          Console.WriteLine("Listening to database changes...");         Console.ReadLine();     }      static void RegisterSqlDependency()     {         using (SqlConnection connection = new SqlConnection(connectionString))         {             if (connection.State != System.Data.ConnectionState.Open)             {                 connection.Open();             }              // 4. create a sql command              // you can't say select *, and also you have to specefy the db owner (dbo.)             SqlCommand command = new SqlCommand("select Id, Name from dbo.Employee", connection);              // 5. create dependency and associtate it to the sql command             SqlDependency dependency = new SqlDependency(command);              // 6. subscribe to sql dependency event             dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);              // 7. execute the command             using (SqlDataReader reader = command.ExecuteReader())             {              }         }     }      static void OnDependencyChange(object sender, SqlNotificationEventArgs e)     {         var InsertOrUpdateOrDelte = e.Info;            //-----------------------------Finally-------------------------         // after you knew that there is a change happened          // you have to unsubscribe the event and execute the command again and then re-subscribe to the event          // 1. unsubscribe the event         SqlDependency dependency = sender as SqlDependency;         dependency.OnChange -= OnDependencyChange;          // 2. re-subscribe to the event and execute the command again         RegisterSqlDependency();      } }