| The Data Form wizard will generate a Windows Form bound to a DataTable in a DataSet. While I would never really use one of these forms in any of my projects, the code they generate can help us understand how data binding works in .NET. The Data Form wizard is quite powerful and while I wouldn't use it to generate forms for a serious application, it could be useful if you needed a quick-and-dirty data entry form. We are going to use it for a different purpose. We will create a form and then analyze the resulting code to see how they work. The first step is to create a Windows Forms project. Do this now. Call it ADOBook08-01. To invoke the data form wizard, you add a new form to the project. When the template dialog opens, you can select the Data Form Wizard icon, as in Figure 8.1. Figure 8.1. Selecting the Data Form Wizard.  Leave the form name as is and click Open . The Data Form Wizard welcome screen appears. Click Next to begin. The first screen is the Select DataSet screen as in Figure 8.2. Figure 8.2. The Select DataSet screen.  Tell it to create a DataSet and name it dsMain. When you click Next you will be prompted for a connection. Use our Northwind database connection. The next screen asks you to pick the tables you would like in the DataSet. Move the tables or view from the left pane to the right pane to select them. Use the tables show in Figure 8.3. Figure 8.3. Selecting the tables.  Look familiar? Yes, we will use our familiar Customers, Orders, and Order Details tables. You will see why in a minute. The next screen allows us to set up our relations. We set up the relation in the left side of the form, then click the ">" button to move the relation to the right. You can click on the relation in the list to see its details and modify them if needed (see Figure 8.4). Figure 8.4. The Relations screen.  The next screen allows us to select the fields we want displayed on our form (See Figure 8.5). Figure 8.5. Select fields to display.  The next screen allows you to control how the data will be displayed on the form, in a grid or as separate fields. Choose separate fields as in Figure 8.6. Figure 8.6. Selecting the Display format.  When you click Finish the form will be generated. Notice : -
The wizard used the OLEDB versions of the data controls. This is because it must use a lowest common denominator approach since it must be able to use any database server. -
The form had to be resized to be usable. This is probably a bug. -
It only supports two levels of master detail. I deliberately used three levels to show this. You can make a slight modification to the form to drill down into the details. You should already know how to do this, but we will demonstrate soon. Figure 8.7 shows the finished form at runtime. Figure 8.7. The wizard-generated form.  Remember in the last chapter how we used DataViews to customize the data in the grid? We didn't have to do that. Look at what the Data Form wizard does. It uses a relation as the setting of the DataMember property. That's right, the relation itself is bindable. When we do this, we do not have to use any code at all to repopulate the data grid control. So to complete our form, all we have to do is add another grid control and set the DataMember property to the correct relation object. When setting the property, it is important to use the correct value. Set the DataSource property to the DataSet. Do not set it to a DataTable. Now we will set the DataMember property to the OrdersDetails relation that is nested under the Customers table. Do not use the one under the Orders table (see Figure 8.8). Figure 8.8. Selecting the correct relation object.  Now our form will automatically show three levels of detail as before. We didn't have to write any code to support the feature this time. We can already see how powerful the data binding features are in .NET. Now let's look at the generated code to get an idea of what it's doing. We will be concentrating on the data-binding features of the form. We have already covered filling DataSets and updating the database enough times. Figure 8.9 shows the new form with the three levels of detail. Figure 8.9. The form with three levels of detail.  Examining the Code Next, we will look at the code. We will concentrate on the use of the BindingConText property of the form. VB6 You VB 6 coders will notice that many of the navigation and editing functionality of the RecordSet has been moved into the BindingManager class. Methods to begin and end editing, add new records, and navigate are all now in the BindingManager class.
Before we begin, let me admit that the generated code is not perfect. It does not conform to the coding standards laid out in the Visual Basic documentation and some of the techniques it uses go against recommended techniques in the documentation. For example, it uses the BindingContext to delete and add records to the master table, but this is expressly not recommended in the documentation for the CurrencyManager class. I've always had the idealistic notion that generated code should be a shining example of the right way of doing things. Microsoft put all this energy into the code generator, why not make it generate code that at least conforms to the documentation? But my idealism has gotten me in trouble before, so why should this time be any different? But I digress. Anyway, as a learning tool it can still teach us some things about data binding in the .NET Framework. Row Navigation First we will examine how the form does row navigation. The navigation buttons on the form are reminiscent of the old ADO data control, but they are implemented entirely in the code of the form. As with the old control, we have Last, Previous, Next, and First buttons , along with a label area that shows the current and total number of records. The code to implement these functions is quite simple. When you are first learning ADO .NET it often seems difficult to find what were simple functions in ADO. The old RecordSet Move methods were like this with me. I was mystified until I discovered the BindingContext property. Let's look at the code for the navigation buttons. Private Sub btnNavFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavFirst.Click Me.BindingContext(objdsMain, "Customers").Position = 0 Me.objdsMain_PositionChanged() End Sub Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click Me.BindingContext(objdsMain, "Customers").Position = (Me.objdsMain.Tables("Customers").Rows.Count - 1) Me.objdsMain_PositionChanged() End Sub Private Sub btnNavPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavPrev.Click Me.BindingContext(objdsMain, "Customers").Position = (Me.BindingContext(objdsMain, "Customers").Position - 1) Me.objdsMain_PositionChanged() End Sub Private Sub btnNavNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavNext.Click Me.BindingContext(objdsMain, "Customers").Position = (Me.BindingContext(objdsMain, "Customers").Position + 1) Me.objdsMain_PositionChanged() End Sub There is another procedure we need to see, the objdsMain _PositionChanged procedure. This updates the display. Private Sub objdsMain_PositionChanged() Me.lblNavLocation.Text = (((Me.BindingContext(objdsMain, "Customers").Position + 1).ToString + " of ") _ + Me.BindingContext(objdsMain, "Customers").Count.ToString) End Sub Here is one of those weird things: The procedure looks like an event procedure, but it isn't. There is no event of the DataSet or the DataTable that fires when the current row is changed because these classes do not have a concept of a current row. It looks almost as if Microsoft took the old ADO data form generator and made some minor changes to make it generate ADO .NET code. Ironically there is an event that can be used for this purpose: Private Sub dtCustomers_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtCustomers.PositionChanged Me.lblNavLocation.Text = (((Me.BindingContext(objdsMain, "Customers").Position + 1).ToString + " of ") _ + Me.BindingContext(objdsMain, "Customers").Count.ToString) End Sub To use this event, you must declare a variable, dtCustomers, as a CurrencyManager and assign it to the BindingContext of the Customers table in the DataSet. Make sure you use the WithEvents keyword. Private WithEvents dtCustomers As CurrencyManager Finally, you have to make the assignment. We put the assignment in the constructor so it is executed when the instance of the form class is created. Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call dtCustomers = Me.BindingContext(objdsMain, "Customers") End Sub To create the event procedure automatically, open the code window and pull down the objects list and select dtCustomers, then open the events list and select PositionChanged. You can then copy the code from the other PositionChanged procedure into the correct one. Now delete all the references to the old procedure, as they are no longer needed, and delete the old procedure. You must put a call to the new procedure into the click event for the Load button. This is because it does not fire the PositionChanged event when the data is first loaded. 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 dtCustomers_PositionChanged(dtCustomers, New System.EventArgs()) End Sub This initializes the display with the 1 of 91 when the DataSet is first loaded. It's interesting that the mistakes in the Data Form wizard allowed us to learn something about how the framework functions. Now when we run the program, the navigation label is automatically updated. This was one of the things I found difficult when learning ADO .NET. Methods that were all in one place before are now spread all through the class library. It takes some getting used to, but once you get the hang of it, it really is just as easy to use as ADO was, but with more functionality than ever. I like ADO .NET much better that the old ADO now. I would never want to go back. The navigation buttons themselves do nothing more than increment or decrement the position property of the CurrencyManager. Here is another strange thing. The code that was generated works, but there is an easier way to accomplish the same thing. Private Sub btnNavPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavPrev.Click 'Me.BindingContext(objdsMain, "Customers").Position = (Me.BindingContext(objdsMain, "Customers").Position - 1) Me.BindingContext(objdsMain, "Customers").Position -= 1 End Sub Private Sub btnNavNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavNext.Click 'Me.BindingContext(objdsMain, "Customers").Position = (Me.BindingContext(objdsMain, "Customers").Position + 1) Me.BindingContext(objdsMain, "Customers").Position += 1 End Sub The commented out code is the generated code. At first glance it seems fine. In older versions of VB this would have to be the way it was done. But we now have unary add and subtract operators in VB. It's like the developers didn't know about them or something. My version of the line of code does the same thing, but more efficiently . This is a great example of code refactoring. My code is much more efficient because it does not have to access the object's properties twice, which can be relatively expensive because it is late-bound . No error is thrown when you attempt to set the position past the end of the table or before the beginning, so we don't have to worry about that. There are no end-of-file (EOF) or beginning-of-file (BOF) properties, which I found a little annoying at first, but again I was thinking like an ADO programmer. There is no need for these functions because you can just test the position property for greater than the count property minus one or less than zero. Also if you use an enumerator (For Each Next construct) to loop though the rows in a DataTable, you will automatically exit the loop when all rows have been processed . There is no longer any need for the Do Until x.EOF Loop construct. The Update Process Now let's look at the code to update the database with any changes. The Update button's click event procedure calls UpdateDataSet, which, ironically, updates the database, not the DataSet. The DataSet is updated automatically when the use changes the values on the screen. Let's look at the generated code. Public Sub UpdateDataSet() 'Create a new DataSet to hold the changes that have been made to the main DataSet. Dim objDataSetChanges As ADOBook08_01.dsMain = New ADOBook08_01.dsMain() 'Stop any current edits. Me.BindingContext(objdsMain, "Customers").EndCurrentEdit() Me.BindingContext(objdsMain, "Orders").EndCurrentEdit() 'Get the changes that have been made to the main DataSet. objDataSetChanges = CType(objdsMain.GetChanges, ADOBook08_01.dsMain) '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 the datasource by 'calling the update method and passing the DataSet and any parameters. Me.UpdateDataSource(objDataSetChanges) objdsMain.Merge(objDataSetChanges) objdsMain.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 that may have been 'pushed into the row object's error. End If End Sub The first things to notice are the two lines that end in EndCurrentEdit. These two lines of code formally commit any changes that were made on the screen to the DataSet. Note that there is no EndCurrentEdit for the Order Details table. This is strange because other places in the program do refer to the Order Details table. I know the program is supposed to support only one level of detail but it should be consistent. It does fill the table in the load procedure. Let's add one more EndCurrentEdit to the procedure so that any changes we make to the details grid get updated as well. It then does a weird thing to find out if there are any changes to the DataSet. It uses the GetChanges method to pull any changes into another DataSet. Then it checks to see if the new DataSet is nothing. Well, yes, that works, but didn't the developers know about the HasChanges property? This property returns true if there are any pending changes to the DataSet since the last call to AcceptChanges was made. Also, I don't understand why it needs to pull the changes into another DataSet, use this DataSet to update the database, then remerge the changed data back into the original DataSet. This seems very round about when you can just call the Update method directly on the main DataSet. The only thing I can think of is that the developers were thinking the database update code would be located in a remote procedure or a web service, then by capturing only the changes you might minimize network traffic. In this case doing all this logic makes no sense when three lines of code could replace it all. Let's get rid of the old code and use the one line of code technique to simplify things. There is no need to call AcceptChanges because it is called automatically by the DataAdapter. We can then get rid of the UpdateDataSource procedure altogether. Here is the refactored code. Isn't it easier to understand now? Public Sub UpdateDataSet() 'Stop any current edits. Me.BindingContext(objdsMain, "Customers").EndCurrentEdit() Me.BindingContext(objdsMain, "Orders").EndCurrentEdit() Me.BindingContext(objdsMain, "Order Details").EndCurrentEdit() If objdsMain.HasChanges Then Try 'There are changes that need to be made, so attempt to update the datasource by 'calling the update method and passing the DataSet and any parameters. OleDbDataAdapter1.Update(objdsMain, "Customers") OleDbDataAdapter2.Update(objdsMain, "Orders") OleDbDataAdapter3.Update(objdsMain, "Order Details") 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 that may have been 'pushed into the row object's error. End If End Sub And, yes, it does work. You can compile the project and try it. Loading the Data The load procedure is fraught with similar strangeness. Here is the unmodified procedure: Public Sub LoadDataSet() 'Create a new DataSet to hold the records returned from the call to FillDataSet. 'A temporary DataSet is used because filling the existing DataSet would 'require the DataBindings to be rebound. Dim objDataSetTemp As ADOBook08_01.dsMain objDataSetTemp = New ADOBook08_01.dsMain() 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. objdsMain.Clear() 'Merge the records into the main DataSet. objdsMain.Merge(objDataSetTemp) Catch eLoadMerge As System.Exception 'Add your error handling code here. Throw eLoadMerge End Try End Sub Right at the top of the procedure the developer makes the comment that the intermediate DataSet is needed, because if you fill the main DataSet directly you will have to rebind all of the controls. I have no idea where this is coming from except that in VB 6 this was sometimes necessary with the databound combo box and the OLEDB DataGrid control. It was due to a bug. This reinforces my hypothesis that this is really a lame conversion of a VB 6 program. The following procedure works just as well: Public Sub LoadDataSet() Try objdsMain.Clear() Me.FillDataSet(objdsMain) Catch eFillDataSet As System.Exception 'Add your error handling code here. Throw eFillDataSet End Try End Sub The Add and Delete Methods The Add and Delete methods are pretty simple. They only have to deal with the master Customers table because the cascade and update rules in the relation object in the DataSet will handle cascading deletes and updates to the detail levels. Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click If (Me.BindingContext(objdsMain, "Customers").Count > 0) Then Me.BindingContext(objdsMain, "Customers").RemoveAt(Me.BindingContext(objdsMain, "Customers").Position) End If End Sub Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Try 'Clear out the current edits Me.BindingContext(objdsMain, "Customers").EndCurrentEdit() Me.BindingContext(objdsMain, "Customers").AddNew() Catch eEndEdit As System.Exception System.Windows.Forms.MessageBox.Show(eEndEdit.Message) End Try End Sub Delete simply removes the current row from the Customers table. Add first ends and, pending edits, then is uses AddNew to append a new blank row to the Customers table. There is one potentially quirky thing about this code, and possibly ADO .NET as well. The Order Number column in the orders database is an autoincrement field. This means that it automatically adds 1 to the last order number when appending new rows. Suppose there are multiple users adding rows to the table? How does it handle the incrementing of the numbers by multiple users if the DataSet is disconnected while the users are adding rows? There will be a lot of duplicate key errors. The best practice in this situation may be to not include the AutoIncrement fields in the DataSet. Then the numbers would be generated when the DataSet is updated to the database instead of at runtime. You could also use a different DataSet to display the rows from and another one to update with. Or you could update the rows manually using a command object and SQL. These are interesting questions that the Data Form wizard does not address very well. This reinforces my statement that I would not use the data for wizard to generate code for any serious application. There is another issue with adding and deleting master-detail structures. The order in which the updates are performed is different for each operation. In a delete operation, we want to delete the lowest level of detail first, then the next lowest, and so on. The reasoning being that we will violate relational integrity rules if we try to delete the top level first. For example, if we tried to delete the row from the Customers table, we would get an integrity violation because the detail rows require a row in the master table. In an add operation, the opposite is true; we want to add the highest level first, in this case the Customers row. This is for the same reason as previously stated: If we try to add a lower level first, we will get an integrity violation. One would think that a way to avoid this issue is to use DRI on the database itself. This makes some sense, but then look what would happen. We are using cascading rules in our DataSet. When we delete the Customers row in the DataSet, all the orders and order details are also deleted. Then when we go to update the database, we update only the Customers table, thinking that the other tables will get cleaned up because of the DRI rules in the database. In reality, you get an integrity violation. When the Customers row in the database is deleted, it also deletes all the child rows, but then along comes the program and tries to delete them again, failing, of course. Now the rows are still marked as deleted in the DataSet but are actually deleted in the database. This is a strange situation. One way around this might be to delete the Customers row and not touch the other rows. Let the database cascade the delete down to the detail tables. Then simply call the AcceptChanges method on the orders and order details tables in the DataSet. This is not a great way to do it because you can never be certain that the database and DataSet are in synch. Another idea would be to pull only the added rows into another DataSet using the GetChanges(DataRowState) method. This seems to be the easiest to implement and does not depend on whether the database has DRI rules defined. First pull all the deleted rows into a temporary DataSet and update them, then pull all the added or modified rows and update them. Here is the modified code: Public Sub UpdateDataSet() Dim ds As ADOBook08_01.dsMain 'Stop any current edits. Me.BindingContext(objdsMain, "Customers").EndCurrentEdit() Me.BindingContext(objdsMain, "Orders").EndCurrentEdit() Me.BindingContext(objdsMain, "Order Details").EndCurrentEdit() If objdsMain.HasChanges Then Try 'There are changes that need to be made, so attempt to update the datasource by 'calling the update method and passing the DataSet and any parameters. ds = objdsMain.GetChanges(DataRowState.Deleted) If Not ds Is Nothing Then OleDbDataAdapter3.Update(ds, "Order Details") OleDbDataAdapter2.Update(ds, "Orders") OleDbDataAdapter1.Update(ds, "Customers") End If ds = objdsMain.GetChanges(DataRowState.Added Or DataRowState.Modified) If Not ds Is Nothing Then OleDbDataAdapter1.Update(ds, "Customers") OleDbDataAdapter2.Update(ds, "Orders") OleDbDataAdapter3.Update(ds, "Order Details") End If objdsMain.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 that may have been 'pushed into the row object's error. End If End Sub With this change we can handle either condition. Notice how with the add and modify section we can combine the parameters using an OR (addition) operation. Using the Data Form wizard, we have seen how to bind controls to a DataSet and DataTable. Now let's look into other objects we can bind controls to. | 
