SqlTableDependency

SqlTableDependency relies on SQL Server infrastructure. Ensure the following requirements are met:

1. Enable Service Broker

Run the following SQL command on your database:

ALTER DATABASE MyDatabase SET ENABLE_BROKER

2. Permissions

If the connection user is not a db_owner or Administrator, they must be granted the following permissions:

• ALTER, CONNECT, CONTROL, CREATE CONTRACT, CREATE MESSAGE TYPE, CREATE PROCEDURE, CREATE QUEUE, CREATE SERVICE, EXECUTE, SELECT, SUBSCRIBE QUERY NOTIFICATIONS, VIEW DATABASE STATE, VIEW DEFINITION.

Note: You can skip the permission check in the constructor by setting executeUserPermissionCheck to false, but insufficient permissions will still cause SQL exceptions.

3. Trustworthy Property

If you specify the QueueExecuteAs property (default is "SELF"), you may need to set the database to TRUSTWORTHY:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON

To detect table record changes without continuous re-querying, follow these steps:

1. Define a C# model: Create a class where properties map to the columns you want to monitor. You do not need to include all columns, only the ones of interest.
2. Map properties (Optional): If your model property names differ from the database column names, use ModelToTableMapper<T> to establish the mapping.
3. Initialize SqlTableDependency<T>: Pass the connection string and the database table name. If the model name matches the table name, the table name parameter is optional.
4. Subscribe to OnChanged: Attach an event handler to the OnChanged event.
5. Start the dependency: Call .Start() to begin listening for notifications.
6. Stop the dependency: Call .Stop() to release the database infrastructure (triggers, service broker, etc.).

public class Customer
{
 public int Id { get; set; }
 public string Name { get; set; }
 public string Surname { get; set; }
}

public class Program
{
 private static string _con = "data source=.; initial catalog=MyDB; integrated security=True";
   
 public static void Main()
 {
  // Map model properties to different table column names
  var mapper = new ModelToTableMapper<Customer>();
  mapper.AddMapping(c => c.Surname, "Second Name");
  mapper.AddMapping(c => c.Name, "First Name");

  // Initialize with connection string, table name, and mapper
  using (var dep = new SqlTableDependency<Customer>(_con, "Customers", mapper: mapper))
  {
   dep.OnChanged += Changed;
   dep.Start();

   Console.WriteLine("Press a key to exit");
   Console.ReadKey();

   dep.Stop();
  }
 }

 public static void Changed(object sender, RecordChangedEventArgs<Customer> e)
 {
  var changedEntity = e.Entity;
      
  Console.WriteLine("DML operation: " + e.ChangeType); // INSERT, UPDATE, or DELETE
  Console.WriteLine("ID: " + changedEntity.Id);
  Console.WriteLine("Name: " + changedEntity.Name);
  Console.WriteLine("Surname: " + changedEntity.Surname);
 }
}

Database Compatibility Level

Even if using SQL Server 2008 R2 or later, notifications may fail if the database was created using an older compatibility version (e.g., SQL Server 2005). Ensure the database compatibility level is appropriate for your SQL Server version.

Connection Loss

If the database connection is lost, SqlTableDependency cannot automatically reconnect to its queue. You must create a new instance of SqlTableDependency to resume receiving notifications.

Unsupported Column Types

The following SQL Server column types are not supported:

• XML, IMAGE, TEXT/NTEXT, STRUCTURED, GEOGRAPHY, GEOMETRY, HIERARCHYID, SQL_VARIANT.

Limitations

• In-Memory OLTP: Does not work with In-Memory OLTP tables; only traditional disk-based tables are supported.
• String Comparison: The library does not distinguish between an empty string ('') and a string containing only spaces (' '), nor between NULL and an empty string. Changes between these values will not trigger a notification.
• Windows Service State: A Windows service using SqlTableDependency must not enter SLEEP or IDLE mode, as this can trigger the watchdog to drop database objects.