В этой статье описан способ слежения за изменениями, происходящими в БД под управлением MS SQL Server, в приложениях .NET.

Рассмотрим один из примеров ситуации, когда слежение за изменениями в БД необходимо. Пусть существуют два независимых модуля программы. Первый из них — планировщик задач, который запускает различные задачи по расписанию, которое в некотором виде хранится в БД. Второй модуль — подпрограмма, реализующая пользовательский интерфейс; она имеет возможность вносить изменения в расписание, которым руководствуется первый из описанных модулей. Важно: ни один из модулей явно не связан с другим, поэтому планировщик задач может получить информацию о том, что расписание изменилось, только посредством обращения к таблице (таблицам) БД, где это расписание хранится.

Один из вариантов решения задачи слежения за изменениями в таблицах БД — это использование механизма Service Broker в MS SQL Server совместно с объектом SqlDependency из фреймворка .NET.

SQLServerServiceBroker предоставляет возможность отправлять сообщения и составлять очереди сообщений для внешних приложений из SQL Server Database Engine. Это упрощает для разработчиков создание сложных приложений, которые используют компоненты Database Engine для сообщения между различными базами данных. Разработчики могут использовать Service Broker, чтобы с лёгкостью создавать распределённые и надёжные приложения. [1]

Объект SqlDependency представляет очередь оповещений, которая организует зависимость между приложением и экземпляром SQL Server. Приложение может создать объект SqlDependency и зарегистрироваться для получения оповещений посредством обработчика событий OnChangeEventHandler. [2]

Решаемую задачу можно разбить на несколько этапов.

I. Настройка базы данных

Необходимо убедиться в том, что для используемой БД включен Service Broker, и, если он не включен, включить его. Для этого надо открыть свойства базы данных и посмотреть в разделе Options значение пункта ServiceBroker / BrokerEnabled.

Если выяснилось, что Service Broker не включен, его можно включить, выполнив запрос следующего вида:

use master
go
alter database DatabaseName set enable_broker
go

Важно, что для того, чтобы этот запрос выполнился успешно, должны отсутствовать активные подключения к БД.

Теперь, когда работает Service Broker, SQLServer может отправлять приложениям .NET уведомления об изменении состоянии БД.

II. Подписка на события в коде программы

Далее приведён пример кода на языке C#, в котором наиболее простым способом реализуется подписка на изменения в таблицах БД.

public class SubscriptionsWatcher
{
    private const string connectionString = "123";

    public void StartWatching()
    {
        SqlDependency.Stop(connectionString);
        SqlDependency.Start(connectionString);
        ExecuteWatchingQuery();
    }

    private void ExecuteWatchingQuery()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand(
                "select Id, CronString from dbo.Subscriptions", connection))
            {
                var sqlDependency = new SqlDependency(command);
                sqlDependency.OnChange += new OnChangeEventHandler(OnDatabaseChange);
                command.ExecuteReader();
            }
        }
    }

    private void OnDatabaseChange(object sender, SqlNotificationEventArgs args)
    {
        SqlNotificationInfo info = args.Info;
        if (SqlNotificationInfo.Insert.Equals(info)
            || SqlNotificationInfo.Update.Equals(info)
            || SqlNotificationInfo.Delete.Equals(info))
        {
            //todo
        }
        ExecuteWatchingQuery();
    }
}

Для совершения подписки необходимо вызвать метод StartWatching(). Первое, что он делает — запускает listener (метод Start) для экземпляра СУБД, на который ссылается строка подключения. Предварительно на всякий случай вызывается метод Stop, чтобы не возникло исключения из-за того, что listener уже запущен.

Затем метод ExecuteWatchingQuery вызывает SQL-запрос, построенный определённым образом. После ключевого слова select в запросе перечислены поля таблиц БД, изменения значений которых необходимо отслеживать.

В этом примере запрос построен таким образом, что события вызываются в момент добавления или удаления строк в таблице расписания, а также при изменениях значений полей cron-string.

Очень важно, что в запросе для каждой таблицы должна быть явно указана схема, даже если это схема по умолчанию dbo. В запросах можно использовать join-ы.

При возникновении событий в БД вызывается метод OnDatabaseChange, в который можно поместить команды, выполняющиеся в случае изменений в БД. В приведённом примере в этом методе выполняется проверка типа команды, которая вызвала событие. По окончании выполнения метода происходит обращение к методу ExecuteWatchingQuery, заново запускающему слежение.

Ссылки

  1. MSDN: SQL Server Service Broker
  2. MSDN: SqlDependency Class
  3. C# & SqlDependency — Monitoring your database for data changes