How to populate DataGrid on background thread with data binding by using Visual Basic 2005 or Visual Basic .NET

Article translations Article translations
Article ID: 318604 - View products that this article applies to.
This article was previously published under Q318604
For a Microsoft Visual C# .NET version of this article, see 318607.
Expand all | Collapse all

On This Page

SUMMARY

When large queries to a database are executed, the application may become unresponsive for a long period of time. To avoid this behavior and decrease the waiting time of the user, the query can be executed on a background thread, releasing the application for other tasks until the data is returned from the database and databinding is performed.

This step-by-step article demonstrates how to query a database on a background thread and use databinding to display the results in a DataGrid object.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET
  • Access to the Northwind sample database

Background

By design, Microsoft Windows Forms or Control methods cannot be called on a thread other than the one that created the form or control. If you attempt to do this, an exception is thrown. Depending on the exception handling implemented in your code, this exception may cause your application to terminate. If no exception handling is implemented, the following error message is displayed:
An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

Additional information: Controls created on one thread cannot be parented to a control on a different thread.
The exception is raised because Windows Forms are based on a single-threaded apartment (STA) model. Windows Forms can be created on any thread; after they are created, however, they cannot be switched to a different thread. In addition, the Windows Forms methods cannot be accessed on another thread; this means that all method calls must be executed on the thread that created the form or control.

Method calls that originate outside the creation thread must be marshalled (executed) on the creation thread. To do this asynchronously, the form has a BeginInvoke method that forces the method to be executed on the thread that created the form or control. The synchronous method call is done with a call to the Invoke method.

Build the Windows Forms application

This section describes how to create a Windows Forms application that queries a database on a background thread and uses the BeginInvoke method to perform databinding on a DataGrid.
  1. Start Visual Studio 2005 or Visual Studio .NET.
  2. Create a new project, select Visual C# as the Project Type, and use the Windows Application template.
  3. Add a Button object to the form, and change its Text property to "Query on Thread".
  4. Add another Button to the form, and change its Text property to "Query on Form".
  5. Add a Label to the form, and clear its Text property.
  6. Add a TextBox to the form.
  7. Add a DataGrid to the form.
  8. Right-click the form, and then click View Code; this displays the code of your application.
  9. Add the following import statements to the top of the page to import the Threading and SqlClient namespaces.
       Imports System.Threading
       Imports System.Data.SqlClient
    					
  10. Add the following code immediately below the Windows Forms Designer Generated Code.
       Dim UpdateThread As Thread
       Dim UpdateThreadStart As New ThreadStart(AddressOf QueryDataBase)
       Dim CallDataBindToDataGrid As New MethodInvoker(AddressOf Me.DataBindToDataGrid)
    
       Dim MyDataSet As DataSet
       Dim MyDataAdapter As SqlDataAdapter
       Dim MyQueryString As String = "SELECT Products.* FROM [Order Details] CROSS JOIN Products"
       Dim MyConnection As New SqlConnection("data source=localhost;initial catalog=northwind;integrated security=SSPI;")
    						
    Note The query used in this demonstration is a Cartesian Product that returns over 165,000 rows from the Northwind database. The amount of data returned is large so that the responsiveness of the form can be demonstrated.
  11. Open the Windows Forms Design View.
  12. Double-click the Query on Thread button, and paste the following code in the Click event for this button.
       UpdateThread = New Thread(UpdateThreadStart)
       UpdateThread.IsBackground = True
       UpdateThread.Name = "UpdateThread"
       UpdateThread.Start()
    					
  13. Open the Design view again, and then double-click the Query on Form button. Paste the following code in the Click event for this button.
       QueryDataBase()
    					
  14. Paste the following code below the button events that you added in the earlier steps.
    
       ' Sub routine that is to be executed on Form's thread.
       Public Sub DataBindToDataGrid()
          DataGrid1.DataSource = MyDataSet
          DataGrid1.DataMember = "MyTable"
          MyDataAdapter = Nothing
          MyDataSet = Nothing
       End Sub
    
       ' Sub routine used by the background thread to query database.
       Public Sub QueryDataBase()
          MyDataSet = New DataSet()
          MyConnection.Open()
          Dim cmd As New SqlCommand(MyQueryString, MyConnection)
          MyDataAdapter = New SqlDataAdapter(cmd)
          Label1.Text = "Filling DataSet"
          MyDataAdapter.Fill(MyDataSet, "MyTable")
          MyConnection.Close()
          Label1.Text = "DataSet Filled"     
          ' Make asynchronous function call to Form's thread.
          Me.BeginInvoke(CallDataBindToDataGrid)
       End Sub
    						
    These Sub routines are used by the background thread to query the database and databind it to the DataGrid located on the Windows Form when the first button is clicked. The Click event of the second button calls the QueryDataBase Sub routine directly, and will be executed on the Windows Forms thread.
  15. Press CTRL+SHIFT+B to build your application.

Demonstration

To see the benefit that is gained by using a background thread to query the database, follow these steps:
  1. Press CTRL+F5 to execute your application without debugging.
  2. Click Query on Form. This begins the query on the Windows Forms thread. If you then try to enter some text in the text box that is displayed on the form, the application does not respond. After the query has completed (this may take some time, depending on your computer), the DataGrid displays the results of the query.
  3. Click the Query on Thread button. This creates a background thread that queries the database and keeps the application responsive to user interaction. To see this, click the button, and then type some text in the text box on the form.

Complete code listing

Imports System.Threading
Imports System.Data.SqlClient

Public Class Form1
  Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

  Public Sub New()
    MyBase.New()

    'This call is required by the Windows Form Designer.
    InitializeComponent()

    'Add any initialization after the InitializeComponent() call.

  End Sub

  'Form overrides dispose to clean up the component list.
  Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    If disposing Then
      If Not (components Is Nothing) Then
        components.Dispose()
      End If
    End If
    MyBase.Dispose(disposing)
  End Sub

  'Required by the Windows Form Designer.
  Private components As System.ComponentModel.IContainer

  'NOTE: The following procedure is required by the Windows Form Designer.
  'It can be modified using the Windows Form Designer.  
  'Do not modify it using the code editor.
  Public WithEvents DataGrid1 As System.Windows.Forms.DataGrid
  Friend WithEvents Button1 As System.Windows.Forms.Button
  Friend WithEvents Button2 As System.Windows.Forms.Button
  Friend WithEvents Label1 As System.Windows.Forms.Label
  Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    Me.DataGrid1 = New System.Windows.Forms.DataGrid()
    Me.Button1 = New System.Windows.Forms.Button()
    Me.Button2 = New System.Windows.Forms.Button()
    Me.Label1 = New System.Windows.Forms.Label()
    Me.TextBox1 = New System.Windows.Forms.TextBox()
    CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
    Me.SuspendLayout()
    '
    'DataGrid1
    '
    Me.DataGrid1.DataMember = ""
    Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
    Me.DataGrid1.Location = New System.Drawing.Point(8, 8)
    Me.DataGrid1.Name = "DataGrid1"
    Me.DataGrid1.Size = New System.Drawing.Size(688, 276)
    Me.DataGrid1.TabIndex = 0
    '
    'Button1
    '
    Me.Button1.Location = New System.Drawing.Point(12, 296)
    Me.Button1.Name = "Button1"
    Me.Button1.Size = New System.Drawing.Size(136, 23)
    Me.Button1.TabIndex = 1
    Me.Button1.Text = "Query on Thread"
    '
    'Button2
    '
    Me.Button2.Location = New System.Drawing.Point(160, 296)
    Me.Button2.Name = "Button2"
    Me.Button2.Size = New System.Drawing.Size(132, 23)
    Me.Button2.TabIndex = 3
    Me.Button2.Text = "Query on Form"
    '
    'Label1
    '
    Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 15.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.Label1.Location = New System.Drawing.Point(12, 332)
    Me.Label1.Name = "Label1"
    Me.Label1.Size = New System.Drawing.Size(680, 23)
    Me.Label1.TabIndex = 4
    '
    'TextBox1
    '
    Me.TextBox1.Location = New System.Drawing.Point(300, 296)
    Me.TextBox1.Name = "TextBox1"
    Me.TextBox1.Size = New System.Drawing.Size(392, 20)
    Me.TextBox1.TabIndex = 5
    Me.TextBox1.Text = ""
    '
    'Form1
    '
    Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    Me.ClientSize = New System.Drawing.Size(704, 382)
    Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.TextBox1, Me.Label1, Me.Button2, Me.Button1, Me.DataGrid1})
    Me.Name = "Form1"
    Me.Text = "Form1"
    CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
    Me.ResumeLayout(False)

  End Sub

#End Region

  Dim UpdateThread As Thread
  Dim UpdateThreadStart As New ThreadStart(AddressOf QueryDataBase)
  Dim CallDataBindToDataGrid As New MethodInvoker(AddressOf Me.DataBindToDataGrid)

  Dim MyDataSet As DataSet
  Dim MyDataAdapter As SqlDataAdapter
  Dim MyQueryString As String = "SELECT Products.* FROM [Order Details] CROSS JOIN Products"
  Dim MyConnection As New SqlConnection("data source=localhost;initial catalog=northwind;integrated security=SSPI;")

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    UpdateThread = New Thread(UpdateThreadStart)
    UpdateThread.IsBackground = True
    UpdateThread.Name = "UpdateThread"
    UpdateThread.Start()
  End Sub

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    QueryDataBase()
  End Sub

  Public Sub DataBindToDataGrid()
    DataGrid1.DataSource = MyDataSet
    DataGrid1.DataMember = "authors"
    MyDataAdapter = Nothing
    MyDataSet = Nothing
  End Sub

  Public Sub QueryDataBase()
    MyDataSet = New DataSet()
    MyConnection.Open()
    Dim cmd As New SqlCommand(MyQueryString, MyConnection)
    MyDataAdapter = New SqlDataAdapter(cmd)
    Label1.Text = "Filling DataSet"
    MyDataAdapter.Fill(MyDataSet, "authors")
    MyConnection.Close()
    Label1.Text = "DataSet Filled"
    Me.BeginInvoke(CallDataBindToDataGrid)
  End Sub
End Class
				
Note You must change the code in Visual Basic 2005. By default, Visual Basic creates two files for the project when you create a Windows Forms project. If the form is named Form1, the two files that represent the form are named Form1.vb and Form1.Designer.vb. You write the code in the Form1.vb file. The Windows Forms Designer writes the code in the Form1.Designer.vb file. The Windows Forms Designer uses the partial keyword to divide the implementation of Form1 into two separate files. This behavior prevents the designer-generated code from being interspersed with your code.

For more information about the new Visual Basic 2005 language enhancements, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms379584(vs.80).aspx
For more information about partial classes and the Windows Forms Designer, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms171843.aspx

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
316422 Roadmap for threading in Visual Basic .NET
To see a sample multithreaded Windows Forms control, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/3s8xdz5c(vs.71).aspx

Properties

Article ID: 318604 - Last Review: May 16, 2007 - Revision: 3.6
APPLIES TO
  • Microsoft Visual Basic 2005
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft ADO.NET 1.0
Keywords: 
kbvs2005swept kbvs2005applies kbhowtomaster KB318604

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com