By now, you have been exposed to a lot of information about Visual Studio .NET.

Of all the new technologies associated with .NET, perhaps no other technology is more mysterious than ADO.NET. The purpose of this article, the first in a series, is to give you a brief overview of ADO.NET and how it is implemented in Visual Studio .NET. Future articles will expand on the material presented here. After reading this article, you will be able to understand how the various ADO.NET objects and generated code work to provide data for your applications.

To build a data-aware form in Visual Studio .NET, you don't need to write a single line of code. As professional developers, you know instinctively that auto-generated code from a wizard process is rarely production quality. The code and finished product presented in this article is no exception. The goal of this article is to introduce basic techniques for building data-aware forms in Visual Studio .NET. This article will demonstrate using the Visual Studio .NET tools to create a data-aware form and then will examine the resulting objects and code. A future article will demonstrate creating production quality code.

The 10,000 foot view of ADO.NET

Before diving into the details of creating a data-aware form, you should take time to learn the "nuts and bolts" of ADO.NET. If you have worked with prior versions of ADO, much of this will be familiar territory. First off, the good news: the Connection and Command objects that you were introduced to in ADO are basically the same. The Connection Object provides connectivity to database servers and the Command object is still used to execute stored procedures. The bad news (it's not that bad!) is that if you look for the Recordset Object, you will look for a very long time. In a smart move, Microsoft decided to split the Recordset Object into two separate objects: the Dataset and DataAdapter Objects.

If you recall, the Recordset Object operated as the data store and had the intelligence to perform the primary CRUD (Create, Update, and Delete) data operations. If you have ever been tasked with building a complete ADO-based application, you quickly learned that the best chance of success rested on using the Recordset Object for one purpose: as a data store. You then delegated the CRUD operations to the Command objects. Microsoft, following this idea, decided to split the Recordset Object along these lines. The Dataset Object is a disconnected cache of data and the DataAdapter contains the functionality to fill the Dataset Object with data as well perform CRUD operations.

Fortunately, you don't have to get into the messy details of ADO.NET in order to take advantage of the technology. You can use the Data Form Wizard to create a data-aware form. The Data Form Wizard guides you through the process of specifying the information Visual Studio .NET requires to access data in your application. The Data Form Wizard creates and configures the various data objects, creates a form and binds the data to objects within the form The finished product contains a lot of generated code that is, for the most part, easy to follow and understand.

With the introductory material out of the way, let's create a data-aware form!

Creating Your First Data-Aware Project and Form

For quick reference, the following code demonstrates creating a project containing a data-aware form. If you don't want to go through the steps, simply download the code from http://www.mainlinesoftware.com/files/adodotnetpart1.zip.

Steps to create the VB.NET Project

  1. Create a new VB.NET application called ADOdotNET.
  2. In the project properties dialog, change the startup object to Sub Main.
  3. Add a new module to the project called main.
  4. Create a sub main in the main module.
  5. Delete the default form created in the project.

Creating a Database Connection

  1. From the Tools menu, choose Connect to Database.
  2. In the dialog:

a. Use the (local) Server.

b. Select Windows NT Integrated Security.

c. Select the pubs database.

Steps to Create your Data-Aware Form

  1. In the project manager, select Add New Item and choose Data Form Wizard.
  2. Name the form "frmAuthors.vb".
  3. Complete the steps of the Data Form Wizard:

a.Create a Dataset named dsAuthors.

b.Select the connection you created when you connected to the pubs database.

c.Select the Authors table.

d.De-select the au-id field.

e.Choose single-record in individual controls and retain all other defaults.

4. Click Finish.

Modifying Sub Main()

Modify the Sub Main in the Main Module as follows:

Dim frmAuthors as new frmAuthors
frmAuthors.ShowDialog()

A Quick Overview of the Project

The following illustrations highlight how the various parts of your project should appear in Visual Studio .NET. Figure 1 illustrates the Server Explorer.

Figure 1: As the name implies, the Server Explorer allows you to connect to and interact with various server services

The only code you need to write is contained in the Sub Main() procedure. Figure 2 illustrates how this code should appear. Alternatively, you could have simply made the form the startup object. However, this would not have allowed you to specify various setup routines that normally execute when an application starts. Specifying a Sub Main() is considered a best practice in Visual Basic Development.

Figure 2:Sub Main(), being the startup object, is the first procedure to execute.

Figure 3 illustrates the finished product of the Data Form Wizard. As you can see, the wizard takes care of placing the various textbox and command button controls on your form. Take note of the three controls that are part of the form, though not contained on the form design surface. Moving from left to right, there are the Dataset, the Connection and the DataAdapter Objects.

Figure 3:The Data Form Wizard takes care of creating and pulling all of the pieces together to create a data-aware form.

A Closer Look at the Dataset, Connection and DataAdapter Objects

Now you can examine what happened when your form was created. Figure 4 illustrates the Authors Dataset Object in further detail. In the Solution Explorer, note the dsAuthors.xsd file. This is an XML Schema Definition file and contains the schema information for the table selected in the Data Form Wizard. Note that the Namespace property in the dsAuthors Properties Sheet points to the dsAuthors.xsd file.

Figure 4:The Dataset Object and XSD file work together to define and create the local data cache.

Figure 5 illustrates the connection object. All of the information required to establish the connection was provided when you worked through the steps of connecting to the pubs database. The ConnectionString property contains information needed to connect to a database. This information includes: the server, the database, and security settings like userid and password. Without the Connection Object, the DataAdapter Object could not obtain data to fill the Dataset Object nor could it perform the CRUD operations to create, update and delete data.

Figure 5:The connection Object is used by the DataAdapter Object to connect to the data source.

Finally, there is the DataAdapter Object, which is shown in Figure 6. The DataAdapter contains all of the logic to select, insert, update and delete data. You should take note of the Property Sheet, and specifically, the SelectCommand property. The SelectCommand is nothing more than the Command Object outfitted with specific information to carry out its task. In this case, the Select Command uses the form's Connection Object and contains a SQL SELECT statement as its command text. If you take a few moments, you will see the same basic scenario for the insert, update and delete command properties.

Figure 6:The DataAdapter Object hosts the various commands used to select, insert, update and delete data

To put it mildly, the Data Form Wizard generates a lot of code! In spite of the quantity of code, what goes on is actually quite simple and straightforward. The bulk of the code is in the Windows Form Designer Generated Code Section; this is code that you normally don't see and don't need to modify. This code in this section is focused on instantiating and setting up the form objects, including the Dataset, Connection and DataAdapter Objects. In addition, the code also sets up the data bindings for each of the controls. Additional details on how the data binding works will be discussed later. Once all the objects are created and set up, the form loads and waits for the user to click the Load button.

Loading the Dataset

Data forms created by the wizard have a LoadDataSet method. The Load button on the form calls this method. Listing 1 illustrates the LoadDataSet code generated by the wizard.

As you can see, the code is well documented and fairly simple. In this case, the DataAdapter is used to retrieve records from the data source. The records are stored into a temporary Dataset. The old records in the main data set are cleared out and replaced with the records just obtained.

Data Bindings

In the Windows-generated code that you don't normally see, each of the edit controls are prepared. One of the ways this is done is through data binding. Each control is made aware of which field in the Dataset the control is bound to. The following code uses the editau_lname textbox to illustrate how to create a data binding in Visual Studio .NET:

Me.editau_lname.DataBindings.Add(New _
         System.Windows.Forms.Binding("Text", _
         Me.objdsAuthors, "authors.au_lname"))

In this case, the Text property of the control reflects the value of the au_lname field found in the objdsAuthors Dataset Object. This process is repeated for every bound control on the form. Once the data bindings are in place, the rest of the process is fairly automatic. As the data in the underlying Dataset changes, the bound controls immediately reflect those changes. And, as data is modified in a bound control, the data in the underlying Dataset is modified to reflect the change.

Data Navigation

Using the Next button as an illustration, let's take a moment to see how you can navigate through a Dataset. The following code is attached to the Next button:

        Me.BindingContext(objdsAuthors, _
           "authors").Position = _
           (Me.BindingContext(objdsAuthors, _
            "authors").Position + 1)

At first, you might think you simply tell the Dataset to change positions. That is only part of the story. Remember that there are bound controls on the form. Somehow, you have to let the controls know that the context has changed and that they have to refresh their contents. This is where the form's BindingContext property comes into play. The BindingContext Class manages all of the data binding on a form. For the most part, this is a black box process. In the code listed above, the BindingContext is informed of the new Dataset position. Also, in the same operation, the Dataset position is actually moved.

Updating Edits

The next area to explore is how edits are updated. The Update button references a form method created by the wizard called UpdateDataSet, as shown in Listing 2.

Like the previous examples, the wizard does a nice job of commenting the code. This code performs a simple test to see if edits occurred. Changes are determined through a call to the GetChanges method of the Dataset. If changes were made, GetChanges returns a copy of the Dataset with the changes. The result Dataset obtained from GetChanges is passed to another custom form method called UpdateDataSource. Listing 3 shows the code for that method.

When attempting an update, the Connection Object is opened. Then, the passed Dataset that reflects the modified data is passed to the Update method of the DataAdapter Object. If any errors occur, they will be trapped with the Try/Catch exception handling mechanism. Regardless of whether an exception occurs, the Connection Object is then closed. Once the update process concludes, control passes back to the UpdateDataSet method to complete the process.

Running the Completed Form

Figure 7 illustrates the new data-aware form in action. By no means does it represent a truly completed product, but it does have the basic functionality to interact with data via ADO.NET.

Figure 7: The new form can navigate through and update data in the pubs Authors Tables

Summary

The purpose of this article was to introduce to you to ADO.NET and how it is implemented in Visual Studio .NET. Specifically, this article uses the Data Form Wizard to create a data-aware form. As you have seen, building a data-aware form in Visual Studio .NET is quite simple, although many details could not be covered here. After you have looked over the code that the wizard generates, ask yourself the following questions:

  • Which of the generated code is good?
  • Which of the generated code is bad?
  • How can the code be optimized?

In the next article, these questions will be examined. Given that Visual Studio .NET is a new product, we are all taking the fun ride of learning together. What's important to remember is that, as with any wizard, what comes out of the box is only a starting point. The most important aspect of the Data Form Wizard is that it provides a quick and efficient way to see how the pieces of ADO.NET interoperate with both the VB.NET language as well as the Windows.Forms Namespace.