Asynchronous Data Loading using TableAdapter with Cancel Feature

Level: Intermediate Knowledge Required:

• BackgroundWorker Control
• DataGridView Control
• Data Binding
• TableAdapter

Description: In my previous post Implement SQLCommand Cancel with Threading I have discussed how to implement the SQLCommand Cancel using Threading. In this post we shall see, how we can implement the same feature in a TableAdapter. As we know that TableAdapter is used as a middle level man standing between Physical Database and Typed DataSet. Which gets the data from Physical Database and loads into Typed DataSet. Sometimes this loading of data takes time (for example due to complex query). Therefore we can provide the user an ability to cancel the on going command by clicking on Cancel Button. I have created a Test Database (TestDB) and have added a table (tbl_Student). Also I have added a Stored Procedure (GetStudent). Take a look at this Stored Procedure:

CREATE PROCEDURE [dbo].[GetStudent] 
AS
BEGIN
    SET NOCOUNT ON;

    -- here we are simulating a very long query
    -- which takes 3 seconds to complete
    WAITFOR DELAY '00:00:03';

    SELECT *
    FROM tbl_Student;
END

In this procedure I have added a WAITFOR DELAY SQL Statement. Which simulates a long query that takes time 3 seconds. We will call this procedure from our TableAdapter to fill the DataTable. Now we will add the SQLCommand Cancel stuff in our TableAdapter. This can be achieved by creating a Partial Class of TableAdapter,

Namespace TestDataSetTableAdapters
    Partial Class StudentTableAdapter
        Public Sub CancelSelect()
            Me.Adapter.SelectCommand.Cancel()
        End Sub
    End Class
End Namespace

As you see I have given the name of method CancelSelect which actually cancels the SelectCommand of Adapter. Note that I haven’t implemented any checking here (for example is command executing or NOT). This is upto the user of TableAdapter class. He/She will be responsible to check before executing this method. Here we have done with our TableAdapter. Now next thing is to implement the loading in the different Thread. For this purpose I have used the BackgroundWorker control which is quite simple. The overall idea is,

• Create 2 buttons
• LoadButton
• CancelButton
• When LoadButton is clicked
• Execute the BackgroundWorker
• In BackgroundWorker DoWork Event Handler
• Note somewhere that Execution is started
• Execute the Fill() Method of TableAdapter
• When CancelButton is clicked
• Check if execution is started then
• Execute the CancelSelect() method of TableAdapter

Update Sep-4-2008: Here is the code of Form1

Public Class Form1

    ' ErrorText will contain the error information if occurred
    ' while loading data in different thread
    Private _ErrorText As String
    ' this boolean variable will be true when command is executed
    Private _CommandExecuted As Boolean

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        ' first we will disable the Load button so it should NOT be clicked again
        Me.btnLoad.Enabled = False
        ' then we will enable the Cancel button so the command can be canceled
        Me.btnCancel.Enabled = True
        ' set the status text
        Call Me.SetStatusText("Loading...")

        ' clear the Error Text
        Me._ErrorText = ""

        ' here we have first clear the currently loaded data
        ' from DataSet, because the Adapter will fill the DataSet
        ' in different thread, therefore it will create problems
        ' while displaying the same data (which is being modified
        ' in different thread) in DataGridView
        Me.TestDataSet.Clear()

        ' start the asynchronous task
        Me.BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Try
            ' first set the executed flag
            Me._CommandExecuted = True
            ' execute the command
            Me.StudentTableAdapter.Fill(Me.TestDataSet.Student)
        Catch ex As Exception
            _ErrorText = ex.Message
        End Try
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        ' ok our data is loaded now
        ' so enable the Load button
        Me.btnLoad.Enabled = True
        ' disable the Cancel button
        Me.btnCancel.Enabled = False
        ' if NO error was occurred in thread
        If Me._ErrorText = "" Then
            Me.SetStatusText(Me.TestDataSet.Student.Count & " Records loaded")
        Else ' else (some error was occurred)
            Me.SetStatusText(Me._ErrorText)
        End If
        ' since data is changed in different thread
        ' therefore by reseting the Bindings the DataGridView
        ' will refresh itself and will display all the data
        Me.StudentBindingSource.ResetBindings(False)
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        ' if command is executed then
        If Me._CommandExecuted Then
            ' cancel it
            Me.StudentTableAdapter.CancelSelect()
        End If
    End Sub


    Private Sub SetStatusText(ByVal sText As String)
        Me.lblStatus.Text = sText
    End Sub
End Class

Download: TableAdapterWithCancel.zip Note: The zip file contains TestDBScript.sql T-SQL Script which will, 1) Create a database "TestDB" 2) Create a table "tbl_Student" in that database 3) Create a Stored Procedure "GetStudent" 4) Put some data in tbl_Student Please execute this script before running the project.

Implement SQLCommand Cancel with Threading

Level: Advanced

Knowledge Required:

• ADO.net
• Threading

Description:
In a data driven application, sometimes we built queries which take time. In these type of scenarios usually developers put the query execution on different thread, so the User Interface keep responsive.

A more user friendly applications provide a flexibility to cancel the currently executing command. This can be achieved by using SQLCommand.Cancel() method.

So to implement it,

• Create a Thread
• Execute Query in that Thread
• Meanwhile if user clicks the Cancel button we will call the SQLCommand.Cancel method

Cancel method actually tries to cancel the in-process query. When the attempt to cancel the query succeeds then an exception is occured at the same point where Command was executed i.e. SQLCommand.ExecuteReader (or other Execute method) was called. When the cancellation is failed then no exception occurs and command continues its execution. Therefore we also need to handle the Exception at the same point where we have executed the query. Here is an example:

Private Sub DoSomeWork()
    objCon = New SqlConnection("Data Source=.;Integrated Security=True")
    objCmd = New SqlCommand("WAITFOR DELAY '00:00:05';", objCon)

    objCon.Open()
    Try
        objCmd.ExecuteReader()
    Catch ex As SqlException
        Debug.Print(ex.Message)
    End Try
    objCon.Close()
End Sub

The above method just executes a WAITFOR DELAY query which simulates a very long query that takes 5 seconds to complete. Note that objCon and objCmd are Module Level variables. This method will be executed in a different thread using the following code,

Dim t As Threading.Thread
t = New Threading.Thread(AddressOf DoSomeWork)
t.Start()

While this query is being executed we will perform a query cancel on a button click event as,

Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    If objCmd IsNot Nothing Then
        objCmd.Cancel()
    End If
End Sub

See Also:

Asynchronous Data Loading using TableAdapter with Cancel Feature