Executing Commands Asynchronously ado.net tutorials

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:

  • BeginExecuteReader
  • BeginExecuteNonQuery
  • BeginExecuteXmlReader

Each Begin method has an appropriate End method:

  • EndExecuteReader
  • EndExecuteNonQuery
  • EndExecuteXmlReader

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.


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.