how to keep sql dependency doing the its purpose

Question

I have a console application.

I wanna keep watching the changes on a specific column in my database table.

I read through internet and I have found that sql dependency is good for my purpose. I started learning about it and I did the following:

1. create a class.
2. In the constructor, I called the static function start and I called a function that has all the sql dependency settings.

My problem

When I run the application using the start click on visual studio 2013, the apps works and then stops. However, what I need is that the apps starts working and keep watching for changes in my database's table.

Could you help me please?

Code:

This is a very very simple c# code.

public class MyListener
    {
        public MyListener()
        {
            SqlDependency.Start(getConnectionString());
            this.listen();
        }

        private string getConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["popup"].ConnectionString.ToString();
        }
        private void listen()
        {
            string query = "SELECT CallerID FROM TransferToSIP WHERE hasBeenRead = 0";
            SqlConnection con = new SqlConnection(getConnectionString());
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            using (cmd)
            {
                SqlDependency dependency = new SqlDependency(cmd);
                dependency.OnChange += new
                   OnChangeEventHandler(OnDependencyChange);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                }
            }
        }
        void OnDependencyChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("Roma");
        }
        void Termination()
        {
            SqlDependency.Stop(getConnectionString());
            Console.Read();
        }

Answer 1

The problem is in absence of the resubscruption. You should call the listen method inside of OnDependencyChange. I know that it is weird, but it is the SqlDependency class.

Answer 2

Be careful using the SqlDependency class to monitor changes in the database tables - it has the problems with the memory leaks. However, you can use your own realization with DDL triggers and SQL Service Broker API or use one of an open source projects, e.g. SqlDependencyEx:

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

Hope this helps.