WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button Liberty on Beta 2

Just-In-Time Data Loading For DataGrids

by Jesse Liberty
12/20/2005

In my previous column I said that I will be working on an application and discussing it as I go. Well, it turns out that real customers and real life haven't actually gotten the project past the "I Have A Dream" stage, so it will be a few weeks before I have much that is interesting to report (though in the meantime you may want to take a look at the web services technology from Amazon that I'll be using).

While I've been non-productive on that project, a client posed the following problem: she has a database with 2 million records and wants to display these records in a data grid, but does not want to load them all into memory from the database. She wants them loaded "just in time," maximizing performance and minimizing memory load on the web server. After all, the vast majority of users will need to see only a small fraction of the records.

It turns out that the new DataGridView has a property VirtualMode (which defaults to false), that provides the capabilities needed. Microsoft has an excellent walk-through on this topic, and this article will explore a direct solution to this problem as well as showing you how to quickly create an application to create a very large edition of some Northwind tables to test with.

In my next article, I'll show how you can bypass this nifty approach and use a more generic approach that will work with any data grid (using the Infragistics grid as an alternative, for example).

Related Reading

Visual C# 2005: A Developer's Notebook
By Jesse Liberty

Creating the Virtual Grid

Create a new Windows Application and name it JIT Data. Use your language of choice; for this column I'll write in C# as I've been writing a lot of VB lately.

Drag a DataGridView control onto your form, and name it dgvJIT. Also add a label control named lblMsg and set its text to "Ready." (While you are at it, widen the form, widen the grid, set the title of the form to "JIT Data" and change the class file name for the form to JITData.cs.)

Your next step is to change the VirtualMode property of the grid from false to true. This can be done programmatically, but it is easier to just set it in the properties window. The bad news is that you can no longer bind a data source to the grid, but the good news is that you can easily create a cache to hold a page or two of data, and a simple class to fetch data as needed.

There is a bit of a chicken and egg phenomenon here[1], because your class needs a member variable of type Cache, and the constructor needs an instance of DataRetriever so it is tempting to stop and create those classes. However, to understand how those classes work and what they are for, it is better to create them in the order they are used. To cut this knot, you'll stub out these two classes, providing just enough to move forward, and you'll come back and flesh them out as you need them.

Even to stub out the DataRetriever you want to indicate that it implements the IDataPageRetriever interface that Microsoft suggests you define, so let's take a small step back and define that interface first. To do so, right-click on the project and choose Add...New item, stare at the dialog box for a while wondering why the templates are not in alphabetical order, and then choose Interface and name your new file IDataPageRetriever.cs, filling it with this definition:

using System.Data;

namespace JITDataColumn
{
   interface IDataPageRetriever
   {
      DataTable SupplyPageOfData(
         int lowerPageBoundary, int rowsPerPage );
   }
}

Next, stub out your DataRetriever class and its constructor:

public class DataRetriever : IDataPageRetriever
{
  // member variables here
   public DataRetriever( string database, string table )
   {
        //  Properiesy called by JITData Constructor
      public DataColumnCollection Columns { get { return new DataColumnCollection(); } } 
        public int RowCount { get { return 0; } }
   }

Finally, stub out the Cache class and its constructor:

class Cache
{
    public Cache( IDataPageRetriever dataSupplier, int rowsPerPage )

You are now (finally!) ready to create the JITData constructor:

public JITData()
{
   InitializeComponent();   
   const int PageSize = 10;
   DataRetriever dr = new DataRetriever();
   memoryCache = new Cache( dr, PageSize );
   foreach ( System.Data.DataColumn column in dr.Columns )
   {
      grid.Columns.Add( column.ColumnName, column.ColumnName );
   }
   grid.RowCount = dr.RowCount;
}

As you can see, you create a DataRetriever, and then pass that instance (along with the page size) to the constructor for the Cache. You then ask the DataRetriever for its Columns property (described below and stubbed out for now) and iterate through the collection, adding columns to the grid. Finally, you ask the DataRetriever for the RowCount to assign to the grid.

Pages: 1, 2, 3, 4, 5, 6

Next Pagearrow