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 SetTo 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. ConstraintsAdding 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. RelationsTo 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 ExpressionsYou 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 FormAfter 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). |