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.

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.

How To Increase Performance of Loading Large Data from Database (Part-3)

Level: Advanced

Knowledge Required:
Part-3:

• Typed DataSet
• TableAdapter
• DataGridView

Description:
So far we have discussed the 2 better ways of loading large data,

Implement Paging in SQL Server 2005
Implement Paging in Application using SQLDataAdapter

Now the last one is to use to Virtual Mode of DataGridView control. Here we will NOT use the Data Binding since I have already discussed in Part-1 that Data Binding also slows down the process.

When DataGridView control is in Virtual Mode (VirtualMode=True) then CellValueNeeded Event is triggered for Unbound Columns. In the same event we receive the Row and Column Index, in which DataGridView is needing the Value. Therefore we just go in the same Row and Column of our loaded DataTable, get the Value and give to the DataGridView control.

So what we will do is,

1. Create a DataGridView Control NOT bound to anything
2. Add the Columns (manually) equal to number of Columns in our DataTable
3. Set the property of DataGridView Control i.e. VirtualMode = True
4. Load the Data in our DataTable which is also NOT bound to anything
5. Add the same number Rows in the DataGridView
6. And finally in the CellValueNeeded event you can use the following code

Private Sub grdLargeData_CellValueNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) Handles grdLargeData.CellValueNeeded
    Dim rowLargeData As LargeDataDataSet.LargeDataRow
    rowLargeData = Me.dsLargeData.LargeData(e.RowIndex)
    e.Value = rowLargeData(e.ColumnIndex)
End Sub

This approach is a bit faster as compare to the Data Bound version. But has limitations i.e. Ordering and Filtering cannot be performed.

Source Code:
LargeDataWithVirtualDGV.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

How To Increase Performance of Loading Large Data from Database (Part-2)

Level: Advanced

Knowledge Required:
Part-2:

• Typed DataSet
• TableAdapter
• DataAdapter
• Partial Class
• Data Binding

Description:
In my previous post, I showed you how to Implement Paging in SQL Server 2005 (Database Level). In this post I will discuss How To Implement Paging Using DataAdapter (Application Level). This is a simple technique,

SQLDataAdapter gives us an option to load the Data from Particular Row and to Limit the Loading of Data by providing the Maximum Number of Records to be Loaded. The Fill Method of SQLDataAdapter has 5 Declarations, one of them is: SQLDataAdapter.Fill(StartRecord, MaxNumberOfRecordsToLoad, ParamArray DataTables()) We will use the above Fill Method to start loading Data from Particular Record Number and Limit the SQLDataAdapter to load only 20 or 30 Records, as per our Page Size.

To implement it professionaly, we will create another Fill Method in our TableAdapter Class (using Partial Class) as,

Partial Class LargeDataTableAdapter
    Public Function FillByDataAdapterPaging(ByRef LargeDataDataTable As LargeDataSet.LargeDataDataTable, ByVal PageNumber As Integer, ByVal PageSize As Integer) As Integer
        Dim StartRow As Integer
        StartRow = ((PageNumber - 1) * PageSize)
        Me.Adapter.SelectCommand = Me.CommandCollection(0)
        If (Me.ClearBeforeFill = True) Then
            LargeDataDataTable.Clear()
        End If
        Dim returnValue As Integer = Me.Adapter.Fill(StartRow, PageSize, LargeDataDataTable)
        Return returnValue
    End Function
End Class

Source Code:
LargeDataWithDataAdapterPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

How To Increase Performance of Loading Large Data from Database (Part-1)

Issue: While loading Large amount of Data from Database:

• Connection Timeout occurs
• Application gets Hang/Stuck
• Application takes too much time to load data

Level: Advanced

Knowledge Required:
Part-1:
SQL Server 2005
Stored Procedures

Description:
First of all this is NOT a good practice to load ALL THE DATA from a Table into memory. A Human being cannot process/analyse all this huge data in a glance, instead we usually interested in a short information. For example we have a Table that contains all the Contact Numbers living in the City along with there Addresses and Names. Now we usually want to extract out one particular number or one particular name, NOT all the names and numbers.

But still we (developers) are forced to create such applications which give the user to access all the data by SCROLLING up or down.

So in this Article I will show you how to increase this performance by the following ways:

1. Implement Paging in SQL Server 2005 (Database Level)
2. Implement Paging using DataAdapter (Application Level)
3. Use the Virtual Mode of DataGridView Control

In this part I will discuss the 1st one.
1) Paging in SQL Server 2005
Paging means we divide our huge data into number of small chunks. We will display 1 page at a time and will provide next previous buttons so user can navigate forward and backword. I think this is the fastest way to load data. We will send the Page Number and Number of Records Per Page to Stored Procedure, which will return only that part of data. For example: I have created a Table tbl_LargeData in which there are 3 Fields:

• Row_ID [primary key, int, Identity Column]
• SomeData [varchar(255)]
• InsertDateTime [DateTime, Default = GetDate()]

In this table I have put some fake data with Total 1,000,000 Rows.

Now this is a bit Large Data (NOT that much Large). Now to test, I created a project in VB and simply loaded the Data (in a DataTable) by executing the Query

SELECT * FROM tbl_LargeData

I have 1 GB RAM and AMD Athlon 64 Processor 3500+.

Unbound DataTable: It took 28 Seconds to fill the DataTable.
Bound DataTable: I bind that DataTable to BindingSource, it took 51 seconds, almost double.

BindingSource also increases time to Fill a DataTable, becuase BindingSource itself keeps another Cache of Data for Sorting and Filtering Purpose.

The Loading of Data can significantly increase Time, if:

• System has low RAM and Processing Speed
• Other Applications are also running on Client PC
• Database Server is NOT on same machine, it is somewhere on the LAN
• Client PC is connected to server using low Band Width

Therefore to Implement Paging I have created 2 Stored Procedures in Database:

1. GetLargeDataPageInfo
2. GetLargeDataWithPaging

The GetLargeDataPageInfo stored procedure has a Parameter @PageSize int. User has to provide the Page Size (i.e. Number of Records Per Page) then this stored procedure will return

Total Records	Total Pages
1000000	50000

GetLargeDataWithPaging stored procedure is the main stored procedure which Returns the Particular Page of Data. The script is:

CREATE PROCEDURE [dbo].[GetLargeDataWithPaging]
    @PageNumber int,
    @PageSize int
AS
BEGIN
    SET NOCOUNT ON;

    -- For Paging we have used the ROW_NUMBER() function
    -- which operates on Ordering of Column

    DECLARE @RowStart int;
    DECLARE @RowEnd int;

    -- Calculate the first row's Index
    -- and Last Row's Index
    SET @RowStart = ((@PageNumber - 1) * @PageSize) + 1;
    SET @RowEnd = @RowStart + @PageSize - 1;
 
    SELECT Row_ID, SomeData, InsertDateTime
    FROM (
        SELECT    ROW_NUMBER()
                OVER (
                    ORDER BY Row_ID
                    ) AS Row_Num,
                *
        FROM    tbl_LargeData
        ) AS DerivedTable
    WHERE Row_Num Between @RowStart AND @RowEnd;
END

Source Code:
LargeDataWithSQLPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

Update (12-May-2008): Point #2 changed to "Implement Paging using DataAdapter" which was "Implement Paging using DataReader"

Creating a Friendly User Interface for Many to Many Relationship Scenario

Level: Advanced
Knowledge Required: To understand the following solution you must have the knowledge of:

• Typed DataSets
• DataTables
• Data Binding
• DataGridView Control

Description:

In our daily life development, we usually face a scenario where we need to create a user interface for many to many relationship scenario. For example I will discuss here a scenario where we require to store the Shops along with the Products they deal in.

We have created 2 Typed DataSets here:

• Product DataSet
• Shop DataSet

Product DataSet contains 1 Table i.e. Product. We will load products in this Table. Note that I haven’t created any Physical Database here, so I will be manually filling some products in this table at runtime.

In the Shop DataSet we have 2 Tables:

• Shop Table
• ShopProducts Table

ShopProducts Table is a Junction Table, which means a shop can have multiple Products. One way to create this interface is to place a DataGridView control for entering the Shop’s Products as I have shown in the following figure:

As you can see user can add multiple Products in this list by opening the ComboBox and selecting a Product. This way of creating user interface is fine but NOT friendly. As you can see whenever user opens the ComboBox all the Products display no matter how many products user has already selected. Therefore whenever user tries to duplicate a product the Primary Key Violation exception will occur which we have handled in the DataError event of the DataGridView control. You can note that this Exception which user sees is also NOT Friendly.

So to make it simple we can add a Checked List Box here, in which all the products are displayed and user just has to check the products which he/she wants to be added with Shop. Since Checked List Box control is a bit old fashioned and NOT a good looking control so we will use the same DataGridView control but in a different way.

We will now add another DataSet here i.e. ProductSelectionDataSet. This DataSet is same as ProductDataSet except that it’s Product Table contains a field Boolean field IsSelected, which will be used to check whether a product is selected or NOT.

Next we will replace current DataGridView control with another DataGridView control having 2 Columns

• IsSelected Column
• Product Name Column

We will setup this DataGridView in the following manner:

• AllowUserToAddRows = False
• AllowUserToDeleteRows = False
• RowHeadersVisible = False
• ReadOnly = True

Also we will set the 1st Column i.e. IsSelected column as:

• HeaderText = “” (empty string, we don’t want its header to be displayed)
• Resizable = False
• Width = 32

And for the 2nd column (Product Name):

• HeaderText = “Product”
• AutoSizeMode = Fill

This will make our DataGridView a bit like Checked ListBox. Now we just have to handle some of the events. First we will create a method SelectCurrentShopProducts; this method will select the Products according the given Products in the ShopProduct Table (of ShopDataSet). This method will be executed each time when Binding Source’s Position is changed.

Private Sub SelectCurrentShopProducts()
    ' first we will clear the currently selected shops
    Call Me.ClearAllSelection()

    Dim drvShop As DataRowView
    ' get current shop
    drvShop = Me.ShopBindingSource.Current
    ' if there is some shop selected
    If drvShop IsNot Nothing Then
        Dim rowShop As ShopDataSet.ShopRow
        Dim rowsProduct() As ShopDataSet.ShopProductsRow
        ' get the row from shop table
        rowShop = CType(drvShop.Row, ShopDataSet.ShopRow)
        ' get the current selected products of that shop
        rowsProduct = rowShop.GetShopProductsRows()
        ' for each selected product
        For Each r As ShopDataSet.ShopProductsRow In rowsProduct
           Dim rowProdSel As ProductSelectionDataSet.ProductWithSelectionRow
           ' get the product row from ProductWithSelection table
            rowProdSel =Me.ProductSelectionDataSet.ProductWithSelection.FindByProd_ID(r.Prod_ID)
           ' and mark it as selected
            rowProdSel.IsSelected = True
        Next
    End If
End Sub

Private Sub ClearAllSelection()
    For Each r As ProductSelectionDataSet.ProductWithSelectionRow In Me.ProductSelectionDataSet.ProductWithSelection
        r.IsSelected = False
    Next
End Sub

Next we will handle the CellContentClick event of DataGridView control. This event will be triggered whenever the CheckBox is checked or unchecked. In this event we will first check: If CheckBox is NOT selected then we will add this Product in our ShopProduct table otherwise if CheckBox is selected then it means we have already added this Product in ShopProduct table so now we will remove it.

Private Sub ProductWithSelectionDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ProductWithSelectionDataGridView.CellContentClick
    If e.RowIndex >= 0 Then
        ' if checkbox is clicked
        If e.ColumnIndex = Me.ColumnIsSelected.Index Then
           Try
               Me.ShopBindingSource.EndEdit()

               Dim rowProdSel As ProductSelectionDataSet.ProductWithSelectionRow
                rowProdSel = CType(Me.ProductWithSelectionDataGridView.Rows(e.RowIndex).DataBoundItem, DataRowView).Row
                   ' if currently selected then we will de-select it
                   If rowProdSel.IsSelected Then
                   Try
                       Call Me.RemoveProductFromCurrentShop(rowProdSel.Prod_ID)
                        rowProdSel.IsSelected = False
                   Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "De-Select Shop")
                   End Try
               Else ' if NOT selected then we will select it
                   Try
                       Call Me.AddProductInCurrentShop(rowProdSel.Prod_ID)
                        rowProdSel.IsSelected = True
                   Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Select Shop")
                   End Try
                End If
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Edit Shop")
           End Try
        End If
    End If
End Sub

Private Sub AddProductInCurrentShop(ByVal Prod_ID As Integer)
    Dim drvShop As DataRowView
    Dim rowNew As ShopDataSet.ShopProductsRow
    ' get the current shop
    drvShop = Me.ShopBindingSource.Current
    ' create new ShopProduct Row
    rowNew = Me.ShopDataSet.ShopProducts.NewShopProductsRow()
    ' set values
    With rowNew
        .Shop_ID = drvShop("Shop_ID")
        .Prod_ID = Prod_ID
    End With
    ' add it in DataTable
    Me.ShopDataSet.ShopProducts.AddShopProductsRow(rowNew)
End Sub

Private Sub RemoveProductFromCurrentShop(ByVal Prod_ID As Integer)
    Dim drvShop As DataRowView
    Dim rowShopProduct As ShopDataSet.ShopProductsRow
    ' get the current shop
    drvShop = Me.ShopBindingSource.Current
    ' get the ShopProductRow
    rowShopProduct = Me.ShopDataSet.ShopProducts.FindByShop_IDProd_ID(drvShop("Shop_ID"), Prod_ID)
    rowShopProduct.Delete()
End Sub

NOTE: We have used the READONLY version of DataGridView that means when user clicks on the CheckBox in first column, then it does NOT get checked or un-checked Automatically. We handle this in the CellContentClick event of the DataGridView control. In this event we set the IsSelected = True or False which automatically updates the CheckBox in DataGridView control since it is binded to this table.

Download the full code from here:

JunctionTable.rar

Identity Column Primary Key Violation in Typed DataSet

Title: Primary Key Violation Exception on Identity Column while Updating via Table Adapter
Issue: Exception occurs when Table Adapter's Update method is called
Level: Advanced
Knowledge Required:
To understand the following solution you must have the knowledge of:

• Typed DataSets
• Table Adapters
• Tables in Database
• Identity Columns

Description:
Identity Column in a Typed DataSet may throw Primary Key Voilation Exception while Updating via Table Adapter. This happens due to the incorrect use of AutoIncreamentSeed and AutoIncreamentStep Properties.

For example:
We have a Typed DataSet called StudentDataSet having a table called StudentDataTable which has an Identity Column i.e. Student_ID

By default DataSet Designer sets following properties for the Identity Column as,
AutoIncreamentSeed = 0
AutoIncreamentStep = 1

So when we add 2 rows in the said table the first row will have Student_ID = 0 and the second Row will have Student_ID = 1

Now if we give this Data Table to Table Adapter for update with the following code as:

StudentTableaAapter.Update(StudentDataSet.Student)

The table adapter will insert the first row in Database using Insert Stored Procedure then stored procedure will return the Student_ID which is actually returned by the Database (since this is also an identity column in database) so suppose database returned New Student_ID = 1 and now the table adapter will refresh this row and try to replace the Student_ID = 0 with Student_ID = 1 but at the same time we have a second row in the DataTable having Student_ID = 1 which will cause an exception of violating of Primary Key or Constraint.

So to overcome this issue the simple solution is to set the:
AutoIncreamentStep = -1
this will create the next value to -1 and will never be equal to the value returned by database (if database has an AutoIncreamentStep = 1)