Executing Commands Asynchronously
Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013
This lesson describes how to execute ADO.NET commands asynchronously
First off, to make asynchronous calls to SQL Server in ADO.NET you must append the statement Asynchronous Processing=true to your Connection String e.g.
Dim connection As New SqlConnection( _ "Data Source=SqlServerNameOrIP;Initial Catalog=DBName;" + _ "Integrated Security=True;Asynchronous Processing=true;")
The asynchronous calls enhance the responsiveness of your application and can result in a huge perceived performance gain, especially if you ever have to execute a long running SQL statement.
The SqlCommand object offers three different asynchronous call options:
Each Begin method has an appropriate End method:
Meaning after getting the connection set, you build the Command object and initialize it to be able to execute the long running SQL query.
' declare and create a sql command Dim command As SqlCommand = connection.CreateCommand ' define how the command text will be interpreted command.CommandType = CommandType.Text ' set the sql statements (long running one) command.CommandText = "SELECT * FROM Users" ' make an asynchronous call command.BeginExecuteReader( _ AddressOf Me.AsynchronousCallback, _ command, _ CommandBehavior.CloseConnection)
As now you have initiated the asynchronous call, and have defined a callback for your asynchronous call you are all set, so while this query is executing, you can perform other tasks in your Windows Forms application.
Finally let's take a look at the method that is being called back, the AsynchronousCallback method:
Private Sub AsynchronousCallback(ByVal async As IAsyncResult) 'Get the command Dim command As SqlCommand = CType(async.AsyncState, SqlCommand) 'Get the reader Dim reader As SqlDataReader = command.EndExecuteReader(async) 'Get a table from the reader. Dim table As New DataTable() table.Load(reader) ' ToDo: something with the table End Sub
Please notice that you cannot just take your DataTable and bind it to the grid or another bindable control.
It will not work, because right now you are executing on a thread other than the main Windows thread.
In order to do the data binding, it must be called only in the context of the Windows main thread, so you have to marshal it via the Invoke method of the Form object.