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:

 string postalCode =    (string)dataset.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 Visual Studio .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 : DataSet {   ...   public class CustomersRow : DataRow {     ...  public string CustomerID {...}   public string CompanyName {...}  } } 

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

 // Create a typed data set CustomerSet dataset = new CustomerSet(); // Fill the data set as normal // ... // Untyped access // string postalCode =  //   (string)dataset.Tables["Customers"].Rows[0]["PostalCode"]; // Typed access string postalCode = dataset.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 in Typed Data Sets

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 in Typed Data Sets

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 foreach(  CustomerSet.OrdersRow row in   dataset.Customers[0].GetOrdersRows()  ) {...} 

Expressions in Typed Data Sets

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 string titleName =  dataset.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:

 void Form1_Load(object sender, EventArgs e) {   // Connection already created   // Adapters already created   // Data set already created  // Fill the data sets   this.customersAdapter.Fill(this.customerSet1, "Customers");   this.ordersAdapter.Fill(this.customerSet1, "Orders");  // Commands for updating already created   // Unique constraint already added   // Relation already established   // Expression column already added   // Populate list boxes   PopulateListBoxes(); } void PopulateListBoxes() {   customersListBox.Items.Clear();  // Enumerate typed customers   foreach( CustomerSet.CustomersRow row in   this.customerSet1.Customers.Rows ) {  if( (row.RowState & DataRowState.Deleted) !=           DataRowState.Deleted ) continue;  // Use the typed expression column   customersListBox.Items.Add(row.ContactTitleName);   }  PopulateChildListBox(); } void PopulateChildListBox() {   ordersListBox.Items.Clear();   int index = customersListBox.SelectedIndex;   if( index == -1 ) return;  // Get row from data set   CustomerSet.CustomersRow parent =   this.customerSet1.Customers[index];   // Enumerate typed child order rows using   // typed relation method GetOrdersRow   foreach( CustomerSet.OrdersRow row in parent.GetOrdersRows() ) {  if( (row.RowState & DataRowState.Deleted) !=          DataRowState.Deleted ) continue;  // Use typed properties   ordersListBox.Items.Add(row.OrderID + ", " + row.OrderDate);   }  } void customersListBox_SelectedIndexChanged(object sender, EventArgs e) {   PopulateChildListBox(); } 

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

 void addRowMenuItem_Click(object sender, EventArgs e) {  // Add a new typed row   CustomerSet.CustomersRow row =   this.customerSet1.Customers.NewCustomersRow();   row.CustomerID = "SELLSB";   row.CompanyName = "Sells Brothers, Inc.";   row.ContactName = "Chris Sells";   row.ContactTitle = "Chief Cook and Bottle Washer";   row.Address = "555 Not My Street";   row.City = "Beaverton";   row.Region = "OR";   row.PostalCode = "97007";   row.Country = "USA";   row.Phone = "503-555-1234";   row.Fax = "503-555-4321";   this.customerSet1.Customers.AddCustomersRow(row);  // Update list boxes   PopulateListBoxes(); } void updateSelectedRowMenuItem_Click(object sender, EventArgs e) {   int index = customersListBox.SelectedIndex;   if( index == -1 ) return;  // Update a typed row   CustomerSet.CustomersRow row = this.customerSet1.Customers[index];   row.ContactTitle = "CEO";  // Update list boxes   PopulateListBoxes(); } void deleteSelectedRowMenuItem_Click(object sender, EventArgs e) {   int index = customersListBox.SelectedIndex;   if( index == -1 ) return;  // Mark a typed row as deleted   CustomerSet.CustomersRow row = this.customerSet1.Customers[index];   row.Delete();  // Update list boxes   PopulateListBoxes(); } void commitChangesMenuItem_Click(object sender, EventArgs e) {   try {  // Update a typed table   this.customersAdapter.Update(dataset, "Customers");  }   catch( SqlException ex ) {     MessageBox.Show(ex.Message,       "Error(s) Committing Customer Changes");   }   try {  // Update a typed table   this.ordersAdapter.Update(dataset, "Orders");  }   catch( SqlException ex ) {     MessageBox.Show(ex.Message,       "Error(s) Committing Order Changes");   }   // Update list boxes   PopulateListBoxes(); } 

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).