C#

C# trigger after database changes with SQLDependency

I found the following amazing article with some code that works without any modifications. With SQLDependency, you can listen to database changes with a predefined query.  If something happens to this result set (INSERT/DELETE/UPDATE) an event will be triggered, allowing you to take action.

Assume following console application:

class Program
 {
 static void Main(string[] args)
 {
 string connectionString = Settings.Default.ConnString;
 var changeListener = new DatabaseChangeListener(connectionString);
 changeListener.OnChange += () =>
 {
 Console.WriteLine("There was a change"); 
 changeListener.Start(@"SELECT [Name] FROM [dbo].[Cars]");
 };
 changeListener.Start(@"SELECT [Name] FROM [dbo].[Cars]");

 Console.ReadLine();
 }

 static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
 {
 Console.WriteLine("Hi!");
 }
 }

Two things need to be configured here: the connection string and the query you want to listen to. Please note that wildcard won’t work so you need to define your exact columns.

Next up, the definition of the database listener:

public class DatabaseChangeListener
 {
 #region Constructor

 public DatabaseChangeListener(string connectionString)
 {
 this.connectionString = connectionString;
 SqlDependency.Stop(connectionString);
 SqlDependency.Start(connectionString);
 connection = new SqlConnection(connectionString);
 }

 #endregion Constructor

 #region Finalizer

 ~DatabaseChangeListener()
 {
 SqlDependency.Stop(connectionString);
 }

 #endregion Finalizer

 #region Properties

 private readonly string connectionString;
 private readonly SqlConnection connection;

 public delegate void NewMessage();
 public event NewMessage OnChange;

 #endregion Properties

 #region Methods

 public DataTable Start(string changeQuery)
 {
 using (SqlCommand cmd = new SqlCommand(changeQuery, connection) { Notification = null })
 {
 SqlDependency dependency = new SqlDependency(cmd);
 dependency.OnChange += NotifyOnChange;
 if (connection.State == ConnectionState.Closed)
 {
 connection.Open();
 }
 using (DataTable dt = new DataTable())
 {
 dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
 return dt;
 }
 }
 }

 void NotifyOnChange(object sender, SqlNotificationEventArgs e)
 {
 var dependency = sender as SqlDependency;
 if (dependency != null) dependency.OnChange -= NotifyOnChange;
 if (OnChange != null) { OnChange(); }
 }

 #endregion Methods
 
 }

SQLDependency starts listening as soon as you create a new instance of the listener class. it will actually start listening to your query if the start method is fired. If something happens on the database level that affects the defined query, the NotifyOnChange delegate will be called as well, allowing you to take action where necessary.

An alternative approach of SQLDependency could be Quartz.NET, what would result in more of database polling system rather than event-based communication.

Advertisements

One thought on “C# trigger after database changes with SQLDependency

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s