SqlCommand.BeginExecuteNonQuery 方法
定義
重要
部分資訊涉及發行前產品，在發行之前可能會有大幅修改。 Microsoft 對此處提供的資訊，不做任何明確或隱含的瑕疵擔保。
啟動非同步執行由此 SqlCommand描述的 Transact-SQL 語句或儲存程序。
多載
| 名稱 | Description |
|---|---|
| BeginExecuteNonQuery() |
啟動非同步執行由此 SqlCommand描述的 Transact-SQL 語句或儲存程序。 |
| BeginExecuteNonQuery(AsyncCallback, Object) |
在回調程序與狀態資訊下，啟動由此 SqlCommand描述的 Transact-SQL 語句或儲存程序的非同步執行。 |
BeginExecuteNonQuery()
啟動非同步執行由此 SqlCommand描述的 Transact-SQL 語句或儲存程序。
public:
IAsyncResult ^ BeginExecuteNonQuery();public IAsyncResult BeginExecuteNonQuery();member this.BeginExecuteNonQuery : unit -> IAsyncResultPublic Function BeginExecuteNonQuery () As IAsyncResult傳回
IAsyncResult可用來輪詢或等待結果，或兩者兼用;此值在呼叫EndExecuteNonQuery(IAsyncResult)時也很重要，該值會回傳受影響的列數。
例外狀況
當 設定為 Stream時，會使用除 Binary 或 VarBinary 以外的 。ValueSqlDbType 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
-或-
SqlDbType除了 Char、NChar、NVarChar、VarChar 或 Xml，當 Value 設定為 TextReader時，會使用其他 Char、NChar、NVarChar、VarChar 或 Xml。
-或-
名稱/值對「Asynchronous Processing=true」未包含在定義此 SqlCommand連線的連接字串中。
-或-
在 SqlConnection 串流操作中關閉或掉落。 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
在串流操作中，物件或 發生StreamXmlReaderTextReader錯誤。 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
Stream該 ， XmlReader 或TextReader物件在串流操作中被關閉。 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
範例
以下主控台應用程式會在 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";
}
}
Imports System.Data.SqlClient
Module Module1
Sub 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.
Dim commandText As String =
"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()
End Sub
Private Sub RunCommandAsynchronously(
ByVal commandText As String, ByVal connectionString As String)
' 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 connection As New SqlConnection(connectionString)
Try
Dim count As Integer = 0
Dim command As New SqlCommand(commandText, connection)
connection.Open()
Dim result As IAsyncResult = command.BeginExecuteNonQuery()
While Not 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.
Threading.Thread.Sleep(100)
count += 1
End While
Console.WriteLine("Command complete. Affected {0} rows.",
command.EndExecuteNonQuery(result))
Catch ex As SqlException
Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message)
Catch ex As InvalidOperationException
Console.WriteLine("Error: {0}", ex.Message)
Catch ex As Exception
' You might want to pass these errors
' back out to the caller.
Console.WriteLine("Error: {0}", ex.Message)
End Try
End Using
End Sub
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 is not able
' to execute asynchronously.
Return "Data Source=(local);Integrated Security=SSPI;" &
"Initial Catalog=AdventureWorks; Asynchronous Processing=true"
End Function
End Module
備註
此 BeginExecuteNonQuery 方法啟動非同步執行一個不回傳列的 Transact-SQL 陳述式或儲存程序的過程，讓其他任務能在該陳述式執行時同時執行。 當敘述完成後，開發者必須呼叫該 EndExecuteNonQuery 方法來完成該操作。 該 BeginExecuteNonQuery 方法會立即回傳，但在程式碼執行對應 EndExecuteNonQuery 的方法呼叫之前，不能執行任何對同一 SqlCommand 物件啟動同步或非同步執行的其他呼叫。 在指令執行完成前呼叫 ， EndExecuteNonQuery 物件會阻塞 SqlCommand 直到執行完成。
請注意，指令文字與參數是同步傳送到伺服器的。 若傳送大型指令或多個參數，此方法可能會在寫入時阻塞。 指令發送後，方法會立即返回，無需等待伺服器回應——也就是說，讀取是非同步的。
由於此過載不支援回調程序，開發者必須輪詢指令是否完成，利用IsCompleted方法返回BeginExecuteNonQuery的屬性IAsyncResult;或等待一個或多個指令完成，使用AsyncWaitHandle返回IAsyncResult的 屬性。
此方法忽略了該 CommandTimeout 性質。
另請參閱
適用於
BeginExecuteNonQuery(AsyncCallback, Object)
在回調程序與狀態資訊下，啟動由此 SqlCommand描述的 Transact-SQL 語句或儲存程序的非同步執行。
public:
IAsyncResult ^ BeginExecuteNonQuery(AsyncCallback ^ callback, System::Object ^ stateObject);public IAsyncResult BeginExecuteNonQuery(AsyncCallback callback, object stateObject);member this.BeginExecuteNonQuery : AsyncCallback * obj -> IAsyncResultPublic Function BeginExecuteNonQuery (callback As AsyncCallback, stateObject As Object) As IAsyncResult參數
- callback
- AsyncCallback
當 AsyncCallback 指令執行完成時會被召喚的代理人。 在 Microsoft Visual Basic 中 Pass nullNothing 表示不需要回調。
- stateObject
- Object
一個由使用者定義的狀態物件，傳遞給回調程序。 利用 プロパティ AsyncState 從回調程序中擷取此物件。
傳回
IAsyncResult可用來輪詢或等待結果，或兩者兼用;此值在呼叫EndExecuteNonQuery(IAsyncResult)時也很重要，該值會回傳受影響的列數。
例外狀況
當 設定為 Stream時，會使用除 Binary 或 VarBinary 以外的 。ValueSqlDbType 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
-或-
SqlDbType除了 Char、NChar、NVarChar、VarChar 或 Xml，當 Value 設定為 TextReader時，會使用其他 Char、NChar、NVarChar、VarChar 或 Xml。
-或-
名稱/值對「Asynchronous Processing=true」未包含在定義此 SqlCommand連線的連接字串中。
-或-
在 SqlConnection 串流操作中關閉或掉落。 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
在串流操作中，物件或 發生StreamXmlReaderTextReader錯誤。 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
Stream該 ， XmlReader 或TextReader物件在串流操作中被關閉。 欲了解更多串流資訊，請參閱 SqlClient 串流支援。
範例
以下 Windows 應用程式示範此 BeginExecuteNonQuery 方法的使用，執行包含數秒延遲的 Transact-SQL 語句（模擬長時間執行的指令）。
此範例展示了許多重要的技巧。 這包括呼叫一個從獨立執行緒與表單互動的方法。 此外，這個範例說明了你必須阻止使用者同時執行多次指令，以及如何確保表單在呼叫回調程序被呼叫前不會關閉。
若要設定此範例，請建立新的 Windows 應用程式。 在表單上加上一個 Button 控制項和一個 Label 控制項（接受每個控制項的預設名稱）。 在表單的類別中加入以下程式碼，並根據環境需要修改連接字串。
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();
}
}
}
}
}
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class Form1
' Add this code to the form's class:
' 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 Sub DisplayInfoDelegate(ByVal Text As String)
' This flag ensures that the user does not 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 is 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 is not able
' to execute asynchronously.
Return "Data Source=(local);Integrated Security=true;" &
"Initial Catalog=AdventureWorks; 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 FormClosingEventArgs) _
Handles Me.FormClosing
If isExecuting Then
MessageBox.Show(Me, "Cannot 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 does not do much, but that's the point--
' it does not 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 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.
Dim callback As New AsyncCallback(AddressOf HandleCallback)
command.BeginExecuteNonQuery(callback, command)
Catch ex As Exception
isExecuting = False
DisplayStatus(String.Format("Ready (last error: {0})", 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 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:
Me.Invoke(New DisplayInfoDelegate(AddressOf DisplayStatus),
String.Format("Ready(last error: {0}", ex.Message))
Finally
isExecuting = False
If connection IsNot Nothing Then
connection.Close()
End If
End Try
End Sub
End Class
備註
此 BeginExecuteNonQuery 方法啟動非同步執行一個不回傳列的 Transact-SQL 陳述式或儲存程序的過程，讓其他任務能在該陳述式執行時同時執行。 當敘述完成後，開發者必須呼叫該 EndExecuteNonQuery 方法來完成該操作。 該 BeginExecuteNonQuery 方法會立即回傳，但在程式碼執行對應 EndExecuteNonQuery 的方法呼叫之前，不能執行任何對同一 SqlCommand 物件啟動同步或非同步執行的其他呼叫。 在指令執行完成前呼叫 ， EndExecuteNonQuery 物件會阻塞 SqlCommand 直到執行完成。
這個 callback 參數讓你指定一個 AsyncCallback 代理，當敘述完成時會被呼叫。 你可以在這個代表程序中撥打 EndExecuteNonQuery 該方法，或在申請表的其他任何地點。 此外，你可以傳遞參數中 asyncStateObject 的任何物件，回調程序也能利用該 AsyncState 屬性取得這些資訊。
請注意，指令文字與參數是同步傳送到伺服器的。 若傳送大型指令或多個參數，此方法可能會在寫入時阻塞。 指令發送後，方法會立即返回，無需等待伺服器回應——也就是說，讀取是非同步的。
由於回調程序是在 Microsoft .NET 通用語言執行環境提供的背景執行緒中執行，因此你必須嚴格處理應用程式內部的跨執行緒互動。 例如，你不得在回撥程序中與表單內容互動;如果你必須更新表單，必須切回表單的討論串才能繼續工作。 本主題中的例子展示了這種行為。
操作執行過程中發生的所有錯誤都會在回調程序中拋出為例外。 你必須在回撥程序中處理例外，而不是在主應用程式中。 關於回調程序中處理異常的更多資訊，請參見本主題中的範例。
此方法忽略了該 CommandTimeout 性質。
