Асинхронные вызовы SQL в ADO.NET

ОГЛАВЛЕНИЕ

Как использовать асинхронные вызовы SQL, чтобы получать сообщения о ходе работы

Введение

Данная статья показывает, как использовать асинхронные вызовы в ADO.NET, чтобы получать сообщения о ходе работы от вызовов хранимых процедур, выполняющихся в течение длительного периода времени, или любых других команд SQL, которые порождают сообщения для клиента (пользователя).

Нашей первоначальной идеей было показать, как вы можете ретранслировать клиенту сообщения оператора  SQL BACKUP, но если у вас нет достаточно большой базы данных, вы не заметите проблему, о которой мы расскажем. Нет существенного различия между выполнением хранимой процедуры и команды BACKUP, обе отправляют свои информационные сообщения как ошибки (которыми они не являются). Обе используют RAISERROR с NOWAIT, из которой мы можем принимать сообщения. Мы предполагаем, что BACKUP имеет другой метод для возврата информационных сообщений. Для хранимых процедур есть только команда RAISERROR.

Это хорошо, поскольку есть флаг серьезности ошибки, являющийся частью вызова, и любое значение серьезности, меньшее 10, используется только для информации (смотрите документацию MSDN). Так что можно использовать значение серьезности для наших целей.

Почему вы хотите это сделать?

Мы обычно используем это для одной из хранимых процедур, которые сильно изменяют базу данных, обновляют базу данных, или для BACKUP, которые запускаются из сервисной программы клиента, где они ожидают увидеть сообщения о том, что процедура не остановилась и продолжает выполняться!

Используемая хранимая процедура

Следующая хранимая процедура будет использоваться в следующих примерах:

CREATE PROCEDURE AdoProcess_Test1
AS
BEGIN
    SET NOCOUNT ON     -- Используется для повышения производительности и с целью
            -- убедиться, что обратно не отправляется ненужная информация
    DECLARE @time VARCHAR(16)
    DECLARE @time VARCHAR(16)
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
    RAISERROR( 'Completed 25%% At %s', 2, 1, @time) WITH NOWAIT
    WAITFOR DELAY '00:00:03'
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
    RAISERROR( 'Completed 50%% At %s', 2, 2, @time) WITH NOWAIT
    WAITFOR DELAY '00:00:03'
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
    RAISERROR( 'Completed 75%% At %s', 1, 3, @time) WITH NOWAIT
    WAITFOR DELAY '00:00:03'
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
    RAISERROR( 'Completed 100%% At %s', 1, 4, @time) WITH NOWAIT
END;

Самыми важными строками являются те, которые начинаются с RAISERROR. Для этого примера мы жестко запрограммировали строки сообщений внутри хранимой процедуры. Обычной рекомендацией является добавление пользовательских сообщений в базу данных и ссылка на номер сообщения в RAISERROR, смотрите документацию MSDN. В строке сообщения используется такой же синтаксис, что и для функции C printf. Вот почему двойной символ %% присутствует в строке.

Формат строки RAISERROR такой:

RAISERROR( <Message/MessageId>, <Severity>, <State>[, <Arguments>]) [WITH <Options>]
  • Message/MessageId (сообщение/идентификатор сообщения) -  Реальное сообщение, которое будет возвращаться клиенту, можно использовать символы-заполнители printf для указания расположения переменных.
  • Severity (серьезность) сообщения – Меньше 10 для информационных сообщений.
  • State (Состояние) – Это уникальный номер, обозначающий, где в хранимой процедуре была возбуждена RAISERROR.
  • Arguments (Аргументы) – Необязательные аргументы для Сообщения, если используются символы-заполнители printf.
  • Options (Параметры) – Дополнительные параметры, в нашем случае мы хотим возвращать сообщение, как только оно было возбуждено, поэтому используется параметр NOWAIT. Вы можете отправлять сообщения в журнал регистрации событий, если нужно.

Пример имеет пару вспомогательных функций, которые подключаются к базе данных и создают/удаляют хранимую процедуру автоматически в tempdb. Поэтому вам необходимо разрешение (полномочия) CREATE/DROP PROCEDURE в tempdb.

Связывающая строка

Связывающая строка является одной из самых важных вещей во всей установке. Для данного примера мы жестко закодировали ее внутри класса C#, но обычно она помещается в файл конфигурации приложения или веб-приложения.

private const string ConnectionStr = "Data Source=(local);
    Initial Catalog=tempdb;Trusted_Connection=SSPI;Asynchronous Processing=true";

Полный список всех параметров, которые вы можете использовать при подключении к SQL, смотрите в документации MSDN. Описание используемых  нами параметров приводится ниже:

  • Источник данных – Это сервер, к которому вы собираетесь подключаться. Мы используем собственный локальный сервер. Если вы установили версию SQL Express с Visual Studio, то вам нужно изменить имя сервера с (локальный) на (локальный)\SQLEXPRESS.
  • Исходный каталог – Это имя реальной базы данных, размещающейся на сервере SQL, которую вы будете использовать. Если вы не имеете полномочий CREATE/DROP в tempdb, то измените значение параметра на имя той базы данных, в которой у вас есть эти полномочия.
  • Защищенное соединение – Этот параметр сообщает клиентской программе SQL, что вы используете ваши параметры доступа в Windows для возможностей технологии единого входа (Single Sign On). Рекомендуется использовать SSPI вместо значения истина.
  • Асинхронная обработка – Чтобы заставить ADO.NET выполнять асинхронную обработку (вызов методов Begin (начать) на командном объекте), этот параметр должен иметь значение trueили в противном случае вы получите InvalidOperationException в примере. (истина),

Для реальных приложений мы обычно добавляем параметры 'Имя приложения' и 'Идентификатор Workshare' в соединение. DBA знает, которое приложение/компьютер вызывает проблему в базе данных (поддерживает дружественность с DBA!).


Настройка асинхронной обработки

В данном примере мы создали два метода, показывающие, как это делается. Единственное различие между ними состоит в том, что используется реальный командный метод SQL.

Для начала нам нужен способ сообщения о том, что хранимая процедура закончила обработку, и можно продолжать выполнение нашего кода. Для этого мы будем использовать класс обработчика ManualResetEvent. Как только этот класс перешел в сигнализирующее состояние, мы хотим, чтобы он оставался в сигнализирующем состоянии. Мы объявили это состояние как член класса.

once it is signalled, I want it to stay signalled. I have declared it as a class member.

private static readonly ManualResetEvent _reset = new ManualResetEvent(false);

Настройка соединения и командных объектов для использования.

using (SqlConnection connection = new SqlConnection(ConnectionStr))
{
    using (SqlCommand cmd = new SqlCommand("AdoProcess_Test1", connection))
    {
        .....
    }
}

Поскольку команда SQL – это хранимая процедура без параметров, нам еще нужно инициализировать команду. Если вы думаете, что команда отнимет больше 30 секунд, то вам также нужно изменить значение свойства лимита времени команды. Свойство лимита времени соединения управляет тем, сколько времени занимает подключение к базе данных, а не тем, сколько времени требуется на выполнение команды/запроса.

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30;     // По умолчанию 30 секунд,
        // только показывает, как устанавливать. Нет
//необходимости его устанавливать.

Теперь нужно сообщить соединению, какой метод вызывать, когда оно получает сообщения от базы данных. Сигнатурой метода является void <ИмяМетода>(object sender, SqlInfoMessageEventArgs e).

private static void ConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    if ( e.Errors.Count > 0 )
    {
        // Проверяем, что принимаются только информационные сообщения
        Console.WriteLine("Получено {0} сообщений", e.Errors.Count);
        foreach( SqlError info in e.Errors )
        {
            if ( info.Class > 9 ) // Серьезность
            {
                Console.WriteLine("Сообщение об ошибке : {0} :
            State : {1}", info.Message, info.State );
            }
            else
            {
                Console.WriteLine("Информационное сообщение : {0} :
            State : {1}", info.Message, info.State);
            }
        }
    }
    else
    {
        Console.WriteLine("Получено информационное сообщение соединения : {0}", e.Message);    
    }
}

Как видно, мы проверяем свойство Class у экземпляра SqlError. Это реальное значение серьезности, с которым был запущен метод RAISERROR. Свойство State имеет такое же имя, что и свойство State в вызове RAISERROR, почему разработчики ADO также не могли сохранить имя Серьезность. SqlInfoMessageEventArgs.Message является объединением совокупности ошибок, если ошибки присутствуют, с символом новой строки в качестве разделителя.

Сейчас у нас есть метод, который нужно подключить к объекту соединения.

cmd.Connection.InfoMessage += ConnectionInfoMessage;

Когда работа в асинхронном режиме завершается, нам нужен метод обратного вызова, чтобы обработать результаты и сообщить остальной части приложения, что работа завершена. Необходимо создать метод, который будет поддерживать интерфейс IAsyncResult, сигнатурой метода является void <ИмяМетода>(IAsyncResult result).

Поскольку мы демонстрируем выполнение Non Query и выполнение Reader, мы должны создать два метода обратного вызова, один для каждого вида используемой команды. Их именами будут NonQueryCallBack иReaderCallBack:

private static void NonQueryCallBack(IAsyncResult result)
{
    SqlCommand command = (SqlCommand) result.AsyncState;
    try
    {
        if (command != null)
        {
            Console.WriteLine("Ожидание завершения асинхронного вызова");
            command.EndExecuteNonQuery(result);
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine("Ошибка выполнения команды! - [{0}]", ex.Message);
    }
    finally
    {
        Console.WriteLine
        ("Завершился обратный вызов, поэтому сообщаем, что основной поток может продолжить выполняться....");
        _reset.Set();
    }
}

Когда выполняется command.EndExecuteNonQuery(result), он будет ждать поток обратного вызова до тех пор, пока выполнение команды не завершится или не произойдет исключение. Так или иначе, когда он завершается, нужно сообщить основному потоку, что работа закончена, поэтому в конце блока вызывается метод Set для экземпляра ManualResetEvent.

Давайте выполним команду, передав ее в объект SqlCommand как Асинхронное Состояние и метод обратного вызова.

AsyncCallback result = NonQueryCallBack;
cmd.Connection.Open();
cmd.BeginExecuteNonQuery(result, cmd);
Console.WriteLine("Ожидание завершения выполнения хранимой процедуры....");
_reset.WaitOne();

После начала выполнения можно продолжить другую работу (не закрывайте текущее соединение, или иначе команда будет отменена) или ждать до тех пор, пока она не закончится, что и делается в данном примере.

Теперь есть проблема с командой Execute Non Query (выполнить без запроса), состоящая в том, что она будет ждать до конца, перед тем как возвратит любые сообщения, как показывает выход из этого метода:

Waiting for completion of executing stored procedure....
Waiting for completion of the Async call
Received 4 messages
Info Message : Completed 25% At 15:23:19:697 : State : 1
Info Message : Completed 50% At 15:23:22:697 : State : 2
Info Message : Completed 75% At 15:23:25:697 : State : 3
Info Message : Completed 100% At 15:23:28:697 : State : 4
Completed call back so signal main thread to continue....
Completion of Non Execute Method....

Когда мы начали использовать этот метод для создания резервных копий баз данных перед обновлением, не был замечен этот эффект до тех пор, пока создание резервной копии не начало занимать больше чем пару секунд (когда наши тестовые базы данных были пустыми, это занимало только одну секунду!). Мы предположили, что не возвращаются наборы данных, которые мы должны  были использовать в методе ExecuteNonQuery, и это неправильно. Этот метод оптимизирован внутренне (мы надеемся), поэтому он не будет обрабатывать никаких сообщений до конца, и, подумав, вы поймете, что это правильное решение.

Чтобы справиться с этой проблемой, взамен нужно использовать методы BeginExecuteReader и EndExecuteReader. В примере мы создали другой набор методов, которые используют эти методы, так что давайте посмотрим на вывод.

Waiting for completion of executing stored procedure....
Waiting for completion of the Async call
Received 1 messages
Info Message : Completed 25% At 15:32:48:410 : State : 1
Received 1 messages
Info Message : Completed 50% At 15:32:51:410 : State : 2
Received 1 messages
Info Message : Completed 75% At 15:32:54:410 : State : 3
Received 1 messages
Info Message : Completed 100% At 15:32:57:410 : State : 4
Completed call back so signal main thread to continue....
Completion of Execute Reader Method....

Как видите, метод ConnectionInfoMessage запускается каждый раз, когда получено сообщение, а не в самом конце.

Также мы реализовали другой метод, чтобы показать, как запускать резервное копирование, и чтобы показать, что он возвращает сообщения такого же типа. Мы не выполняем  автоматически этот метод, потому что он пытается сделать резервную копию базы данных msdb, и вы можете не иметь к ней доступа.

Загрузить исходный код - 8.2 KB