Click here to Skip to main content
Click here to Skip to main content

Caching Data in WinForms DataGridView in NET 2.0

, 4 May 2006
Rate this:
Please Sign up or sign in to vote.
An article on how to show large amounts of records using DataGridView's virtual mode in paging.

Win Form DataGridView Caching

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.

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.

License

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

Share

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

 
You must Sign In to use this message board.
    Spacing  Noise  Layout  Per page   
Generalyour Caching Data is not Caching DatamemberMember 871442126-Feb-14 9:41 
not using the top row in the query D'Oh! | :doh:
QuestionHello Sirmemberblawal1217-Oct-11 0:08 
Sir I need code in C#,thanks Smile | :)
AnswerRe: Hello SirmemberMember 871442126-Feb-14 9:45 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
 
public class VirtualJustInTimeDemo : System.Windows.Forms.Form
{
private DataGridView dataGridView1 = new DataGridView();
private Cache memoryCache;
 
// Specify a connection string. Replace the given value with a
// valid connection string for a Northwind SQL Server sample
// database accessible to your system.
private string connectionString =
@"Initial Catalog=NorthWind;Data Source=STGUMW-DXP0101\MATRIX;" +
"Integrated Security=SSPI;Persist Security Info=False";
private string table = "Orders";
 
protected override void OnLoad(EventArgs e)
{
// Initialize the form.
this.AutoSize = true;
this.Controls.Add(this.dataGridView1);
this.Text = "DataGridView virtual-mode just-in-time demo";
 
// Complete the initialization of the DataGridView.
this.dataGridView1.Size = new Size(800, 250);
this.dataGridView1.Dock = DockStyle.Fill;
this.dataGridView1.VirtualMode = true;
this.dataGridView1.ReadOnly = true;
this.dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.AllowUserToOrderColumns = false;
this.dataGridView1.SelectionMode =
DataGridViewSelectionMode.FullRowSelect;
this.dataGridView1.CellValueNeeded += new
DataGridViewCellValueEventHandler(dataGridView1_CellValueNeeded);
 
// Create a DataRetriever and use it to create a Cache object
// and to initialize the DataGridView columns and rows.
try
{
DataRetriever retriever =
new DataRetriever(connectionString, table);
memoryCache = new Cache(retriever, 16);
foreach (DataColumn column in retriever.Columns)
{
dataGridView1.Columns.Add(
column.ColumnName, column.ColumnName);
}
this.dataGridView1.RowCount = retriever.RowCount;
}
catch (SqlException)
{
MessageBox.Show("Connection could not be established. " +
"Verify that the connection string is valid.");
Application.Exit();
}
 
// Adjust the column widths based on the displayed values.
this.dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.DisplayedCells);
 
base.OnLoad(e);
}
 
private void dataGridView1_CellValueNeeded(object sender,
DataGridViewCellValueEventArgs e)
{
e.Value = memoryCache.RetrieveElement(e.RowIndex, e.ColumnIndex);
}
 
[STAThreadAttribute()]
public static void Main()
{
Application.Run(new VirtualJustInTimeDemo());
}
 
}
 
public interface IDataPageRetriever
{
DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage);
}
 
public class DataRetriever : IDataPageRetriever
{
private string tableName;
private SqlCommand command;
 
public DataRetriever(string connectionString, string tableName)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
command = connection.CreateCommand();
this.tableName = tableName;
}
 
private int rowCountValue = -1;
 
public int RowCount
{
get
{
// Return the existing value if it has already been determined.
if (rowCountValue != -1)
{
return rowCountValue;
}
 
// Retrieve the row count from the database.
command.CommandText = "SELECT COUNT(*) FROM " + tableName;
rowCountValue = (int)command.ExecuteScalar();
return rowCountValue;
}
}
 
private DataColumnCollection columnsValue;
 
public DataColumnCollection Columns
{
get
{
// Return the existing value if it has already been determined.
if (columnsValue != null)
{
return columnsValue;
}
 
// Retrieve the column information from the database.
command.CommandText = "SELECT * FROM " + tableName;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.FillSchema(table, SchemaType.Source);
columnsValue = table.Columns;
return columnsValue;
}
}
 
private string commaSeparatedListOfColumnNamesValue = null;
 
private string CommaSeparatedListOfColumnNames
{
get
{
// Return the existing value if it has already been determined.
if (commaSeparatedListOfColumnNamesValue != null)
{
return commaSeparatedListOfColumnNamesValue;
}
 
// Store a list of column names for use in the
// SupplyPageOfData method.
System.Text.StringBuilder commaSeparatedColumnNames =
new System.Text.StringBuilder();
bool firstColumn = true;
foreach (DataColumn column in Columns)
{
if (!firstColumn)
{
commaSeparatedColumnNames.Append(", ");
}
commaSeparatedColumnNames.Append(column.ColumnName);
firstColumn = false;
}
 
commaSeparatedListOfColumnNamesValue =
commaSeparatedColumnNames.ToString();
return commaSeparatedListOfColumnNamesValue;
}
}
 
// Declare variables to be reused by the SupplyPageOfData method.
private string columnToSortBy;
private SqlDataAdapter adapter = new SqlDataAdapter();
 
public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
{
// Store the name of the ID column. This column must contain unique
// values so the SQL below will work properly.
if (columnToSortBy == null)
{
columnToSortBy = this.Columns[0].ColumnName;
}
 
if (!this.Columns[columnToSortBy].Unique)
{
throw new InvalidOperationException(String.Format(
"Column {0} must contain unique values.", columnToSortBy));
}
 
// Retrieve the specified number of rows from the database, starting
// with the row specified by the lowerPageBoundary parameter.
command.CommandText = "Select Top " + rowsPerPage + " " +
CommaSeparatedListOfColumnNames + " From " + tableName +
" WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
lowerPageBoundary + " " + columnToSortBy + " From " +
tableName + " Order By " + columnToSortBy +
") Order By " + columnToSortBy;
adapter.SelectCommand = command;
 
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
return table;
}
 
}
 
public class Cache
{
private static int RowsPerPage;
 
// Represents one page of data.
public struct DataPage
{
public DataTable table;
private int lowestIndexValue;
private int highestIndexValue;
 
public DataPage(DataTable table, int rowIndex)
{
this.table = table;
lowestIndexValue = MapToLowerBoundary(rowIndex);
highestIndexValue = MapToUpperBoundary(rowIndex);
System.Diagnostics.Debug.Assert(lowestIndexValue >= 0);
System.Diagnostics.Debug.Assert(highestIndexValue >= 0);
}
 
public int LowestIndex
{
get
{
return lowestIndexValue;
}
}
 
public int HighestIndex
{
get
{
return highestIndexValue;
}
}
 
public static int MapToLowerBoundary(int rowIndex)
{
// Return the lowest index of a page containing the given index.
return (rowIndex / RowsPerPage) * RowsPerPage;
}
 
private static int MapToUpperBoundary(int rowIndex)
{
// Return the highest index of a page containing the given index.
return MapToLowerBoundary(rowIndex) + RowsPerPage - 1;
}
}
 
private DataPage[] cachePages;
private IDataPageRetriever dataSupply;
 
public Cache(IDataPageRetriever dataSupplier, int rowsPerPage)
{
dataSupply = dataSupplier;
Cache.RowsPerPage = rowsPerPage;
LoadFirstTwoPages();
}
 
// Sets the value of the element parameter if the value is in the cache.
private bool IfPageCached_ThenSetElement(int rowIndex,
int columnIndex, ref string element)
{
if (IsRowCachedInPage(0, rowIndex))
{
element = cachePages[0].table
.Rows[rowIndex % RowsPerPage][columnIndex].ToString();
return true;
}
else if (IsRowCachedInPage(1, rowIndex))
{
element = cachePages[1].table
.Rows[rowIndex % RowsPerPage][columnIndex].ToString();
return true;
}
 
return false;
}
 
public string RetrieveElement(int rowIndex, int columnIndex)
{
string element = null;
 
if (IfPageCached_ThenSetElement(rowIndex, columnIndex, ref element))
{
return element;
}
else
{
return RetrieveData_CacheIt_ThenReturnElement(
rowIndex, columnIndex);
}
}
 
private void LoadFirstTwoPages()
{
cachePages = new DataPage[]{
new DataPage(dataSupply.SupplyPageOfData(
DataPage.MapToLowerBoundary(0), RowsPerPage), 0),
new DataPage(dataSupply.SupplyPageOfData(
DataPage.MapToLowerBoundary(RowsPerPage),
RowsPerPage), RowsPerPage)};
}
 
private string RetrieveData_CacheIt_ThenReturnElement(
int rowIndex, int columnIndex)
{
// Retrieve a page worth of data containing the requested value.
DataTable table = dataSupply.SupplyPageOfData(
DataPage.MapToLowerBoundary(rowIndex), RowsPerPage);
 
// Replace the cached page furthest from the requested cell
// with a new page containing the newly retrieved data.
cachePages[GetIndexToUnusedPage(rowIndex)] = new DataPage(table, rowIndex);
 
return RetrieveElement(rowIndex, columnIndex);
}
 
// Returns the index of the cached page most distant from the given index
// and therefore least likely to be reused.
private int GetIndexToUnusedPage(int rowIndex)
{
if (rowIndex > cachePages[0].HighestIndex &&
rowIndex > cachePages[1].HighestIndex)
{
int offsetFromPage0 = rowIndex - cachePages[0].HighestIndex;
int offsetFromPage1 = rowIndex - cachePages[1].HighestIndex;
if (offsetFromPage0 < offsetFromPage1)
{
return 1;
}
return 0;
}
else
{
int offsetFromPage0 = cachePages[0].LowestIndex - rowIndex;
int offsetFromPage1 = cachePages[1].LowestIndex - rowIndex;
if (offsetFromPage0 < offsetFromPage1)
{
return 1;
}
return 0;
}
 
}
 
// Returns a value indicating whether the given row index is contained
// in the given DataPage.
private bool IsRowCachedInPage(int pageNumber, int rowIndex)
{
return rowIndex <= cachePages[pageNumber].HighestIndex &&
rowIndex >= cachePages[pageNumber].LowestIndex;
}
 
}
GeneralMy vote of 5memberPraveen Kullu31-Jul-11 1:15 
Nice article
GeneralNot displaying the data properly without Order By ClausememberMember 33281363-Apr-09 15:55 
This examples works good if the table is having an unique field to sort. Without Order By clause It displays the same records multiples times(Just Select * From tablename). For E.x If we set the number of rows to retrieve as 16, the same 16 records are repeated again and again without showing the remaining data...
 

How to solve this issue? Because I want to display the records from the tables which does not contain the unique sort field...
 
Also If I scroll down, it's getting locked up for a few seconds(non responsive mode)...Pls looking forward your reply
Generalproblems in displaying data without sort fieldmemberMember 33281363-Apr-09 15:51 
Hi,
This examples works good if the table is having an unique field to sort. Without Order By clause It displays the same records multiples times. For E.x If we set the number of rows to retrieve as 16, the same 16 records are repeated again and again without showing the remaining data...
 
How to solve this issue? Because I want to display the records from the tables which does not contain the unique sort field...
GeneralPerformance is slowmemberEric Rickers4-Feb-09 11:13 
Hi;
 
I am trying to implement a solution using the DataGridView in virtual mode, I have a table with 20K of records. I used your example project and when I scroll the grid is very slow and seems to lock up. Have you been able to run this 'smoothly' with the solution you are using. Thank you for posting it, I look forward to your comments.
 
Eric
GeneralCaching DATA - very good...memberMember 262132130-Dec-08 22:28 
Thank you for the CODE !
 
Really pretty. This is exactly what i need. I had to modify a little bit, because i'm using SQL 2005, not oracle. The SQL statements are not really compatible... Smile | :)
 
Maily, the code for SQL 2005 :
WITH [ tablename ORDERED BY ROWID] AS(SELECT ROW_NUMBER() OVER (ORDER BY ; ASC) AS ROWID, * from )
Select * From [; ordered by rowid] where rowid > and rowid <=
 

Thank you again, and wish you a happy new YEAR !
 
Best regards,
 
Andras Szekely

GeneralContactmemberAndi.NET17-Jun-07 9:21 
Hi,
 
i did something similar in an application i wrote. I think there is more stuff to discuss. For example the algorithm which is used to fetch the data from the database (only the new data). What i'am also thinking of is that this probably should be on a higher level (if anything wants data, check the cache and if its not in, fetch it from the database).
 
It would be nice if we could discuss this via email.
 
Please mail me on andreas(ATTTT)rudischhauser.de
QuestionIs it possible to use this with a BindingSource?membernazzyg30-May-07 5:44 
I have been unable to find any reference to doing this with a BindingSource. Is this possible?
 
- Gaines
GeneralBuffer graphics context cannot be disposed of because a buffer operation is currently in progressmemberrock_0720-Dec-06 20:39 
I was trying to populate data grid view with 23000 rows. the data gird view got populated as i wanted. i then scrolled through the rows and after reaching 6000 rows, it generated an exception - "Buffer graphics context cannot be disposed of because a buffer operation is currently in progress".
 
as i reached 6000 rows i got the same picture very similar to the picture in your article- Caching Data in WinForms DataGridView in NET 2.0.
 
i am using c sharp as the programming language. i have been trying for long but am not able to circumvent the problem.
 
please suggest me some solution.
 
Rohan
GeneralInsert and Deleting from GridmemberDPulsar27-Nov-06 0:58 
Hi there,
Thanks for this useful code.
 
I also require (like a few people on here) need to insert new rows and delete existing rows, but am unable to achieve this Confused | :confused:
 
Are you able to help us with some code to help inserting and deleting rows?
 
I look forward to hearing back from you
 
Many thanks,
DPulsar
QuestionInsert New Rowmemberrghbhdww23-Nov-06 13:00 
Thanks very much for your code, it's been really helpful.
 
However, the sample code does not allow insertions of new rows to the datagrid.
 
How can I achive this?
 
Thanks
Ali
Generalnice article [modified]membernjscorpion17-Jun-06 2:59 
thanks Wink | ;)
GeneralGood workmemberDeep Amberkar11-May-06 20:37 
keep up the good work nikhil
 
Regards
Deep
GeneralDatagridView rotatememberhorvat9-May-06 20:00 
Is it possible to rotate datagridview by 90 degrees?
That means, is it possible to swap columns and rows in datagridview
 
Dejan

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

| Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 4 May 2006
Article Copyright 2006 by Nikhil Contractor
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid