970 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS

The answer to all these questions is to update the database as frequently as possible through stored procedures. If a row fails to update, we must notify the user immediately, giving the user a chance to review the error and make the necessary corrections. But this approach violates the whole idea of using disconnected DataSets. Indeed, DataSets are a great tool for a disconnected world, but not all of our applications are disconnected. An application that runs in a small business environment isn’t going to benefit much from the disconnected nature of ADO.NET. It’s very likely that ADO.NET will be augmented in the near future to support connected DataSets as well. Until then, you must do most of the work manually, and you’ll see in this chapter how to update the underlying tables.

The DataForm Wizard

One of the tools that come with Visual Studio is the DataForm wizard, which creates data-entry forms for you. Let’s look at this tool in action, then we’ll discuss its limitations. You will also find interesting coding examples in the output generated by the wizard. The example of this section is the EditProducts project, whose main form is shown in Figure 22.2. This form allows you to edit the rows of the Products table of the Northwind database, enter new rows, and delete existing rows. The interface and the code behind the controls were generated by the DataForm wizard. What you see in Figure 22.2 is the form of the EditProducts project as it was generated by the wizard. The main form of the EditProducts project on the CD is quite different, because we’ll edit this form extensively in this section to make its interface more user-friendly.

Figure 22.2

Editing the Products table on a form generated by the DataForm wizard

Start a new project, name it EditProducts, and delete the Form1 component. Then right-click the project’s name and select Add Add New Item. In the dialog box that appears, select DataForm Wizard. A wizard starts that will take you through the steps of setting up a new DataForm. The first screen displays a welcome message; click Next to skip it. On the next screen, you’re prompted to specify the DataSet on which the DataForm will be based. Since the project doesn’t contain a DataSet, specify the name of a new DataSet, which the wizard will create for you. Enter the name DSProducts and click Next to view the next screen of the wizard.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATAFORM WIZARD 971

You’re prompted to select a connection to a database or specify a new one. Select your connection to the Northwind database and click Next to see the Choose Tables Or Views screen. Here you will see a list of all tables and views in the database, and you can select one or more items on which the DataForm will be based. If you select multiple tables, make sure they’re related. Select the Products table and then click the button with the right arrow to add the Products table to the list of selected items. Each product has a related row in the Categories table and another related row in the Suppliers table. Add these two tables to the list of selected items and then click the Next button to see the next screen.

On the next screen of the wizard, you’re asked to establish relationships between tables. Establish two relationships here, one between categories and products and another one between suppliers and products. In the Name box, enter the name of the first relationship, CategoriesProducts. The Parent table of the relationship is the Categories table, and the Child table is Products. The Keys are the matching columns in each table, and they are the CategoryID columns of the two tables. Then click the button with the right arrow to add the relationship to the list of Relations.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

972 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS

Establish another relationship between the Suppliers and Products tables in a similar manner and add it to the list of Relations. You have established the relations between the DataSet’s tables, and you can click Next to view the next screen of the wizard.

On the next screen of the wizard, you’re prompted to choose the tables and columns that will be stored in the DataForm. We will not display related rows (as we did with the DataGrid control in Chapter 21). We want to design a form that displays all the columns of the Products table, and you’ll see shortly how the other two tables will be used (you can guess that we’ll use them to look up the name of the category and supplier of each product, rather than displaying all categories and all suppliers).

By default, the wizard selects all the fields of the Products table. If you wanted to create a master/detail form, you’d have to specify the Detail table as well. For now click Next to see the next screen of the wizard.

On the last screen, you must select the display style. You can display all rows on a DataGrid control or create a new form with separate controls for each row. Check the radio button Single Record In Individual Controls, and the check boxes at the bottom of the window will be enabled. These check boxes allow you to specify whether the DataForm will contain an Add button (to add new rows to the Products table), a Delete button (to delete the current row), a Cancel button (to cancel any changes in the DataSet and reload all rows from the underlying table) and the Navigation controls (to move from row to row). Leave all the check boxes checked, as they are by default.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATAFORM WIZARD 973

Now click Finish to generate the DataForm. The wizard will add a new form to your project, populate it with the necessary TextBox controls to display the field of the current row in the Products table, and also generate the necessary code. As you can see, all the TextBox controls have the same width and you must adjust them according to the length of the fields they display.

The navigational buttons at the bottom of the form allow you to move to the first/last and previous/next rows in the table. You will see the navigational controls (as shown in Figure 22.2) only if you build the project following the guidelines presented here. The EditProducts project on the CD is an improved version of the form generated by the wizard, and you will see shortly how you can enhance the default user interface.

You can notice immediately that the navigational method of the DataForm wizard is totally inadequate. We can’t expect users to keep clicking the Next button until they hit the desired row. To make the navigational model a little more user-friendly, we’ll add a list with the product names, so that the user can quickly locate the desired product. Figure 22.3 shows the new interface we’re going to build based on the form generated by the wizard.

Figure 22.3

A more functional form for editing the products

First, make the form wider. Then grab all the controls on the form with the mouse and move them to the right to make room for a ListBox control on the left side of the form. Place a ListBox control on the form, as shown in Figure 22.3, and set its properties as follows:

Property	Value
DataSource	objDSProducts.Products
DisplayMember	ProductName
ValueMember	ProductID

This ListBox will be populated with the names of all products in the Products list. The ValueMember property stores the IDs of the products, so that we can retrieve the selected product’s details by the ProductID field when the user selects a product on the list. Then enter the line of Listing 22.4 in the ListBox control’s SelectedIndexChanged event handler. This event is fired every time the user clicks another item on the control, and in its handler we’ll add code to move to the selected product’s row in the Products table in our DataSet. The various TextBox controls on the Form are bound to the corresponding columns of the Products table. As a result, when we move to another row of the Products, the text boxes are updated.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

974 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS

Listing 22.4: Moving to the Selected Product

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Me.BindingContext(objDSProducts, “Products”).Position = ListBox1.SelectedIndex

End Sub

We no longer need the so-called navigation buttons at the bottom of the form, so delete them. Delete the Label control that displays the current row’s number as well—we can easily visualize our approximate position in the table by looking at the ListBox control.

If you run the project now, you’ll be able to quickly locate a product in the ListBox control and click it to view its fields. Before you run the project, however, you must specify that the DataForm1 form is its Startup object. The form behaves as expected most of time, but there are a few quirks to this design. The product’s supplier and category are displayed as numeric values. Sure, this is how we want to store the information to our database, but when we design forms for the end user, we want to be able to show them a supplier name and a category name. We’ve been through this in the previous chapter, so you already know how to replace these two TextBox controls with ComboBox controls that display the equivalent strings. Delete the TextBox control that corresponds to the SupplierID field. Then place a ComboBox control in its place and set the following properties of the control:

Property	Value
DataSource	objDSProducts.Suppliers
DataMember	CompanyName
ValueMember	SupplierID
SelectedValue	objDSProducts - Products.SupplierID

Delete the TextBox control that displays the CategoryID and replace it with a ComboBox control with the following settings:

Property	Value
DataSource	objDSProducts.Categories
DataMember	CategoryName
ValueMember	CategoryID
SelectedValue	objDSProducts - Products.CategoryID

Run the project now, load the DataSet, and edit a few rows. You have a functional interface, and you can edit the rows of a table in the DataSet. My advice is to disallow the editing of the current row in browsing mode by turning on the ReadOnly property of the TextBox controls. Place an Edit button on the form, which will make the controls editable by resetting their ReadOnly property. While the user can edit the current row, disable all navigational controls on the form and force users to end the edit operation by clicking the OK or Cancel button.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATAFORM WIZARD 975

The new interface is much more functional than the original one, produced by the wizard, but it’s not very robust. Let’s say you wish to take a look at the suppliers, or categories, by expanding the appropriate ComboBox control. It sure isn’t your intention to change the current row. Click a category name by mistake and you’ve edited the current row. When you move to the next row, the changes will be committed to the DataSet, and when you click the Update button the changes will be sent to the database. Sure, my suggested approach takes a few more keystrokes, or mouse clicks, but real-world applications must be robust.

Let’s continue our exploration of the application the wizard has generated for us. Try adding a few products. Don’t bother specifying the new product’s ID; it will be assigned one by the database. The Add button will add a new row to the Products table in the local DataSet. The new rows will be submitted to the database along with the edits and deletions (if any) when you click the Update button.

When adding a new product, make sure you click the CheckBox control (field Discontinued) once to check it, and once again to clear it. The default state of this control isn’t cleared, even though you don’t see a check mark. If you don’t touch the CheckBox, the application will crash with an error message to the effect that the Discontinued field can’t be Null. The same will happen if you don’t supply a value to the ProductName field, because this field can’t be Null. As you can see, some of the table’s basic constraints are embedded into the DataSet’s DataTable object, and they’re enforced. This is a good thing, but should they crash the application?

The program crashes when it attempts to move to another row in the table and the current row has errors. We can trap this error with a structured error handler and prevent the program from crashing. Open the SelectedIndexChanged event handler of the ListBox control and insert the code shown in Listing 22.5.

Listing 22.5: Handling Data-Entry Errors

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

Try

Me.BindingContext(objDSProducts, “Products”).Position = _ ListBox1.SelectedIndex

Catch dataException As Exception MsgBox(dataException.Message)

Me.BindingContext(objDSProducts, “Products”).CancelCurrentEdit() Me.BindingContext(objDSProducts, “Products”).Position = _

ListBox1.SelectedIndex End Try

End Sub

Note OK, the problem is the statement we inserted to improve the navigational capabilities of the DataForm, so it’s not really the wizard’s fault. But our custom code and the simplistic navigational tools inserted on the form by the wizard do the same thing—they change the Position property of the BindingContext object—so the original code would have failed in the same manner.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com