Caching Data in WinForms DataGridView in NET 2.0

• Download demo project and source code - 44.9 Kb

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 the lowest index of a page

        ' containing the given index.

        Return (rowIndex \ RowsPerPage) * RowsPerPage
 
    End Function
 
    Private Shared Function MapToUpperBoundary( _
        ByVal rowIndex As Integer) As Integer
 
        ' Return the highest index of a page

        ' containing the given index.

        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)
        'MessageBox.Show(dtUpdate.Rows.Count.ToString & _

        '     " Rows are chaged in Page 0")

    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)
        'MessageBox.Show(dtUpdate.Rows.Count.ToString & _

        '     " Rows are chaged in Page 1")

    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
        'Remove from the database first here

        'DatabaseOP.DeleteRecord(connectionString, _

        '   grdFunctions.Rows(e.Row.Index).

        '   Cells(0).Value.ToString())

        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.

• MSDN.

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.