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