New DataSet Features in Visual Studio 2005

Article
02/03/2012

Jackie Goldstein
Renaissance Computer Systems

Updated October 2005

Applies To:
Microsoft ADO.NET 2.0
Microsoft Visual Studio 2005

Summary: Learn about the new features in the typed DataSet class and the new TableAdapter class that are generated by Visual Studio 2005, as well as the tools for designing these classes. Also learn about the new BindingSource and BindingNavigator components, and see how to use them to rapidly build flexible, data-bound WinForms applications. (20 printed pages)

Download the associated VSDatasetSamples.exe sample code.

Introduction
Data Sources
Building Data-Centric Forms
Customizing the Generated Code
Conclusion

Introduction

In a previous article, New DataSet Features in ADO.NET 2.0, I discussed some of the upcoming changes and enhancement to the ADO.NET DataSet class and associated classes such as DataSet, DataTable, and DataView. These are all classes that are part of the Microsoft .NET Framework Base Class Library.

In this article, I will focus on developing with these and derived classes from within the Microsoft Visual Studio 2005 development environment. In particular, this article discusses the changes to the typed DataSet and the new typed TableAdapter that are generated by Visual Studio 2005. This article also looks at the designers and tools that offer tremendous flexibility and productivity for developing the data-centric aspects of your application. In order to explain the different concepts and features, I will step through the process that a developer will typically go through when implementing the data part of an application. The code samples use the Northwind database that comes as a sample database with Microsoft SQL Server (and MSDE) 7.0 and 2000.

Data Sources

Visual Studio 2005 introduces the concept of Data Sources for a project. A Data Source represents the data that is available to an application. This data does not necessarily come from a database—the Data Source Configuration Wizard that you use to define a Data Source allows you to get the data from three different sources:

Database—This can be either a server-based database such as SQL Server or Oracle, or a file-based database such as Access or SQL Server Express. Visual Studio automatically generates typed DataSets and other classes and adds them to your project.
Object—Any object with public properties can be the source of the data. It is not necessary to implement any special interfaces.
Web service—Creating a Data Source from a Web service creates objects that correspond to the type of data that is returned from the Web service.

The purpose of the Data Source is twofold. First of all, it is a way to make it easier to specify, design, and generate strongly-typed classes that represent the application's data. Secondly, it provides a flexible, but uniform, mechanism for building rich and highly functional WinForm and WebForms user interfaces very quickly. As we go through this article, we'll see just how quick, easy, and flexible this is.

In this article we will focus on the creation of database (DataSet) Data Sources and their use in WinForms applications. However, it is important to remember the following two points:

Once a Data Source is created, you use it in the same ways, regardless of where its data is coming from. That means that just as you can easily (and graphically) bind a Database-based Data Source to a grid or a set of controls, the same is true for data that is actually coming from a Web service or from your custom business objects.
Data Sources are defined in the same manner, regardless of whether they are going to be used in a WinForms or WebForms application. The different data providers are also abstracted, so that if your data access is exposed only using DataSets and TableAdapters, all you need to do to change your actual database is to change the connection string and regenerate the classes.

Typed DataSet and TableAdapter

A database Data Source is the combination of a strongly-typed DataSet and one or more pairs of strongly-typed DataTables and TableAdapters. The idea of a typed DataSet is not new—we had this in Visual Studio 2002/2003. A typed DataSet is a generated class that is derived from the .NET Framework's generic DataSet class, but has a defined schema, along with properties and methods that are specific to that schema. At the same time, for each table in the DataSet, three additional derived classes are generated —DataTable, DataRow, and DataRowChangeEvent classes that are specific to that DataSet. Each of these classes has specific schema, properties, and methods for the associated table. For example, if I define a Data Source based on the Northwind Employees table, I end up with the following classes being generated:

NorthwindDataSet
EmployeesDataTable
EmployeesDataRow
EmployeesRowChangeEvent

These four classes make up the typed DataSet. In Visual Studio 2005 there is a fifth class that is also generated, the typed TableAdapter named EmployeesTableAdapter, which we will discuss shortly. Of course, if you are defining queries dynamically, you can't generate the typed DataSets and need to use the standard DataSet.

Why bother with a typed DataSet? In addition to forcing you to think about your data's schema up front rather than just "winging it," a typed DataSet offers several concrete advantages:

DataSets, DataTables, DataRows, and the RowChangeEvent are specific to the schema being handled.
Tables, columns, and relations are exposed as named properties, rather than generic collection elements.
As a result of (2), you have full IntelliSense and statement completion support in the Visual Studio code editor.
Also as a result of (2), compile-time type checking is made possible (misspelling a field name is caught at compile-time instead of at run-time, for example).
The code is more concise and readable—Instead of:

country = dsNorthwind.Tables ("Employees").Rows (row) ("Country")

you have

country = dsNorthwind.Employees (row).Country

The bottom line is the design-time and compile-time assistance offered by typed DataSets will significantly reduce not only your initial development time, but also the amount of time it takes to debug and stabilize the application.

The concept of a TableAdapter, on the other hand, is new to Visual Studio 2005. The idea behind this is that a strongly-typed TableAdapter is the strongly-typed equivalent of the standard DataAdapter. You use the TableAdapter to connect to a database and execute queries (or stored procedures) against that database and to fill an associated DataTable with data. Each DataTable-TableAdapter pair is referred to simply as a TableAdapter.

The TableAdapter is essentially a wrapper around a standard DataAdapter, which offers several benefits:

The same TableAdapter class can be used on more than one form or component so that any changes to the queries/commands are automatically reflected in all instances. This is different than the existing situation, where every component that accesses the database must have its own individually configured DataAdapter. This makes it much easier to ensure that DataTables and DataAdapters remain synchronized.
Instead of using multiple DataAdapters (or hand-crafted switching code) in order to have multiple queries/commands for a single DataTable, a TableAdapter allows you to easily define multiple commands for a given DataTable.
The Fill command(s) have readable ("friendly") names, and the TableAdapter includes code to automatically fill in type and value information for all of the parameters to those command methods. No longer do you need to worry about passing in provider-specific data types like SqlInt.

A simple code snippet will help illustrate these features. In Visual Studio 2002/2003, even using a typed DataSet, the code to execute a simple query with two parameters would be non-trivial—for the query

SELECT FirstName, LastName from Employees WHERE Country = @country AND City = @city

we would have to write something like this:

Me.SqlAdapter1.SelectCommand.Parameters ("@country").value =     
Me.CountryListbox.SelectedValue.Trim()   
Me.SqlAdapter1.SelectCommand.Parameters ("@city").value =     
Me.CityTextbox.Text.Trim()   
Me.SqlAdapter1.Fill (Me.NorthwindDataSet.Employees)

Of course, as the number of parameters increase, so does the number of lines of code. More significantly, the odds of correctly remembering and typing each of the parameter names decrease greatly. Even if I get the parameter name right, I then need to remember the data type of the parameter. The worst part of all this is that if I do incorrectly enter the field name or try to assign a value of the wrong type, I don't find out about it until run-time!

With the TableAdapter in Visual Studio 2005, once I define the command FillByCountryAndCity, all I need to do to use it anywhere is to write one line of code, passing in the parameter values:

Me.EmployeesTableAdapter.FillByCountryAndCity ( _
   Me.NorthwindDataSet.Employees, Me.CountryListbox.SelectedValue.Trim(), 
      _    Me.CityTextbox.Text.Trim() )

It is important to note that not only do we get multiple named commands from a single TableAdapter, but these commands are strongly-typed. This means that as we are writing code in Visual Studio, we get full IntelliSense to see these commands as methods of the TableAdapter. We also get compile-time type checking of the parameters to these commands, as well as the tool tip with the method and parameter type definitions to help us along. The TableAdapter can have multiple methods that execute different commands and accept different parameters. We will take a closer look at the TableAdapter a little later, as we build our sample form.

Getting Started—Creating a Data Source

In the course of this article, we are going to build a form to display order information for each of the orders in the Northwind database. The first thing we must do, after opening a new Visual Basic WinForms project, is to Add a new Data Source to our project (We will be walking through this using Visual Basic, but everything applies to C# as well).

To add a Data Source:

Display the Data Sources window (if it is not already shown) by selecting Show Data Sources from the Data menu item on the main Visual Studio menu.
In the Data Sources window, click on the Add New Data Source toolbar button. This starts the Data Source Configuration Wizard, which combines much of the functionality of the DataAdapter Configuration Wizard and the DataSet generation tools in Visual Studio 2002/2003.
If your build of Visual Studio still includes a Welcome page for wizards, select Next. This displays the Choose a Data Source Type page.
Select Database.
Select Next. This displays the Choose Your Data Connection page.
Select New Connection. This displays the Add Connection dialog window.
Enter the information to connect to your instance of SQL Server or MSDE and the Northwind database.
Select OK to dismiss the dialog.
Note that the connection string is now saved as a settings property and can be accessed by
```
My.Settings.NorthwindConnectionString
```
In C# it would be
```
VSDataSets.Properties.Settings.Default.NorthwindConnectionString;
```

Select Next. This displays the Choose Your Database Objects page.
Note that you can choose from Tables, Views, Stored Procedures, or Functions.

Expand the Tables node and select the Orders and Order Details tables. We will use all of the columns in the tables, but you can select only those columns that you need for your application.
Select Finish to exit the wizard.

Figure 1 shows the resulting Data Sources Window with the Order Details table expanded to display all of its columns.

ms379590.newdtastvs05_fig1(en-US,VS.80).gif

Figure 1. Order Details table in Data Sources Window

If you want to re-enter the Data Source Configuration Wizard, to make some changes, you can do so by selecting Configure DataSet with Wizard in the Data Sources window's toolbar or the context menu that you get when right-clicking on any element in that window. However, a more powerful tool for editing the generated DataSet and TableAdapter(s) is the DataSet Designer.

The DataSet Designer

Visual Studio 2005 includes the DataSet Designer—a tool that is really designed to specify and edit DataSets and their associated TableAdapters. This is a significant improvement over the situation in Visual Studio 2002/2003, where we were forced into using an XML schema editor to define strongly typed DataSets. The DataSet and TableAdapter definitions are still saved in an .XSD file, and there is still an XML editor for when you really want to edit XML schemas. But that is where the similarities end. The intention is not for the DataSet Designer to support arbitrary XSD files, but rather it is just a convenient file format.

You enter the DataSet designer by selecting Edit DataSet with Designer in the Data Sources window's toolbar or the context menu that you get when right-clicking on any element in that window. When designing the DataSets and their DataTables, the designer probably feels very similar to other tools you have used to design databases. You can add a table from the database to which you are connected by either dragging a database object (table, view, stored procedure, for example) from the Server Explorer onto the designer surface, or by starting the TableAdapter Configuration Wizard by selecting Add TableAdapter from the main Data menu item on the designer's context menu. Since a DataSet can also have tables that are loaded directly and are not connected to a database, you can also add an independent table by selecting Add DataTable from the menus. Of course, you can also add and/or rename columns in the editor, as well. Another neat feature is that the editor automatically recognizes relations between tables in the database and defines the corresponding Relations between tables in the DataSet for you.

Figure 2 shows our Data Source, consisting of the Orders and Order Details tables, in the DataSet designer. Note that closely associated with each DataTable is the corresponding TableAdapter that is used to fill the table with data and (optionally) update the database with the changes to that data.

ms379590.newdtastvs05_fig2(en-US,VS.80).gif

Figure 2. Orders and Order Details tables in Data Source

The TableAdapter Configuration Wizard

You start the TableAdapter Configuration Wizard inside the DataSet designer by selecting either Add Query or Configure (an existing query) on the main Data menu or on the context menu that you get when right-clicking on a TableAdapter in the designer. This wizard is identical to the DataAdapter Configuration Wizard of Visual Studio 2002/2003—with the exception of the two additional pages that it offers. The first is the Choose a Query Type page, shown in Figure 3. Since the TableAdapter is the central point for all commands against a given table, it allows you to not only define multiple Select/Fill commands, but also multiple queries of any kind—Update, Insert, Delete, or one that returns a single value. Note that these update queries are named methods of the TableAdapter and are meant to be called directly. They are in addition to the update queries that called automatically when you execute the TableAdapter's Update method—just as with the DataAdapter.Update method.

The second additional page in the wizard is the Choose Methods to Generate page. Here you get to choose the name(s) for each of the query/command methods that you define. The wizard offers you both a Fill and a Get method for each command, as shown in Figure 3. The Fill requires you to provide the DataTable to be filled, while the Get version will return a newly created and filled DataTable.

ms379590.newdtastvs05_fig3(en-US,VS.80).gif

Figure 3. Fill and Get methods in the Choose Methods to Generate page

In typical usage, you will define multiple Fill commands for a TableAdapter, which return the same schema (columns) but have different WHERE clauses. That is why, by default, the wizard offers a prefix of FillBy and GetDataBy for the method names. Of course, you are free to give the methods any name you choose.

Although a TableAdapter can have multiple Fill commands, there is only one set of update commands that are executed when the TableAdapter's Update method is called. These are generated automatically, based on the TableAdapter's main query. The query that is defined when the TableAdapter is first created is considered the main query for the TableAdapter. If any subsequently defined queries return a schema that is different that the main query's schema, the designer will warn you about it with a message box. Alternatively, if you modify the schema of the main query, Visual Studio will go and modify your other queries to match that schema.

Adding a New Command Using the TableAdapter Configuration Wizard

Now let's add another command to the TableAdapter for the Orders table.

Open the DataSet designer by selecting Edit DataSet with Designer in the Data Sources window's toolbar.
Select the Orders's TableAdapter, and select Add Query from its context menu.
Select Next on the Welcome page if it still exists in your build of Visual Studio. This displays the Choose a Command Type page.
Select Next to accept the default of SQL Statements. This displays the Choose a Query Type page.
Select Next to accept the default SELECT statement. This displays the Specify a SQL SELECT Statement page.

Enter the following SQL statement;

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,
ShipRegion, ShipPostalCode, ShipCountry
FROM Orders
WHERE CustomerID = @CustID

This will return all of the Orders for the customer specified by the @CustID parameter to the query.

Select Next. This displays the Choose Methods to Generate page.
Leave both checkboxes checked. Modify the method names to be FillByCustomer and GetDataByCustomer, respectively.
Select Next and then Finish to complete the process.

If you look at the OrdersTableAdapter in the DataSet designer, you will see that it now shows a second pair of commands, FillByCustomer and GetDataByCustomer, which take a CustomerID value as a parameter. What is the (.NET) type of this method parameter? Let's go and take a look.

A Peek Behind the Scenes

When you make changes in the DataSet designer or any of the associated wizards, Visual Studio goes and generates code for a set of typed classes. Specifically, it generates the following classes:

A DataSet class
A DataTable class
A TableAdapter class
A DataRow class
A DataRowChangeEvent class

Except for the DataSet class, which is only one per Data Source, each of the other four classes is repeated for each table defined in the DataSet. You can see the code for these classes by doing the following:

In the Solution Explorer window, click the Show All Files button on the toolbar to display all the files associated with the project.
Expand the node for the NorthwindDataSet.xsd file.
Double-click on the file node for NorthwindDataSet.Designer.vb. This is the code that is generated to implement the classes that make up our DataSet.

Open the Listbox on the top left side of the code window. There you can see the list of classes in this file:

NorthwindDataSet
Order_DetailsDataTable	OrdersDataTable
Order_DetailsRow	OrdersRow
Order_DetailsRowChangeEvent	OrdersRowChangeEvent
Order_DetailsTableAdapter	OrdersTableAdapter

Select the OrdersTableAdapter class in the left Listbox.

Select the FillByCustomer method in the right Listbox. This displays the code for this method, as shown below:



Public Overloads Overridable Function FillByCustomer(ByVal dataTable
As NorthwindDataSet.OrdersDataTable, ByVal CustID As String) As Integer
Me.Adapter.SelectCommand = Me.CommandCollection(1)
If (CustID Is Nothing) Then
Throw New System.ArgumentNullException("CustID")
Else
Me.Adapter.SelectCommand.Parameters(0).Value = CType(CustID,String)
End If
If (Me.m_clearBeforeFill = true) Then
dataTable.Clear
End If
Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
Return returnValue
End Function

We can learn a few things from this snippet of code.

The FillByCustomer methods actually takes two parameters—an OrdersDataTable to be filled, and a CustID parameter, which is a string.
Without going through all of the code in the file, we can see that the TableAdapter class maintains a collection of commands, from which it automatically assigns the correct command to the .NET DataAdapter that it is using to actually communicate with the database.
The method checks that an instance of a state parameter was passed to the method (if the parameter's AllowDBNull property is set to False).
The CustID parameter value is assigned to the (already configured) parameter of the DataAdapter's SelectCommand property.
Once everything is set up, the standard DataAdapter.Fill () method is called to fill the OrdersDataTable.

Remember that this is not code that needs to be written by you. Everything has already been generated and configured. Take some time to poke around the code for the other classes that have been generated for you. You'll get a broader and deeper understanding of how these classes are implemented—and probably learn one or two good coding ideas, as well!

Note Although the typed DataSet and its associated classes, including TableAdapters, are all generated in a single source file, the TableAdapters are generated within a separate namespace. This reflects the fact there should be a separation between the entity objects (DataSets) and the actual data access objects (TableAdapters).

Building Data-Centric Forms

Now that we have generated a typed DataSet, it is time to go build a form that displays this data. Although I will not dive into all of the details and new features of WinForms and data binding in the .NET Framework 2.0 and Visual Studio 2005 (and there is a lot to dive into), this is a good opportunity to see some of what has been done to make building functional data-centric forms much easier and more flexible.

Data Components in the Toolbox

If you are used to building your data-centric code by starting with the standard Data Components on the Data tab of the toolbox, you may be a little distressed and concerned when you open Visual Studio 2005 and can't find them there. This, of course, is "By Design"—Microsoft wants to nudge us into taking advantage of the new typed DataSets and TableAdapters. If you really, really want to use the old un-typed components, you can manually add them back to the toolbox. I strongly suggest that you don't…By leveraging the new TableAdapters along with the partial class technology, the new typed DataSets, and TableAdapters are much easier to use and to extend.

You can find more information on these and other design decisions, along with the thinking that went into them, on the Visual Basic team blog.

In particular, check out two of Steve Lasker's postings:Why are the Data Components no longer on the Toolbox?, and Why can't I drag from Server Explorer to my form?

When you open a form in the Visual Studio designer, the Visual Studio toolbox will have a tab labeled with the name of your project. After adding a Data Source to your project and building it at least once, this tab will include the typed DataSets and TableAdapters that you have created. Although you can drag these components onto the form designer, this is probably not what you are normally going to do (If, however, you are implementing a data access component using the designer, this is a great thing to do). You will normally use one of the three different approaches to building a data-centric form. Let us step through the first method, which is the easiest and maybe even the most common. This is what is known as "Drag Once" data binding.

Double-click on Form1.vb in the Solution Explorer, to open Form1 in the Visual Studio Form Designer.
In the Data Sources window, expand the node for the Orders table.

Note that there is an icon associated with each table and each column in the DataSet. These icons represent the type of WinForm control (or "drop type") that will be used to bind to the data if the table or column is dragged and dropped onto a form. You can change the type of control by selecting an item and choosing a control from the associated drop-down list. Note that the list includes options for None (nothing displayed) as well as for Customize (you specify whatever control you'd like).

These icons and the drop type list are only visible if the current active window is a form (or component) designer. Otherwise, you cannot drag and drop controls from the Data Sources window, and the icons change to indicate this.
Change the control type for the Orders table from the DataGridView to Details. What this means is that when you drag the entire Orders table onto a form, rather than building a form that shows all of the data in a grid (all the rows at once), it will build a form that displays the details of the rows one at a time. For a Details view, a label and control will be added for each column, with the type of control being that which is specified in the Data Sources window.
Drag the Orders table from the Data Sources window onto Form1 in the designer.
Select the last seven (7) fields (along with their labels) and drag them alongside the first seven (7) fields, so that the form looks like that shown in Figure 4.

Figure 4. Form1 in the designer
Start the application and verify that it works by stepping through the records using the navigation buttons on the toolbar at the top of the form.

Let us review what Visual Studio did for us when we dragged a Data Source table onto a form. By looking at the component tray underneath the form, we see that it added four components to the form. We already know (and love?) two of these components—NorthwindDataSet and OrdersTableAdapter. The OrdersTableAdapter is used to fill the OrdersDataTable of the NorthwindDataSet with data from the database. Even the one line of code to do this Fill is already written and automatically added to Form1's Load event handler.
```
Me.OrdersTableAdapter.Fill(Me.NorthwindDataSet.Orders)
```

The pivotal class for data binding is the BindingSource class, which in our current case is the clearly named OrdersBindingSource. The BindingSource (known as the DataConnector in Beta 1) provides the services that are required for binding controls on a form. It provides a layer of indirection between a Data Source and the controls that are bound to it. You attach the BindingSource to a Data Source by setting the BindingSource's DataSource and DataMember properties, and then bind the controls to the BindingSource by adding to the controls' DataBindings collection. All interaction with the data, such as record navigation, sorting, filtering, and editing is done through the BindingSource. The BindingSource also allows access to the underlying data through the List, Item, and Current properties.

The other component added is the OrdersBindingNavigator. A BindingNavigator class is a toolbar that provides a standard user interface for navigating and manipulating data on a form. The BindingNavigator (known as the DataNavigator in Beta 1) is a ToolStrip control, with a set of pre-configured buttons. It attaches to a BindingSource as the source of its data and provides toolbar buttons for controlling navigation through the available data. If, however, instead of controlling the navigation you want to respond to some of the navigation events, you should be hooking the events of the BindingSource object.

Building a Master-Details Form

Now that we have a form that displays data from a single table, how easy (or hard) is it to display data from a second, related table in a Master-Details format? Just continue with these steps on Form1:

We will now use "Connect the Dots" data binding to build the form, where we drag a control from the toolbox and position it ourselves on the form, and then drag an element from the Data Source window and drop it on that control to connect them to each other.
Select the DataGridView control from the All Windows Forms tab in the toolbox. Drag it onto Form1 and position it so that it takes up most of the bottom half of the form.
Return to the Data Source window, as shown in Figure 5. Note that the Order Details table actually appears twice in this window. The first time it is a direct child of the NorthwindDataSet and a sibling of the Orders table. The second time it appears as a child of the Orders table, indicating that it is a related table. If we wanted to independently display the Order Details table on the form, we would choose the occurrence of the Order Details table that is directly beneath the NorthwindDataSet. However, when, as in our case, we want to display the Order Details table as it is related to the (master) Orders table, we choose the occurrence of the Order Details table that is directly beneath the Orders table.

Figure 5. Order Details table in the Data Source window
Select the Order Details table that appears beneath the Orders table and drag it onto the DataGridView on Form1.
Note the Order_DetailsBindingSource and Order_detailsTableAdapter that have been added to the component tray of Form1.
Run the application and use the BindingNavigator to move through the records in the Orders table, as shown in Figure 6. Note how the Order Details records displayed in the DataViewGrid automatically change to display only those that are related to the current Orders record.

Figure 6. BindingNavigator in the Orders table

Customizing the Generated Code

Earlier, when we looked for the code for the classes of our DataSet, you may have noticed that there are actually two Visual Basic code files, NorthwindDataSet.Designer.vb and NorthwindDataSet.vb. If there is no NorthwindDataSet.vb file, return to the DataSet Designer and double-click on the designer background to cause the file to be created.

Both of these files are used to implement the classes that make up our DataSet. The reason that there are two files is to take advantage of a new, simple, but very powerful feature called partial classes. Partial classes is a compiler feature that allows the definition of a class (or structure) to be split up among several declarations. The different declarations may be in different source code files, as long as the declarations are all in the same assembly and namespace. Visual Studio makes extensive use of this feature to separate designer-generated code from developer-written code for the same class. For example, in Visual Studio 2002/2003, the code for a form is all part of a class declaration for that form, such as

Public Class Form1
    Inherits System.Windows.Forms.Form

The initialization code for that form, including any controls that are placed on the form, is generated by Visual Studio. This code is in the method InitComponent (), which by default appears before user-written code in a code region named "Windows Form Designer generated code." This region is normally left closed in order to minimize the clutter and distraction from the code you are actually writing. In Visual Studio 2005, this code is even less distracting, since it is in a completely different file, named Form1.Designer.vb! Here too, you can see the contents of this file if you want to—click Show All Files on the Solution Explorer toolbar, expand the Form1.vb node, and then double-click on Form1.Designer.vb to display it in the code editor. The file Form1.vb only contains the code that you, as a developer, have written for the Form1 class.

When it comes to the code for the DataSet and associated classes, this use of partial classes and different files to separate the designer code from the developer code is even more significant. Beyond being more orderly, this separation solves a major problem that exists when using typed DataSets in Visual Studio 2002/2003.

Very often, you will want to extend or add to the code that is automatically generated for the DataSet and its associated classes; for example, additional properties or custom validation code. You can go ahead and add these to the generated code. Everything is fine until you change the schema and need to regenerate the DataSet code. In Visual Studio 2002/2003, since your code is simply added to the file with the generated code, all of your additional code is wiped out when the code is re-generated. Thanks to the use of partial classes, this does not occur in Visual Studio 2005. The newly generated code will overwrite the existing designer code the file with the .Designer.vb extension, but the developer-written code in the .vb file will remain intact.

One way to extend the functionality of a DataSet using partial classes is to add custom validation code. This is an opportunity to add some application-specific logic to the generated typed DataSet. Let's add custom validation and initialization to the addition of a new row to the Orders table in the NorthwindDataSet. When adding a new row, we want to check if the passed postal code value is actually in the passed city. If it is not, we will change the value of the ShipPostalCode field to Invalid. Assume that there is an implementation of a function that returns True if the provided postal code is actually in the provided city, as follows:

Function IsPostalCodeInCity (ByVal PostalCode as string, ByVal City as string) As Boolean

We can add this check to our NorthwindDataSet by doing the following:

Open the DataSet designer by selecting Edit DataSet with Designer in the Data Sources window's toolbar.
Double-Click on an empty area of the designer background. This opens the file NorthwindDataSet.vb in the code editor.

Enter the following code instead of the default code:

Partial Public Class NorthwindDataSet Partial Class OrdersDataTable Protected Sub ValidateNewRow(ByVal sender As Object, _ ByVal e As System.Data.DataTableNewRowEventArgs) _ Handles Me.TableNewRow ' Create a strongly typed instance of the row ' This helps us avoid code in quotes, ' eg, e.Row("ShipPostalCode") Dim ordersRow As NorthwindDataSet.OrdersRow ordersRow = e.Row If Not ordersRow.IsShipPostalCodeNull And _ Not ordersRow.IsShipCityNull Then If Not IsPostalCodeInCity(ordersRow.ShipPostalCode, _ ordersRow.ShipCity) Then ' Set the value of the Ship Postal Code ordersRow.ShipPostalCode = "Invalid" ' Typically, changing a users data is a bad user experience ' So indicate an error with the ErrorProvider ' We are illustrating both approaches here ordersRow.SetColumnError( _ ShipPostalCodeColumn.ColumnName, "Invalid Postal Code") Else ' we always need to reset the error when the value is valid ordersRow.SetColumnError( _ ShipPostalCodeColumn.ColumnName, String.Empty) End If End If End Sub End Class

Private Shared Function IsPostalCodeInCity(ByVal postalCode As String, _ ByVal city As String) As Boolean ' This is a stub, just to check functionality If city = "Rio de Janeiro" Then Return False Else Return True End If End Function End Class

Note that all of the DataSet's associated classes, such as the OrdersDataTable, are implemented as nested classes within the DataSet. The partial class declaration shown in the previous code reflects this implementation.

In order to have an error indicated by an error provider, in addition to or instead of changing the Postal code value to Invalid, follow the following steps:

Drag an Error Provider control from the Toolbox onto Form1 and drop it just to the right of the Ship Postal Code textbox.
In the properties window for the Error Provider, set the DataSource property to OrdersBindingSource.

Run the application and navigate to a record where the city is Rio de Janeiro, to see the validation code in action, as shown in Figure 7.

ms379590.newdtastvs05_fig7(en-US,VS.80).gif

Figure 7. Error provider control

This is just one example of how you can easily extend the functionality of the typed DataSet and associated classed that are generated automatically by Visual Studio. Something else you might want to do is to add additional methods and properties to the generated classes. As you design your application and your use of DataSets, you will think of many more possibilities. The key point to remember is that in Visual Studio 2005, thanks to partial classes, the code that you write is in a separate file and is not affected when the DataSet classes are re-generated.

Conclusion

Using typed DataSets generated by Visual Studio 2005 is easier and more flexible than ever before. The DataSet Designer provides an easier and more natural tool for defining DataSets. The new TableAdapter class, configurable from within the DataSet Designer, provides a single, centralized mechanism to easily maintain and execute multiple different queries and commands against a specific data table. Using the partial class compiler feature allows complete separation between designer-generated and developer-written code, allowing regeneration of the DataSet classes without affecting any custom code that has already been written to extend these classes. Finally, the new .NET data-binding classes and mechanisms, combined with the tools offered within Visual Studio 2005, make developing data-centric applications much easier and faster.

Thanks to Steve Lasker, as well as to Alan Griver and Pablo Castro of Microsoft for their help in preparing this article.

About the author

Jackie Goldstein is the principal of Renaissance Computer Systems, specializing in consulting, training, and development with Microsoft tools and technologies. Jackie is a Microsoft Regional Director, the founder of the Israel Visual Basic User Group, and a featured speaker at international developer events including TechEd, VSLive!, Developer Days, and Microsoft PDC. He is also the author of Database Access with Visual Basic.NET (Addison-Wesley, ISBN 0-67232-3435) and a member of the INETA Speakers Bureau. In December 2003, Microsoft designated Jackie as a .NET Software Legend!