ADO.NET 异步 SQL 调用

• 下载源码 - 8.2 KB

引言

本文将介绍如何在 ADO.NET 中使用异步调用来接收长时间运行的存储过程调用或任何其他引发消息给客户端的 SQL 命令的进度消息。

我最初的想法是展示如何将 SQL BACKUP 语句的消息回显到客户端，但如果您没有足够大的数据库，您将不会注意到我即将展示的问题。执行存储过程和 BACKUP 命令之间没有真正的区别，它们都将信息消息作为错误（但它们不是错误）发送。据我所能检测到的，它们都使用 RAISERROR 和 NOWAIT 。我预计底层 BACKUP 有不同的方法来返回信息消息。对于存储过程，我们仅限于 RAISERROR 命令。

这没关系，因为调用中有一个严重性标志，任何低于十的严重性仅用于信息（请参阅 MSDN 文档）。因此，我们可以为我们的目的利用严重性 1。

为什么要这样做？

好问题！我通常将此用于一次性存储过程，这些过程会大量修改数据库、升级数据库或从客户端实用程序运行的 BACKUP ，其中它们期望某种形式的进度来表明它没有停止！

正在使用的存储过程

以下存储过程将在演示中使用

CREATE PROCEDURE AdoProcess_Test1
AS
BEGIN
	SET NOCOUNT ON 	-- Used for performance and making sure 
			-- we don't send back unneeded information.
	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 - 低于十为信息消息。
• State - 这是一个唯一数字，用于识别存储过程中 RAISERROR 被触发的位置。
• Arguments - 如果使用 printf 占位符，则为消息提供的可选参数。
• Options - 可选的选项，在我们的例子中，我们希望在引发消息后立即将其发送回去，因此使用了 NOWAIT 选项。如果需要，您可以将消息发送到事件日志！

该演示包含一些辅助函数，它们会自动连接到数据库并在 tempdb 中创建/删除存储过程。因此，您需要 tempdb 中的 CREATE/DROP PROCEDURE 权限。

连接字符串

连接字符串是整个设置中最重要的部分之一。在本演示中，我已将其硬编码在 C# 类中，但通常会将其放在应用程序/Web 配置文件中。

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

有关 SQL 连接中所有选项的完整列表，请参阅 MSDN 文档。我使用的选项的分解如下：

• Data Source - 这是您希望连接的服务器。我使用的是我自己的本地服务器。如果您在 Visual Studio 中安装了 SQL Express 版本，则需要将 (local) 更改为 (local)\SQLEXPRESS 实例名。
• Initial Catalog - 这是 SQL Server 中托管的实际数据库的名称。如果您在 tempdb 中没有 CREATE/DROP 权限，则将其更改为您拥有权限的数据库。
• Trusted_Connection - 这会告知您正在使用的 SQL 客户端软件使用您的 Windows 凭据进行单点登录。建议使用 SSPI 而不是 true。
• Asynchronous Processing - 要使 ADO.NET 执行异步处理（调用命令对象的 Begin 方法），此项必须为 true，否则在演示中会遇到 InvalidOperationException。

对于实际应用程序，我通常会在连接中添加“Application Name”和“Workshare Id”选项。这样，DBA 就可以知道是哪个应用程序/计算机导致了数据库问题（这有助于 DBA 保持友好！）。

设置异步处理

在本演示中，我创建了两个方法来展示如何做到这一点。它们之间唯一的区别是实际使用的 SQL 命令方法。

首先，我们需要一种方法来发出信号，表明存储过程已完成处理，我们可以继续执行我们的代码。为此，我们将使用 ManualResetEvent 类句柄。使用此类的原因是，一旦发出信号，我希望它保持信号状态。我已将其声明为类成员。

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; 	// Default of 30 seconds, 
			// only showing how to set no need to set it.

现在，我们需要告诉连接在收到来自数据库的消息时调用哪个方法。该方法的签名是 void <MethodName>(object sender, SqlInfoMessageEventArgs e)。

private static void ConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    if ( e.Errors.Count > 0 )
    {
        // Check to make sure we are information only messages
        Console.WriteLine("Received {0} messages", e.Errors.Count);
        foreach( SqlError info in e.Errors )
        {
            if ( info.Class > 9 ) // Severity
            {
                Console.WriteLine("Error Message : {0} : 
			State : {1}", info.Message, info.State );
            }
            else
            {
                Console.WriteLine("Info Message : {0} : 
			State : {1}", info.Message, info.State);
            }
        }
    }
    else
    {
        Console.WriteLine("Received Connection Info Message : {0}", e.Message);     
    }
}

正如您所见，我正在检查 SqlError 实例上的 Class 属性。这实际上是 RAISERROR 方法触发的严重性。State 属性的名称与 RAISERROR 调用中的 State 选项相同，为什么 ADO 团队不能保留 Severity 名称呢？SqlInfoMessageEventArgs.Message 是组合在一起的，如果存在错误集合，则以换行符作为分隔符。

现在我们有了需要将其连接到连接对象的方法。

cmd.Connection.InfoMessage += ConnectionInfoMessage;

因此，当异步操作完成时，我们需要一个回调方法来处理结果并将操作已完成的信号发送到应用程序的其余部分。我们需要创建一个支持 IAsyncResult 接口的方法，该方法的信号是 void <MethodName>(IAsyncResult result)。

由于我演示了非查询执行和读取器执行，因此我必须创建两个回调方法，一个用于每种使用的命令类型。它们的名称将是 NonQueryCallBack 和 ReaderCallBack。

private static void NonQueryCallBack(IAsyncResult result)
{
    SqlCommand command = (SqlCommand) result.AsyncState;
    try
    {
        if (command != null)
        {
            Console.WriteLine("Waiting for completion of the Async call");
            command.EndExecuteNonQuery(result);
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine("Problem with executing command! - [{0}]", ex.Message);
    }
    finally
    {
        Console.WriteLine
		("Completed call back so signal main thread to continue....");
        _reset.Set();
    }
}

当执行 command.EndExecuteNonQuery(result) 时，它将在回调线程上等待，直到命令完成或发生异常。无论哪种情况，当它完成时，我们都希望向主线程发出信号表明我们已完成，因此在 finally 块中，我们调用 ManualResetEvent 实例上的 Set 方法。

让我们将其连接并执行命令，方法是传递 SqlCommand 对象作为异步状态和回调方法。

AsyncCallback result = NonQueryCallBack;
cmd.Connection.Open();
cmd.BeginExecuteNonQuery(result, cmd);
Console.WriteLine("Waiting for completion of executing stored procedure....");
_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 数据库，而您可能没有访问权限。

历史

• 2009 年 9 月 10 日 - 初始发布