ServiceBrokerListener
Cross-platform .NET 4.0 (C# 4.0) and .NET Core compatible component which receives SQL Server table changes into your .net code.
Take a look how it works: http://sbl.azurewebsites.net
How To Use
-
Copy SqlDependecyEx class from
ServiceBrokerListener.Domain
project into your solution. -
Make sure that Service Broker is enabled for your database.
ALTER DATABASE test SET ENABLE_BROKER -- For SQL Express ALTER AUTHORIZATION ON DATABASE::test TO userTest
-
Use the class as in example below:
// See constructor optional parameters to configure it according to your needs var listener = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable"); // e.Data contains actual changed data in the XML format listener.TableChanged += (o, e) => Console.WriteLine("Your table was changed!"); // After you call the Start method you will receive table notifications with // the actual changed data in the XML format listener.Start(); // ... Your code is here // Don't forget to stop the listener somewhere! listener.Stop();
-
Enjoy!
How to use for multiple tables
All you need to do is to create multiple listeners with different identities as shown below:
var listener1 = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable1", identity: 1);
var listener2 = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable2", identity: 2);
How to use in multiple apps
You must create listeners with unique identities for each app. So, only one listener with a specific identity should exist at the moment. This is made in order to make sure that resources are cleaned up. For more information and best practices see @cdfell comment and this answer.
Application 1:
// identities are different
var listener = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable", identity: 1);
Application 2:
// identities are different
var listener = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable", identity: 2);
How to track UPDATEs only
The listenerType
constructor parameter configures SqlDependencyEx
to fire an event for
different notification types (can fire on INSERT, UPDATE, DELETE separately or together)
var listener = new SqlDependencyEx(connectionString, "YourDatabase",
"YourTable1", listenerType: SqlDependencyEx.NotificationTypes.Update);