The Data Form Wizard is one most useful tools for developing database applications. You can use the Data Form Wizard to develop your database application with viewing, updating, and deleting capabilities. This is probably the fastest way to develop database applications in .NET (unless you're an extremely fast typist!).
In the following sections, you'll use the Data Form Wizard to write a fully functional database application including features such as inserting, updating, and deleting data without writing a single line of code. In this simple example, you'll use the familiar Northwind database, and you'll use both the Customers and Orders tables to build a data relationship between table data.
First, you'll create a Windows application. Second, you'll add a Data Form Wizard to it and then call the Data Form Wizard from the main application. Like many parts of this book, this topic is in the form of tutorial. Just follow the simple steps, and in a few minutes you'll be able to run the application.
Create a new Windows project by selecting File New Project Visual Basic Projects Windows Application and typing MyDataFormWizardSamp as your application name.
Now add a Data Form Wizard by selecting Project Add New Item Data Form Wizard from the available templates. You can type DataForm1.vb as the name of your DataForm class in the Name field of the dialog box (see Figure 2-10). (If you're using C# language, the filename would be DataForm1.cs.)
Figure 2-10: Adding a Data Form Wizard
Now click Open, which calls the Data Form Wizard.
The first page of the wizard is a welcome page telling you what the wizard is about to do.
On the second page of the wizard, you can choose a DataSet name that will later be used to access the data. You can either create a new DataSet name or select an existing one. If there's no DataSet available in the project, the Use the Following Dataset Named option is disabled. For this test, type MyDS as the DataSet name in the Create a New Dataset Named option and move to the next page by clicking the Next button.
The next page of the wizard asks you to provide a connection. The combo box displays your available connections. If you didn't create a connection, use the New Connection button, which launches the Server Explorer discussed earlier in this chapter. You'll select the usual database, Northwind. If you followed the Server Explorer discussion in the beginning of this chapter, you should have a Northwind connection in the list. If you don't have any connection listed in this list, create a new connection using the Northwind SQL Server database.
The next page of the wizard lets you pick the tables and views you want to connect to the DataSet. As you can see in Figure 2-11, we selected the Customers and Orders tables in the Available Items list on this page and use the > button to add these tables to the Selected Items list.
Figure 2-11: Choosing tables and views
Now you're ready to create a relationship between these two tables.
The next page of the wizard lets you define a relationship between the Customers and Orders tables. It's useful to provide a relationship between tables when you have a master-detail relationship in the database. In other words, a customer may have many orders associated with it, so there's a relationship through the CustomerID in the Orders table joined to information about the customer in the Customers table. Now, say you want to see all the orders of a customer based on the CustomerID. If you do this manually, you need to write code to select data from the Orders table to correspond to a CustomerID and then fill data to the form. If you use the Data Form Wizard instead, it does everything for you. Cool, huh?
This is the same step you're going to see on the Create a Relationship between Tables page of the wizard. You're going to create a relationship between the Customers and Orders tables based on the CustomerID. Name the relationship between the Customers and Orders table CustOrderRelation. You also need to pick the associated primary key and foreign key that links the parent to the child table. Once you've chosen the joining key (CustomerID), you have to press the > button to tell the wizard that you want to add it.
When you run the final program, you'll see how you can filter all orders for a customer based on the CustomerID. As you can see from Figure 2-12, you need to pick one table as parent and another table as a child based on the relationship between them. In this example, the Customers table is the parent table, and the Orders table is the child table.
Figure 2-12: Selecting Customers as the parent and Orders as the child table to create the CustOrderRelation relationship
The next page of the wizard lets you select which tables and columns you want to show on the form. For this example, select all the columns from both of the tables (this is the default selection). As you can see in Figure 2-13, the Customers table is the master, and the Orders table is the detail table. On this page, you can select whatever column you want to display on the form.
Figure 2-13: Choosing columns to display on the Data Form Wizard
This page is an important part of creating your form. Actually, the Data Form Wizard adds a Form with some controls on it and writes code to fill, update, delete, and navigate data. There are two ways to view the data, and you choose your option on this page. These two options are as follows:
All Records in a Grid
Single Record in Individual Controls
Figure 2-14 displays these options.
Figure 2-14: Choosing between a grid and individual controls on the Data Form Wizard
The output of All Records in a Grid looks like Figure 2-15. After that you can resize or move the controls on the form.
Figure 2-15: Grid DataForm output
The second option, Single Record in Individual Controls, shows data in text boxes and provides you with navigation controls. As you can see from Figure 2-16, the Single Record in Individual Controls option activates Add, Delete, Cancel, and Navigation Controls check boxes. You can uncheck these check boxes if you don't want to add those features to your project.
Figure 2-16: The Single Record in Individual Controls option
The form generated by this option looks like Figure 2-17. As you can see, each column of the table has a field on the form.
Figure 2-17: Data Form Wizard-generated form for the Single Record in Individual Controls option
After selecting the data display style, click the Finish button. The Data Form Wizard adds the Windows form DataForm1 and the class DataForm1.vb corresponding to it.
Now you need to change one more thing. You need to call the DataForm1 form. Generally, you call a DataForm from a menu or a button click event handler. Say you've added two DataForm objects, DataForm1 and DataForm2, using both the options. The code in Listing 2-4 calls DataForm1.
Listing 2-4: Calling DataForm1 from a Button Click
Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim dfw1 As DataForm1 = New DataForm1() dfw1.Show() End Sub
Listing 2-5 calls DataForm2 from a button click handler.
Listing 2-5: Calling DataForm2 from a Button Click
Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim dfw2 As DataForm2 = New DataForm2() dfw2.Show() End Sub
If you have modified the name of your Data Form Wizard-generated form, you need to call that form instead of DataForm1 and DataForm2.
When you run any of the modes (DataGrid mode or individual column mode), you'll see Load and Update buttons. The Load and Update buttons load and update the data, respectively, and Cancel All cancels all the operations. The neat thing is if you move into the top grid, corresponding information changes in the bottom grid.
After compiling and running your application, you'll notice that without writing a single line of code, you just created a fully functional database application.
The Load button on the individual control form loads the data, and the Add, Update, and Delete buttons on the form inserts, updates, and deletes records, respectively.
You just saw how you can develop fully functional database applications with the help of the Data Form Wizard. Now let's see what the wizard does for you in the actual code. (The inherent beauty of the VS .NET IDE is that it magically hides all the messy code for you.) The wizard adds two items to your project: MyDS.xsd and DataForm1.vb.
MyDS.xsd is an XML schema for the DataSet you've added to the project. (It's similar to the one discussed in the "Understanding Typed DataSets in VS .NET" section of this chapter.)
The second item added by wizard is the DataForm1 class, a class derived from System.Windows.Forms.Form. The DataForm1 class defines the class's entire functionality:
Public Class DataForm1 Inherits System.Windows.Forms.Form
The btnUpdate_Click and btnLoad_Click methods are the event handlers for the Update and Load buttons, respectively. As you can see from Listing 2-6, the btnLoad_Click method calls LoadDataSet, and the btnUpdate_Click method calls the UpdateDataSet method.
Listing 2-6: The Load and Update Button Click Event Handlers
Private Sub btnUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click Try 'Attempt to update the datasource. Me.UpdateDataSet() Catch eUpdate As System.Exception 'Add your error handling code here. 'Display error message, if any. System.Windows.Forms.MessageBox.Show(eUpdate.Message) End Try End Sub Private Sub btnLoad_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLoad.Click Try 'Attempt to load the dataset. Me.LoadDataSet() Catch eLoad As System.Exception 'Add your error handling code here. 'Display error message, if any. System.Windows.Forms.MessageBox.Show(eLoad.Message) End Try
The LoadDataSet method loads the data from the data source into the controls by calling FillDataSet (see Listing 2-7). As you can see from the code, it assigns the MyDS DataSet generated by the Data Form Wizard as a temporary DataSet and fills it using the FillDataSet method.
Listing 2-7: LoadDataSet Method Generated by the Data Form Wizard
Public Sub LoadDataSet() 'Create a new dataset to hold the records returned 'A temporary dataset is used because filling the existing dataset would 'require the databindings to be rebound. Dim objDataSetTemp As MyDataFormWizardSamp.MyDS objDataSetTemp = New MyDataFormWizardSamp.MyDS() Try 'Attempt to fill the temporary dataset. Me.FillDataSet(objDataSetTemp) Catch eFillDataSet As System.Exception 'Add your error handling code here. Throw eFillDataSet End Try Try 'Empty the old records from the dataset. objMyDS.Clear() 'Merge the records into the main dataset. objMyDS.Merge(objDataSetTemp) Catch eLoadMerge As System.Exception 'Add your error handling code here. Throw eLoadMerge End Try End Sub
The FillDataSet fills the DataSet from the DataAdapter by calling the Fill method on each DataAdapter. Note that with the Data Form Wizard, a DataAdapter is created for each table—one DataAdapter for the Customers table and one DataAdapter for the Orders table. Both DataAdapters fill the same DataSet. Listing 2-8 shows the FillDataSet method.
Listing 2-8: The FillDataSet Method Generated by the Data Form Wizard
Public Sub FillDataSet(ByVal dataSet As MyDataFormWizardSamp.MyDS) 'Turn off constraint checking before the dataset is filled. 'This allows the adapters to fill the dataset without concern 'for dependencies between the tables. dataSet.EnforceConstraints = False Try 'Open the connection. Me.OleDbConnection1.Open() 'Attempt to fill the dataset through the OleDbDataAdapter1. Me.OleDbDataAdapter1.Fill(dataSet) Me.OleDbDataAdapter2.Fill(dataSet) Catch fillException As System.Exception 'Add your error handling code here. Throw fillException Finally 'Turn constraint checking back on. dataSet.EnforceConstraints = True 'Close the connection whether or not the exception was thrown. Me.OleDbConnection1.Close() End Try End Sub
One thing you may notice is that the Data Form Wizard uses the OleDb provider to write the Data Form Wizard code.
The UpdateDataSource method updates the data source from the DataSet. This method is called by the UpdateDataSet method and utilizes the Update command of the DataAdapters. Listing 2-9 shows the UpdateDataSource method.
Listing 2-9: The UpdateDataSource and UpdateDataSet Methods Generated by the Data Form Wizard
Public Sub UpdateDataSet() 'Create a new dataset to hold the changes that have been made to the main dataset. Dim objDataSetChanges As MyDataFormWizardSamp.MyDS = _ New MyDataFormWizardSamp.MyDS() 'Stop any current edits. Me.BindingContext(objMyDS, "Customers").EndCurrentEdit() Me.BindingContext(objMyDS, "Orders").EndCurrentEdit() 'Get the changes that have been made to the main dataset. objDataSetChanges = CType(objMyDS.GetChanges, MyDataFormWizardSamp.MyDS) 'Check to see if any changes have been made. If (Not (objDataSetChanges) Is Nothing) Then Try 'There are changes that need to be made, so attempt to update" 'calling the update method and passing the dataset and any parameters. Me.UpdateDataSource(objDataSetChanges) objMyDS.Merge(objDataSetChanges) objMyDS.AcceptChanges() Catch eUpdate As System.Exception 'Add your error handling code here. Throw eUpdate End Try 'Add your code to check the returned dataset for any errors" 'pushed into the row object's error. End If End Sub Public Sub UpdateDataSource(ByVal ChangedRows As MyDataFormWizardSamp.MyDS) Try 'The data source only needs to be updated if there are changes pending. If (Not (ChangedRows) Is Nothing) Then 'Open the connection. Me.OleDbConnection1.Open() 'Attempt to update the data source. OleDbDataAdapter1.Update(ChangedRows) OleDbDataAdapter2.Update(ChangedRows) End If Catch updateException As System.Exception 'Add your error handling code here. Throw updateException Finally 'Close the connection whether or not the exception was thrown. Me.OleDbConnection1.Close() End Try End Sub
The btnCancelAll_Click method is the Cancel All button click handler, which calls the DataSet's RejectChanges method (see Listing 2-10).
Listing 2-10: Cancel All Button Click Event Handler
Private Sub btnCancelAll_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnCancelAll.Click Me.objMyDS.RejectChanges() End Sub