oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Just-In-Time Data Loading For DataGrids
Pages: 1, 2, 3, 4, 5, 6

The DataRetriever

It is the job of the DataRetriever to get the data. Interaction with the underlying database is fully encapsulated in the DataRetriever, which has three interesting properties:

Property Purpose
RowCount Computes the number of rows in the entire data set
Columns Returns a DataColumnCollection of all the columns in the table retrieved by the query
CommaSeparatedListOfColumns Returns a string that represents a comma-separated list of all the column names

The only method in DataRetriever is SupplyPageOfData, whose job is to retrieve one page's worth of data from the database and to return it as a table.

public DataTable SupplyPageOfData( int lowerPageBoundary, int rowsPerPage )

   if ( columnToSortBy == null )
      columnToSortBy = this.Columns[0].ColumnName;

   // 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;

Note that by taking this level of control of how your data is retrieved, you now must work at the ADO.NET level, and create DataTables and interact with Connection objects -- details that are normally encapsulated within the .NET 2.0 Data controls. ADO.NET is still the big hammer, there when you need it, but keep your thumbs out of the way!

Choosing the Database

Before testing the application, return to the JitDataDesign and add a group box with the text "Which DB?" inside of which you'll place two radio nuttons, named rbNorthwind and rbNWBig respectively. Set the checked value of the first radio button to true, as shown in Figure 1.

Which DB Buttons
Figure 1. Setting up the "Which DB?" radio buttons

You'll see how to create NWBig below; for now, you'll keep Northwind checked.

Return to the constructor for the page and add two strings, one to hold the database choice based on the user's selection of buttons, and the other to hold the table name, which (for now) you'll hardwire to Orders. (Creating a UI to change tables is left as an exercise for the reader, along with creating a UI to change the number of pages of data stored in the cache!)

const int PageSize = 10;
string theDatabase = this.rbNorthWind.Checked ? "Northwind" : "NWBig";
string theTable = "Orders";
DataRetriever dr = new DataRetriever( theDatabase, theTable );

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

Next Pagearrow