Using the Data Form Wizard

Using the Data Form Wizard

I'm including a discussion of the Data Form Wizard for two reasons. First, Visual Studio .NET is full of great features and this gem will be easy to overlook. You can't use its power if you don't know it exists. Second, this wizard generates very clean and robust code. It will be instructive for you to build a program with the Data Form Wizard and then review the code it generates. You'll understand data sets and data connections more fully by doing so. This wizard also uses the OleDB classes instead of the SqlClient classes (which I demonstrated in Chapter 10) because the wizard is more general in its connection targets.

The Data Form Wizard is used to generate different configurations of forms, both Web Forms pages and Windows Forms. The code that it generates is designed to handle a variety of situations, including various combinations of controls on the form and various data sources. The Data Form Wizard is a well-constructed wizard and can also provide some useful insight to solidify your knowledge of how to communicate with various data sources. In fact, one of the purposes of the wizard is to generate code that you can study to learn how to create your own data-bound forms. Of course, you should be aware of where the wizard-generated code might differ from your own code that is optimized for particular form requirements. Let's spend a few minutes reviewing how simple it is to use the Data Form Wizard.

1. Start a new Visual Basic project, choosing the Empty Project template. Name the project DataFormWiz. Click OK.

2. If the Start Page tab is visible, right-click the Start Page tab and then select Hide to dismiss that page. From the main IDE menu, select Project | Add New Item to display the Add New Item dialog box, shown in Figure A-1. Now we'll add a Data Form Wizard to our project.

   Figure A-1

   The Add New Item dialog box.

3. Select Data Form Wizard, and leave the default name DataForm1.vb. Click Open to have the Data Form Wizard added to the empty project. As soon as you click Open, the Data Form Wizard starts. Click Next.

4. We want to provide a name for a new data set, so give it the name dsDataWizard, as shown in Figure A-2. Click Next.

   Figure A-2

   Create a new data set named dsDataWizard.

5. We could create a new connection at this point, but instead just select the Northwind connection we built in Chapter 10, as shown in Figure A-3. Click Next to continue.

   Figure A-3

   Select the Northwind connection we built in Chapter 10.

6. Add the Categories table and the Products table to the Selected Item(s) list, as shown in Figure A-4. Click Next.

   Figure A-4

   Add the Categories table and the Products table.

   We need to name the relationship between the tables. Enter CategoriesProducts in the Name text box. Categories is the parent table using its CategoryID primary key. Products is the child table with its foreign key CategoryID used as the link, as shown in Figure A-5. When this information is added, click the > button to add this relationship to the Relations box on the right. Click Next.

   Figure A-5

   Create this relationship.

7. In the next window, we select the tables and fields we want to display. Leave all the fields except the picture field in the Categories table checked, as shown in Figure A-6. Click Next.

   Figure A-6

   Select the tables you want to display.

8. Let's display all of the records in a grid for now, so leave the default settings as shown in Figure A-7. Click Finish to have the wizard do its magic and generate our working application.

   Figure A-7

   Leave the default display style settings unchanged.

   After a few seconds of disk whirring, the new DataForm will be constructed and displayed, as shown in Figure A-8. Although the result is a pretty utilitarian-looking interface, the more complex internals are also present. You can use the wizard to build your application and then spiff up the user interface yourself.

   Figure A-8

   The new DataForm.

   You won't be surprised at what the wizard has added to our program. We have the dsDataWizard data set, the OleDB connection to Northwind, and the OleDb data adapters for each of the two tables in our example. If you try to run the program now, however, an error will be generated. You must first tell the project that you want the new Data Form Wizard form to be the startup object. Bring up the Solution Explorer, and then right-click the project named DataFormWiz, and select Properties. Select DataForm1 from the Startup Object drop-down list. Click OK to close the dialog box.

Run the Program

Press F5 to run the newly created program. Click the Load button, which then establishes a connection to the database and retrieves the data, as shown in Figure A-9. Scroll through the top Categories table, and select a record. Notice how the bottom Products table changes because of the relationship we built. You can change any of the fields in any of the records. If Update is clicked, all the changes made in the data grids will be propagated back to the Northwind data source. And all without writing a single line of code.

Figure A-9

The Data Form Wizard project in action.

Under the Hood

As I mentioned, one reason I wanted to describe the Data Form Wizard is to simply let you know that it exists. The other reason is to illustrate the code it generates. Build a few data applications, and then print and review the code. Looking over the code created by the wizard is a great way to become familiar with the Visual Basic .NET database access technology. For example, notice that the Click event handler of the Load button calls a routine to load the data set. This event handler is placed in a structured Try…End Try error handling block. The Data Form Wizard is an example of very good code and design. Here's some of the code created for our example:

Private Sub btnLoad_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLoad.Click Try Me.LoadDataSet() Catch eLoad As System.Exception System.Windows.Forms.MessageBox.Show(eLoad.Message) End Try End Sub

Examine the LoadDataSet routine that the Load button calls. All the moving parts that could generate an error are encased in Try blocks. As I mentioned, this practice is the hallmark of good production code. The important lines are also commented. Spend a few minutes examining the code.

Public Sub LoadDataSet() Dim objDataSetTemp As DataFormWiz.dsDataWizard objDataSetTemp = New DataFormWiz.dsDataWizard() Try 'Execute the SelectCommand on the DatasetCommmand ' and fill the dataset Me.FillDataSet(objDataSetTemp) Catch eFillDataSet As System.Exception 'Add exception handling code here Throw eFillDataSet End Try Try 'Merge the records that were just pulled from the data ' store into the main dataset objdsDataWizard.Merge(objDataSetTemp) Catch eLoadMerge As System.Exception 'Add exception handling code here Throw eLoadMerge End Try End Sub

When you examine the code that the wizard inserted to load the data set, you will be surprised at the amount of code required. Several OleDB commands were added to the code for us. Each of the update commands were added for us as well as all the other commands to load and update. If you are not a database expert, the Data Form Wizard can really make your life more productive. And it's a good instructor of how to write complex code to connect to a data store.

The Properties box of the first OleDbDataAdapter, shown in Figure A-10, shows just a few of the commands that were added for us. Of course, each of these commands is expanded to many lines within the program.

Figure A-10

The OleDbDataAdapter properties.

We can see that the wizard uses the OleDB provider. Although it is possible to modify the provider in the wizard-generated code to use the SqlClient provider, this change can be difficult. You must first modify references to any elements in the System.Data.OleDb namespace to System.Data.Sql. Next you must change the way parameters are handled. The SQL provider requires named parameters, which would require changes both in the SQL statements in the adapter's commands and in the commands' Parameters collection. If you want to switch to the SQL provider, you might find it easier to manually add SqlConnection and SqlDataAdapter objects and configure them, allowing Visual Studio .NET to configure their parameters. But unless the database is large and you are generating many round-trips, it's just as easy to leave the default OleDB provider.