Introduction
The technique described in this article will be very useful when you want to show millions of records in a DataGrid. One of the main problems with desktop applications is that caching is not available like in web applications. But in Visual Studio 2005, Microsoft has provided very good support in the DataGrid to allow the display of millions of data records to the user. This article was inspired by the MSDN article, Implementing Virtual Mode with Just-In-Time Data Loading in the Windows Forms DataGridView Control.
Background
The basic idea behind this article can be used when you want to show large amounts of data to the user and you do not want to load all the data at the same time in memory. This is possible in DataGridView because it has a virtual mode, and in the virtual mode, you can write each cell value when it is repaintes. So as you scroll down a DataGridView, each cell will be painted, and for that, the CellValueNeeded event will be called. So the DataGridView in virtual mode is very user friendly as you can make it work as required.
Using the code
Basically, this code is based on the MSDN article mentioned above. But I have made it somewhat easy for you, and now I will explain to you how it actually works.
Here, the basic idea behind data caching is that when a cell value is required, you should get it and show it. So what happens behind the screen is:
Interface -> IDataPageRetriver
Public Interface IDataPageRetriever
Function SupplyPageOfData( _
ByVal lowerPageBoundary As Integer, _
ByVal rowsPerPage As Integer) _
As DataTable
End Interface
This interface has a method SupplyPageOfData which returns a DataTable for a specific range.
Class - Cache
This is the heart of the solution. To understand the basic idea, think of a page in a book which has page number, and the upper line number and the lower line number which will be like the page's upper index and lower index. So now in the page, you can store the data keeping record.
In this class, a structure DataPage is defined.
Public Structure DataPage
Public table As DataTable
Private lowestIndexValue As Integer
Private highestIndexValue As Integer
Public Sub New(ByVal table As DataTable, ByVal rowIndex As Integer)
Me.table = table
lowestIndexValue = MapToLowerBoundary(rowIndex)
highestIndexValue = MapToUpperBoundary(rowIndex)
System.Diagnostics.Debug.Assert(lowestIndexValue >= 0)
System.Diagnostics.Debug.Assert(highestIndexValue >= 0)
End Sub
Public ReadOnly Property LowestIndex() As Integer
Get
Return lowestIndexValue
End Get
End Property
Public ReadOnly Property HighestIndex() As Integer
Get
Return highestIndexValue
End Get
End Property
Public Shared Function MapToLowerBoundary( _
ByVal rowIndex As Integer) As Integer
Return (rowIndex \ RowsPerPage) * RowsPerPage
End Function
Private Shared Function MapToUpperBoundary( _
ByVal rowIndex As Integer) As Integer
Return MapToLowerBoundary(rowIndex) + RowsPerPage - 1
End Function
End Structure
The structure members are the DataTable, the lower index, and the upper index. When an object of this page is created, the DataTable is assigned to it and the lower and upper indexes are set.
Now, when an object of the Cache class, is creates two default pages. And then get the DataTable of the given PageSize from the database and load both the pages.
Public Sub New(ByVal dataSupplier As IDataPageRetriever, _
ByVal rowsPerPage As Integer)
dataSupply = dataSupplier
Cache.RowsPerPage = rowsPerPage
LoadFirstTwoPages()
End Sub
And as the CellValueNeeded event is called, the RetrieveElement method will get called which has the row index and the column index.
Private Sub grdFunctions_CellValueNeeded(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) _
Handles grdFunctions.CellValueNeeded
If blnStopRepaint Then
If e.RowIndex < dtRetrive.RowCount Then
e.Value = memoryCache.RetrieveElement(e.RowIndex, e.ColumnIndex)
End If
End If
End Sub
Now this method will check that data is cached or not. If it is in the cache, then it returns those values, else it will retrieve new data from the database and fill in the nearest page, either the zero-th page or the first page according to the lower and upper index of each page.
Public Function RetrieveElement(ByVal rowIndex As Integer, _
ByVal columnIndex As Integer) As String
Dim element As String = Nothing
If IfPageCached_ThenSetElement(rowIndex, _
columnIndex, element) Then
Return element
Else
UpdateCahnges()
Return RetrieveData_CacheIt_ThenReturnElement( _
rowIndex, columnIndex)
End If
End Function
Now again, a big problem comes when you update or delete records from the grid. Because we are using page caching and the CellValueNeeded event, we need to update the cells every time an update takes place. So when a user updates any cell, I update the cache DataTable.
Private Sub grdFunctions_CellValuePushed(ByVal sender _
As System.Object, ByVal e As _
System.Windows.Forms.DataGridViewCellValueEventArgs) _
Handles grdFunctions.CellValuePushed
memoryCache.SetRowElement(e.RowIndex, e.ColumnIndex, e.Value)
End Sub
Public Sub SetRowElement(ByVal rowIndex As Integer, _
ByVal colIndex As Integer, ByVal cellValue As String)
If IsRowCachedInPage(0, rowIndex) Then
cachePages(0).table.Rows(rowIndex _
Mod RowsPerPage).Item(colIndex) = cellValue
ElseIf IsRowCachedInPage(1, rowIndex) Then
cachePages(1).table.Rows(rowIndex _
Mod RowsPerPage).Item(colIndex) = cellValue
End If
End Sub
When scrolling, when the user reaches the point when the Cache class decides to replace the cache page from the database page, I get the updated DataSet and raises the event that will be make the grid user to update the database.
Private Sub UpdateCahnges()
Dim dtUpdate As DataTable = cachePages(0).table.GetChanges()
If Not dtUpdate Is Nothing AndAlso dtUpdate.Rows.Count > 0 Then
Dim _updateArgs As New UpdateDataArgs(dtUpdate)
RaiseEvent UpdateChangesToDB(Me, _updateArgs)
End If
dtUpdate = cachePages(1).table.GetChanges()
If Not dtUpdate Is Nothing AndAlso dtUpdate.Rows.Count > 0 Then
Dim _updateArgs As New UpdateDataArgs(dtUpdate)
RaiseEvent UpdateChangesToDB(Me, _updateArgs)
End If
End Sub
But in case of a delete action, we can not apply this method so we have to delete the record from the database and reload the cache pages.
Private Sub grdFunctions_UserDeletingRow(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewRowCancelEventArgs) _
Handles grdFunctions.UserDeletingRow
If blnStopRepaint Then
memoryCache.RemoveRow(e.Row.Index)
grdFunctions.InvalidateRow(e.Row.Index)
End If
End Sub
Points of Interest
Microsoft has provided a very good control for working with the DataGridView in VS-2005.
History
This is the first version of this DataGrid caching solution and was done with an Oracle database, because it is somewhat tough to work with it.
In the next version, I will be working on DataGrid control to make it independent of any database. I mean to say, I will provide a DataGridView control with caching solution that will be database-independent.
