0

I need your help. I'm trying to implement SqlDependency in a console app to constantly monitor a database table (ultimately a service broker queue). Here is my code and the problem is that after one execution application exits. 

class Program
{
    static void Main(string[] args)
    {
        var listener = new Listener();

        listener.Listening();

        Console.ReadKey();
    }
}

public class Listener
{
    const string notificationQuery = "SELECT [ID],[Name] FROM [dbo].[tblUsers]";
    const string sampleConnectionString = @"Server = xxx; Database = yyy; Integrated Security = SSPI;";

    public void Listening()
    {
        try
        {
            SqlClientPermission permission = new SqlClientPermission(PermissionState.Unrestricted);
            permission.Demand();
        }
        catch (Exception ex)
        {

        }

        using (var connection = new SqlConnection(sampleConnectionString))
        {
            connection.Open();

            using (var command = new SqlCommand(notificationQuery, connection))
            {
                SqlDependency.Start(sampleConnectionString);

                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

                var hasChanges = dependency.HasChanges;

                command.ExecuteReader();
            }
        }
    }

    void OnDependencyChange(object sender, SqlNotificationEventArgs e)
    {
        Console.WriteLine(sender);
        Listening();
    }

What I found: how to keep sql dependency doing the its purpose

but adding Listening(); to EventHandler doesn't help. When I'm debugging program and I add some value to the table event is triggered but this adds only one more execution of the Listening method while I want the app to keep working and monitoring the table all the time. Can someone help?

Krzysztof Pe
  • 1
  • 1
  • There is no continuation loop in your code at all. No `While(condition)` to keep it running. The main thread will simply run through your procedure once and terminate as is the intention of a console application. – George Kerwood May 18 '20 at 18:58
  • 1
    An ugly but immediate fix would be a simple `while(true);` after your `listener.Listening();` But you should consider proper asynchronous handling. – George Kerwood May 18 '20 at 19:01
  • @GeorgeKerwood Thank you. Can you elaborate about proper asynchronous handling? Is then Listening() required in OnDependencyChange? – Krzysztof Pe May 18 '20 at 19:07
  • I can't, be Tim Corey can :) https://www.youtube.com/watch?v=2moh18sh5p4. Simply put, your continuous monitoring should be conducted on a thread other than the main application thread. This can be achieved through the use of `Task`, `BackgroundWorker`, or any number of other approaches that I'm not particularity qualified to explain or recommend. This way, you main thread is still free to handle user interactions, such as canceling the monitor, whilst you process is handled continuously else where. – George Kerwood May 18 '20 at 19:38

0 Answers0