isual Basic Express (VB Express) wizards make it easy to connect to a remote data source and start manipulating data. However, the results aren’t always flexible enough for a development environment. Most of the time, you are better off foregoing the wizards and building your own objects from scratch using typed datasets. That way, you maintain control and protect the validity of your data.

For example, a wizard won’t produce a dataset that can automatically handle a change in source when a user connects to a different database or one that can present a simple, single object interface for the dataset?but you can.

What’s a Typed Dataset?

A .NET typed dataset is an in-memory representation of a remote database (e.g., its objects and schema) composed of tables, relationships, constraints, and so on. Its structure can be a bit confusing until you’re more familiar with it. Essentially, .NET DataTable objects contain tables and a .NET DataAdapter object fills the dataset. Furthermore, a dataset contains all the objects as a collection.

In a .NET solution, you use datasets to remotely manipulate and modify data. A .NET typed dataset enforces the data type for each column, so that the dataset can be exposed in the interface in a type-safe manner. For example, if a column (at the table level) doesn’t allow Null values, the typed dataset will generate an exception if you don’t provide a value for that column, whether you use a form, code, or a SQL statement.

In comparison, an untyped dataset doesn’t apply strong typing to the columns. You might compare an untyped dataset to an Access Select query that defaults to the default field width, regardless of the column’s underlying table property.

Solution Versus Project

Throughout this article, you’ll see the terms solution and project. Within the context of .NET, both are containers, but they store different files:

• A solution is a container that stores one or more projects and the files and metadata that define the solution as a whole.
• A project container stores source files and related metadata such as component references and build instructions.

Think of the solution as the big picture and projects as the tasks for creating that picture. A .NET solution can contain a number of projects and you can use projects in more than one solution. That makes .NET more flexible than SQL Server or Access.

By default, a solution supports one project. If you add a second project, VB Express will close the current project in order to create a new one. However, VB Express can handle more than one project in any given solution, but only if you expose the solution file. Otherwise, you’re working within the project rather than within the solution.

Creating the Typed Dataset

Many solutions contain multiple projects. The following example contains two projects: one contains a simple user interface and a second contains the appropriate data structures that the user interface references. Often, it doesn’t matter which project you add first. However, the namespace in this example will use the name given to the first project. For that reason, you will create the user interface project, a Windows Forms application, first:

Figure 1. Create New Project: Open a new project.

1. Launch Visual Basic Express and create a new solution by adding a Windows Forms project. Choose New Project from the File menu, and then select Windows Forms Application. Name the project NorthwindTypedDatasetExample, as shown in Figure 1, and click OK.
2. To establish a connection to Northwind, the database that comes with MS Access, choose Connect to Database from the Tools menu (Or right-click Data Connections in the Database Explorer, and choose Add Connection). Use the Browse button in the resulting Add Connection dialog box to locate Northwind.mdb and then click OK.
3. If you don’t see the solution file in the Solution Explorer as shown in Figure 2, choose Options from the Tools menu. Expand the Projects and Solutions node and click General. To the right, check the Always Show Solution option, as shown in Figure 3, and click OK. If the solution file is hidden, VB Express works with only one project at a time. As stated previously, this solution requires two projects.
   

   Figure 2. VB Express Solution File: Make sure the solution file is visible in the Solution Explorer.

   ?

   Figure 3. Show Solution Option: To work with two projects in one solution, you must check the Always Show Solution option.

Figure 4. Startup Project: Identify the Windows Forms Application project as the solution’s startup project.

4. Right-click the new project (not the solution) and choose Set As Startup Project, as shown in Figure 4. Doing so allows you to run this project when you start the application.

At this point, you have a solution that’s connected to the Northwind database and a Windows Forms application project. You have just started the user interface project, which needs a bit more work. First, however, add a second project: a class library containing the data structures that the form project you just built will reference.

To add the class library, do the following:

1. To add the second project (making sure the solution container is visible and selected), right click on the solution, select Add from the resulting menu, and then choose New Project as shown in Figure 5. If prompted to save the current project, click Save.
   

   Figure 5. Add New Project: Add a new project to the solutions file.

   (You can add a class library from the File menu, but it won’t work as expected unless you are in the solution namespace. To add a second project to the solution, you must work from the solution file in the Solution Explorer.)

2. In the resulting Add New Project dialog shown in Figure 6, select Class Library, name the new library NorthwindDataAccess, and click OK. At this point, you have two projects in the Solution Explorer, as shown in Figure 7.
   

   Figure 6. Select Class Library: Add a class library to the current solution.

   ?

   Figure 7. Two Projects in the Solution Explorer: The current solution comprises two projects, a Windows Forms application and a class library.

The data structure you’ll add to the Class Library project is a typed dataset. To do so, complete the following steps:

1. To add a new typed dataset to the Class Library project, right-click NorthwindDataAccess (in Solution Explorer). Choose Add from the resulting submenu, and then select choose New Item.
2. In the resulting Add New Item dialog box, select DataSet. Name the new dataset CustomersDataset, as shown in Figure 8, and then click Add.
3. In the Database Explorer, expand the Northwind.mdb connection and drag the Customers table to the Dataset Designer, as shown in Figure 9. You just added a typed dataset to the Class Library project. If the Database Explorer isn’t visible, press Ctrl+Alt+S or choose Database Explorer from the View menu.

Figure 8. New Dataset: Create a new dataset named CustomersDataset.

?

Figure 9. New Typed Dataset: VB Express adds a reference to the new dataset to the Solution Explorer.

At this point, you have the two projects your solution needs. To work with both projects, you need a reference between them. The reference should be in only one direction. If you want the Windows Forms Application project to use data from the Class Library project’s NorthwindDataset, you must set the reference in the Windows Forms Application as follows:

Figure 10. Add Reference: Connect the two projects by adding a reference from NorthwindTypedDatasetExample to NorthwindDataAccess.

1. Right-click the NorthwindTypedDatasetExample project (not the solution) in the Solution Explorer, and choose Add Reference. In the resulting Add Reference dialog box, click the Projects tab.
2. Select NorthwindDataAccess, as shown in Figure 10, and click OK.

This reference is necessary for the code to recognize the existence of the objects it’s importing, but it won’t always be necessary to add such a reference at this point (only for code that needs to recognize the existence of your objects). However, it won’t hurt to add it, so you might as well. If you forget, it’ll be harder to troubleshoot its absence later.

Now, you’re ready to use the Customers typed dataset as follows:

1. With Form1.vb in Design view, drag a DataGridView from the Data section of the Toolbox to the form.

Figure 11. Find the Dataset: Expand Form1ListInstances to display CustomersDataset1.

2. From the NorthwindDataAccess Components section of the Toolbox (newly added), drag CustomersDataset to the form. Just dragging the dataset to the form won’t create a DataGridView the way dragging a table does.
3. VB Express will display the DataGridView Tasks dialog box. Open the Choose Data Source control’s dropdown list (currently displays none) and expand Other Data Sources. Expand Form1ListInstances to display CustomersDataset1, as shown in Figure 11. CustomersDataset1 is an alias for CustomersDataset.
4. With the DataGridView still selected, set the DataSource property to CustomersDataset1 and the DataMember property to Customers. Doing so will update the control’s data structure. Expand the size of the form and the DataGridView to display as many columns as possible.
5. Return to the form’s code module and update Class Form1 by entering the following code, as shown in Figure 12:

   Imports NorthwindDataAccess.CustomersDatasetImports NorthwindDataAccess.CustomersDatasetTableAdaptersPublic Class Form1  Dim _adapter As New CustomersTableAdapter  Dim dataset As New NorthwindDataAccess.CustomersDataset  Private Sub Form1Load(ByVal sender As Object, _      ByVal e As System.EventArgs) Handles Me.Load    adapter.Fill(dataset.Customers)    Me.DataGridView1.DataSource = _dataset  End SubEnd Class

6. Press F5 to run the form. Figure 13 shows the typed dataset displaying customer data from the Customers table in Northwind.mdb.
   

   Figure 12. Update Class Form1: Update Class Form1 by entering the following the code that populates the DataGridView.

   ?

   Figure 13. Typed Dataset Displaying Customer Data: The main project’s form displays data from the Customers table.

When you execute the solution, VB Express runs the form. The form’s code uses the data from the Customers table (via the connection to Northwind) to fill the form’s DataGridView. The code itself is brief?you don’t have to hard code a connection string or a single control. That’s the beauty of .NET.

Flexibility Makes Work Worthwhile

Admittedly, this solution seems like a lot of work to get the same results a wizard seemingly supplies. Don’t let looks deceive you though; this longer process produces a more flexible object in many ways:

• The typed dataset isn’t restricted to a table or even multiple tables. It can handle an XML file or even a text file by using a DataAdapter rather than a TableAdapter.
• You can create a typed dataset that allows you to update several related tables simultaneously from a single user form.
• You can display and use calculated values, even a value based on a column within the same record.
• The typed dataset simplifies your job because it’s a reusable object. Drag it onto a form and it waits for you to determine the bound object instead of providing a default object.
• You can use multiple typed datasets on a form to handle related data, such as customers and orders.

If you want powerful and flexible interface objects such as a tab control with subforms on each tab page and on popups, build your own objects on a typed dataset. At first, the process isn’t intuitive, but the advantages they offer are worth the extra effort.