Another Alternative to AdventureWorks

Before the days of COM-based VB6 Web Services, I wrote a VB6 client/server demonstration project that emulated early Web-based retail supply chain management (SCM) applications. The "smart client" front end generated orders for consumer electronics products, and included full support for drop shipments from one of three distributors. Unlike AdventureWorks and Northwind, the project included invoicing, credit-card processing, and drop-shipment purchasing from and invoicing by the distributors. Freight charges were obtained from the UPS Web site. The unified client contained about 20,000 lines of code. The SQL Server 2000 back end consisted of six databases: OCE_Cust for sales order processing and invoicing, OCE_Prod for warehousing and shipping, OMB_Network (OmegaBank) for credit card processing, and three distributors: AlphaDist, BetaDist, and GammaDist. The design was based on separate physical locations for each database. For an early book on .NET Web services, I upgraded the Windows client to VB 2002 (with the Wizard) and converted the credit-card and distributor classes to .NET Web services. A flow diagram, description of the project, typical XML document instances and schemas, and links to WSDL documents and database diagrams are here. The final step was conversion of the client—again with the Upgrade Wizard—to a single ASP.NET Web page, as shown here (click the image to open it in a new window):

The page won't garner any design awards, but it displays all information required to process an incoming order, which takes from 0.5 to slightly over 1 second when generating XML purchase and invoice documents for outsourced items. A live demonstration of the project is available here. My April 2002 "Speed SQL Server Data Access" article discusses relative performance of the three successive versions of the OCE project (see Table 1 on page 2).

Note: The Web Services Interoperability Organization (WS-I.org) subsequently created a sample Retailer SCM interoperability test application that's similar to—but much simpler than—the OCE project. WS-I requested permission to republish the flow diagram and some content from the OakLeaf site, which I granted, but I haven't seen any evidence of their use of the material.

AdventureWorks consolidates human-resources, sales, purchasing, and production data in a single database. In the real world, the tables for each schema undoubtedly would reside in separate databases that are under the control of the related groups. Microsoft is heavily promoting service-oriented architecture (SOA) with autonomous Web services, but AdventureWorks' single-database design isn't suited to SOA. A version with four (or more) individual databases, simplified table structures, and more complete sample data would be better suited to the needs of VS 2005/SQL Server 2005 developers and book writers.

VB6 to VB .NET Upgrade Wizard Gets a Bum Rap

As of today, 273 Microsoft MVPs and 4,152 other interested parties have signed a petition to "further develop VB6 and VBA." The petition's request to include "VB.COM" (a.k.a. "Classic VB" and "vbclassic") in future versions of the Visual Studio IDE has fallen on deaf ears in Redmond. One of the petition's FAQs states:

"Microsoft provided a migration wizard intended to ease the transition from VB6 to VB.NET, true. There is near-universal agreement that using this tool to port code assets is an incomplete solution at best, in that leaves myriad "TODO:"'s scattered throughout the translated code. At worst, the migration wizard is an extremely poor choice in that, unlike a complete rewrite, it doesn't take full advantage of all that the new platform offers."

Migrating production VB6 code to VB 2002 or later is a controversial topic, but the VS 2002 Upgrade Wizard worked fine for me. In 2001 I wrote a large-scale ASP Web application (VBScript) with three VB6 data access layer classes (ActiveX DLLs) that connected to a 1-GB demonstration SQL Server database containing the entire contents of the U.S. Code of Federal Regulations (CFR) obtained from the GPO's beta version of the eCFR. (It's still in the beta stage.) Another VB6 project translated the CFR sections' SGML to XHTML and stored the data in a varchar or text column, depending on the size of the section. Subsequently, I used the SOAP Toolkit 2.0 (beta 2) to wrap the classes as RPC/Encoded Web services. Here's the history of the initial version and the first upgrade process:

• CFR-COM (mid-2001) used Active Server Pages and conventional Visual Basic 6.0 ActiveX DLLs for TOC navigation, text display, and full-text search operations. Microsoft Data Access Components (MDAC) 2.7 handled database access.
• CFR-SOAP (late-2001) was CFR-COM upgraded to XML Web services with the Microsoft SOAP Toolkit 2.0 Beta 2 and MDAC 2.7 for database access.

When Visual Studio .NET, as it was called at the time, went gold, I rewrote the client in ASP.NET 1.0. Then I gave the Upgrade Wizard a shot at upgrading the three classes, which totaled about 10,000 lines of code, to VB .NET for conversion to ASP.NET Document/Literal XML Web services. Here's the history:

• CFR-ASPX (January 2002) was CFR-SOAP with all pages upgraded from ASP to ASP.NET with Visual Basic .NET managed code and MDAC 2.7.
• CFR-Final (February 2002) is the final version that replaced COM-SOAP components with ASP.NET XML Web Services for text display, navigation, and search operations. ADO.NET SqlConnection, SqlCommand, and SqlDataReader objects handle database access.

Here are links to the Wizard's Upgrade Reports for the CFRTocWS (table of contents navigation), CFRSectWS (formatted sections), and CFRSearchWS (full-text search). I spent about two or three hours searching for and fixing known VB6 upgrade issues and than ran the Wizard. Each upgrade report had one warning. Not bad for version 1.0 of a very complex code conversion utility, at least in my opinion. I switched from VB6 to VB .NET and never looked back. Writing VBA code to update clients' Access applications now seems to me like trying to write this blog in Brasilian Portuguese.

Note: In August 2002, the OakLeaf CFR Web Service project won the charter Microsoft .NET Best Award for horizontal solutions. The "Use XML Attributes to Navigate Data" .NETInsight article explains the CFRTocWS service's navigation features and "Run Full-Text Database Searches on a Shoestring" describes the CFRSearchWS service.

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.

Northwind vs. AdventureWorks as a Sample Database

Microsoft "strongly urges" book authors to abandon Northwind as the default sample database in favor of SQL Server (SQLS) 2005's updated AdventureWorks (AW) OLTP database. AW has a very complex, highly normalized structure of 68 tables. The Visio 2003 and HTML versions of an AW database diagram require printing to at least a 17-inch x 22-inch sheet to be readable. AW uses SQLS 2005's new user-schema separation feature, which lets you substitute an arbitrary prefix for the traditional database owner's name (dbo by default). Thus the fully-qualified name changes from SQLS 2000 and earlier's ServerName.DatabaseName.OwnerName.TableName to ServerName.DatabaseName.SchemaName.TableName. The SchemaName lets multiple users own a single schema based on their membership in roles or Windows groups. AW has five schemas: HumanResources, Person, Production, Purchasing, and Sales.

Note: An Accounting schema and Accounting.Invoices table are conspicuous by their absence. Apparently, the firm accepts and fulfills orders but doesn't issue invoices or charge credit cards for goods shipped. Northwind also lacks an Invoices table.)

The Person.AddressType table has six types of addresses: Billing, Home, Main Office, Primary, Shipping, and Archive. The table contains foreign-key values for StateProvince and CountryRegion tables. Thus a T-SQL query to return all columns for an individual customer address is (from the Sales.vIndividual view):

SELECT i.[CustomerID], c.[Title], c.[FirstName], c.[MiddleName], c.[LastName], c.[Suffix], c.[Phone], c.[EmailAddress], c.EmailPromotion], a.[AddressLine1], a.[AddressLine2], a.[City], StateProvinceName = sp.[Name], a.[PostalCode], CountryRegionName = cr.[Name], i.[Demographics], c.[AdditionalContactInfo]

FROM [Sales].[Individual] i INNER JOIN [Person].[Contact] c ON c.[ContactID] = i.[ContactID] INNER JOIN [Sales].[CustomerAddress] ca ON ca.[CustomerID] = i.[CustomerID] INNER JOIN [Person].[Address] a ON a.[AddressID] = ca.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] FROM [Sales].[Customer] WHERE [Sales].[Customer].[CustomerType] = 'I')

Creating (or attempting to create) an updatable typed DataSet for individual customers requires six DataTables and generates a DataSet designer file of more than 6,000 lines. I haven't yet attempted to write FillBy... methods for a parameterized details form that would enable updates and inserts with bound text boxes and dropdown lists.

Following is a simple DataGridView of the Sales.vIndividual view. Click the thumbnail to open it in a resizable window.

Without a parameterized FillBy... method, the Fill method returns 18,508 records and populates the lookup lists. Thus, it takes about 10 seconds to open the release version of AppName.exe with the client app and SQLS 2005 instance running under Windows 2003 Server on a 2.6-GHz Pentium 4 box with 1 GB RAM. TaskMan shows that the AppName.exe process consumes 101 MB. Parameterizing the seven SELECT queries with individual FillBy... methods can solve the opening time and resource-consumption problems but doesn't address the complexity issue.

Northwind's dbo.Customers table provides readable BillTo addresses, and the dbo.Orders table supplies ShipTo information without requiring a series of INNER JOINs. It's my contention that AW's complexity will distract readers when learning typical tasks, such as creating a Customers-Orders-LineItems master-details-subdetails form. Thus, I'm using old-timey Northwind for most design and coding examples in my book. Another reason for using Northwind is it's popularity in VS 2005's offline help files. Searching the February CTP with 'Northwind and Walkthrough' returns 62 hits; 'AdventureWorks and Walkthrough' returns 3. Similarly, 'Northwind and HOWTO' returns 47 hits; 'AdventureWorks and HOWTO' returns 0. If the SQLS team "strongly urge[d]" the User Ed(ucation) folks who write the VS 2005 help files to use AW, the recommendation appears to have fallen on deaf ears.

Note: One of the book's sample projects lets readers populate a Northwind.SalesOrders table—an updated clone of the Northwind.Orders table—with an unlimited number of rows containg randomized data for sequential dates, a specified average number of line items per order, and random ProductID values.

The Opening Salvo - Visual Studio 2005 and SQL Server 2005

Welcome to the OakLeaf Systems blog. I'm in the process of writing a new book about database programming with Microsoft Visual Basic 2005, the successor to Visual Basic 6.0 and Visual Basic .NET. I plan to share my experiences—good and not so good—with the latest Community Technical Previews (CTPs) and betas of Visual Studio 2005 and SQL Server 2005/SQL Express. Stay tuned ...