Converting a Data-Oriented Application from Visual Basic 6 to Visual Basic 2005, Part 2

 

Ken Getz
Paul D. Sheriff

October 2005

Summary: Part 2 of this article series focuses on the data features of converting code from ADO to ADO.NET. ADO.NET operations require less code, and replacing the COM interop features will make your application more efficient. (32 printed pages)

Contents

Introduction
The ADO.NET Classes You'll Need
Update the Utility Module
Overhauling the CustomerHandler Class
Conclusion

Introduction

In the first part of this three-part series, you walked through the steps required to convert an existing Visual Basic 6 data-oriented application into a working, if somewhat creaky, Visual Basic 2005 application. The new application ran within Visual Studio 2005, and allowed you to create a .NET application for Windows, but it's far from finished. (See the first article in the series, Converting a Data-Oriented Application from Visual Basic 6 to Visual Basic 2005, Part 1. In Part 1, you were promised that the second article would focus on both Windows Forms features and on converting the code to ADO.NET. It turns out that performing a reasonable conversion from ADO to ADO.NET required a great deal of effort, so we decided to focus Part 2 on the data features, leaving the form intact. In Part 3, we'll focus instead on rewriting the entire application, with far less code, taking advantage of the new Windows Forms data-binding functionality.

The application in its current state uses COM interop to communicate with ADO, allowing the application to work using the same code, more or less, that it did in Visual Basic 6. In converting such an application, your next goal might be to attempt to replace all the existing ADO code with code that uses ADO.NET, and that's the purpose of this installment in the series. If nothing else, replacing the COM interop requirements should make the application more efficient. In addition, you'll find that similar operations in ADO.NET often require less code, allowing you to streamline the application a bit. (We've included the project, at its starting point for this article, in the associated download file. It would be better if you were to work through the steps in the previous article yourself, but if you can't, start with the project we've supplied.)

The ADO.NET Classes You'll Need

Before you begin ripping out the guts of the existing application, replacing ADO with ADO.NET and its various objects, you should familiarize yourself with the objects you'll be using. Obviously, this article is the wrong place, and lacks the available space, to dig deeply into the various ADO.NET classes, with their properties, methods, and events. But it doesn't take much effort to understand the basics, and if you have some experience with ADO, ADO.NET shouldn't take you too much by surprise—it's all about retrieving, working with, and storing data. (For more information on ADO.NET in general, see the Overview of ADO.NET.

Although ADO.NET provides a large number of classes to work with, this sample application requires only that you understand the System.Data.DataTable class, as well as a number of classes that work directly with the OleDb data provider, including System.Data.OleDbConnection, OleDbCommandBuilder, and OleDbDataAdapter. You'll learn enough about these to make use of them here.

The System.Data namespace includes classes that work with disconnected data. That is, once you retrieve the data you need, you'll use classes in the System.Data namespace to manipulate that data. The System.Data.DataTable class provides an in-memory representation of a set of rows and columns. To most people, that sounds like a table in a database. Actually, the DataTable class simply provides a container for rows and columns of data, with the columns (represented as System.Data.DataColumn instances) providing information about the schema, and the rows (represented as System.Data.DataRow instances) contain the actual data. Rather than using an ADO Recordset instance, the new version of the application uses a single DataTable instance. Of course, in other applications, you'll often need to work with multiple tables concurrently—the DataSet class provides this functionality. By filling multiple DataTable instances within a single DataSet, you can relate the tables (using a DataRelation instance) and easily provide parent/child relationships. A DataTable instance keeps track of the current offline data, as well as the original data at the time the DataTable was filled—that way, when it comes time to save changes, each row "knows" whether it has been changed, and can compare the current values in its columns to the values in the data source before performing the updates.

In order to get data into a DataTable, you can add the data one row at a time, or you can fill the DataTable with all the data from a database table, or view at once. Neither the DataTable nor the DataSet classes can communicate directly with a data source—for that, you need an object contains connection information, like the System.Data.OleDb.OleDbDataAdapter class. The sample application uses an OleDbDataAdapter instance, and its Fill method, to retrieve all the data at once. The OleDbDataAdapter (and its cousins, the System.Data.SqlClient.SqlDataAdapter, System.Data.Odbc.OdbcDataAdapter, and so on) provides the means for both filling a DataTable, and if you want to allow users to make changes to the data in a DataTable, you can use the OleDbDataAdapter instance to update the data in the original data source, as well, by calling its Update method. (Although this article refers only to OleDbDataAdapter and other classes in the System.Data.OleDb namespace, all the concepts apply equally to classes in the System.Data.SqlClient, System.Data.Odbc, and other namespaces. The System.Data.SqlClient namespace provides classes that interact directly with SQL Server, and the System.Data.Odbc namespace provides classes that interact with the ODBC data engine.)

The OleDbDataAdapter class needs some information in order to be able to fill the DataTable—it needs to know what data you need (for example, a SELECT SQL statement or the perhaps the name of a stored procedure, query, or view) and where to get the data (a connection string). You can also supply the OleDbDataAdapter with an OleDbCommand object containing information about the data to be retrieved, and an OleDbConnection object containing information about where to get the data. ADO.NET is flexible—if you want, you can supply the SQL and connection strings when you create the OleDbDataAdapter, and the data adapter will take care of all the "connecting to the data source and getting the data" details for you. You also have the option of creating your own OleDbConnection and OleDbCommand instances yourself, and supplying them to the OleDbDataAdapter. That way, you can control exactly when you open the connection to the data source. The most important rule, however, is that the OleDbDataAdapter guarantees that it will leave the connection in the state that it found it, when you call its Fill method. That is, if the connection was closed, calling the Fill method will open it, get the data, and close it again. If the connection was open, the Fill method gets the data and leaves the connection open.

Obviously, you'll use a SELECT command in order to retrieve data. What if you want to update, delete, or insert data? The OleDbDataAdapter class provides four properties that contain OleDbCommand objects, one for each of the four operations you might want to perform on data. You'll find the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties of the OleDbDataAdapter available for you to work with. When you create the OleDbDataAdapter instance, you generally pass it the SQL string it needs to retrieve the data, and ADO.NET places the OleDbCommand object containing this string into the SelectCommand property of the OleDbDataAdapter. You can supply the corresponding DELETE, UPDATE, and INSERT statements yourself, or you can rely upon a helper object, the OleDbCommandBuilder, to do the work. This class can generate the necessary DELETE, UPDATE, and INSERT SQL statements for you, and the sample application takes advantage of this functionality. Purists may want to hand-create their own SQL, but in this case, that step isn't necessary.

Tip   By default, to help save you typing, Visual Basic 2005 adds a project-wide Imports statement for the System.Data namespace to your projects for you, and the sample project takes advantage of this behavior. You must add your own Imports statements for other namespaces, and you'll see that the sample project adds Imports statements for other the System.Data.OleDb namespace, as well.

Modify Project Settings

Given that the goal of the remainder of this article is to modify the running Visual Basic 2005 application so that it uses ADO.NET rather than ADO, you can follow the steps provided here to make the necessary changes. Remember: although it looks like you need to make a huge number of changes in order to perform this conversion, it's really a matter of learning how the ADO.NET objects work and writing each procedure to take advantage of those objects. Don't be daunted by the amount of code here. If you spend time to understand each procedure, you'll have a good basis from which to work with your own ADO.NET applications.

Before beginning to make modifications to the project, however, take the time to configure Visual Studio 2005 appropriately. Follow these steps to make the changes:

  1. With Visual Studio 2005 running, load the sample project, ADOForm.sln.

  2. On the Properties menu, select the Project | ADOForm.

  3. Select the Compile tab on the left side of the project properties. Ensure that the Option Explicit setting is On, the Option Strict setting is On, and the Option Compare setting is Binary. In the grid below, modify the settings so that they look like those shown in Figure 1.

  4. Close the project settings, and select Build | Rebuild Solution. The sample should still build, with perhaps a warning or two. Later, as you make changes, you may see more warnings. By the time you're done, all of these should be resolved.

    ms364043.adotonet2_01(en-US,VS.80).gif

    Figure 1. Modify the project settings so that they look as shown.

Modify the DataLayer Class

You'll need to modify the data layer quite a lot in order to take advantage of ADO.NET. To get started, open DataLayer.vb in the Visual Studio 2005 code editor. At the top of the file, you'll find lines of code that look like the following:

Option Strict Off
Option Explicit On

Friend Class DataLayer

The conversion utility added the Option Strict Off setting. This setting makes it possible for your Visual Basic 2005 code call make calls to ADO using COM interop. As you probably know, COM objects often use object references (as opposed to strongly typed references), and instead of you having to fix all these problems, the conversion utility simply turned off the Option Strict setting in order to let these loose references slide. Because you will be modifying all of the code in this class, and because you set the Option Strict/Option Explicit settings project-wide, you no longer need either of these Option statements at the top of this class. Go ahead and delete these two statements now.

Because you'll be working with objects from the System.Data.OleDb namespace, you can save some typing by including an Imports statement at the top of the file. Add this statement now, above all the code in the file:

Imports System.Data.OleDb

It's likely that you may move the DataLayer.vb class to a separate assembly at some point in the future, so you can re-use this code in other projects. As a result, you modify the declaration of this class to be Public instead of Friend. That way, it can be accessed from an external assembly:

' Change:
Friend Class DataLayer
' to:
Public Class DataLayer

Although the original version of the project used ADO Recordset objects to move data around, the new version will use DataTable instances instead. Therefore, you'll need to fix up some names. Although you could use the standard Search/Replace mechanism, Visual Basic 2005 supports powerful Rename refactoring—that is, you can rename a method, and Visual Basic will fix up all references to that method.

Because the code must later retrieve the CustomerID of the newly added row, you'll need to make some special considerations. The topic is rather complex (that is, how to retrieve the autonumber value in a newly added row using an Access database), but it's covered well in Retrieving Identity or Autonumber Values. The issues are different for Access and for SQL Server databases, and the article covers both. We'll simply take advantage of the concepts, without explaining them in detail here. Suffice it to say that in order to retrieve the new autonumber value, you must handle the OleDbDataAdapter RowUpdated event, and in that event handler, use the SQL query SELECT @@IDENTITY to retrieve the new value. In order to support this behavior, add the following code to the DataLayer class, outside of any procedure:

' The OleDbDataAdapter.RowUpdated event handler
' needs a connection:
Private Shared mcnn As OleDbConnection

Add the following procedure to the DataLayer class. This procedure will handle the OleDbDataAdapter RowUpdated event and retrieve the new CustomerID value for you:

Private Shared Sub OnRowUpdated( _
 ByVal sender As Object, ByVal eventArgs As OleDbRowUpdatedEventArgs)

  ' Retrieve the new CustomerID value.
  ' This technique requires the same connection used
  ' for performing the update, so mcnn is a class-level
  ' variable. 
  Using cmd As New OleDbCommand("SELECT @@IDENTITY", mcnn)
    If eventArgs.StatementType = StatementType.Insert Then
      ' Retrieve the identity value and store it 
      ' in the CustomerID column.
      eventArgs.Row("CustomerID") = CInt(cmd.ExecuteScalar())
    End If
  End Using
End Sub

Within the DataLayer class, locate the GetRecordset method. Make five changes to this procedure:

  1. Right-click on the GetRecordset method name and select Rename from the context menu. Enter the new name, GetDataTable, and click OK. Not only will Visual Studio change the definition of the method, but it will also fix up all code that calls the method.

  2. Modify the return type, so that instead of returning an ADODB.Recordset, the method returns a DataTable.

  3. Modify all the parameters, changing ByRef to ByVal. Although Visual Basic 6 passed all its parameters by value, unless you specified otherwise, Visual Basic 2005 passes parameters by value by default. This behavior matches that used by other languages, and is generally more efficient. For new code you write, Visual Studio 2005 will add the ByVal for you—for now, do it yourself. When you're done, the procedure declaration should look like this:

    Public Function GetDataTable( _
    ByVal ConnectionString As String, _
    ByVal SQL As String) As DataTable

  4. Because there's never a reason to create multiple instances of the DataLayer class, it seems like overkill to require callers to create an instance of the DataLayer class, and then call its GetDataTable method. Instead, you can make it easier on the callers—by adding the Shared keyword on the procedure, callers can simply call the method, and Visual Basic will create a single instance of the class for you. The following code demonstrates the difference:

    ' Instead of this code:
    Dim data As New DataLayer
    Dim dt As DataTable = data.GetDataTable(ConnectionString, SQL)

' you can use code like this: Dim dt As DataTable = DataLayer.GetDataTable(ConnectionString, SQL)

  1. Using a Shared method in a class is equivalent, from the caller's perspective, to calling a method in a standard Visual Basic Module. A Module is simply a class in which all the members have been declared Shared by the compiler. Using the Shared keyword, you control which methods can be called without creating an instance of the class. Therefore, add the Shared keyword so that the procedure declaration looks like this:
    Public Shared Function GetDataTable( _
    ByVal ConnectionString As String, _
    ByVal SQL As String) As DataTable
    Finally, it's time to replace the inside of the procedure. Modify the procedure
    so that it looks like the following:
    Public Shared Function GetDataTable( _
    ByVal ConnectionString As String, _
    ByVal SQL As String) As DataTable

' Let exceptions bubble up to the caller. Dim dt As New DataTable Dim da As New OleDbDataAdapter(SQL, ConnectionString)

da.Fill(dt)

Return dt End Function

The code creates a new DataTable instance, ready to hold data. Then, the code creates a new OleDbDataAdapter, passing its constructor the SQL SELECT string and the connection string. The code calls the OleDbDataAdapter.Fill method in order to retrieve the data from the database and put that data into the DataTable, dt. Finally, the method returns the filled DataTable.

At this point, you can delete the other two methods in the DataLayer class (CloseRecordset and OpenConnection). Because the DataTable you're using to work with the data is always disconnected, there's nothing to close. In addition, because filling the data adapter handles opening and closing the connection, you don't need a separate procedure for that operation, either. Once you're done, the DataLayer class should contain only the GetDataTable method.

In addition to the existing method in the DataLayer class, the new version requires an additional procedure. This new method allows you save changes made to the data within the DataTable instance back to the original data source. Although you could accomplish this goal in many ways, the simplest solution is to allow an OleDbCommandBuilder instance to generate the appropriate SQL UPDATE statement for you, and then call the Update method of an OleDbDataAdapter.

Insert the following procedure into the DataLayer class:

Public Shared Sub UpdateDataTable( _
 ByVal dt As DataTable, _
 ByVal ConnectionString As String, ByVal SQL As String)

  ' Pass exceptions back out to the caller.

  ' Create the data adapter, and an associated
  ' command builder:
  Try
    ' The RowUpdated event handler requires the same 
    ' connection used when performing the update, 
    ' so this code creates it, and leaves it open.
    mcnn = New OleDbConnection(My.Settings.ConnectString)
    mcnn.Open()

    Dim da As New OleDbDataAdapter(SQL, mcnn)
    Dim bld As New OleDbCommandBuilder(da)

    AddHandler da.RowUpdated, AddressOf OnRowUpdated

    ' The Update method uses the INSERT, UPDATE, and DELETE
    ' statements generated by the command builder:
    da.Update(dt)
  Finally
    ' No matter what, close the connection when the code
    ' completes.
    mcnn.Close()
  End Try
End Sub

This method creates an OleDbDataAdapter, just as in the GetDataTable method. When you call the UpdateDataTable method, you pass in a DataTable from which you'll retrieve changed rows, along with a connection string and the SQL SELECT string that you used to retrieve the data.

Tip   The UpdateDataTable method hooks up the RowUpdated event handler for its OleDbDataAdapter, using the AddHandler statement. This statement, new in .NET, allows you to dynamically tell Visual Basic which procedure it should call in reaction to an object's event. In this case, the code indicates that when the RowUpdate event occurs, the .NET runtime should call the OnRowUpdated procedure in the same class.

You may wonder why you're passing in a SELECT statement, even though you're attempting to perform an update. The OleDbCommandBuilder class can take the SELECT statement associated with a data adapter, and from that, generate the corresponding INSERT, UPDATE, and DELETE statements. In this procedure, the command builder does its work, and then the code calls the Update method of the data adapter to save the changes back to the data source.

Tip   If you're interested, you may find it useful to view the UpdateCommand, InsertCommand, and DeleteCommand properties of the OleDbDataAdapter while running the application. Once you've completed the steps in this article, you can put a breakpoint within the UpdateDataTable procedure, and once you hit the breakpoint, use the debugging tools in Visual Studio 2005 to investigate these properties.

Update the Utility Module

Next, you'll update the Utility.vb file to take advantage of ADO.NET features. Open the file within the code editor, and you'll note that this is a standard Visual Basic Module. From the Visual Basic 2005 perspective, the project contains a class named basUtility with several Shared methods. Although you won't do it here, in a new project, you might want to consider creating standard classes (rather than modules) and using the Shared keyword explicitly to indicate procedures that can be called without creating an instance of the host class.

Follow these steps to get started modifying Utility.vb:

  1. As in the previous class, start at the top and remove the two statements added by the conversion utility:

    Option Strict Off
    Option Explicit On

  2. Perform a search and replace operation and change all ByRef statements to ByVal statements.

  3. Remove the first method, HandleUnexpectedError, which is no longer needed in this application.

Now go ahead and modify each of the methods as shown in the next few sections in this article.

AddQuotes Method

Replace the AddQuotes method with the following method, which takes advantage of two .NET features. The code returns its value using the Return statement (for consistency with other Visual Basic 2005 code). In addition, the procedure uses the String.Format method, which allows you to create a template with replaceable parameters. In this case, the {0} value within the template gets replaced with the value returned by calling the Replace method of the supplied string, replacing single apostrophes with double apostrophes:

Public Function AddQuotes(ByVal strValue As String) As String
  Return String.Format("'{0}'", strValue.Replace("'", "''"))
End Function

FindString Method

The FindString method uses the Visual Basic If TypeOf statement to determine the type of the control you pass in. This statement behaves differently in Visual Basic 2005 than it did in Visual Basic 6, but only when used with Structure types. In this case, you're not using structures, so the warnings you see in the code aren't valid in this case. If you want to satisfy the warnings, you can replace the procedure with the following procedure. We don't really see the need, but doing so will satisfy the warnings:

Public Sub FindString(ByVal ctl As ListControl, _
 ByVal strFind As String)
  Dim intPos As Integer = -1

  ' Determine type of control. This would be much 
  ' simpler if ListBox and ComboBox both inherited from
  ' a base control type that provides a FindStringExact method,
  ' but they don't. Therefore, you have to treat each separately.
  If ctl.GetType() Is GetType(ListBox) Then
    ' Cast control and attempt to find the string
    intPos = CType(ctl, ListBox).FindStringExact(strFind)
  ElseIf ctl.GetType() Is GetType(ComboBox) Then
    ' Cast control and attempt to find the string
    intPos = CType(ctl, ComboBox).FindStringExact(strFind)
  End If

  ' Set the selected index
  ctl.SelectedIndex = intPos
End Sub

To be honest, all those calls to GetType seem overly complex to us. You may want to use the original If TypeOf construct (a perfectly reasonable solution, in our eyes), and if so, you can replace the procedure with the following version, which takes advantage of Visual Basic 2005 features but still uses If TypeOf to compare object types:

Public Sub FindString(ByVal ctl As ListControl, _
 ByVal strFind As String)
  Dim intPos As Integer = -1

  ' Determine type of control. This would be much 
  ' simpler if ListBox and ComboBox both inherited from
  ' a base control type that provides a FindStringExact method,
  ' but they don't. Therefore, you have to treat each separately.
  If TypeOf ctl Is ListBox Then
    ' Cast control and attempt to find the string
    intPos = CType(ctl, ListBox).FindStringExact(strFind)
  ElseIf TypeOf ctl Is ComboBox Then
    ' Cast control and attempt to find the string
    intPos = CType(ctl, ComboBox).FindStringExact(strFind)
  End If

  ' Set the selected index
  ctl.SelectedIndex = intPos
End Sub

FixBoolean Method

The FixBoolean procedure expects to receive an ADO Field object, and sets the value of a check box to match the truth value of the field's contents. To fix this procedure, change the input value to be an Object type, and pass the parameter directly to the CBool function. When you're done, the procedure should look like this:

Public Function FixBoolean(ByVal fld As Object) As CheckState
  ' Convert a Yes/No/Null field value
  ' into a checkbox value.
  If CBool(fld) Then
    Return CheckState.Checked
  Else
    Return CheckState.Unchecked
  End If
End Function

NullToText Method

The NullToText method relied on the fact that in Visual Basic 6, you could concatenate any variant (even the value Nothing) with an empty string, and the result would be a string. Because ADO.NET handles null values separately from the special Nothing value, this behavior no longer works. Instead, your code needs to check specifically for a null database value, and return an empty string in that case. Therefore, modify NullToText so that it looks like the following:

Public Function NullToText(ByVal fld As Object) As String
  If IsDBNull(fld) Then
    Return String.Empty
  Else
    Return CStr(fld)
  End If
End Function

TextToNull Method

Although the TextToNull method contains a number of dire-appearing warnings, it really doesn't require any changes—all the warnings are benign. However, to keep in the spirit of writing good Visual Basic 2005 code, you should modify this method to check the value of the string coming in and return either System.DBNull.Value or the string itself. Therefore, replace the procedure with the following:

Public Function TextToNull(ByVal strValue As String) As Object
  If String.IsNullOrEmpty(strValue) Then
    Return System.DBNull.Value
  Else
    Return strValue
  End If
End Function

At this point, you've handled the two easy classes. The CustomerHandler class and the ADO form require a bit more effort. Hopefully, you'll agree that taking the time to dig into this conversion is worth the effort. Along the way, you'll learn both Visual Basic 2005 tips, and more about ADO.NET as well.

Overhauling the CustomerHandler Class

The CustomerHandler class is next in line. Open Customer.vb in the code editor, and follow these steps to perform the updates:

  1. Just as you did with the other two files, remove the two Option statements at the top of the file:

    Option Strict Off
    Option Explicit On

  2. Because you may also later move the CustomerHandler class to a different assembly in order to reuse the class, change the Friend keyword to Public:

    Public Class CustomerHandler

  3. Remove the CloseRecordset method, as it's no longer needed.

  4. Because you'll need class-level variables to keep track of the customer DataTable, and the current row within the DataTable, add the following two variables within the class, but outside any procedure:

    Private mdt As DataTable
    Private mintCurrentRow As Integer

Working with the Connection String

Currently, the connection string is hard-coded within the GetConnection string method in the CustomerHandler class. Although this might be fine for a simple application, it's generally not practical for real applications. Rather than hard-coding this information within the application, it would be better if you were to place it somewhere where it could be modified easily—in other words, in a configuration file. Visual Basic 2005 has strong support for storing and retrieving data from configuration files, and the .NET Framework even makes it reasonably easy to secure this information, if you must.

For now, you'll just move the connection string to a configuration file, and use the Visual Basic 2005 My.Settings class to retrieve it. Follow these steps to move the connection string:

  1. On the Properties menu, select Project | ADOForm.

  2. Click the Settings tab on the left side of the Properties window.

  3. Click the link prompting you to create a new settings file.

  4. In the settings grid, set the Name to ConnectString, and enter the following string as the value, changing the path to the location where you've placed the Visual Basic 6Demo.MDB file on your computer:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Samples\ADOFormNET\VB6Demo.MDB

    When you're done, the grid should look as shown in Figure 2.

    ms364043.adotonet2_02(en-US,VS.80).gif

    Figure 2. Modify settings so that they look as shown.

Tip   This application is complicated somewhat because it uses a JET MDB file for its data store. The connection information for JET databases requires a complete path, so that if you move the database, the connection no longer works. You may find it easier in the long run (and instructive in the short run) to convert the application so that it stores its data in SQL Server, MSDE, or SQL Express. You won't have to make many changes, but you will need to change the connection string.

  1. Close the settings window, and save the files when prompted.

  2. Just for fun, double-click app.config in the Solution Explorer window to see what changes you've made. Near the bottom, you'll find the following data (you can close the window when you're done):

    applicationSettings>
    <ADOForm.Settings>
    <setting name="ConnectString" serializeAs="String">
    <value>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Samples\ADOFormNET\VB6Demo.MDB</value>
    </setting>
    </ADOForm.Settings>
    </applicationSettings>

    Note   When you add settings to the app.config file, Visual Basic 2005 generates a class named Settings within the My namespace, with a property corresponding to each setting. Therefore, you can refer to My.Settings.ConnectString within your code, and Visual Basic 2005 will automatically retrieve the value from the configuration file for you.

  3. Because it makes more sense, move the GetConnectionString method from the Customer.vb file to the Utility.vb file, and modify its code so that it looks like the following:

    Public Function GetConnectionString() As String
    ' Use My.Settings to retrieve the connection string from the
    ' configuration file:
    Return My.Settings.ConnectString
    End Function

GetCustomers Method

The GetCustomers method simply needs to retrieve the data, set the current row, and return the stored DataTable as its return value. Therefore, replace the contents of this procedure with the following code:

Public Function GetCustomers() As DataTable
  mdt = DataLayer.GetDataTable(GetConnectionString(), _
         "SELECT * FROM tblCustomer ORDER BY LastName")
  mintCurrentRow = 0
  Return mdt
End Function

Tip   Because you created the DataLayer.GetDataTable method using the Shared keyword, you don't need to create an instance of the DataLayer class in order to call the method. The DataLayer.GetStates method you'll use in the next section exhibits the same behavior.

GetStates Method

The GetStates method can take advantage of the DataLayer class, just like the GetCustomers method did. Modify the GetStates procedure so that it looks like the following:

Public Function GetStates() As DataTable
  Return DataLayer.GetDataTable(GetConnectionString(), _
   "SELECT State FROM tblStates")
End Function

In converting this application to take advantage of ADO.NET, we made the "executive decision" to have the CustomerHandler class take care of its own navigation—that is, the CustomerHandler class needs methods to handle moving around within the data. This section discusses the new methods, and walks you through adding them to the CustomerHandler class.

Add Location Properties

Because the DataTable class doesn't (and shouldn't) keep track of a current row (that's the job of the code that consumes the DataTable), the CustomerHandler class needs properties to return the current row number, the number of rows, and whether the current row is at the beginning or the end of the set of rows. Add the following read-only properties to the CustomerHandler class to support this functionality:

Public ReadOnly Property RowCount() As Integer
  Get
    Return mdt.Rows.Count
  End Get
End Property

Public ReadOnly Property CurrentRow() As DataRow
  Get
    Return mdt.Rows(mintCurrentRow)
  End Get
End Property

Public ReadOnly Property BOF() As Boolean
  Get
    Return (mintCurrentRow = 0)
  End Get
End Property

Public ReadOnly Property EOF() As Boolean
  Get
    Return (mintCurrentRow = (mdt.Rows.Count - 1))
  End Get
End Property

Add Navigation Methods

The demonstration form includes buttons that allow you to move around within the CustomerHandler class data. Because the CustomerHandler class is now taking control over its own navigation, you'll need to add methods that allow consumers to move around in the data. To that end, add the following procedures to the CustomerHandler class:

Public Sub MoveFirst()
  mintCurrentRow = 0
End Sub

Public Sub MoveLast()
  ' If you have rows, move to the last row.
  If mdt.Rows.Count > 0 Then
    mintCurrentRow = mdt.Rows.Count - 1
  End If
End Sub

Public Sub MoveNext()
  If mintCurrentRow < mdt.Rows.Count Then
    mintCurrentRow += 1
  End If
End Sub

Public Sub MovePrevious()
  If mintCurrentRow > 0 Then
    mintCurrentRow -= 1
  End If
End Sub

Add Search Functionality

The demonstration form makes it possible for users to search for a row, based on the LastName field. The DataTable class provides a Find method, to which you pass an incomplete SQL WHERE clause (that is, without the WHERE clause), and you can use this method to find a row (or rows) within the DataTable. The Find method returns an array of DataRow objects, and in this case, you'll just want to navigate to the first match, if the resulting array contains any rows.

The Find method in the CustomerHandler class will allow you to pass in the search criteria, and will set the internal current row value to the first matched row, if it finds one. (It will leave the current row value intact if it doesn't find a match.) In addition, the method will return True if it succeeds, and False if it does not.

Add the following procedure to the CustomerHandler class:

Public Function Find( _
 ByVal SearchCriteria As String) As Boolean
  Dim retval As Boolean = False

  Dim adr As DataRow() = mdt.Select(SearchCriteria)
  If adr.Length > 0 Then
    ' If the search returned any rows, then
    ' set the current row to be the first
    ' row (row 0) within the array:
    retval = True
    mintCurrentRow = mdt.Rows.IndexOf(adr(0))
  End If

  Return retval
End Function

You can pass the Find method (and correspondingly, the DataTable.Select method) a string like these:

LastName = 'Jones'
' or
CompanyName LIKE 'A%'

Add Code to Save Changes

After making changes to the data within the CustomerHandler class' DataTable instance, you'll want to be able to save the data back to the underlying data source. The DataLayer class provides an UpdateDataTable method that you can call, passing in the DataTable to be saved, a connection string, and the original SELECT statement (so that the underlying code can generate the appropriate UPDATE, DELETE, and INSERT statements).

Although you could pass the entire Customer DataTable to the UpdateDataTable method, if you later decide to move the DataLayer class to a separate assembly, you can achieve slightly better efficiency by only passing the rows that have changed. Why send all the rows, when all you care about are the changed rows? The DataTable class makes it simple to retrieve just those rows—the DataTable.GetChanges method filters the rows so that only the changed rows are "visible". Calling this method before passing the data to the UpdateDataTable provides a simple yet effective improvement.

Add the following procedure to the CustomerHandler class:

Public Sub SaveToDatabase()
  ' Pass just the changed rows to the UpdateDataTable method
  DataLayer.UpdateDataTable(mdt.GetChanges(), _
   GetConnectionString(), _
   "SELECT * FROM tblCustomer ORDER BY LastName")
End Sub

Add Code to Update Changes

Updating a row in the CustomerHandler class requires a few steps. First, you must save the changes back to the underlying database. Then, you must instruct the DataTable to flush its "original row" information, so that it no longer thinks that the row needs to be modified in the data source.

The Update method that you'll add to the CustomerHandler class takes care of these tasks. The code calls the SaveToDatabase method to save changes, and then calls the AcceptChanges method of the DataTable that causes the DataTable to "forget" that rows had been changed.

Add the following method to the CustomerHandler class:

Public Sub Update()
  SaveToDatabase()

  ' Convince the DataTable that there aren't any
  ' pending changes:
  mcust.AcceptChanges()
End Sub

Warning   The code in this sample isn't particularly careful about concurrency errors, or problems that might occur because rows are locked when you attempt to save changes. These issues are outside the scope of this demonstration, but you'll certainly want to investigate the issues and how to handle them. For more information, see Concurrency Control in ADO.NET.

Add Code to Delete a Customer

Deleting a row from the CustomerHandler class requires just one more step than updating does—you must first delete a specific row. Therefore, add the following procedure to the CustomerHandler class, so that you can later delete a customer:

Public Sub Delete()
  ' Delete the current row.
  mdt.Rows(mintCurrentRow).Delete()

  ' Save the changes:
  Update()
End Sub

Allow Adding New Customers

Finally, the CustomerHandler class needs a way in which to set up a new row. From the form, you won't call this method until it's actually time to save the new row—the form itself simply clears out its controls when you ask it to create a new customer, and doesn't call the Customer.AddNew method until you commit the changes.

Add the following procedure to the CustomerHandler class, which handles creating a new blank row, adding it to the DataTable, and setting the current row to the new index:

Public Sub AddNew()
  Dim row As DataRow

  ' Create a new row:
  row = mdt.NewRow
  ' Add the new blank row to the DataTable:
  mdt.Rows.Add(row)

  ' Set the current row to this new row:
  mintCurrentRow = mdt.Rows.Count - 1
End Sub

Modify the Sample Form

Now that all the support procedures are done, it's time to attack the user interface. Obviously, because you've made massive changes to the underpinnings of this application, you'll need to make a lot of changes to the form as well. In many cases, you'll simply delete unneeded code. In other cases, you'll change code to better take advantage of Visual Basic 2005 features. Follow these steps to get started modifying the form:

  1. In the Solution Explorer window, right-click ADO.vb and select View Code on the context menu.

  2. Scroll to the top of the file and delete the two directives, as you have for each of the other classes:

    Option Strict Off
    Option Explicit On

  3. Locate and delete the following declaration:

    Private customerRecordset As ADODB.Recordset
    Search for all instances of the call to the CloseRecordset method, and delete
    those lines of code.
    Search and replace all instances of ByRef with ByVal.
    Remove the InitForm procedure.
    Remove the frmADO_FormClosed procedure.
    Remove the txtData_TextChanged procedure.
    Remove the chkActive_CheckStateChanged procedure.

ShowData Method

The ShowData method is responsible for getting the current row of data from the DataTable and displaying it on the form. Because of all the changes to the data store, this procedure must be completely replaced. Modify the ShowData method so that it looks like the following:

Private Sub ShowData()
  Dim row As DataRow

  ' Set this flag so event procedures
  ' don't do anything during this process.
  currentDataState = DataState.Loading

  Try
    If customer.RowCount = 0 Then
      AddNewRow()
    Else
      ' Store the Current row in local variable
      ' to avoid writing a lot of code.
      row = customer.CurrentRow

      ' Note that when you convert from VB6 to VB 2005, 
      ' the conversion process uses a custom control to 
      ' emulate the behavior of control arrays. txtData
      ' isn't really the name of the control--on the form
      ' the controls are named _txtData_1, _txtData_2, and so 
      ' on. For new forms, you wouldn't use this functionality,
      ' but instead, would create separate controls, one for
      ' each field. The custom control hides all this from you.
      txtData(0).Text = NullToText(row("CustomerID"))
      txtData(1).Text = NullToText(row("FirstName"))
      txtData(2).Text = NullToText(row("LastName"))
      txtData(3).Text = NullToText(row("Address"))
      txtData(4).Text = NullToText(row("City"))
      txtData(5).Text = NullToText(row("ZipCode"))

      FindString(cboState, NullToText(row("State")))
      chkActive.CheckState = FixBoolean(row("Active"))

      HandleButtonState()
      currentDataState = DataState.Normal
    End If
    GotoFirstControl()

  Catch ex As Exception
    MessageBox.Show(ex.Message)

  End Try
End Sub

This code isn't much different in intent from the original Visual Basic 6 code. The big difference here, of course, is that you're now working with a DataRow object rather than with the entire Recordset.

Tip   When you converted the application from Visual Basic 6 to Visual Basic 2005, the conversion process left the text box control array intact, using a custom control that emulates control arrays in Visual Basic 2005. Normally, you won't use control arrays in Visual Basic 2005; instead, you'll use individual controls that can share event handlers. We decided not to fix this up for this article in the series, with the knowledge that we'd handle this differently in the next article.

Modify the Navigation Methods

Because you've moved the navigation code from the form to the CustomerHandler class, you'll need to modify all button Click event handlers that move you through the data. Make the necessary changes to update the following procedures:

Private Sub cmdFirst_Click( _
 ByVal eventSender As System.Object, _
 ByVal eventArgs As System.EventArgs) _
 Handles cmdFirst.Click
  customer.MoveFirst()
  ShowData()
End Sub

Private Sub cmdLast_Click( _
 ByVal eventSender As System.Object, _
 ByVal eventArgs As System.EventArgs) _
 Handles cmdLast.Click
  customer.MoveLast()
  ShowData()
End Sub

Private Sub cmdNext_Click( _
 ByVal eventSender As System.Object, _
 ByVal eventArgs As System.EventArgs) _
 Handles cmdNext.Click
  customer.MoveNext()
  ShowData()
End Sub

Private Sub cmdPrevious_Click( _
 ByVal eventSender As System.Object, _
 ByVal eventArgs As System.EventArgs) _
 Handles cmdPrevious.Click
  customer.MovePrevious()
  ShowData()
End Sub

Fix up the HandleNavButtons Method

As part of its work, the ShowData method calls the HandleButtonState method. Nothing needs to change in the HandleButtonState method. It, however, calls the HandleNavButtons method to ensure that the navigation buttons are turned on and off at the appropriate times depending on which row in the DataTable in current. You can really simplify this code, because all of the logic for calculating EOF and BOF are now contained within the CustomerHandler class itself. Modify the HandleNavButtons procedure so that it looks like the following:

Private Sub HandleNavButtons()
  ' Handle the enabling/disabling of
  ' the navigation buttons.

  Select Case currentDataState
    Case DataState.Adding, DataState.Editing
      cmdFirst.Enabled = False
      cmdPrevious.Enabled = False
      cmdNext.Enabled = False
      cmdLast.Enabled = False

    Case Else
      cmdFirst.Enabled = Not customer.BOF
      cmdPrevious.Enabled = Not customer.BOF

      cmdNext.Enabled = Not customer.EOF
      cmdLast.Enabled = Not customer.EOF
  End Select
End Sub

Loading the Form

The form's Load event handler sets up the data, and the display of the data. Obviously, because the underpinnings have changed, you'll need to modify the form's Load event handler. This code creates an instance of the CustomerHandler class, and calls the GetCustomers method of the new instance in order to then display the data. Modify the load event handler so it looks like the following:

Private Sub frmADO_Load( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
  ' Create the New Customer Object
  customer = New CustomerHandler

  Try
    customer.GetCustomers()
    If customer.RowCount > 0 Then
      LoadStates()

      ' Display the form.
      ShowData()
    Else
      MessageBox.Show("No rows to load. Exiting the application.")
      Me.Close()
    End If

  Catch ex As Exception
    MessageBox.Show(ex.Message)
  End Try
End Sub

Load the ComboBox Containing States

The LoadStates method, called from the Load event handler, sets up the combo box containing the list of states. The ComboBox control makes it easy to bind its list and value to a DataTable, and this procedure handles this for you. Modify the LoadStates procedure to match the following example:

Private Sub LoadStates()
  ' Load cboStates from tblStates.

  Try
    ' Watch out! If the Sorted property
    ' of the ComboBox control is set to 
    ' True, the data binding will fail 
    ' silently. In this case, the ComboBox control
    ' converted from VB6 with the Sorted property
    ' set to True, so this code simply didn't do anything.
    ' Setting the Sorted property to False solved
    ' the problem.
    Dim states As DataTable = customer.GetStates()

    cboState.Sorted = False
    cboState.DisplayMember = "State"
    cboState.ValueMember = "State"
    cboState.DataSource = states

  Catch ex As Exception
    MessageBox.Show(ex.Message)

  End Try
End Sub

Note   When we first wrote this code, the combo box simply showed up empty. If we deleted it and created a new one from scratch, it worked fine. This indicated that some property was set incorrectly. After some research, it became clear—the Sorted property was set to True, and data binding can't work if it is. You'll need to set the Sorted property to False in order to get the data binding to work. Until you do, the combo box will appear without any items.

Handle Field Values Changing

In the original application, both the txtData.TextChanged and chkActive.CheckedChanged event handlers ended up calling the HandleChange procedure. It's simpler to have the HandleChange procedure take care of both events itself. To do that, modify the HandleChange procedure declaration so that the procedure looks like this:

Private Sub HandleChange( _
 ByVal sender As Object, ByVal e As EventArgs) _
 Handles txtData.TextChanged, chkActive.CheckedChanged

  ' Deal with changes to text
  ' boxes or check box.
  ' If there are no rows, now you're
  ' editing.
  ' If you're just viewing data,
  ' now you're editing.
  ' Otherwise, get out.
  Select Case currentDataState
    Case DataState.NoRows
      currentDataState = DataState.Adding
    Case DataState.Normal
      currentDataState = DataState.Editing
    Case Else
      Exit Sub
  End Select

  ' Set up the buttons.
  HandleButtonState()
End Sub

States SelectedIndexChanged Event Procedure

The code that handles the SelectedIndexChanged event for the State field ComboBox needs to keep track of the current state of the form, and handle changes correctly. The problem is that the SelectedIndexChanged event handler gets called when you select an item in the list portion of the combo box, even if you didn't change the value. Switching to edit mode, in this case, doesn't make sense. Therefore, the code in the SelectedIndexChanged event handler needs to compare the new value with the original value, and only switch to edit mode if the value changed. Modify the SelectedIndexChanged event handler to match the following code:

Private Sub cboState_SelectedIndexChanged( _
 ByVal eventSender As System.Object, _
 ByVal eventArgs As System.EventArgs) _
 Handles cboState.SelectedIndexChanged

  ' If the State changes, modify the current DataState
  Select Case currentDataState
    Case DataState.NoRows
      currentDataState = DataState.Adding
    Case DataState.Normal
      ' Check to see if same State was chosen.
      ' If so, don't do anything.
      If cboState.Text = NullToText( _
       customer.CurrentRow("State")) Then
        Exit Sub
      End If
      currentDataState = DataState.Editing
    Case DataState.Editing
      ' Do nothing.
    Case Else
      Exit Sub
  End Select

  HandleButtonState()
End Sub

**Note   **When preparing this article, we ran across an error in the original SelectedIndexChanged event handler, and fixed it here. That's why the code looks slightly different than in the previous version.

Modify the KeyDown Event Procedure

You can move through rows on the sample form using keystrokes instead of the mouse. To accomplish this, the sample has set the KeyPreview property of the form to True, and includes code to handle the keystrokes in the KeyDown event handler. As part of the conversion process, the KeyDown event handler includes a lot of code that checks the state of various buttons, and if they're enabled, calls the Click event handler of the button. Luckily, none of this is necessary. The Button class provides a PerformClick method that calls the Click event handler only if the button was enabled. Therefore, you can simplify the KeyDown event handler so that it includes only the following code:

Private Sub frmADO_KeyDown(ByVal eventSender As System.Object, _
 ByVal eventArgs As System.Windows.Forms.KeyEventArgs) _
 Handles MyBase.KeyDown
  ' Add support for PageUp, PageDown,
  ' Ctrl+PageUp, Ctrl+PageDown,
  ' Ctrl+Home, Ctrl+End
  Select Case eventArgs.KeyCode
    Case Keys.PageDown
      ' Ctrl+PgDn goes to the last row.
      ' PgDn goes to the next row.
      If eventArgs.Control Then
        cmdLast.PerformClick()
      Else
        ' The PerformClick method is great: If the 
        ' button isn't enabled, it simply doesn't 
        ' call the method. There's no need to even
        ' check to see if the button is enabled or not!
        cmdNext.PerformClick()
      End If
    Case Keys.PageUp
      ' Ctrl+PgUp goes to the first row.
      ' PgUp goes to the previous row.
      If eventArgs.Control Then
        cmdFirst.PerformClick()
      Else
        cmdPrevious.PerformClick()
      End If
    Case Keys.Home
      ' Ctrl+Home goes to the first row.
      If eventArgs.Control Then
        cmdFirst.PerformClick()
      End If
    Case Keys.End
      ' Ctrl+End goes to the last row.
      If eventArgs.Control Then
        cmdLast.PerformClick()
      End If
  End Select
End Sub

**Note   **The KeyDown event handler takes advantage of its second parameter to determine if you've pressed the Ctrl key along with another key: the eventArgs.Control property will be True if so. You'll find that the eventArgs parameter includes a number of other useful properties, if you want to determine the state of the keyboard when the event was raised.

Clearing All the Form Controls

The ClearForm method loops through all the controls on the form, clearing each to a known state. Just as you saw in the earlier FindString code, you can resolve the conversion warnings by either writing a lot of code that uses the Control.GetType method, or you can continue to use the If TypeOf construct. We voted for the latter, and so you can replace the existing code with the following:

Private Sub ClearForm()
  ' Clear out all the controls.
  ' This form has only text boxes, check boxes,
  ' and combo boxes.

  ' Your forms may have other controls to worry about.

  ' Watch out for recursive events: setting the
  ' text of a textbox to "" will trigger its Change
  ' event, for example. That's why this code
  ' sets currentDataState to be Loading. No current
  ' code uses that particular state value, but
  ' it at least indicates that something's going on.

  Try
    currentDataState = DataState.Loading

    For Each ctl As Control In Me.Controls
      If TypeOf ctl Is TextBox Then
        ctl.Text = ""
      ElseIf TypeOf ctl Is CheckBox Then
        CType(ctl, CheckBox).CheckState = CheckState.Checked
      ElseIf TypeOf ctl Is ComboBox Then
        CType(ctl, ComboBox).SelectedIndex = -1
        ' TODO: Add more control types, if necessary.
      End If
    Next ctl

    If customer.RowCount = 0 Then
      currentDataState = DataState.NoRows
    Else
      currentDataState = DataState.Adding
    End If

  Catch ex As Exception
    MessageBox.Show(ex.Message)

  End Try
End Sub

Saving Data

The Save button is only enabled when the currentDataState internal value is set to DataState.Adding or DataState.Editing. Clicking the Save button checks the state, and takes appropriate action. This procedure doesn't need to change much, as you can see—now, instead of calling methods of a Recordset, you're calling methods of the CustomerHandler class. In addition, there's no longer a need to keep track of the current bookmark in case of error. Just as in the Visual Basic 6 version, the SaveData procedure includes code to retrieve the new primary key value, and display it on the form. Replace the SaveData procedure with the following:

Private Function SaveData() As Boolean
  Try
    If currentDataState = DataState.Adding Then
      customer.AddNew()
    End If

    ' Whether adding or editing,
    ' you need to save fields and update
    ' the recordset.
    SaveFields()

    customer.Update()

  Catch ex As Exception
    MessageBox.Show(ex.Message)
  End Try

  If currentDataState = DataState.Adding Then
    ' Display the newly added key.
    GetID()
  End If

  ' Reset buttons.
  currentDataState = DataState.Normal
  HandleButtonState()
End Function

Moving Field Values from the Form to the Table

The SaveFields method doesn't require much change—you simply need to move data from the form to a DataRow instance instead. Therefore, replace the SaveFields method with the following code:

Private Sub SaveFields()
  Try
    Dim row As DataRow = customer.CurrentRow

    row.BeginEdit()
    row("FirstName") = TextToNull(txtData(1).Text)
    row("LastName") = TextToNull(txtData(2).Text)
    row("Address") = TextToNull(txtData(3).Text)
    row("City") = TextToNull(txtData(4).Text)
    row("ZipCode") = TextToNull(txtData(5).Text)
    row("State") = TextToNull((cboState.Text))
    row("Active") = CBool(chkActive.CheckState)
    row.EndEdit()

  Catch ex As Exception
    MessageBox.Show(ex.Message)

  End Try
End Sub

Retrieving the New Customer ID

Retrieving the new customer ID value isn't any trickier now, but it does require a small change to the code. Now, rather than retrieving a value from a RecordSet, you retrieve it from the DataTable. Replace the GetID procedure with the following code:

Private Sub GetID()
  txtData(0).Text = customer.CurrentRow("CustomerID").ToString()
End Sub

Deleting a Customer

The DeleteRow method becomes a little bit simpler, in this version. You no longer need to worry about maintaining a bookmark referring to the current row, so that if something goes wrong you can return to the current row. Instead, because the CustomerHandler class takes care of the details, you simply call the Delete method of the class, and then the ShowData method to display the current row. In addition, you no longer need to move to the next row, because the row number hasn't changed.

Note also that this procedure uses the MessageBox.Show method, taking advantage of the various options provided by this method. Although you're welcome to use the MsgBox function, we wanted to show off the .NET Framework equivalent in this example.

Replace the existing DeleteRow method with the following code:

Private Sub DeleteRow()
  Try
    If MessageBox.Show("Delete the current Customer?", _
     "Delete", MessageBoxButtons.YesNoCancel, _
     MessageBoxIcon.Exclamation) = DialogResult.Yes Then
      ' Delete the customer record in the DataTable
      customer.Delete()

      ' Whether there's any data or not,
      ' show a row.
      ShowData()
    End If

  Catch ex As Exception
    MessageBox.Show(ex.Message)

  End Try
End Sub

Finding a Row

Finally (it's the last procedure you'll need to change), the FindRow method becomes substantially simpler in this version. You don't need to track the current bookmark, and restore its value if the search fails. Instead, the CustomerHandler.Find method returns True if it succeeded, making the code really simple. Replace the existing FindRow method with this code:

Private Sub FindRow()
  Dim searchCriteria As String
  Dim nameToFind As String

  nameToFind = InputBox("Enter " & conFindField & _
   " value to find:", Me.Text)

  If Len(nameToFind) > 0 Then
    ' Search with a wildcard, using the
    ' value the user entered as the beginning
    ' of the search string.
    searchCriteria = conFindField & _
     " LIKE " & AddQuotes(nameToFind & "%")

    ' Find the record.
    If customer.Find(searchCriteria) Then
      ShowData()
    Else
      MsgBox("No match found!", MsgBoxStyle.OKOnly, Me.Text)
    End If
  End If
End Sub

Conclusion

As you've seen, replacing existing ADO code with the equivalent ADO.NET isn't terribly difficult, but you'll end up touching just about every piece of your application that communicates with the data. Of course, proper separation of user interface and data should ease the transition—we attempted to perform some fix-up in this area as we performed the ADO to ADO.NET conversion, as well. Of course, our simple application doesn't even come close to emulating a real data-oriented application—that wasn't its point. Moving on from here, you should consider issues involving data concurrency, error handling, and more advanced uses of ADO.NET; all these are covered in other articles available on MSDN (for more information, see the Overview of ADO.NET).

If we were writing this for our own use, we would have made use of inheritance, creating a base class that represents an entity (a Customer, an Order, an Employee, and so on) and included all the navigation code there. Then, each individual entity type could inherit from this base class, and wouldn't need specific navigation code. Again, this sort of addition is beyond the scope of this (already very long) article, but it's worth investigating.

In the final article in this series, we'll again gut the entire application and take advantage of the exciting new data-binding features available in Visual Studio 2005 and Visual Basic 2005. You'll be amazed at how much code you won't have to write.

About the Authors

Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP .NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).

Paul D. Sheriff is the President of PDSA, Inc. (www.pdsa.com), a Microsoft Partner in Southern California. Paul acts as the Microsoft Regional Director for Southern California assisting the local Microsoft offices with Developer Days and several other large events each year. Paul has authored five books on .NET, and two eBooks on SharePoint can be purchased at the PDSA Web site.

© Microsoft Corporation. All rights reserved.