Click here to Skip to main content
15,397,959 members
Articles / Programming Languages / Visual Basic
Posted 4 May 2006


71 bookmarked

Caching Data in WinForms DataGridView in NET 2.0

Rate me:
Please Sign up or sign in to vote.
4.76/5 (15 votes)
4 May 20063 min read
An article on how to show large amounts of records using DataGridView's virtual mode in paging.

Win Form DataGridView Caching


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.


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
            Return lowestIndexValue
        End Get
    End Property
    Public ReadOnly Property HighestIndex() As Integer
            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

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
        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())
    End If
End Sub

Points of Interest

Microsoft has provided a very good control for working with the DataGridView in VS-2005.


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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Nikhil Contractor
Software Developer (Senior) Self Employed
India India
I am working as Software Profession in Sungard Offshore Services.Basically i have stared with Java,Java servlet and oracle then shifted to .Net.Since 3.5 years i am working in .Net.I am very much impressed with my one Project leader Mr.Deep Ambarkar.I have learnt so much things form him and i am very much thankful to him.

Comments and Discussions

Generalyour Caching Data is not Caching Data Pin
Member 871442126-Feb-14 9:41
MemberMember 871442126-Feb-14 9:41 
QuestionHello Sir Pin
bilawal1217-Oct-11 0:08
Memberbilawal1217-Oct-11 0:08 
AnswerRe: Hello Sir Pin
Member 871442126-Feb-14 9:45
MemberMember 871442126-Feb-14 9:45 
GeneralMy vote of 5 Pin
Praveen Kullu31-Jul-11 1:15
MemberPraveen Kullu31-Jul-11 1:15 
GeneralNot displaying the data properly without Order By Clause Pin
Viji Raj3-Apr-09 15:55
MemberViji Raj3-Apr-09 15:55 
Generalproblems in displaying data without sort field Pin
Viji Raj3-Apr-09 15:51
MemberViji Raj3-Apr-09 15:51 
GeneralPerformance is slow Pin
Eric Rickers4-Feb-09 11:13
MemberEric Rickers4-Feb-09 11:13 
GeneralCaching DATA - very good... Pin
szandras252430-Dec-08 22:28
Memberszandras252430-Dec-08 22:28 
GeneralContact Pin
Andi.NET17-Jun-07 9:21
MemberAndi.NET17-Jun-07 9:21 
QuestionIs it possible to use this with a BindingSource? Pin
nazzyg30-May-07 5:44
Membernazzyg30-May-07 5:44 
GeneralBuffer graphics context cannot be disposed of because a buffer operation is currently in progress Pin
rock_0720-Dec-06 20:39
Memberrock_0720-Dec-06 20:39 
GeneralInsert and Deleting from Grid Pin
DPulsar27-Nov-06 0:58
MemberDPulsar27-Nov-06 0:58 
QuestionInsert New Row Pin
Ali Gullen23-Nov-06 13:00
MemberAli Gullen23-Nov-06 13:00 
Generalnice article [modified] Pin
njscorpion17-Jun-06 2:59
Membernjscorpion17-Jun-06 2:59 
GeneralGood work Pin
Deep Amberkar11-May-06 20:37
MemberDeep Amberkar11-May-06 20:37 
GeneralDatagridView rotate Pin
horvat9-May-06 20:00
Memberhorvat9-May-06 20:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.