Manage Data With VS 2005 (Continued)

Typed DataSets Create Heavyweight Projects
Creating a three-table (Customers, Orders, Order Details) typed DataSet autogenerates large chunks of code. The NorthwindDataSet.xsd file contains a 698-line annotated XML schema and the NorthwindDataSet.Designer.vb file weighs in at 3,391 instructions. (The VS 2003 versions created by choosing Generate DataSet from the Data menu are 70 and 1,571 lines, respectively.) The DataSet designer file defines partial classes, which let you customize DataSet behavior with partial classes that you add to a NameDataSet.vb/cs source file. Partial classes enable retaining your DataSet modifications when you refresh the DataSet's schema, which overwrites the NameDataSet.Designer.vb/cs file. Cached DataSets provide the local data source that Smart Clients access when disconnected from the database or network.

Dragging a table—Customers, in this example—as a Details view from the Data Sources window to a form adds labeled and bound TextBox, CheckBox, or DatePicker controls for each table field you select in the Data Sources window. (A drop-down list lets you select between dropping a Details view or DataGridView.) You also can choose to bind a NumericUpDown, ComboBox, Label, LinkLabel, or ListBox to a table field. Related child (detail) tables appear as fields added to their parent (master) table, which simplifies creating master-detail forms. The first table you drop on a form adds a TableNameDataNavigator ToolStrip control that has predefined VCR-style record navigation and Insert, Delete, and Save Data buttons. Then DatabaseNameDataSet, TableNameBindingSource, and TableNameTableAdapter data component icons materialize in the form designer's tray (see Figure 1). The new DataSet designer gains a field list with editable TableAdapter items (see Figure 2).

Dropping the table icon on the form adds this command to the Form1_Load event handler to fill the first TableAdapter:

Me.NameTableAdapter.Fill(Me.NameDataSet.Orders)

The first table also adds a bindingNavigatorSaveItem_Click event handler to update the base table(s) with edits made to the DataSet by the user.

Dragging related tables to generate and populate new DetailsView or DataGridView controls adds TableNameBindingSource and TableNameTableAdapter data components to the tray, table field lists to the DataSet designer, and TableNameTableAdapter.Fill instructions. The default Form1.Designer.vb file for a basic Customer-Orders-Order Details editing form contains about 670 instructions for a total of more than 4,000 code lines for the project. Clearly, VS 2005 doesn't generate "data-bound forms without code," but, unlike its predecessors, you don't need to write code or edit property values to demonstrate a superficial—but non-trivial—database front end in five minutes or less. Reaching this point in VS 2003 requires a substantial amount of DataSet and DataAdapter customization and code to handle PropertyManager and CurrencyManager objects. Partial classes let you customize strongly typed DataSet code without losing your modifications when regenerating the DatabaseDataSet.designer.vb file.

The new TableAdapter class is a strongly typed, RDBMS-independent wrapper over a SqlDataAdapter, OracleDataAdapter, OleDbDataAdapter, or OdbcDataAdapter. TableAdapters and their associated DataTables support default and parameterized Fill and GetData SELECT queries, as well as default and customized UPDATE, INSERT, and DELETE commands. The completely redesigned DataSet designer/editor provides a graphic environment for customizing the default TableAdapters that you add when generating the DataSet with the Wizard. You can edit the Fill method's SELECT query to add a parameter or TOP n modifier to reduce the DataSet's size. This SELECT statement is the main query for auto-generating the UPDATE, INSERT, and DELETE commands. Alternatively, you can add named queries to serve the same purpose. For example, you might want to return only the last 100 Orders records and their related Customers and Order Details records. In this case, right-click on the OrdersTableAdapter header and choose Add Query to start the Table Adapter Query Configuration Wizard, which gives you the option of using SQL SELECT statements, creating a new stored procedure, or using an existing stored procedure. The FillLast100Orders and GetLast100Orders SELECT statements are:

SELECT TOP 100 OrderID, CustomerID, 
EmployeeID, OrderDate, RequiredDate, 
ShippedDate, ShipVia, Freight, ShipName, 
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, 
ShipCountry 
FROM dbo.Orders ORDER BY OrderID DESC