Now it's time to create a simple database form. There are two ways to accomplish this: use the data form wizard or build it ourselves . Using the wizard is quick but we wouldn't learn anything. As you will learn, when you begin the build, a Windows form is considerably more complex than the console application.
VB 6 In VB 6 the easiest way to create a data form without using wizards was by using the ADO Data Control. You could drop this on a form and by setting a few properties you could gain access to your database. Then you could drop some data-bound controls on the form, set the DataSource and DataField properties and you were done. You didn't have to write any code. The Data Control handled all the details for you. Not so with VB .NET. Since ADO .NET is a disconnected protocol, there is a lot more to creating a data form. There is no Data Control. The database will not be updated automatically, so we will have to write code for this. We will have to write code to navigate through the data as well.
The purpose of this exercise is to demonstrate the basics of creating a data-bound Windows form. We will not explain every aspect of the code here. We want only to get started. We will go into much more detail in later chapters so hang in there.
Begin by closing any open solutions. Next create a project. When the New Project dialog opens, select Windows Application under the VB .NET templates. Name the Project ADOBook2. The IDE will create a project and it will also create a Windows form called Form1.vb. The form designer should be displayed. For you VB 6 coders this should look similar to a new VB 6 application. Switch to Code view by clicking the Code button in the Project Explorer. You should see something like the image in Figure 1.2.
Figure 1.2. Empty Windows form code.
You can see that the form inherits from the Windows.Forms.Form base class. There is also a collapsed region. Regions are new in VB .NET. Basically they enable the programmer to hide code. This is only cosmeticany user can open the region and view or edit what's inside. The main purpose is for code organization and the ability to tidy up the display. Expand the region by clicking the plus box next to it. You will see a bunch of code that was added by the forms designer. Do not modify this code. The form designer will maintain this area of code for you. We will explain this code in detail in a later chapter. Go ahead and collapse the region again. Go back to the form designer.
Now we have to begin adding the data components to the form. There is a real easy way to do this. Open Server Explorer. This should be rolled up on the left side of the designer window and should appear as a vertical tab. If you hover the mouse pointer over the tab, it should unroll. (If you have turned off animation or have messed with the IDE since you installed it, the Server Explorer may not be readily seen. I will leave it up to you to find it. Check the View menu.)
Open the subtree for one of the listed servers with the SQL Server on it that you are using. Expand the SQL Servers node. Under this node find the Northwind database. Expand it. Expand the Tables folder. Find the Customers table. (See Figure 1.3.) Now drag the Customers table onto the form and drop it anywhere . Two components appear in the component area under the form. There is an SqlConnection1 object and an SqlDataAdapter1 object.
Figure 1.3. The Server Explorer.
VB 6 In VB 6 nonvisual controls appeared on the form at design time. They were actually visual controls that had no visual interface, so they were invisible at runtime. With VB .NET there is a new type of class called a component class. These are part of the Component model and are not visual. They can be dropped on any form or component that supports the IComponentModel interface. This enables us to visually create components that contain other components. My advice is to create all of your components using the Component model. This makes creating and using them much easier and saves a lot of coding effort.
Right-click on SqlDataAdapter1. Select Generate DataSet from the context menu. When the dialog opens, check Add to designer when finished. Accept the defaults and click Finished. A new DataSet will appear in the designer. This is a typed DataSet. What actually happened is that the IDE generated a whole new class, inherited from the DataSet class that was customized using the SQL statement in the DataAdapter. The DataSet you created is a type of the class just generated. In this way, it is easy to create more instances of the same DataSet object by typing the DataSet. It took me a while to figure all this out so if you are lost, stay with it. We will clear it all up in later chapters. For now just accept it at face value.
Creating the Visual Interface
Let's switch tracks and start creating the visual design of the form. Start by rolling out the toolbox by hovering your mouse pointer over it. Select the TextBox control. Drop it on your form. Next move and resize it so it is positioned in a convenient place. If you've never done any visual design before, this is a simple matter of grabbing the control's handles (the tiny black boxes that appear when you select the control) and dragging them to a new size . Move the control by clicking on the grab border and dragging the control to where you want it. Next do the same thing with a Label control. Position the label next to the text box. Change the text property of the label control to Customer ID. Change the name of the text box to txtCustomerID. To set the data binding for the text box, click on the text box to select it. Select the property labeled (DataBindings). Click the plus box to expand the property. Your Properties window should look like that shown in Figure 1.4.
Figure 1.4. The Properties window.
Select the property labeled Text. This is not the same as the regular text property. This is telling VB to bind the Text property to the selected field in the DataSet. Drop down the list of selections for the Text property. You will see the DataSet we just created listed. Expand the DataSet and you will see the Customers DataTable. Expand that and findand selectthe column CustomerID. You have just bound the control to the CustomerID field in DataSet1. Now scroll down the Properties window until you see the regular text property. Notice there is a database symbol next to the property name. This indicates that this property has been bound. Repeat this process for the following database fields: CompanyName, ContactName, Address, City, Region, PostalCode, and Country. When you are done your form should look like Figure 1.5.
Figure 1.5. The basic user interface design.
VB 6 In VB 6 you could only bind what the control author designated as bindable. In addition, you had to set the DataSource property to either an ADO Data Control or a Recordset object in code. You could also set the property to a data environment object. You then set the DataField property to the column you wanted to bind to. The DataField property could only bind to one property, usually text or caption depending on the control. In VB .NET, the property that displays text to the Text property has been standardized. There are no more Caption properties. This is in line with the Windows API, making changing a Label to a TextBox real easy. All you have to do is change its declaration and initializer. No more global search and replace Text for Caption. VB .NET also allows you to bind to any property, as long as the data types are compatible. If you open Advanced in the (DataBindings) property, you will see what I mean in Figure 1.6.
Figure 1.6. The advanced data binding dialog.
Getting Data from the Database
Now compile and run the project. You should get a form with blank controls. What happened to the data binding? Why doesn't it show any data? Remember we said that ADO .NET is disconnected. This means that there is no way for the DataSet to know where you want to pull the data from unless you tell it programmatically. I personally think that this is a flaw in ADO .NET. I agree with the concept of disconnected data, but I think there should be a property you can set either on the DataSet, to tell it which DataAdapter to link to, or on the DataAdapter to tell it which DataSet to manage. You could set this at design time, or leave it blank and do it programmatically. At any rate, right now the only way to do it is programmatically. We could have it populate the DataSet when the form starts, but I'd rather use a command button for now. This will also enable us to refresh the data later on. Drop a command button on the form. Name it btnLoad. Change the Text property of the button to Get Data. Double-click on the button and the code window opens with the button's Click event precreated. Enter the following code:
Private Sub LoadData() Dim dsTemp As New DataSet1() Try SqlDataAdapter1.Fill(dsTemp) DataSet11.Clear() DataSet11.Merge(dsTemp) Catch errobj As Exception MsgBox(errobj.Message) End Try End Sub Private Sub btnLoad_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLoad.Click LoadData() End Sub
I prefer to make my procedures that actually do the work into separate procedures instead of putting the code directly into the event procedure. This enables me to reuse the code if I need to perform the same function from somewhere other than the event. Why do I go through the trouble of creating a temporary DataSet to fill? It has to do with a peculiar effect of data binding. If I filled the DataSet directly we would have to re-bind all of the controls programmatically. By merging the DataSets it doesn't require the re-binding. This is what the documentation says. I tried it the other way and it seems to work fine. I would still go with the recommended practice.
Compile and run the project. When the form opens, click the Get Data button. The controls should fill with data. Your form should look like Figure 1.7.
Figure 1.7. The form with the button on it.
Navigating the Records
This is great but how do we navigate the records? We'll have to create navigation buttons. We'll use the typical First, Previous, Next, and Last buttons . When we are done what we will have is a group of controls that resembles the old Data Control from previous versions of VB. It will not be a true data control; we will create one of these in Chapter 11.
Begin by dropping a panel control on the form and sizing it so it is long horizontally and short vertically. The panel control is new to VB .NET. We will use the panel as a container for the other controls we will use.
VB 6 In previous versions of VB, if you wanted to group controls you had to use a Frame control and turn off the border, or use a Picture Box, which comes with a lot of overhead. Microsoft addressed this issue with the panel control. It is a simple control to be used for grouping other controls. It has low overhead and includes properties for positioning and arranging the controls it contains.
Next, add four command buttons to the control, one each for the four functions. Name them, in order, btnFirst, btnPrev, btnNext, and btnLast. Be sure to name them as listed. To make the buttons align properly we will use the Dock property. Select btnFirst. Scroll down the Properties window until you find the Dock property. Click the down arrow. What appears is a graphical representation of the docking enumeration. Select left by clicking the button that is all the way on the left. Figure 1.8 should help.
Figure 1.8. The dock graphical representation.
Repeat the process with btnPrev. Dock btnNext and btnLast on the right. Add a label control to the panel. Use the fill setting for its docking property. This is the center button in the drop down. This causes the label control to fill up the remaining area of the panel not used by the four buttons.
For the Text property of the buttons, we will use greater-than (>) and less-than (<) signs to represent the four functions. Change the Text property of btnFirst to <<, btnPrev to <, btnNext to >, and btnLast to >>. Your panel should look like Figure 1.9.
Figure 1.9. The data navigator.
Open the code window and enter the following procedures:
Private Sub btnNext_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnNext.Click Me.BindingContext(DataSet11, "Customers").Position += 1 UpdatePos() End Sub Private Sub btnLast_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLast.Click Me.BindingContext(DataSet11, "Customers").Position = Me.BindingContext(DataSet11, "Customers").Count - 1 UpdatePos() End Sub Private Sub btnFirst_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnFirst.Click Me.BindingContext(DataSet11, "Customers").Position = 0 UpdatePos() End Sub Private Sub btnPrev_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnPrev.Click Me.BindingContext(DataSet11, "Customers").Position -= 1 UpdatePos() End Sub Private Sub UpdatePos() lblRecs.Text = "Record " & Me.BindingContext(DataSet11, _ "Customers").Position + 1 & " of " & _ Me.BindingContext(DataSet11, "Customers").Count End Sub
There are two items worth noting in these procedures:
The BindingContext property connects the DataSet's DataTable object to the controls on the form. We will investigate the BindingContext object in detail in a later chapter. The basic function of the BindingContext is to keep track of the current row position and to manage the editing of records through the form. Familiarity with the BindingContext object is essential to working with data-bound controls on a form. UpdatePos updates the caption on the Navigation control. It will have additional functionality later on.
VB .NET now supports the +=, -=, *=, and /= operators, a la C/C++. Hallelujah! (The increment and decrement unary operators would be nice too!)
Compile and run the project. Click the Get Data button. The form should fill with data. Click the navigation buttons. The controls should change with the rows. The form should look like the one shown in Figure 1.10.
Figure 1.10. The form with the navigator on it.
Adding and Deleting Rows
Next we have to add functionality to add and delete records. We also must create a way to update the data source with the changes. First, let's tidy up. Dock the panel controls with the navigation buttons to the bottom of the form. Next, drop four more buttons onto the form. Name them btnAdd, btnDelete, btnSave, and btnReject. Change the text captions to look like the form in Figure 1.10. Drop a picture box on the form. Change the image property to the save.bmp bitmap in the Graphics folder in the Visual Studio folders under Program Files. Size the picture box to the size of the bitmap. Name this control picDirty. We will use this as an indicator that changes have been made to the data. The finished form designer should look like Figure 1.11.
Figure 1.11. The final design of the form.
Before we can save our changes to the database, we have to be able to make changes. To edit a record, you can just type the new data over the existing. How do we add and delete records? Let's put that functionality in first. Add the following event procedures for the Add New and Delete buttons:
Private Sub btnAdd_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnAdd.Click Me.BindingContext(DataSet11, "Customers").EndCurrentEdit() Me.BindingContext(DataSet11, "Customers").AddNew() End Sub Private Sub btnDelete_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnDelete.Click DataSet11.Tables("Customers").DefaultView.Delete((Me.BindingContext(Dat aSet11, "Customers").Position)) UpdatePos() End Sub
| || |
Double-click the button control to create an empty event procedure template. The event used depends on the default event for the control, but the default event for the button is the click event.
These two procedures open a new row for adding, and deleting, the current row, respectively. They do nothing to update the database. They only enable editing the DataSet. Try the code. Compile and run the project. Click the Add New button. The controls should all blank out. Fill in some data. Click the Next button. The record counter should now say 92 records. Go back to the record you just entered. Click Delete. The record counter should now go back to 91 records.
Updating the Database
Next, we want to add the ability to update the database. Add the following event procedures to the form:
Private Sub btnSave_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnSave.Click Dim dsTemp As DataSet1 Me.BindingContext(DataSet11, "Customers").EndCurrentEdit() If DataSet11.HasChanges Then dsTemp = CType(DataSet11.GetChanges, DataSet1) SqlDataAdapter1.Update(dsTemp) DataSet11.AcceptChanges() End If UpdatePos() End Sub
This procedure performs four basic functions for saving changed data in a DataSet. First we call the EndCurrentEdit method of the binding context. This has the same effect as if we moved off the row using the visual interface. It ends the editing session and saves the changed values in the controls to the DataTable in the DataSet. There is a complementary method called CancelCurrentEdit which discards changes made on the screen and restores the original values.
Next, we call the HasChanges method. This method returns true (1) if the DataSet has any changed, added, or deleted rows. If there were changes, we store them in a temporary DataSet. We do this so we can minimize network traffic. Since we are using a local SQL Server, the effect of this is minimal, but if the DataSet were large and we had a wide area network (WAN) connection to the server, minimizing traffic has major benefits. To get the changed rows into a temporary DataSet, we call the GetChanges method of the DataSet. This gets the changes from all of the DataTable objects in the DataSet. Since we only have one, this is not a consideration right now, but it may be in the future. Note the call to the CType function. This is because the GetChanges method returns an untyped DataSet. In order for the assignment to work, we have to typecast the DataSet to the DataSet1 type that was created when we created the typed DataSet. That is what the CType function does.
We then call the update method of the DataAdapter and pass it the temporary DataSet we just created. This does an atomic update of the database, meaning it employs an implicit transaction automatically. If there were multiple tables to be updated, they would all succeed or all fail. You can override this functionality. We will see how in a later chapter.
The last statement in the if block causes the DataSet to reset all the change flags on the rows. The way the DataSet knows which rows have changed is that each row has a Changed, Added, or Deleted flag. This is how it knows which rows to extract when the GetChanges method is called. The AcceptChanges method simply resets the flags so we know any changes were flushed to the database. Finally, we call the UpdatPos procedure to update the navigation label.
Compile and run the project. Make some changes to a row. Click the Save Changes button. The data will be written to the database. Try adding some rows, saving the data and deleting some rows. (Don't delete any of the rows that were originally in the Northwind database.) You should look at the table from outside the application to see the data being changed. You can use the Server Explorer for this purpose. If you navigate to the Customers table and double-click it, a grid will open showing the data in the table. You will have to refresh the grid after each change is made through our program.
A final couple of finishing touches and our first Windows data form will be finished. What if our user decides to discard the changes that he made? We can easily add this feature. We already placed a Reject Changes button on our form. We will use this button for this purpose. Add the following event procedure to the form:
Private Sub btnReject_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnReject.Click Me.BindingContext(DataSet11, "Customers").CancelCurrentEdit() DataSet11.RejectChanges() UpdatePos() End Sub
This code cancels any edits on the current row. It then calls the RejectChanges method of the DataSet object. This method effectively rolls back any changes made since the DataSet was filled or since the last call to the AcceptChanges method. Any deleted rows will reappear, any added rows will vanish , and any changes to existing rows will be restored to their original values. We will examine how the DataSet does this in a later chapter.
It would also be nice for the user of our form to know if any changes were made to the DataSet so it can be saved. We will employ two visual cues for this purpose. We will enabled/disable the Save and Reject Changes buttons, and we will use a bitmap of a floppy disk to indicate that the data has changed.
First we will enable/disable the Save Changes and Reject Changes buttons. The buttons should only be enabled if there are changes to save. I prefer this method to displaying a message box to inform the user of an error after the fact. If there are no changes to save, just disable the buttons. That way the user cannot make a mistake.
To do this, we must make sure the buttons are disabled by default. Open the form designer and select the Save Changes button. Go to the Properties window and find the enabled property. Set it to false. Next do the same thing with the Reject Changes button. Remember the picture box we added with the bitmap of the floppy disk? We will also use this as a cue. When there are changes to be saved, the floppy icon will appear. If you haven't already done so, name the picture box picDirty. Now add the following code to the UpdatePos procedure.
Private Sub UpdatePos() lblRecs.Text = "Record " & Me.BindingContext(DataSet11, _ "Customers").Position + 1 & " of " & _ Me.BindingContext(DataSet11, "Customers").Count picDirty.Visible = DataSet11.HasChanges btnSave.Enabled = DataSet11.HasChanges btnReject.Enabled = DataSet11.HasChanges End Sub
This is the only change you should need. Since this procedure is called each time the DataSet is updated, the visual cues will always be correct. Compile and run the program. Try deleting a row. Click the Reject Changes button. The row reappears. Add a row. Click Reject Changes. The new row disappears. Try making some changes. Save them to the database. Notice the cues change appropriately. Congratulations! Your new data form is finished!