有时候我们需要在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相同,同时它们还提供了重载方法接受两个额外的参数以支持异步操作:
- 一个System.AsyncCallBack类型的委托,操作完成后会调用委托指向的方法。如果该委托为null,那就要使用另一种机制来判断异步操作何时完成了;
- 一个object对象引用,运行时通过它与异步操作建立联系。异步操作不能访问这个对象,但我们的代码却可以在操作完成时访问它,这样就可以将异步操作与有用的状态信息联系在一起。
当心: 在异步操作执行过程中,必须确保我们使用的对象不能被不经意地释放掉。尤其要注意SqlConnection和SqlCommand对象。
示例代码 :代码中使用的数据库是Northwind,演示了上述技术的基本用法。
{
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"; } }