Creating a Typed DataSet

Creating a Typed DataSet

ADO.NET is comprised of classes. As you know, classes can be generalizedinherited fromto extend existing behaviors and add new behaviors. One such set of generalizations is to create typed DataSet , DataTable , and DataRow objects. (We'll speak in terms of typed DataSet objects , but keep in mind the same basic principle applies to other ADO.NET classes by way of supporting inheritance.)

In general, when you use a DataSet , you can access a DataTable in that DataSet through the DataSet.Tables collection property. The same is true of DataRow objects: you can access rows through the DataTable.Rows collection. The end result is complicated-looking object strings and indexed collections, for example, DataSet.Tables(0).Rows(1) . This kind of code is a bit unsightly and considered weakly typed because we are dealing with ADO.NET types and generic objects returned from these types. Ultimately you get down to a row and have to typecast that field to work with a specific type of data.

The object-oriented paradigm promotes strongly typed objects, and there is every reason a good practical approachusing strong typesshould be carried over into database applications. ADO.NET and Visual Studio .NET actually promote strongly typed DataSet objects using XML Schemas. I say that ADO.NET supports strongly typed objects because you can inherit from and extend ADO.NET types, and Visual Studio .NET facilitates this by providing an XML Schema designer. In fact, Visual Studio .NET goes one step further: It will generate the strongly typed ADO.NET classes for you.

In this section I'll show you how to add and create an XML Schema, generate strongly typed DataSet objects, and use those generalized types. To finish we'll compare similar weakly typed DataSet objects with strongly typed DataSet objects.

Defining an XML Schema

Visual Studio .NET uses templates to create projects and project items. A template at its essence is parameterized source code. For example, when you add a new class to a project, there is a parameter.cs file containing generic source code for a class. A wizard uses the automation model for Visual Studio .NET to replace the class name and incorporate the class file into your project. There are dozens of templates, including a template for an XML Schema. (XML Schema files have the .xsd extension and fall under the auspices of XSD.)

TIP

There are dozens of templates for Visual Basic .NET. To see examples of project templates, explore \Program Files\Microsoft Visual Studio .NET\Vb7\VBWizards . VBProjects and VBProjectItems in the same VB7 folder contain additional templates.

XSD refers to another technology in .NET that solves a practical problem. XSD, or XML Schemas and Data, allow you to convert weakly typed database code into strongly typed database code. Your code becomes more comprehensible, and it is possible to implement the code in the vernacular of the solution domain. We will begin by adding an XSD item to a project. (You can start a new Windows Forms or Web Forms application for this purpose. The sample application TypedDataSetDemo.sln contains my example code.) To add an XML Schema to a project, follow these steps.

1. With a project open in Visual Studio .NET, select FileAdd New Item from the menu.

2. From the Add New Item dialog, select the XML Schema applet from the templates list.

3. In the Add New Item dialog, change the .xsd filename to CustomerOrders.xsd and click OK.

The preceding three steps will add a file named CustomerOrders.xsd to your project solution. The XSD file has its own designer, and the designer will generate a .xsx (resource) file and ultimately a .vb source file containing the generated typed DataSet objects.

With the XSD file selected and open in Visual Studio .NET, you will notice some changes in the IDE. The Toolbox will have an XML Schema tab, and a Schema menu will appear in Visual Studio .NET's main menu bar. These features exist to help you manually design the XML Schema, and you are welcome to use them if you want to define the schema from scratch. Fortunately we can also define the schema by inference by dragging and dropping tables from the Server Explorer.

Adding Multiple Tables to a DataSet

Let's suppose we have elected to create a strongly typed DataSet for the Northwind database. (I am assuming you have a data connection already in the Server Explorer.) We can drag tables from the Server Explorer onto the XML Schema designer, and the designer will generate the XML Schema code for us (Figure 12.5).

Figure 12.5. Defining an XML Schema by dragging tables from the Server Explorer in Visual Studio .NET.

Figure 12.5 shows three tables in the XML Schema designer. Dragging the Customers table created the topmost element and the Customers element. I renamed the topmost element CustomerOrders by modifying the name property for this element in the Properties window. To insert additional tables in the schema I dragged and dropped them into the CustomerOrders element. The result is the visually defined schema shown in Figure 12.5. The figure looks a bit like an entity-relationship diagram, and that is a plausible way to think of it.

Behind the scenes, the schema designer is writing XML for us. You can view the XML by switching from the Schema view (shown in Figure 12.5) to the XML view by clicking the XML tab or by selecting XML Source from the View menu. Just as the schema designer can read the schema and write XML for us, it can also write .NET source code, using the CodeDOM.

NOTE

The CodeDOM is a document object model for source code. .NET can be written capably to generate IL (byte code) and source code. Well-established patterns can be codified to automatically generate source code and compile executable assemblies.

At this point we have enough information to generate code. (In fact, one table is enough information with which to generate a typed DataSet .) However, we haven't expressed relationships that exist in the database, so we'll do that before generating the typed DataSet objects.

Defining a Master-Detail Relationship in the Schema

We know from earlier experiences that customers have orders and orders have order details. These relationships are expressed as primary keys between the Customers.CustomerID and Orders.CustomerID columns and the Orders.OrderID and Order Details.OrderID columns. We can express these relationships in the schema, and the designer will use them to generate additional XML and source code that takes advantage of them.

To add a relationship to the XML Schema, follow these instructions.

1. Select the Customers element in the schema designer. Right-click over CustomerID and select AddNew Relation from the context menu.

2. In the Edit Relation dialog (Figure 12.6), set the parent element to Customers and the child element to Orders .

   Figure 12.6. The Edit Relation dialog used to define a relationship between tables in a schema.

   

3. The key fields should both be CustomerID .

4. You can create foreign key constraints at the bottom of the dialog. (Experiment with this as an exercise.)

5. Click OK to complete adding the relation.

After you close the Edit Relation dialog, a keyref element will be displayed in the Schema view attaching the Customers table to the Orders table. (At this point the Schema view really begins to look like an entity-relationship diagram.) Repeat the steps above to define a relationship between the Orders and Order Details tables. Orders and Order Details are joined by the OrderID field.

At this juncture we have a completely defined schema, including a DataSet , DataTable s, relationships, and DataRow s.

Generating a Typed DataSet

When tools start to get as good as the XML Schema designer, there is a brief period when managers talk about programmers being obsolete. This is hooey. I live in Michigan, and for a while we heard a lot about how robots were going to put car factory employees out of work. In reality, tools simply help tradespeople focus on more interesting problems. Writing XML and typed DataSet objects seems like a perfect job for a tool.

We have the option of previewing the DataSet from the SchemaPreview DataSet menu. However, if we need to make changes, we can do so at any time by modifying the schema visually and regenerating the DataSet . Let's go ahead and select SchemaGenerate DataSet.

If you click the Show All Files button on the Toolbar at the top of the Solution Explorer, you will see that the CustomerOrders.xsd file has two down-line files, the .xsx resource file and a CustomerOrders.vb source code file (as long as we have generated the DataSet ).

Our selections caused approximately 1,600 lines of code to be generated. To get a bird's-eye view of the generated code, you can open the Class view. Select ViewClass View from the main menu to display the Class view (expanded in Figure 12.7 to show the typed DataSet ).

Figure 12.7. The Class view of the generated CustomerOrders DataSet .

Notice that all the elements in the Class view are subordinate to the CustomerOrders class. This means that these elements are nested. I won't provide the code listing here (see CustomerOrders.vb in TypedDataSetDemo.sln ), but I can tell you about the generated code. The CustomerOrders class inherits from the DataSet class. Within the CustomerOrders class are three DataTable objects: CustomersDataTable , OrdersDataTable , and Order_DetailsDataTable . These represent strongly typed objects inherited from the DataTable class. The CustomerOrders class also contains nested CustomersRow , OrdersRow , and Order_DetailsRow classes that inherit from the DataRow class. At this level of granularity the code becomes very OOPY. Instead of referring to DataSet , DataTable , and DataRow classes, we can refer to Customers , Orders , Order_Details , and properties within these classes. Writing code in the vernacular of the problem domainin our example, customers and ordersmakes code that is more comprehensible and useful in the particular domain.

The maintained benefit is that because we are using inheritance here, we are also still talking about DataSet , DataTable , and DataRow objects. The combined result is that we can use the ADO.NET skills we have worked hard to acquire yet write code that is domain-specificthat is, code that speaks to customers and orders.

Finally, it is worth mentioning that VB6 did not support nested classes but VB .NET does, and it makes sense to use nested classes in the present context. If you imagine that our typed DataSet has specific typed DataTable and DataRow objects, all used to coordinate relationships, it doesn't make sense to define these classes independently of the containing typed DataSet . For example, it wouldn't make sense to initialize a single DataRow without its containing DataTable . Nested classes take some getting used to, but they support some powerful idioms previously supported only in languages like C++ and Delphi.

Programming with Typed DataSet Objects

To work with typed DataSet objects you still need connections, commands, and adapters to initialize and manage the data. However, as I mentioned, the code begins to read as domain-specific code. Additionally, because the XML Schema designer has generated extended classes for us, things like the DataRelations can be built in the new, derived classes automatically. The code in Listing 12.10 demonstrates how to initialize the CustomerOrders DataSet and provides examples showing how the code maps the problem domain more closely.

Listing 12.10 Initializing the Typed CustomerOrders DataSet

 1:  Imports System.Data 2:  Imports System.Data.OleDb 3:  Imports System.Configuration 4: 5:  Public Class Form1 6:      Inherits System.Windows.Forms.Form 7: 8:  [ Windows Form Designer generated code ] 9: 10:   Private CustomerOrders As CustomerOrders 11:   Private Connection As OleDbConnection 12:   Private AdapterCustomers As OleDbDataAdapter 13:   Private AdapterOrders As OleDbDataAdapter 14:   Private AdapterOrderDetails As OleDbDataAdapter 15: 16: 17:   Private Sub Form1_Load(ByVal sender As System.Object, _ 18:     ByVal e As System.EventArgs) Handles MyBase.Load 19: 20:     Connection = New OleDbConnection( _ 21:       ConfigurationSettings.AppSettings("ConnectionString")) 22:     AdapterCustomers = New OleDbDataAdapter( _ 23:       "SELECT * FROM Customers", Connection) 24: 25:     AdapterOrders = New OleDbDataAdapter( _ 26:       "SELECT * FROM Orders", Connection) 27: 28:     AdapterOrderDetails = New OleDbDataAdapter( _ 29:       "SELECT * FROM [Order Details]", Connection) 30: 31:     CustomerOrders = New CustomerOrders() 32: 33:     AdapterCustomers.Fill(CustomerOrders, "Customers") 34:     AdapterOrders.Fill(CustomerOrders, "Orders") 35:     AdapterOrderDetails.Fill(CustomerOrders, "Order Details") 36: 37:     DataGrid1.DataSource = CustomerOrders.Customers 38: 39:   End Sub 40: End Class 

As promised and as you can plainly see here, the initialization code hasn't changed much. I declare connections, a DataSet , and adapters to fill the DataSet . A few differences hint at future benefits. First of all, I declared a CustomerOrders type in line 10, and I assigned DataGrid1.DataSource to CustomerOrders.Customers (line 37) rather than DataSet.Tables(0) . Another benefit I am already getting is that the relationship exists between the Customers , Orders , and Order Details tables, but as you can see, there is no sign of this code in Listing 12.10. The relationships are created automatically in the initialization code for the CustomerOrders DataSet .

Keep in mind that only a small part of your ADO.NET code will be initialization code. The bulk of the code will be interactions with the data itself. When you begin using the DataSet , DataTable , and DataRow objects defined in the typed DataSet , you will get the greatest benefit of typed DataSet objects.

Assume I have a single instance of a row in a Customers table. I can interact with the data fields as typed properties of an individual customer. Here is a brief example.

 Dim Customer As CustomerOrders.CustomersRow _   = CustomerOrders.Customers.Rows(0) Customer.Address = "1313 Mockingbird Ln." 

The code is clearly referring to a Customer.Address field, and the type of the address is known to be a string, a typed value, rather than simply a field in a DataSet . In fact, if the DataSet object and Rows collection were not shown too, a discerning reader would not know that we are even referring to instances of ADO.NET objects. The statement Customer.Address is typed code in the language of the solution domain, customers and orders.

Typed DataSet objects are easy to use. If you are going to implement a database-centric solution, using typed DataSets will result in code that makes more sense in the solution domain than weakly typed ADO.NET classes. Additionally, if you are creating UML models as part of your design process, typed DataSet objects tie in nicely with a UML model that maps the problem domain rather than simply having an entity-relationship diagram of the database.