异步操作 BeginExecuteNonQuery

问题  

有时候我们需要在SQL Server数据库上执行异步操作，即在后台任务中执行该操作，主程序则可以执行其它操作。

解决方案

使用SqlCommand类的BeginExecuteNonQuery、BeginExecuteReader或BeginExecuteXmlReader方法开始执行一个后台数据库操作。这些方法都会返回一个System.IAsyncResult对象，我们可以用它来获取操作的状态或使用同步线程等待该操作完成。使用IAsyncResult对象及SqlCommand相应的EndExecuteNonQuery、EndExecuteReader或EndExecuteXmlReader方法来获取操作的结果。

SqlCommand.BeginExecuteNonQuery 方法
启动此 SqlCommand 描述的 Transact-SQL 语句或存储过程的异步执行。

SqlCommand.BeginExecuteNonQuery 方法 (AsyncCallback, Object)

如果提供回调过程和状态信息，则启动此 SqlCommand 描述的 Transact-SQL 语句或存储过程的异步执行。

SqlCommand.EndExecuteNonQuery 方法
完成 Transact-SQL 语句的异步执行。

注意：只有SqlCommand类支持这里所将的异步操作，与其等价的Oracle、OleDb等Data Provider的Command类没有提供这种功能。

原理

通常我们都需要执行同步的数据库操作，即调用代码会一直等待这些操作完成。这是因为我们通常都会用到数据库操作的结果。但有些时候，异步数据库操作也很有用。

注意：要对一个SqlConnection连接执行异步操作，需要在其连接字符串中添加如下属性：Asynchronous Processing=true。

BeginExecuteNonQuery、BeginExecuteReader以及BeginExecuteXmlReader的参数可以跟其相应的同步操作方法ExecuteNonQuery、ExecuteReader、ExecuteXmlReader相同，同时它们还提供了重载方法接受两个额外的参数以支持异步操作：

1. 一个System.AsyncCallBack类型的委托，操作完成后会调用委托指向的方法。如果该委托为null，那就要使用另一种机制来判断异步操作何时完成了；
2. 一个object对象引用，运行时通过它与异步操作建立联系。异步操作不能访问这个对象，但我们的代码却可以在操作完成时访问它，这样就可以将异步操作与有用的状态信息联系在一起。

当心： 在异步操作执行过程中，必须确保我们使用的对象不能被不经意地释放掉。尤其要注意SqlConnection和SqlCommand对象。

示例代码 ： 

代码中使用的数据库是Northwind，演示了上述技术的基本用法。

class  Program
{
     public   static   void  CallbackHandler(IAsyncResult result)
    {
         using  (SqlCommand cmd  =  result.AsyncState  as  SqlCommand)
        {
             using  (SqlDataReader reader  =  cmd.EndExecuteReader(result))
            {
                 lock  (Console.Out)
                {
                    Console.WriteLine( " Price of the The Most Expensive Products: " );

                     while  (reader.Read())
                    {
                        Console.WriteLine( "   {0} = {1} " , reader[ " TenMostExpensiveProducts " ], reader[ " UnitPrice " ]);
                    }
                }
            }
        }
    }

     static   void  Main( string [] args)
    {
         using  (SqlConnection conn  =   new  SqlConnection())
        {
            conn.ConnectionString  =   @" server=(local);database=Northwind;uid=sa;Asynchronous Processing=true " ;
            SqlCommand cmd  =  conn.CreateCommand();
            cmd.CommandType  =  CommandType.StoredProcedure;
            cmd.CommandText  =   " Ten Most Expensive Products " ;

            conn.Open();
            cmd.BeginExecuteReader(CallbackHandler, cmd);

             for  ( int  count  =   0 ; count  <   10 ; count ++ )
            {
                 lock  (Console.Out)
                {
                    Console.WriteLine( " {0} : Continue processing " , DateTime.Now.ToString( " HH:mm:ss.ffff " ));
                }
                Thread.Sleep( 400 );
            }
        }

        Console.WriteLine();
        Console.ReadLine();
    }
}

 

下面的 Windows 应用程序演示 BeginExecuteNonQuery 方法的用法，并执行一个包含几秒钟延迟的 Transact-SQL 语句（模拟长时间运行的命令）。
该示例演示了许多重要的技术。 其中包括从单独的线程中调用与窗体进行交互的方法。 此外，该示例还演示了必须如何阻止用户并发地执行同一个命令，以及必须如何确保窗体不会在调用回调过程之前关闭。

using System.Data.SqlClient;

namespace Microsoft.AdoDotNet.CodeSamples
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        // 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:
        private void Form1_Load(object sender, EventArgs e)
        {
            this.button1.Click += new System.EventHandler(this.button1_Click);
            this.FormClosing += new System.Windows.Forms.
                FormClosingEventHandler(this.Form1_FormClosing);

        }

        // You 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 does not 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 is 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 is not able
            // to execute asynchronously.
            return "Data Source=(local);Integrated Security=true;" +
                "Initial Catalog=AdventureWorks; 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, "Cannot 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 does not do much, but that's the point--
                    // it does not 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 is 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);
                    command.BeginExecuteNonQuery(callback, command);

                }
                catch (Exception ex)
                {
                    isExecuting = false;
                    DisplayStatus(string.Format("Ready (last error: {0})", 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 are now running code in a separate thread, 
                // if you do not handle the exception here, none of your other
                // code catches the exception. Because none of 
                // your code is on the call stack in this thread, there is nothing
                // higher up the stack to catch the exception if you do not 
                // 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),
                    String.Format("Ready(last error: {0}", ex.Message));
            }
            finally
            {
                isExecuting = false;
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
    }
}

下面的控制台应用程序在 AdventureWorks 示例数据库中异步创建更新数据。 为了模拟长时间运行的进程，此示例在命令文本中插入 WAITFOR 语句。 通常，您不必特意减慢命令的运行速度，但如果在这种情况下减慢运行速度，会使演示异步行为变得更加容易。

using System.Data.SqlClient;

class Class1
{
    static void Main()
    {
        // This is a simple example that demonstrates the usage of the 
        // BeginExecuteNonQuery functionality.
        // The WAITFOR statement simply adds enough time to prove the 
        // asynchronous nature of the command.

        string commandText = 
            "UPDATE Production.Product SET ReorderPoint = ReorderPoint + 1 " + 
            "WHERE ReorderPoint Is Not Null;" + 
            "WAITFOR DELAY '0:0:3';" + 
            "UPDATE Production.Product SET ReorderPoint = ReorderPoint - 1 " + 
            "WHERE ReorderPoint Is Not Null";

        RunCommandAsynchronously(commandText, GetConnectionString());

        Console.WriteLine("Press ENTER to continue.");
        Console.ReadLine();
    }

    private static void RunCommandAsynchronously(
        string commandText, string connectionString)
    {
        // Given command text and connection string, asynchronously execute
        // the specified command against the connection. For this example,
        // the code displays an indicator as it is working, verifying the 
        // asynchronous behavior. 
        using (SqlConnection connection = 
                   new SqlConnection(connectionString))
        {
            try
            {
                int count = 0;
                SqlCommand command = new SqlCommand(commandText, connection);
                connection.Open();

                IAsyncResult result = command.BeginExecuteNonQuery();
                while (!result.IsCompleted)
                {
                    Console.WriteLine("Waiting ({0})", count++);
                    // Wait for 1/10 second, so the counter
                    // does not consume all available resources 
                    // on the main thread.
                    System.Threading.Thread.Sleep(100);
                }
                Console.WriteLine("Command complete. Affected {0} rows.", 
                    command.EndExecuteNonQuery(result));
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message);
            }
            catch (InvalidOperationException ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
            catch (Exception ex)
            {
                // You might want to pass these errors
                // back out to the caller.
                Console.WriteLine("Error: {0}", ex.Message);
            }
        }
    }

    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 is not able
        // to execute asynchronously.
        return "Data Source=(local);Integrated Security=SSPI;" +
            "Initial Catalog=AdventureWorks; Asynchronous Processing=true";
    } 
}