Understanding Variable Scope in Anonymous Methods / Lambda Statements

There are 2 goals of this article. 1) to understand the variable scope in anonymous methods and 2) to look at a real world scenario where we have used the anonymous methods effectively.

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

Creating Form on different Thread (UI Threading)

Level: Advanced

Knowledge Required:
Threading

Description:
Recently I was doing some research on Threading, I found that we can create Form in other Thread, but it requires to Execute Message Loop using Application.Run.

Example:
I have created 2 forms (Form1 and Form2) and put a Button on Form1 which creates the Form2 in different thread and shows it.
Form1 Code:

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Debug.Print("Form1.Button1_Click: Current Thread=" & Threading.Thread.CurrentThread.ManagedThreadId)
        Dim t As Threading.Thread
        t = New Threading.Thread(AddressOf ShowForm2)
        t.Start()
    End Sub
    Private Sub ShowForm2()
        Debug.Print("Form1.ShowForm2: Current Thread=" & Threading.Thread.CurrentThread.ManagedThreadId)
        Dim frmNew As Form2
        frmNew = New Form2
        'here frmNew.Show() will NOT work properly
        'because we are in other thread then main thread
        Application.Run(frmNew)
    End Sub
End Class

And in Form2 I just put code in Load Event Handler to check the Current Thread in which Form2 is.
Form2 Code:

Public Class Form2
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Debug.Print("Form2_Load: Current Thread=" & Threading.Thread.CurrentThread.ManagedThreadId)
    End Sub
End Class

I have put the Debug statements so that we can see which thread is performing that task, so the output of program,

Form1.Button1_Click: Current Thread=11
Form1.ShowForm2: Current Thread=12
Form2_Load: Current Thread=12

As you can see the output, when Button1 was clicked that was Thread=11 and when ShowForm2 method on Form1 was executed then it was Thread=12, therefore when Form2_Load() event fired it was also Thread=12.

So the new thread that was created will remain alive until the Form2 is NOT closed. I am NOT sure but I think this type of behaviour is called UI Threading.