Как получить @@SPID из запущенной хранимой процедуры, вызванной из кода С#

Я пытаюсь реализовать функцию «отмены» на страницах с потенциально длительными запросами или процессами выбора. Индексация и другие оптимизации были сделаны, чтобы свести к минимуму эту возможность, но во время более высокой загрузки сервера некоторое ожидание в сложных процессах все еще возможно.

Когда процесс запущен, отображается модальный счетчик занятости с кнопкой «Отмена». Если пользователь решит остановить процесс, я хочу использовать следующие шаги:

У меня есть тестовая хранимая процедура:

BEGIN
    DECLARE @SpID varchar(10) = CONVERT(varchar(10), @@SPID);
    RAISERROR(99999, 10, 1, @SpID) WITH NOWAIT;
    WAITFOR DELAY '00:00:15'
END

Идея состоит в том, чтобы выполнить следующее...

  1. В вызванной хранимой процедуре получить назначенный SPID из @@SPID
  2. Используйте оператор RAISERROR, чтобы создать сообщение об отсутствии ошибок, содержащее SPID
  3. Во время задержки WAITFOR функция приложения, вызвавшая хранимую процедуру, извлекает сообщение с SPID
  4. Отправьте SPID другой хранимой процедуре, чтобы убить этот процесс (KILL позаботится о закрытии соединения и откате действий с данными))

На данный момент я понимаю, что сообщение из этой настройки может быть получено вызывающим кодом ASP.NET C#, но я не могу найти пример, который будет извлекать сообщение, в то же время позволяя продолжить выполнение вызывающего кода (с ExecuteNonQuery, ExecuteReader или ExecuteScalar).

/* CommentTest Procedure */
CREATE PROCEDURE CommentTest
AS
BEGIN
    DECLARE @SpID varchar(10) = CONVERT(varchar(10), @@SPID);
    RAISERROR(99999, 10, 1, @SpID) WITH NOWAIT;
    WAITFOR DELAY '00:00:30'
END
private string _spID;

private string RunCommentTestWaitForSP()
{
   _spID = "";

   try
   {
      using (SqlConnection conn = new SqlConnection(connStr))
      {
         using (SqlCommand cmd = new SqlCommand("CommentTest", conn))
         {
            cmd.CommandType = CommandType.StoredProcedure;

            if (conn.State == ConnectionState.Closed)
            {
               conn.Open();
            }

            // this is where I'm stuck...
            cmd.ExecuteNonQuery();
            _spID = "The message returned from RAISERROR...";
         }
      }
   }
   catch (Exception ex)
   {
      log.Error("RunCommentTestWaitForSP exception: " + ex.ToString() + " / " + ex.Message);
      log.Error("RunCommentTestWaitForSP stack trace: " + ex.StackTrace);
   }

   return spID;
}

private void LinkButtonCancelProcess_Click(object sender, EventArgs e)
{
    RunCommentTestWaitForSP();
    if (_spID.Length > 0 && "IsNumeric") {
        "call function to run sp_KillSpID"
    }
}
CREATE PROCEDURE [dbo].[sp_KillSpID]
    @SpID varchar(5)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @KillStatement varchar(10) = 'KILL ' + @SpID;
    EXEC(@KillStatement);
END

Примечание: вы не должны использовать префикс sp_ для своих хранимых процедур. Microsoft зарезервировала этот префикс для собственного использования (см. Именование хранимых процедур) , и вы рискуете столкнуться с конфликтом имен в будущем. Это также плохо сказывается на производительности ваших хранимых процедур. Лучше просто избегать sp_ и использовать что-то другое в качестве префикса или вообще не использовать префикс!

marc_s 31.01.2023 22:15

Хорошая мысль: использование этого префикса — плохая привычка, которая появилась, когда я присоединился к моей нынешней команде. Спасибо!

fourells5 01.02.2023 01:08
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
0
2
67
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вам нужно обработать событие InfoMessage в объекте SqlConnection:

private string RunCommentTestWaitForSP()
{
    try 
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        using (SqlCommand cmd = new SqlCommand("CommentTest", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure; 
            conn.InfoMessage += ReceiveSQLInfo;
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
   catch (Exception ex)
   {
      log.Error($"RunCommentTestWaitForSP exception: {ex} / {ex.Message}");
      log.Error($"RunCommentTestWaitForSP stack trace: {ex.StackTrace}");
   }
}

private void ReceiveSQLInfo(object sender, SqlInfoMessageEventArgs e)
{
    //sender will be the "conn" connection above
    var spID = e.Message;
  
    // Here you could open a new connection 
    //   (we expect the existing connection is still busy)
    //    and use it to kill the spID
}

Просто имейте в виду, что функция ExecuteNonQuery() не завершится, пока не завершится хранимая процедура, то есть только после завершения кода WAIT FOR DELAY. Поэтому метод LinkButtonCancelProcess_Click() также не будет делать то, что вы ожидаете, поскольку блок if () не будет достигнут до тех пор, пока хранимая процедура не будет завершена в любом случае.

Кроме того, в зависимости от того, как вызывается C# RunCommentTestWaitForSP(), компьютер может быть не в состоянии обрабатывать какие-либо сообщения о событиях, пока он не завершится.

Это полезно понять, мне было интересно, есть ли в отношениях между вызываемым SP и вызывающим кодом асинхронный хук при использовании RAISERROR. Возможно, мне нужно выбрать другую дорожку внутри кода кнопки отмены и вызвать sp_who, чтобы получить список и (каким-то образом) определить, какой процесс я должен убить.

fourells5 31.01.2023 22:07

Обратите внимание, что // we expect the existing connection is still busy также потенциально очень опасен. SQL Server не принимает никаких специальных мер предосторожности, чтобы предотвратить быстрое повторное использование идентификаторов SPID, поэтому, если время пойдет не так, и в зависимости от прав вашей учетной записи, вы можете в конечном итоге убить невинный процесс. @fourells5: рассмотрите возможность предоставления экземпляра SqlCommand и вызова .Cancel() на нем (это можно сделать из другого потока). Это по-прежнему потенциально включает проблемы с синхронизацией, но, по крайней мере, их можно исправить. .Cancel() не гарантируется быстрая работа, но и KILL тоже.

Jeroen Mostert 31.01.2023 22:22

Другие вопросы по теме