异步执行SQL

参考文档： SqlCommand.BeginExecuteNonQuery 方法 (AsyncCallback, Object)

有些操作， 不是那么重要， 但偏偏运行时间比较长， 这种就非常影响用户体验。

比如说： 

A：用户发表了一篇文章， 这个操作当然重要， 需要立即知道是否成功。

B：将这个用户发表了新文章的消息通知给庞大的用户群， 就并不是那么重要和紧急， 但比较消耗时间。

A适合同步， 而B适合异步。

异步可以用多线程实现， 也可以用异步方法。

在 C# 中针对SQL Server 操作有专门的异步方法： BeginExecuteNonQuery 

下面是 demo :

一、在SQL Server中创建测试表及数据：

--其它库也行，没什么关系
USE tempdb
GO
IF OBJECT_ID('dbo.TestAsync') IS NOT NULL
DROP TABLE dbo.TestAsync
GO
CREATE TABLE dbo.TestAsync(
	isAsync BIT NOT NULL PRIMARY KEY,
	cnt INT NOT NULL
)
GO
INSERT INTO dbo.TestAsync(isAsync,cnt) VALUES(0,0)
INSERT INTO dbo.TestAsync(isAsync,cnt) VALUES(1,0)

SELECT * FROM dbo.TestAsync
/*
isAsync	cnt
0	    0
1	    0
*/

二、C# 测试代码：

using System;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        //连接串。注意用异步必须加上：Asynchronous Processing=true
        static readonly string connString = @"data source=(local)\sqlserver2014;initial catalog=tempdb;Integrated Security=True;"
            +"Asynchronous Processing=true";

        static void Main(string[] args)
        {
            string sql = 
@"WAITFOR DELAY '00:00:03'
UPDATE dbo.TestAsync SET cnt = cnt + 1 WHERE isAsync=@isAsync";

            Stopwatch watcher = new Stopwatch();  
            watcher.Start();
            Exec(sql);
            Console.WriteLine("{0:HH:mm:ss} 同步方法消耗 {1} ms", DateTime.Now, watcher.ElapsedMilliseconds);
            watcher.Reset();
            watcher.Start();
            ExecAsync(sql);
            Console.WriteLine("{0:HH:mm:ss} 异步方法消耗 {1} ms", DateTime.Now, watcher.ElapsedMilliseconds);

            Console.Read();
        }

        /// <summary>
        /// 同步执行
        /// </summary>
        /// <param name="sql"></param>
        private static void Exec(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@isAsync", false);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 异步执行
        /// </summary>
        /// <param name="sql"></param>
        private static void ExecAsync(string sql)
        {
            //注意不能写 using, 也不能在 finally 中关闭连接
            //连接的关闭只能在 AsyncCallback 异步调用方法中完成
            //但在 catch 中必须有关闭操作，防止万一
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddWithValue("@isAsync", true);
                AsyncCallback callback = new AsyncCallback(HandleCallback);
                command.BeginExecuteNonQuery(callback, command);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                if (conn != null)
                    conn.Close();
            }
        }

        /// <summary>
        /// 异步回调方法
        /// </summary>
        /// <param name="result"></param>
        private static void HandleCallback(IAsyncResult result)
        {
            SqlCommand command = (SqlCommand)result.AsyncState;
            try
            {
                int rowCount = command.EndExecuteNonQuery(result);
                Console.WriteLine("{0:HH:mm:ss} 异步方法完成", DateTime.Now);
                PrintInto();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error :{0}", ex.Message);
            }
            finally 
            {
                if(command.Connection!=null)
                    command.Connection.Close();
            }
        }

        /// <summary>
        /// 输出表内容
        /// </summary>
        private static void PrintInto() 
        {
            using (SqlConnection conn = new SqlConnection(connString)) 
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select isAsync,cnt from dbo.TestAsync",conn);
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read()) 
                {
                    Console.WriteLine("isAsync:{0}\t cnt:{1}", sdr["isAsync"], sdr["cnt"]);
                }
            }
        }
    }//end of class
}//end of namespace

输出结果：

msdn 中的 demo 是 winform 中的， 写的也非常好， 贴在下面吧：

using System.Data.SqlClient;
using System.Windows.Forms;
using System;

namespace WindowsFormsApplication1
{
    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)\sqlserver2014;initial catalog=AdventureWorks2014;Integrated Security=True;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();
                }
            }
        }
    }
}