Northwind vs. AdventureWorks as a Sample Database - Round 2

Here's more on the issue of AW vs. NW as a sample database for books about Visual Studio and SQL Server 2005. This post uses AW's Sales.SalesOrderHeader and Sales.SalesOrderDetail tables as examples.

It's easy to create a master-details form from these two tables but providing the user readable values from foreign-key fields is a pain. As an example, The Sales.SalesOrderHeader table has nine foreign-key fields: CustomerID, ContactID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, and CurrencyRateID.

Here's a screen capture of a sample project that loads the last 1,000 Sales.SalesOrderHeader rows and related Sales.SalesOrderDetail rows in descending order. Click the thumbnail to open it in a resizable window.

Note: VS 2005 introduces "smart captions" for autogenerated detail view controls. Spaces substitute for underscores and are inserted between a sequence of lower-case and upper-case characters. Unfortunately, DataGridView column headers lack this convenient feature.

Each foreign-key field requires a lookup operation to display readable data from the related table; some values require joins to related sub-tables. Lookups require adding 11 or more TableAdapters to the DataSet. Northwind requires only three or four added lookup tables: Employees, Shippers, Products and, optionally Customers.

Some text boxes, such as Sales Order Number and Total Due are bound to computed columns, and the uSalesOrderHeader trigger updates Revision Number, Sub Total, and Modified Date text box values. Thus, these six text boxes are read-only. The DataGridView's Line Total column also is computed, and the uSalesOrderDetail trigger updates the DateModifed column. However, the autogenerated UpdateCommand.CommandText instructions attempt to update the computed columns, which throws an exception. Elmininating the exception requires editing the dataset code to remove the column(s) from the UPDATE statement and the corresponding parameter(s). Partial classes let you supplement autogenerated code but not alter it directly.

Most AW tables have guid columns, which complicates INSERT operations. You must add code to the DataGridView's UserAddedRow event handler to add a Guid.NewGuid() value to the rowguid Cell. The DateModified column also requires Now or the like as a datetime value, which the iSalesOrderDetail trigger overwrites. Similar modifications are required for the corresponding SalesOrderHeader columns.

AW is a useful sample database for demonstrating gotchas with UPDATE and INSERT operations on tables that have computed columns and values inserted or updated by triggers. However, creating a real-world project with dropdown lists to enable editing the master form and detail DataGridView with readable values is more than a challenge-and-a-half, especially for users new to VS and VB. I'll probably include sample projects similar to these in an advanced chapter and a forthcoming Visual Studio Magazine article. Like the VS 2005 help files, I plan to stick with good old Northwind for the majority of the book's sample code and project examples.