oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

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

Testing with a Very Large Database

If you have this working with Northwind, you'll want to create a new database with lots of records, to prove to yourself that this kind of caching can be very efficient. To do so, I've whipped together a quick-and-dirty utility program to create additional records in a copy of the Northwind database. Here's how you do it. First, make a copy of Northwind (the easiest way is to backup the database and then restore it to a new name, e.g., NWBig.) Then create a new Windows Project named BigNWBuilder (or just download the finished project).

Rename the form to BigNWBuilder.cs, set its text property to "Big NW Builder" and set the form size to 225,275. Add the controls as indicated in Figure 3.

Builder Form
Figure 3. Adding controls to the Builder form

Note that the text box txtStartingNum and the label lblStartingNum both should have their Enabled and Visible properties set to false in the properties window.

For this quick-and-dirty utility, the tables that the program updates (Customers and Orders) are hard-wired. You are free, of course, to add user-interface elements to enable the user to populate other tables.

When the user clicks Go the starting number is retrieved from the database by checking how many records are already in the Customers table. That value is then placed in txtStartingNum which is then made visible along with its associated label. The number of records to add (retrieved from the UI) and the starting number are then passed to the UpdateDB method. The job UpdateDB is to insert values into the Customers table, and then to add from 1 to 5 orders for each of the newly added customers.

One tricky aspect to all this is that in Northwind (and thus in BigNW), the customer ID is a five-character string, which must be unique. To create these IDs, we'll generate a five-letter string by converting a counter to base 26 (where a = 0, b = 1, etc.). This can (and will) still generate IDs that conflict with existing records, but it will not create its own duplicates as it goes.

To keep the code simple we set the companyName to Company_XXXX and the contactName to Mr. JohnXXXX, where the final four characters are the newly generated company ID. We set the address to be the company ID with Main Street appended. All the other fields are set to hard-coded harmless values.

customerID = TranslateToBaseTwentySix( idNumber );
companyName = "Company_" + customerID;
contactName = "Mr. John " + customerID;
address = idNumber + " Main Street";

All of this is then inserted into the Customers table and errors are entered into the list box lbErrors.

   int numRowsAffected = command.ExecuteNonQuery();
catch (Exception dataException)
   this.lbErrors.Items.Add("Cust: " + idNumber + ": " +
      dataException.Message );
   this.lbErrors.SelectedIndex = lbErrors.Items.Count - 1;

Once the customer is added, a pseudo-random number between 1 and 5 is generated, and that many orders are created for the customer:

   // add between 1 and 5 orders per user
   Random ran = new Random();
   int numOrders = ran.Next(1,6);
   for (int i = 0; i < numOrders; i++)
      // set the shipping cost between $1 and $100
      int shipCost = ran.Next( 1, 101 );
      command.CommandText = "Insert orders values ('" +
         customerID + "',1" + ", GetDate(), GetDate(), GetDate(),1," + shipCost +
         ", 'UPS', '" + address + "','" + city + "','" + state +
         "','" + zip + "','" + country + "')";
      int numRowsAffected = command.ExecuteNonQuery();
   }     // end for
}        // end try

Every 500 records, we update the UI, which both keeps the user informed, and provides an opportunity for the stop button to work (DoEvents yields the thread).

if ( idNumber % 500 == 0 )
   this.lblStatus.Text = customerID + " (" + idNumber.ToString("N0") + ")";

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

Next Pagearrow