How to detect Database Changes with SqlDependency and c#

Question

I want to fire a Method each Time a database detected a change. I tried this in a Console Application: (ServiceBroker in Database is active)

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlDependencyExample
{
    class Program
    {
        static void Main(string[] args)
        {
            
            string connectionString = "xxx";
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();

            using (SqlCommand command = new SqlCommand(
            "SELECT [ID], [Name] FROM [dbo].[Users]", connection))
            {
                SqlDependency dependency = new SqlDependency(command);
   
                SqlDependency.Start(connectionString);
                
                dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
                
                using (SqlDataReader reader = command.ExecuteReader())
                {                   
                    while (reader.Read())
                    {
                        Console.WriteLine("ID: {0}, Name: {1}", reader.GetInt32(0), reader.GetString(1));
                    }
                }
            }
        }
        private static void OnDependencyChange(object sender, SqlNotificationEventArgs e)
        {
            
            Console.WriteLine("Database change detected.");
            //Fire something
        }
    }
}

But the Problem is, the application starts, and instantly writes "Database change detected." and closed and when I´m adding a row in my Database, it is not handling "OnDependencyChange"... The Console is writing every row which exists. So the Connection is working, but it detects no new rows in the Database

Make sure that the Service Broker is enabled for the database — Vivek Nuna
– Vivek Nuna, Commented Jan 4, 2023 at 15:10

Manuel Fabbri · Accepted Answer · 2023-01-04 14:01:35Z

1

There is nothing that prevents the program to stop, after it executes once. You can add the Console.ReadLine() statement before the closing bracket of the Main function.

answered Jan 4, 2023 at 14:01

Manuel Fabbri

5881 gold badge8 silver badges19 bronze badges

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

1 Comment

Robin Schu Over a year ago

I´ve added Console.ReadLine(). Now i've started it again, let it run, added a row in the database but nothing happend? Its again not handling the OnChange Event

Collectives™ on Stack Overflow

How to detect Database Changes with SqlDependency and c#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related