Manage Data With VS 2005 (Continued)

At this point, you fill the CustomersTableAdapter and Order_DetailsTableAdapter with records related to those returned by the preceding query:

SELECT CustomerID, CompanyName, ContactName, 
ContactTitle, Address, City, Region, PostalCode, 
Country, Phone, Fax 
FROM dbo.Customers WHERE CustomerID IN 
(SELECT TOP 100 CustomerID 
FROM dbo.Orders ORDER BY OrderID DESC)

and

SELECT OrderID, ProductID, UnitPrice, Quantity, 
Discount 
FROM dbo.[Order Details] WHERE OrderID IN 
(SELECT TOP 100 OrderID 
FROM dbo.Orders ORDER BY OrderID DESC)

Replacing the Fill method with the FillLast100Orders or FillByLast100Orders method in the Form_Load event handler creates a much more lightweight DataSet and doesn't affect autogenerated UPDATE, INSERT, and DELETE commands.

The BindingSource class, as its name implies, links simple and complex data-bound controls to DataTables and manages record navigation and editing. A TableNameBindingSource has DataSource and DataMember properties. For the sample form's Customers parent table, the DataSource is NorthwindDataSet and DataMember is the Customers DataTable. Autogenerated code in the Form1.Designer.vb file binds each textbox's Text property to the appropriate field of the CustomersBindingSource. The OrdersBindingSource uses the CustomersBindingSource as its DataSource and FK_Orders_Customers (the CustomerID foreign key constraint) as its DataMember to display in the DataGridView only Orders records related to the current Customer record. Similarly, the Order_DetailsBindingSource has OrdersBindingSource as its DataSource and FK_Order_Details_Orders as its Data Member. TableNameBindingSource also has Filter and Sort properties; Filter takes a string that corresponds to a SQL WHERE clause (without the WHERE keyword), and Sort takes an ORDER BY string (without ORDER BY).

BindingSources handle disconnected record navigation with MoveFirst, MoveNext, MovePrevious, MoveLast, AddNew, and Find methods. Use this VB code to set the CustomersBindingSource.Current item to the record for Rattlesnake Canyon Grocery:

CustomersBindingSource.Position = 
        CustomersBindingSource.Find("CustomerID", 
        "RATTC")

Add this code to filter Customers records for U.S. firms only, and then remove the filter:

CustomersBindingSource.Filter = 
        "Country = 'USA'"
...
CustomersBindingSource.RemoveFilter

This code shows you how to reverse the default sort order of the CustomersBindingSource:

CustomersBindingSource.Sort = 
        "CustomerID DESC"

Invoke the BindingSource.RemoveAt(intIndex) method to delete the record specified by intIndex; to delete the current record from the DataSet, apply the BindingSource.Remove method.

The new complex-bound DataGridView replaces VS 2002/3's less-than-stellar bound DataGrid control for Smart Client projects. Dragging the default table node from the Data Source window to a form generates a TableNameDataGridView with columns and rows that have DataGridViewTextBoxColumns bound to the corresponding TableNameBindingSource's data members. A Smart Task tag lets you select an existing data source or create a new one; edit, add, or reorder columns; enable adding, editing, or deleting rows; or dock the DataGridView below the DataNaviagator. To accommodate non-text data types, you can substitute other ColumnTypes—DataGridViewCheckBoxColumns for Boolean values, DataGridViewImageColumns for graphics, and DataGridViewLinkColumns for URLs. DataGridViewButtonColumns fire events and DataGridViewComboBoxes act as bound pick lists that you populate with read-only data sources from foreign keys (see Figure 3). Clicking on the Columns property's Builder button opens the Edit Columns dialog, which lets you select the ColumnType for existing or added bound columns.