언어
이 예제를 사용하려면 Microsoft.Data.SqlClient NuGet 패키지를 설치합니다.
EndExecuteNonQuery 및 BeginExecuteNonQuery 메서드는 계속 지원됩니다. 해당 항목은 비동기 연결 문자열 옵션이 필요하지 않습니다. 추가Asynchronous Processing=true하지 마세요. Microsoft. Data.SqlClient는 해당 키워드를 지원하지 않습니다. 새 코드의 경우 비동기 이벤트 처리기에서 ExecuteNonQueryAsync와 await를 사용하는 것을 선호합니다.
대부분의 비동기 처리 시나리오에서는 데이터베이스 작업을 시작하고 데이터베이스 작업이 완료될 때까지 기다리지 않고 다른 프로세스를 계속 실행하려고 합니다. 그러나 많은 시나리오에서는 데이터베이스 작업이 종료된 후 다른 작업을 수행해야 합니다. 예를 들어, Windows 애플리케이션에서는 장기 실행 작업을 배경 스레드에 위임하면서 사용자 인터페이스 스레드가 응답을 유지하도록 할 수 있습니다. 그러나 데이터베이스 작업이 완료되면 결과를 사용하여 폼을 채웁니다. 이러한 종류의 시나리오는 콜백을 사용할 때 가장 효과적으로 구현됩니다.
AsyncCallback, BeginExecuteNonQuery 또는 BeginExecuteXmlReader 메서드의 BeginExecuteReader 대리자 매개 변수를 사용하여 콜백을 정의합니다. 작업이 완료되면 프로바이더가 델리게이트를 호출합니다. 콜백이 명령에 액세스하고 전역 변수 없이 일치하는 End 메서드를 호출할 수 있도록 SqlCommand를 상태 개체로 전달합니다.
예시
다음 Windows 애플리케이션에서는 BeginExecuteNonQuery 메서드를 사용하여 몇 초의 지연이 포함된 Transact-SQL 문을 실행하는 방법을 보여 줍니다(장기 실행 명령을 에뮬레이션).
이 예제에서는 개별 스레드의 폼과 상호 작용하는 메서드를 호출하는 것을 비롯하여 여러 가지 중요한 기법을 보여 줍니다. 또한 이 예제에서는 사용자가 하나의 명령을 동시에 여러 번 실행하지 못하도록 해야 하는 이유와 콜백 프로시저를 호출하기 전에 폼을 닫지 않아야 하는 이유를 설명합니다.
이 예제를 설정하려면 새 Windows 애플리케이션을 만듭니다. 각 컨트롤에 대해 기본 이름을 적용하여 양식에 Button 컨트롤과 2개의 Label 컨트롤을 배치합니다. 사용자의 환경에 필요한 대로 연결 문자열을 수정하여 양식의 클래스에 다음 코드를 추가합니다.
GetConnectionString에서 반환된 줄임표를 연결 문자열로 바꿉니다. Windows 통합 인증을 사용하는 원격 서버의 경우 Server=<server>;Database=<database>;Integrated Security=true;Encrypt=true을 사용하세요.
<database> 서버 및 <server> 데이터베이스 이름으로 바꾸고 클라이언트가 신뢰하는 서버 인증서를 구성합니다. 다른 인증 옵션은 연결 문자열 구문을 참조하세요.
' Add these to the top of the class:
Imports System
Imports System.Data
Imports Microsoft.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.
Return "..."
End Function
Private Sub DisplayStatus(ByVal Text As String)
Me.Label1.Text = Text
End Sub
Private Sub DisplayResults(ByVal Text As String)
Me.Label2.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 Microsoft.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.
return "...";
}
private void DisplayStatus(string Text)
{
this.label1.Text = Text;
}
private void DisplayResults(string Text)
{
this.label2.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);
}