Introduction
This article is very usefull when you want to show millions records in datagrid. Main problem with desktop application is caching not availble like web application.But in Visual Studio 2005 microsoft has provided very good support in datagrid to show millions of data to user. This artile is inspired by MSDN Article.
Background
The basic idea behind this article is that when you want to show large amount of data to user and you do not wnat to load all the data at the same time in memory.This can be possible in Datagridview because it has virtual mode and in virtual mode you can write eah cell value when it is repaint.So as you scroll datagridview each cell will be paint for that CellValueNeeded event will be called.So this Datagridview in virtual mode very user friendly to make it work as you require.
Using the code
Basicallly this code is based on the MSDN Article. but i have make this made it somewhat easy for you and now i am explaining you how this actully works.
Here, basic idea behind chaching is that when cell value is required you should get it and show it. So what happen behind the scren is:
Interface -> IDataPageRetriver
Public Interface IDataPageRetriever
Function SupplyPageOfData( _
ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) _
As DataTable
End Interface
This inteface is haveing one method SupplyPageOfData which returns Datatable of specific range.
Class - Cache
This is the heart. Basic idea is that think of one Page of book which have page no,uppler line no and lower line no.This Page upper index and lower index. So now in this page you can store data keeping record In this class one 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
Structure members are Datatable,lower index and upper index. When Object of this Page created datatable is assign to it and lower and upper indes is set.
Now,When object of cache class is created it create two default Pages.And get the datatable of given Pagesize from the databas 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 CellValueNeeded event called RetrieveElement method will get called which is having row index and 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 that values else it will retrive new Data from the database and fill in the nearest Page either 0th page or 1st 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 big problem comes when you update or delete record from the grid.Because we are using Page caching and CellValueNeeded event so we need to update cell every time. So When user update any cell i have updated 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
By scrolling when user reaches the point when Caches class decide to replce that cache page from the database page I got the updated dataset and raised event that will be handle on the form keeping for 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 Delete we can not apply this method so we have to delete it 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
Mircosoft has provided very good control to user for working with DataGridView in VS-2005
MSDN LINK
History
This is the first version of Datagird chaching which made with Oracle Database beacause it is somewhat tough to work with it.
In the next version I am working on datagrid control to make it independent of any database. I mean to say i will provide datagridview contol with caching irrespective of any database.