使用回调的 Windows 应用程序

在大多数异步处理方案中,你希望启动数据库操作并继续运行其他进程,而无需等待数据库操作完成。 但是,许可场景需要在数据库操作结束后执行一些操作。 例如,在 Windows 应用程序中,你可能希望将长时间运行的操作委托给后台线程,同时允许用户界面线程保持响应。 但是,在数据库操作完成后,你需要使用结果来填充窗体。 这种类型的场景最好通过回调实现。

通过在 BeginExecuteNonQueryBeginExecuteReaderBeginExecuteXmlReader 方法中指定 AsyncCallback 委托来定义回调。 操作完成后,将调用该委托。 可以向委托传递对 SqlCommand 本身的引用,这样就可以轻松访问 SqlCommand 对象并调用适当的 End 方法,而无需使用全局变量。

示例

以下 Windows 应用程序演示如何使用 BeginExecuteNonQuery 方法,以执行包含几秒钟延迟(模拟长时间运行的命令)的 Transact-SQL 语句。

此示例演示了许多重要的方法,包括调用从单独线程与窗体进行交互的方法。 此外,此示例还演示了如何阻止用户多次并发执行命令,以及如何确保在调用回调过程之前窗体不会关闭。

若要设置此示例,请创建新的 Windows 应用程序。 将一个 Button 控件和两个 Label 控件置于窗体(接受每个控件的默认名称)。 将以下代码添加到窗体的类中,根据环境的需要修改连接字符串。

' Add these to the top of the class:
Imports System
Imports System.Data
Imports System.Data.SqlClient

' Add this code to the form's class:

    ' You'll need this delegate in order to display text from a
    ' thread other than the form's thread. See the HandleCallback
    ' procedure for more information.
    ' This same delegate matches both the DisplayStatus
    ' and DisplayResults methods.
    Private Delegate Sub DisplayInfoDelegate(ByVal Text As String)

    ' This flag ensures that the user doesn't attempt
    ' to restart the command or close the form while the
    ' asynchronous command is executing.
    Private isExecuting As Boolean

    ' This example maintains the connection object
    ' externally, so that it's available for closing.
    Private connection As SqlConnection

    Private Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,
        ' you can retrieve it from a configuration file.

        ' If you have not included "Asynchronous Processing=true"
        ' in the connection string, the command will not be able
        ' to execute asynchronously.
        Return "..." & _
          "Asynchronous Processing=true"
    End Function

    Private Sub DisplayStatus(ByVal Text As String)
        Me.Label1.Text = Text
    End Sub

    Private Sub DisplayResults(ByVal Text As String)
        Me.Label1.Text = Text
        DisplayStatus("Ready")
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.FormClosingEventArgs) _
        Handles Me.FormClosing
        If isExecuting Then
            MessageBox.Show(Me, "Can't close the form until " & _
             "the pending asynchronous command has completed. " & _
             "Please wait...")
            e.Cancel = True
        End If
    End Sub

    Private Sub Button1_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
        If isExecuting Then
            MessageBox.Show(Me, _
                "Already executing. " & _
                "Please wait until the current query " & _
                "has completed.")
        Else
            Dim command As SqlCommand
            Try
                DisplayResults("")
                DisplayStatus("Connecting...")
                connection = New SqlConnection(GetConnectionString())
                ' To emulate a long-running query, wait for
                ' a few seconds before working with the data.
                ' This command doesn't do much, but that's the point--
                ' it doesn't change your data, in the long run.
                Dim commandText As String = _
                    "WAITFOR DELAY '0:0:05';" & _
                    "UPDATE Production.Product " & _
                    "SET ReorderPoint = ReorderPoint + 1 " & _
                    "WHERE ReorderPoint Is Not Null;" & _
                    "UPDATE Production.Product " & _
                    "SET ReorderPoint = ReorderPoint - 1 " & _
                    "WHERE ReorderPoint Is Not Null"

                command = New SqlCommand(commandText, connection)
                connection.Open()

                DisplayStatus("Executing...")
                isExecuting = True
                ' Although it's not required that you pass the
                ' SqlCommand object as the second parameter in the
                ' BeginExecuteNonQuery call, doing so makes it easier
                ' to call EndExecuteNonQuery in the callback procedure.
                Dim callback As New _
                      AsyncCallback(AddressOf HandleCallback)

                ' Once the BeginExecuteNonQuery method is called,
                ' the code continues--and the user can interact with
                ' the form--while the server executes the query.

                command.BeginExecuteNonQuery(callback, command)

            Catch ex As Exception
                isExecuting = False
                DisplayStatus($"Ready (last error: {ex.Message})")
                If connection IsNot Nothing Then
                    connection.Close()
                End If
            End Try
        End If
    End Sub

    Private Sub HandleCallback(ByVal result As IAsyncResult)
        Try
            ' Retrieve the original command object, passed
            ' to this procedure in the AsyncState property
            ' of the IAsyncResult parameter.
            Dim command As SqlCommand = _
                CType(result.AsyncState, SqlCommand)
            Dim rowCount As Integer = _
                command.EndExecuteNonQuery(result)
            Dim rowText As String = " rows affected."
            If rowCount = 1 Then
                rowText = " row affected."
            End If
            rowText = rowCount & rowText

            ' You may not interact with the form and its contents
            ' from a different thread, and this callback procedure
            ' is all but guaranteed to be running from a different
            ' thread than the form. Therefore you cannot simply call
            ' code that displays the results, like this:
            ' DisplayResults(rowText)

            ' Instead, you must call the procedure from the form's
            ' thread. One simple way to accomplish this is to call
            ' the Invoke method of the form, which calls the delegate
            ' you supply from the form's thread.
            Dim del As New _
                DisplayInfoDelegate(AddressOf DisplayResults)
            Me.Invoke(del, rowText)

        Catch ex As Exception
            ' Because you're now running code in a separate thread,
            ' if you don't handle the exception here, none of your
            ' other code will catch the exception. Because none of
            ' your code is on the call stack in this thread, there's
            ' nothing higher up the stack to catch the exception if
            ' you don't handle it here. You can either log the
            ' exception or invoke a delegate (as in the non-error
            ' case in this example) to display the error on the form.
            ' In no case can you simply display the error without
            ' executing a delegate as in the Try block here.

            ' You can create the delegate instance as you
            ' invoke it, like this:
            Me.Invoke(New _
                DisplayInfoDelegate(AddressOf DisplayStatus), _
                $"Ready (last error: {ex.Message}")
        Finally
            isExecuting = False
            If connection IsNot Nothing Then
                connection.Close()
            End If
        End Try
    End Sub
// Add these to the top of the class, if they're not already there:
using System;
using System.Data;
using System.Data.SqlClient;

// Hook up the form's Load event handler (you can double-click on
// the form's design surface in Visual Studio), and then add
// this code to the form's class:

// You'll need this delegate in order to display text from a thread
// other than the form's thread. See the HandleCallback
// procedure for more information.
// This same delegate matches both the DisplayStatus
// and DisplayResults methods.
private delegate void DisplayInfoDelegate(string Text);

// This flag ensures that the user doesn't attempt
// to restart the command or close the form while the
// asynchronous command is executing.
private bool isExecuting;

// This example maintains the connection object
// externally, so that it's available for closing.
private SqlConnection connection;

private static string GetConnectionString()
{
    // To avoid storing the connection string in your code,
    // you can retrieve it from a configuration file.

    // If you have not included "Asynchronous Processing=true" in the
    // connection string, the command will not be able
    // to execute asynchronously.
    return "..." + "Asynchronous Processing=true";
}

private void DisplayStatus(string Text)
{
    this.label1.Text = Text;
}

private void DisplayResults(string Text)
{
    this.label1.Text = Text;
    DisplayStatus("Ready");
}

private void Form1_FormClosing(object sender, System.Windows.Forms.FormClosingEventArgs e)
{
    if (isExecuting)
    {
        MessageBox.Show(this, "Can't close the form until " +
        "the pending asynchronous command has completed. Please " +
        "wait...");
        e.Cancel = true;
    }
}

private void button1_Click(object sender, System.EventArgs e)
{
    if (isExecuting)
    {
        MessageBox.Show(this, "Already executing. Please wait until " +
        "the current query has completed.");
    }
    else
    {
        SqlCommand command = null;
        try
        {
            DisplayResults("");
            DisplayStatus("Connecting...");
            connection = new SqlConnection(GetConnectionString());
            // To emulate a long-running query, wait for
            // a few seconds before working with the data.
            // This command doesn't do much, but that's the point--
            // it doesn't change your data, in the long run.
            string commandText =
                "WAITFOR DELAY '0:0:05';" +
                "UPDATE Production.Product " +
                "SET ReorderPoint = ReorderPoint + 1 " +
                "WHERE ReorderPoint Is Not Null;" +
                "UPDATE Production.Product " +
                "SET ReorderPoint = ReorderPoint - 1 " +
                "WHERE ReorderPoint Is Not Null";

            command = new SqlCommand(commandText, connection);
            connection.Open();

            DisplayStatus("Executing...");
            isExecuting = true;
            // Although it's not required that you pass the
            // SqlCommand object as the second parameter in the
            // BeginExecuteNonQuery call, doing so makes it easier
            // to call EndExecuteNonQuery in the callback procedure.
            AsyncCallback callback = new AsyncCallback(HandleCallback);

            // Once the BeginExecuteNonQuery method is called,
            // the code continues--and the user can interact with
            // the form--while the server executes the query.
            command.BeginExecuteNonQuery(callback, command);

        }
        catch (Exception ex)
        {
            isExecuting = false;
            DisplayStatus($"Ready (last error: {ex.Message})");
            if (connection != null)
            {
                connection.Close();
            }
        }
    }
}

private void HandleCallback(IAsyncResult result)
{
    try
    {
        // Retrieve the original command object, passed
        // to this procedure in the AsyncState property
        // of the IAsyncResult parameter.
        SqlCommand command = (SqlCommand)result.AsyncState;
        int rowCount = command.EndExecuteNonQuery(result);
        string rowText = " rows affected.";
        if (rowCount == 1)
        {
            rowText = " row affected.";
        }
        rowText = rowCount + rowText;

        // You may not interact with the form and its contents
        // from a different thread, and this callback procedure
        // is all but guaranteed to be running from a different thread
        // than the form. Therefore you cannot simply call code that
        // displays the results, like this:
        // DisplayResults(rowText)

        // Instead, you must call the procedure from the form's thread.
        // One simple way to accomplish this is to call the Invoke
        // method of the form, which calls the delegate you supply
        // from the form's thread.
        DisplayInfoDelegate del =
         new DisplayInfoDelegate(DisplayResults);
        this.Invoke(del, rowText);
    }
    catch (Exception ex)
    {
        // Because you're now running code in a separate thread,
        // if you don't handle the exception here, none of your other
        // code will catch the exception. Because none of your
        // code is on the call stack in this thread, there's nothing
        // higher up the stack to catch the exception if you don't
        // handle it here. You can either log the exception or
        // invoke a delegate (as in the non-error case in this
        // example) to display the error on the form. In no case
        // can you simply display the error without executing a
        // delegate as in the try block here.

        // You can create the delegate instance as you
        // invoke it, like this:
        this.Invoke(new DisplayInfoDelegate(DisplayStatus),
            $"Ready (last error: {ex.Message}");
    }
    finally
    {
        isExecuting = false;
        if (connection != null)
        {
            connection.Close();
        }
    }
}

private void Form1_Load(object sender, System.EventArgs e)
{
    this.button1.Click += new System.EventHandler(this.button1_Click);
    this.FormClosing += new System.Windows.Forms.
        FormClosingEventHandler(this.Form1_FormClosing);
}

请参阅