Typed Data Sets

In addition to potential problems in creating and relating the various data objects, another source of much coding, and therefore potential for error, is in the access to columns. By default, columns are named with a string and then cast to the specific type that is expected:

 Dim postalCode As String = _   CStr(mydataset.Tables("Customers").Rows(0)("PostalCode")) 

If you get it wrong, the best thing that can happen is an exception at run time; the worst is a silent failure that hides the error. It would be useful if we could make the compiler help figure out when there are errors so that we can find and correct them before we even run the code. That's the job of the typed data set.

Creating a Typed Data Set

To create a typed data set, you add a new data set to your project from the Add New Item menu. This adds an .xsd file to the project. In VS.NET you use an XML schema document (.xsd) to generate the classes and use DataSet Designer to configure a typed data set. Figure 12.9 shows an empty typed data set.

Figure 12.9. An Empty Typed Data Set

Apparently this picture is worth 18 words, and, as it states, you can drag objects from either Server Explorer or the Toolbox. Most often it will be Server Explorer, as shown in Figure 12.10.

Figure 12.10. Server Explorer

Server Explorer allows you to navigate to various data providers. If you are using SQL Server, you can navigate directly to the servers to find databases. Otherwise, you will need to create a data connection or select an existing one. If you created new data connections in the earlier examples for adding a connection to a form, you should notice that connection shown here as well.

To create a typed data set, you drag any number of tables, stored procedures, views, or functions from the database onto the Designer surface. Dragging and dropping the Customers and Orders tables form the Northwind database will show you something like Figure 12.11.

Figure 12.11. Typed Data Set with New Tables

Here, dropping the tables onto the design surface created two tables: one for Customers and one for Orders. In each table, the Designer was able to ask the database for the primary key, as indicated by the key icons. In addition, notice that each column is typed, which is what puts the "typed" in "typed data set." Saving the schema generates a new type having the same name as the schema and deriving from the DataSet base class. Inside this new type are nested types that provide type-safe wrappers around each row:

 Public Class CustomerSet   Inherits DataSet   ...   Public Class CustomersRow       Inherits DataRow       ...       Public Property CustomerID() As String       Public Property CompanyName() As String   End Class End Class 

With the new typed data set, the code to pull data out of a column is now shorter and more robust:

 ' Fill the data set as normal ' ... ' Untyped access ' Dim postalCode As String= '   CStr(dataset.Tables("Customers").Rows(0)("PostalCode")) ' Typed access Dim postalCode As String = mydataset.Customers(0).PostalCode 

Because it derives directly from the DataSet class, a CustomerSet object can be filled and manipulated exactly like a regular data set. It is a typed data set, so the new typed properties simplify the code. As an additional benefit, the tables and columns in the typed data are also available in VS.NET's IntelliSense, causing typed data sets to further decrease our typing [sic].

Using typed data sets in this way is very helpful, but what about constraints, relations, and expressions? Data Set Designer supports them, too.

Constraints

Adding a unique constraint to a table is a matter of dropping a key onto the table from the XML Schema Toolbox, as shown in Figure 12.12.

Figure 12.12. XML Schema Toolbox

Dropping a key produces the Edit Key dialog, as shown in Figure 12.13.

Figure 12.13. Edit Key Dialog

To add the unique constraint, you specify the table in the Element drop-down, and the column in the Fields. If you want to create a multicolumn unique constraint, such as requiring that the combination of first and last name be unique, you can specify more than one column under Fields. Adding a foreign key constraint requires adding a Relation from the Toolbox.

Relations

To add a relation to a typed data set, drop a Relation onto the table that will serve as the parent of the relation. This opens the Edit Relation dialog, where you finish the job, as shown in Figure 12.14.

Figure 12.14. Edit Relation Dialog

The Parent element will be set based on the table you dropped the Relation onto, so you'll need to set the Child element. After you do, the Name will be set to something fairly intuitive, although you can change it if you like. By default, the Key Fields (which form the relation) will be the two primary keys from the two tables, which is likely what you want to relate in the first place.

In most cases this dialog will give you all the options you need (the documentation can explain the subtleties of this dialog if you need more). A relation will show in the Designer something like the one shown in Figure 12.15.

Figure 12.15. Typed Data Set with a New Relation

Not only does setting a relation in the Designer free us from writing the code to establish the relation, but it also exposes the relation as a type-safe method for navigation:

 ' Navigate the relation from the parent to the children Dim row As CustomerSet.OrdersRow For Each row In mydataset.Customers(0).GetOrdersRows()   ... Next 

Expressions

You can add expression columns to a table by typing in a new column name and type at the end of a table, as shown in Figure 12.16.

Figure 12.16. Adding a New Column to Be Used as an Expression Column

When you've got the new column, you add an Expression property in the Property Browser:

 ContactTitle + ', ' + ContactName 

Like all the other columns, the new expression column will be accessible through a strongly typed property:

 ' Get the first Contact's Title and Name Dim titleName As String = mydataset.Customers(0).ContactTitleName 

Adding a Typed Data Set to a Form

After you've designed the typed data set class, you can add an instance of it to a form (or any other design surface) by dropping a DataSet from the Data Toolbox onto a form. This action produces the Add Dataset dialog, as shown in Figure 12.17.

Figure 12.17. Adding a Typed Data Set to a Form

This dialog allows you to specify a typed or untyped data set. Any typed data sets that have been created in the project will show up in the drop-down list under Typed Dataset. As you'd expect, pressing OK generates the code to create an instance of the typed data set class. With the typed data set in place, and using the Data Adapter Configuration Wizard and Query Builder, we need to enter only a few lines of code to achieve the functionality of Figure 12.4:

 Sub Form1_Load(sender As Object, e As EventArgs)   ' Connection already created   ' Adapters already created   ' Data set already created  ' Fill the data set  Me.customersAdapter.Fill(Me.customerSet1, "Customers")   Me.ordersAdapter.Fill(Me.customerSet1, "Orders")   ' Commands for updating already created   ' Unique constraint already added   ' Relation already established   ' Expression column already added   ' Populate list boxes   PopulateListBoxes() End Sub Sub PopulateListBoxes()   customersListBox.Items.Clear   ' Enumerate typed customers   Dim row As CustomerSet.CustomerRow   For Each row In Me.customerSet1.Customers.Rows       If (row.RowState And DataRowState.Deleted) = _           DataRowState.Deleted) Then           ' Use the typed expression column           customersListBox.Items.Add(row.ContactTitleName)       End If   Next   PopulateChildListBox() End Sub Sub PopulateChildListBox()   ordersListBox.Items.Clear()   Dim index As Integer = customersListBox.SelectedIndex   If index = -1 Then Exit Sub   ' Get row from data set   Dim parent As CustomerSet.CustomersRow = _       Me.customerSet1.Customers(index)   ' Enumerate typed child order rows using   ' typed relation method GetOrdersRow   Dim row As CustomerSet.OrdersRow   For Each row In parent.GetOrdersRows()       If (row.RowState And DataRowState.Deleted) = DataRowState.Deleted           Then           ' Use typed properties           ordersListBox.Items.Add(row.OrderID.ToString() & _             ", " & row.OrderDate.ToString())       End If   Next End Sub Sub customersListBox_SelectedIndexChanged(sender As Object, _      e As EventArgs)      PopulateChildListBox() End Sub 

Adding CRUD (create/retrieve/update/delete) functionality is also more convenient using a typed data set:

 Sub addRowMenuItem_Click(sender As Object, e As EventArgs)   ' Add a new typed row   Dim row As CustomerSet.CustomersRow = _       Me.customerSet1.Customers.NewCustomersRow()   With row       .CustomerID = "SELLSB"       .CompanyName = "Sells Brothers, Inc."       .ContactName = "Chris Sells"       .ContactTitle = "Chief Cook and Bottle Washer"       .Address = "555 Not My Street"       .City = "Beaverton"       .Region = "OR"       .PostalCode = "97007"       .Country = "USA"       .Phone = "503-555-1234"       .Fax = "503-555-4321"   End With   Me.customerSet1.Customers.AddCustomersRow(row)   ' Update list boxes   PopulateListBoxes() End Sub Sub updateSelectedRowMenuItem_Click(sender As Object, e As EventArgs)   Dim index As Integer = customersListBox.SelectedIndex   If index = -1 Then Exit Sub   ' Update a typed row   Dim row As CustomerSet.Customers.Row = _     Me.customerSet1.Customers(index)   row.ContactTitle = "CEO"   ' Update list boxes   PopulateListBoxes() End Sub Sub deleteSelectedRowMenuItem_Click(sender As Object, e As EventArgs)   Dim index As Integer = customersListBox.SelectedIndex   If index = -1 Then Exit Sub   ' Mark a typed row as deleted   Dim row As CustomerSet.CustomersRow = _     Me.customerSet1.Customers(index)   row.Delete()   ' Update list boxes   PopulateListBoxes() End Sub Sub commitChangesMenuItem_Click(sender As Object, e As EventArgs)   Try       ' Update a typed table       Me.customersAdapter.Update(mydataset, "Customers")   Catch ex As SqlException       MessageBox.Show(ex.Message, _           "Error(s) Committing Customer Changes")   End Try   Try       ' Update a typed table       Me.ordersAdapter.Update(mydataset, "Orders")   Catch ex As SqlException       MessageBox.Show(ex.Message, _           "Error(s) Committing Order Changes")   End Try   ' Update list boxes   PopulateListBoxes() End Sub 

With typed data sets, the code we're responsible for typing is smaller and more robust. The DataSet Designer generates the code for coercing data, and the Forms Designer generates the code for establishing the connection, the commands, the adapters, and the data set. That leaves us to write only the fun stuff (mostly).