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.
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 );
