c# - 来自 SQLCLR 的异步 API 调用中的问题

问题描述

简而言之，当特定表发生更改时，我需要从 SQL Server 异步通知 Web API 服务。

为了实现上述目的，我创建了一个 SQLCLR 存储过程，其中包含用于通知服务的异步 API 调用。SQLCLR 存储过程通过触发器调用，当有插入到名为Table1. 这里的主要挑战是 API 必须从同一个表中读取数据 ( Table1)。

如果我使用HttpWebRequest.GetResponse()的是同步版本，由于插入触发器的隐式锁定，整个操作将被锁定。为了避免这种情况，我使用HttpWebRequest.GetResponseAsync()了调用 API 并且不等待响应的方法。因此它会触发 API 请求并且程序控制继续进行，因此触发器事务不会持有任何锁，table1并且 API 能够从table1.

现在我必须在出现故障时（例如无法连接到远程服务器）实现错误通知机制，并且我需要向管理团队发送电子邮件。我已经在catch()块内编写了邮件组合逻辑。如果我继续上述HttpWebRequest.GetResponseAsync().Result方法，整个操作将变为同步并锁定整个操作。

如果我使用 Microsoft 文档中建议的BeginGetResponse()andEndGetResponse()方法实现并运行 SQLCLR 存储过程，SQL Server 在没有任何信息的情况下挂起，为什么？我在这里做错了什么？为什么RespCallback()方法没有被执行？

分享下面的 SQLCLR 代码片段。

public class RequestState
{
    // This class stores the State of the request.
    // const int BUFFER_SIZE = 1024;
    // public StringBuilder requestData;
    // public byte[] BufferRead;
    public HttpWebRequest request;
    public HttpWebResponse response;
    // public Stream streamResponse;

    public RequestState()
    {
        // BufferRead = new byte[BUFFER_SIZE];
        // requestData = new StringBuilder("");
        request = null;
        // streamResponse = null;
    }
}

public partial class StoredProcedures
{
    private static SqlString _mailServer = null;
    private static SqlString _port = null;
    private static SqlString _fromAddress = null;
    private static SqlString _toAddress = null;
    private static SqlString _mailAcctUserName = null;
    private static SqlString _decryptedPassword = null;
    private static SqlString _subject = null;

    private static string _mailContent = null;
    private static int _portNo = 0;

    public static ManualResetEvent allDone = new ManualResetEvent(false);
    const int DefaultTimeout = 20000; // 50 seconds timeout

#region TimeOutCallBack
/// <summary>
/// Abort the request if the timer fires.
/// </summary>
/// <param name="state">request state</param>
/// <param name="timedOut">timeout status</param>
private static void TimeoutCallback(object state, bool timedOut)
{
if (timedOut)
{
HttpWebRequest request = state as HttpWebRequest;
if (request != null)
{
request.Abort();
SendNotifyErrorEmail(null, "The request got timedOut!,please check the API");
}
}
}
#endregion

#region APINotification
[SqlProcedure]
public static void Notify(SqlString weburl, SqlString username, SqlString password, SqlString connectionLimit, SqlString mailServer, SqlString port, SqlString fromAddress
, SqlString toAddress, SqlString mailAcctUserName, SqlString mailAcctPassword, SqlString subject)
{
_mailServer = mailServer;
_port = port;
_fromAddress = fromAddress;
_toAddress = toAddress;
_mailAcctUserName = mailAcctUserName;
_decryptedPassword = mailAcctPassword;
_subject = subject;

if (!(weburl.IsNull && username.IsNull && password.IsNull && connectionLimit.IsNull))
{
var url = Convert.ToString(weburl);
var uname = Convert.ToString(username);
var pass = Convert.ToString(password);
var connLimit = Convert.ToString(connectionLimit);
int conLimit = Convert.ToInt32(connLimit);
try
{
if (!(string.IsNullOrEmpty(url) && string.IsNullOrEmpty(uname) && string.IsNullOrEmpty(pass) && conLimit > 0))
{
SqlContext.Pipe.Send("Entered inside the notify method");

HttpWebRequest httpWebRequest = WebRequest.Create(url) as HttpWebRequest;
string encoded = Convert.ToBase64String(Encoding.GetEncoding("ISO-8859-1").GetBytes(uname + ":" + pass));
httpWebRequest.Headers.Add("Authorization", "Basic " + encoded);
httpWebRequest.Method = "POST";
httpWebRequest.ContentLength = 0;
httpWebRequest.ServicePoint.ConnectionLimit = conLimit;

// Create an instance of the RequestState and assign the previous myHttpWebRequest
// object to its request field. 
RequestState requestState = new RequestState();
requestState.request = httpWebRequest;

SqlContext.Pipe.Send("before sending the notification");
//Start the asynchronous request.
IAsyncResult result =
(IAsyncResult)httpWebRequest.BeginGetResponse(new AsyncCallback(RespCallback), requestState);
SqlContext.Pipe.Send("after BeginGetResponse");

// this line implements the timeout, if there is a timeout, the callback fires and the request becomes aborted
ThreadPool.RegisterWaitForSingleObject(result.AsyncWaitHandle, new WaitOrTimerCallback(TimeoutCallback), requestState, DefaultTimeout, true);
//SqlContext.Pipe.Send("after RegisterWaitForSingleObject");

// The response came in the allowed time. The work processing will happen in the 
// callback function.
allDone.WaitOne();
//SqlContext.Pipe.Send("after allDone.WaitOne();");

// Release the HttpWebResponse resource.
requestState.response.Close();
SqlContext.Pipe.Send("after requestState.response.Close()");
}
}
catch (Exception exception)
{
SqlContext.Pipe.Send(" Main Exception");
SqlContext.Pipe.Send(exception.Message.ToString());
//TODO: log the details in a error table
SendNotifyErrorEmail(exception, null);
}
}
}
#endregion

#region ResposnseCallBack
/// <summary>
/// asynchronous Httpresponse callback
/// </summary>
/// <param name="asynchronousResult"></param>
private static void RespCallback(IAsyncResult asynchronousResult)
{
try
{
SqlContext.Pipe.Send("Entering the respcallback");
// State of request is asynchronous.
RequestState httpRequestState = (RequestState)asynchronousResult.AsyncState;
HttpWebRequest currentHttpWebRequest = httpRequestState.request;
httpRequestState.response = (HttpWebResponse)currentHttpWebRequest.EndGetResponse(asynchronousResult);
SqlContext.Pipe.Send("exiting the respcallBack");
}
catch (Exception ex)
{
SqlContext.Pipe.Send("exception in the respcallBack");
SendNotifyErrorEmail(ex, null);
}
allDone.Set();
}
#endregion
}

上面的另一种方法是使用 SQL Server Service Broker，它具有排队机制，可以帮助我们实现异步触发器。但是对于上述情况，我们有什么解决办法吗？从方法的角度来看，我做错了什么吗？请指导我。

标签： c#.netsql-serverasync-awaitsqlclr

解决方案

有几件事情可能是突出的问题：

在收到响应之前不会调用allDone.WaitOne();阻塞，否定所有这些异步东西的需要/使用？
即使这确实有效，您是否在单个会话中进行测试？您有几个静态成员（类级别）变量，例如public static ManualResetEvent allDone，在所有会话中共享。SQLCLR 使用共享的应用程序域（应用程序域是每个数据库/每个程序集所有者）。因此，多个会话将覆盖彼此的这些共享静态变量的值。这是非常危险的（因此为什么非只读静态变量只允许在程序集中UNSAFE）。这个模型只有在你可以保证在任何给定时刻都有一个调用者的情况下才有效。

除了任何 SQLCLR 技术细节之外，即使您确实设法克服了这个特定问题，我也不确定这是一个好的模型。

一个更好、更安全的模型是：

创建一个队列表来记录这些更改。您通常只需要键列和时间戳（DATETIME或DATETIME2，不是TIMESTAMP数据类型）。
让触发器记录当前时间和修改到队列表的行
创建一个存储过程，从队列中获取项目，从最旧的记录开始，处理它们（这绝对可以调用你的 SQLCLR 存储过程来执行 Web 服务器调用，但不需要它是异步的，所以删除那些东西和将大会设置回，EXTERNAL_ACCESS因为你不需要/想要UNSAFE）。

在事务中执行此操作，以便在“处理”失败时不会从队列表中完全删除记录。有时使用OUTPUTwith 子句DELETE将您正在处理的行保留到本地临时表中会有所帮助。

一次处理多条记录，即使调用 SQLCLR 存储过程需要在每行的基础上完成。
创建一个 SQL Server 代理作业以每分钟执行一次存储过程（或更少，具体取决于需要）

小问题：

将输入参数复制到静态变量（例如_mailServer = mailServer;）的模式充其量是毫无意义的，而且由于不是线程安全的，所以容易出错。请记住，静态变量在所有会话之间共享，因此任何并发会话都将覆盖以前的值，从而确保竞争条件。请删除名称以下划线开头的所有变量。
使用模式Convert.To...也是不必要的，对性能有轻微影响。所有Sql*类型都有一个Value返回预期 .NET 类型的属性。因此，您只需要：string url = weburl.Value;
无需使用稍后需要转换的错误数据类型。意思是，而不是使用SqlStringfor connectionLimit，而是使用SqlInt32然后你可以简单地做int connLimit = connectionLimit.Value;
您可能不需要手动执行安全性（即httpWebRequest.Headers.Add("Authorization", "Basic " + encoded);）。我很确定您可以使用unameand创建一个新的 NetworkCredential 并将pass其分配给请求。

c# - 来自 SQLCLR 的异步 API 调用中的问题

问题描述

解决方案

推荐阅读