Share via


Implementing Virtual Mode with Just-In-Time Data Loading in the Windows Forms DataGridView Control

One reason to implement virtual mode in the DataGridView control is to retrieve data only as it is needed. This is called just-in-time data loading.

If you are working with a very large table in a remote database, for example, you might want to avoid startup delays by retrieving only the data that is necessary for display and retrieving additional data only when the user scrolls new rows into view. If the client computers running your application have a limited amount of memory available for storing data, you might also want to discard unused data when retrieving new values from the database.

The following sections describe how to use a DataGridView control with a just-in-time cache.

To copy the code in this topic as a single listing, see How to: Implement Virtual Mode with Just-In-Time Data Loading in the Windows Forms DataGridView Control.

The Form

The following code example defines a form containing a read-only DataGridView control that interacts with a Cache object through a CellValueNeeded event handler. The Cache object manages the locally stored values and uses a DataRetriever object to retrieve values from the Orders table of the sample Northwind database. The DataRetriever object, which implements the IDataPageRetriever interface required by the Cache class, is also used to initialize the DataGridView control rows and columns.

The IDataPageRetriever, DataRetriever, and Cache types are described later in this topic.

Note

Storing sensitive information, such as a password, within the connection string can affect the security of your application. Using Windows Authentication (also known as integrated security) is a more secure way to control access to a database. For more information, see Protecting Connection Information (ADO.NET).

Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms

Public Class VirtualJustInTimeDemo
    Inherits System.Windows.Forms.Form

    Private WithEvents dataGridView1 As New DataGridView()
    Private memoryCache As Cache

    ' 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 connectionString As String = _
        "Initial Catalog=NorthWind;Data Source=localhost;" & _
        "Integrated Security=SSPI;Persist Security Info=False" 
    Private table As String = "Orders" 

    Private Sub VirtualJustInTimeDemo_Load( _
        ByVal sender As Object, ByVal e As EventArgs) _
        Handles Me.Load

        ' Initialize the form. 
        With Me
            .AutoSize = True
            .Controls.Add(Me.dataGridView1)
            .Text = "DataGridView virtual-mode just-in-time demo" 
        End With 

        ' Complete the initialization of the DataGridView. 
        With Me.dataGridView1
            .Size = New Size(800, 250)
            .Dock = DockStyle.Fill
            .VirtualMode = True
            .ReadOnly = True
            .AllowUserToAddRows = False
            .AllowUserToOrderColumns = False
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
        End With 

        ' Create a DataRetriever and use it to create a Cache object 
        ' and to initialize the DataGridView columns and rows. 
        Try 
            Dim retriever As New DataRetriever(connectionString, table)
            memoryCache = New Cache(retriever, 16)
            For Each column As DataColumn In retriever.Columns
                dataGridView1.Columns.Add( _
                    column.ColumnName, column.ColumnName)
            Next 
            Me.dataGridView1.RowCount = retriever.RowCount
        Catch ex As SqlException
            MessageBox.Show("Connection could not be established. " & _
                "Verify that the connection string is valid.")
            Application.Exit()
        End Try 

        ' Adjust the column widths based on the displayed values. 
        Me.dataGridView1.AutoResizeColumns( _
            DataGridViewAutoSizeColumnsMode.DisplayedCells)

    End Sub 

    Private Sub dataGridView1_CellValueNeeded( _
        ByVal sender As Object, ByVal e As DataGridViewCellValueEventArgs) _
        Handles dataGridView1.CellValueNeeded

        e.Value = memoryCache.RetrieveElement(e.RowIndex, e.ColumnIndex)

    End Sub

    <STAThreadAttribute()> _
    Public Shared Sub Main()
        Application.Run(New VirtualJustInTimeDemo())
    End Sub 

End Class
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=localhost;" +
        "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());
    }

}

The IDataPageRetriever Interface

The following code example defines the IDataPageRetriever interface, which is implemented by the DataRetriever class. The only method declared in this interface is the SupplyPageOfData method, which requires an initial row index and a count of the number of rows in a single page of data. These values are used by the implementer to retrieve a subset of data from a data source.

A Cache object uses an implementation of this interface during construction to load two initial pages of data. Whenever an uncached value is needed, the cache discards one of these pages and requests a new page containing the value from the IDataPageRetriever.

Public Interface IDataPageRetriever

    Function SupplyPageOfData( _
        ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) _
        As DataTable

End Interface
public interface IDataPageRetriever
{
    DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage);
}

The DataRetriever Class

The following code example defines the DataRetriever class, which implements the IDataPageRetriever interface to retrieve pages of data from a server. The DataRetriever class also provides Columns and RowCount properties, which the DataGridView control uses to create the necessary columns and to add the appropriate number of empty rows to the Rows collection. Adding the empty rows is necessary so that the control will behave as though it contains all the data in the table. This means that the scroll box in the scroll bar will have the appropriate size, and the user will be able to access any row in the table. The rows are filled by the CellValueNeeded event handler only when they are scrolled into view.

Public Class DataRetriever
    Implements IDataPageRetriever

    Private tableName As String 
    Private command As SqlCommand

    Public Sub New( _
        ByVal connectionString As String, ByVal tableName As String)

        Dim connection As New SqlConnection(connectionString)
        connection.Open()
        command = connection.CreateCommand()
        Me.tableName = tableName

    End Sub 

    Private rowCountValue As Integer = -1

    Public ReadOnly Property RowCount() As Integer 
        Get 
            ' Return the existing value if it has already been determined. 
            If Not rowCountValue = -1 Then 
                Return rowCountValue
            End If 

            ' Retrieve the row count from the database.
            command.CommandText = "SELECT COUNT(*) FROM " & tableName
            rowCountValue = CInt(command.ExecuteScalar())
            Return rowCountValue
        End Get 
    End Property 

    Private columnsValue As DataColumnCollection

    Public ReadOnly Property Columns() As DataColumnCollection
        Get 
            ' Return the existing value if it has already been determined. 
            If columnsValue IsNot Nothing Then 
                Return columnsValue
            End If 

            ' Retrieve the column information from the database.
            command.CommandText = "SELECT * FROM " & tableName
            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = command
            Dim table As New DataTable()
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            adapter.FillSchema(table, SchemaType.Source)
            columnsValue = table.Columns
            Return columnsValue
        End Get 
    End Property 

    Private commaSeparatedListOfColumnNamesValue As String = Nothing 

    Private ReadOnly Property CommaSeparatedListOfColumnNames() As String 
        Get 
            ' Return the existing value if it has already been determined. 
            If commaSeparatedListOfColumnNamesValue IsNot Nothing Then 
                Return commaSeparatedListOfColumnNamesValue
            End If 

            ' Store a list of column names for use in the 
            ' SupplyPageOfData method. 
            Dim commaSeparatedColumnNames As New System.Text.StringBuilder()
            Dim firstColumn As Boolean = True 
            For Each column As DataColumn In Columns
                If Not firstColumn Then
                    commaSeparatedColumnNames.Append(", ")
                End If
                commaSeparatedColumnNames.Append(column.ColumnName)
                firstColumn = False 
            Next

            commaSeparatedListOfColumnNamesValue = _
                commaSeparatedColumnNames.ToString()
            Return commaSeparatedListOfColumnNamesValue
        End Get 
    End Property 

    ' Declare variables to be reused by the SupplyPageOfData method. 
    Private columnToSortBy As String 
    Private adapter As New SqlDataAdapter()

    Public Function SupplyPageOfData( _
        ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) _
        As DataTable Implements IDataPageRetriever.SupplyPageOfData

        ' Store the name of the ID column. This column must contain unique  
        ' values so the SQL below will work properly. 
        If columnToSortBy Is Nothing Then
            columnToSortBy = Me.Columns(0).ColumnName
        End If 

        If Not Me.Columns(columnToSortBy).Unique Then 
            Throw New InvalidOperationException(String.Format( _
                "Column {0} must contain unique values.", columnToSortBy))
        End If 

        ' 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

        Dim table As New DataTable()
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        adapter.Fill(table)
        Return table

    End Function 

End Class
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;
    }

}

The Cache Class

The following code example defines the Cache class, which manages two pages of data populated through an IDataPageRetriever implementation. The Cache class defines an inner DataPage structure, which contains a DataTable to store the values in a single cache page and which calculates the row indexes that represent the upper and lower boundaries of the page.

The Cache class loads two pages of data at construction time. Whenever the CellValueNeeded event requests a value, the Cache object determines if the value is available in one of its two pages and, if so, returns it. If the value is not available locally, the Cache object determines which of its two pages is farthest from the currently displayed rows and replaces the page with a new one containing the requested value, which it then returns.

Assuming that the number of rows in a data page is the same as the number of rows that can be displayed on screen at once, this model allows users paging through the table to efficiently return to the most recently viewed page.

Public Class Cache

    Private Shared RowsPerPage As Integer 

    ' Represents one page of data.   
    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 

    Private cachePages As DataPage()
    Private dataSupply As IDataPageRetriever

    Public Sub New(ByVal dataSupplier As IDataPageRetriever, _
        ByVal rowsPerPage As Integer)

        dataSupply = dataSupplier
        Cache.RowsPerPage = rowsPerPage
        LoadFirstTwoPages()

    End Sub 

    ' Sets the value of the element parameter if the value is in the cache. 
    Private Function IfPageCached_ThenSetElement(ByVal rowIndex As Integer, _
        ByVal columnIndex As Integer, ByRef element As String) As Boolean 

        If IsRowCachedInPage(0, rowIndex) Then
            element = cachePages(0).table.Rows(rowIndex Mod RowsPerPage) _
                .Item(columnIndex).ToString()
            Return True 
        ElseIf IsRowCachedInPage(1, rowIndex) Then
            element = cachePages(1).table.Rows(rowIndex Mod RowsPerPage) _
                .Item(columnIndex).ToString()
            Return True 
        End If 

        Return False 

    End Function 

    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 
            Return RetrieveData_CacheIt_ThenReturnElement( _
                rowIndex, columnIndex)
        End If 

    End Function 

    Private Sub LoadFirstTwoPages()

        cachePages = New DataPage() { _
            New DataPage(dataSupply.SupplyPageOfData( _
                DataPage.MapToLowerBoundary(0), RowsPerPage), 0), _
            New DataPage(dataSupply.SupplyPageOfData( _
                DataPage.MapToLowerBoundary(RowsPerPage), _
                RowsPerPage), RowsPerPage) _
        }

    End Sub 

    Private Function RetrieveData_CacheIt_ThenReturnElement( _
        ByVal rowIndex As Integer, ByVal columnIndex As Integer) As String 

        ' Retrieve a page worth of data containing the requested value. 
        Dim table As DataTable = 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)

    End Function 

    ' Returns the index of the cached page most distant from the given index 
    ' and therefore least likely to be reused. 
    Private Function GetIndexToUnusedPage(ByVal rowIndex As Integer) _
        As Integer 

        If rowIndex > cachePages(0).HighestIndex AndAlso _
            rowIndex > cachePages(1).HighestIndex Then 

            Dim offsetFromPage0 As Integer = _
                rowIndex - cachePages(0).HighestIndex
            Dim offsetFromPage1 As Integer = _
                rowIndex - cachePages(1).HighestIndex
            If offsetFromPage0 < offsetFromPage1 Then 
                Return 1
            End If 
            Return 0
        Else 
            Dim offsetFromPage0 As Integer = _
                cachePages(0).LowestIndex - rowIndex
            Dim offsetFromPage1 As Integer = _
                cachePages(1).LowestIndex - rowIndex
            If offsetFromPage0 < offsetFromPage1 Then 
                Return 1
            End If 
            Return 0
        End If 

    End Function 

    ' Returns a value indicating whether the given row index is contained 
    ' in the given DataPage.  
    Private Function IsRowCachedInPage( _
        ByVal pageNumber As Integer, ByVal rowIndex As Integer) As Boolean 

        Return rowIndex <= cachePages(pageNumber).HighestIndex AndAlso _
            rowIndex >= cachePages(pageNumber).LowestIndex

    End Function 

End Class
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;
    }

}

Additional Considerations

The previous code examples are provided as a demonstration of just-in-time data loading. You will need to modify the code for your own needs to achieve maximum efficiency. At minimum, you will need to choose an appropriate value for the number of rows per page of data in the cache. This value is passed into the Cache constructor. The number of rows per page should be no less than the number of rows that can be displayed simultaneously in your DataGridView control.

For best results, you will need to conduct performance testing and usability testing to determine the requirements of your system and your users. Several factors that you will need to take into consideration include the amount of memory in the client machines running your application, the available bandwidth of the network connection used, and the latency of the server used. The bandwidth and latency should be determined at times of peak usage.

To improve the scrolling performance of your application, you can increase the amount of data stored locally. To improve startup time, however, you must avoid loading too much data initially. You may want to modify the Cache class to increase the number of data pages it can store. Using more data pages can improve scrolling efficiency, but you will need to determine the ideal number of rows in a data page, depending on the available bandwidth and the server latency. With smaller pages, the server will be accessed more frequently, but will take less time to return the requested data. If latency is more of an issue than bandwidth, you may want to use larger data pages.

See Also

Tasks

Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control

How to: Implement Virtual Mode with Just-In-Time Data Loading in the Windows Forms DataGridView Control

Concepts

Best Practices for Scaling the Windows Forms DataGridView Control

Virtual Mode in the Windows Forms DataGridView Control

Reference

DataGridView

VirtualMode

Other Resources

Performance Tuning in the Windows Forms DataGridView Control