The last topic we need to cover is how you can make changes to an existing database. There are a number of ways that you can modify a database. These are add new records to the database, edit an existing record in the database, and delete a record from the database. We'll explore each of these in a moment. However, before you do anything else, you should make a backup copy of the Xtreme.mdb database. Up to this point, we've done nothing that might change what's in the database. However, in this section, we'll be modifying the content of the database. Therefore, you should make a backup copy to preserve the original database content before testing any of the code in this section.
A Delete Digression
I should also mention from the outset that I'm not a big fan of deleting data from a database. Although I'll cover how to delete records from a database, I believe there are better ways to accomplish the same goal. My preference is to create a Status field in each table. Early in the design phase, it may seem that a Boolean data type is a good choice for a Status field because it can mark the absence or presence of some status or condition. For example, either the customer is active or not. The person is either a client or not. The person is either a member of an organization or not. Even though a Boolean seems the obvious choice, resist the urge.
Life rarely presents things in pure black or white. Gray is pretty common. For example, members do take a leave of absence. Customers you haven't heard from for years suddenly place an order. By making the Status field an Integer variable, you can code values that appear later on that you didn't think about during the design phase. For example, might mean the person is not a member, 1 means he's a member, and 2 means he has taken a leave of absence. With a Boolean , it's either true or false; there's no room for a design hiccup.
By using a Status field, you can alter the status of a table entity without physically removing it from the database. For example, if you're searching a list of active clients , simply use a WHERE predicate that ends with ...WHERE Status = 1 . This would exclude all inactive clients. That's not where the real benefit comes in, however.
Just as sure as you're sitting there, right after you delete a client, someone will come in (perhaps an attorney trying to settle a contested will) and ask you to reconstruct all purchases for the client you just deleted from the database. Depending on how you've constructed the database, deleting the client might actually delete all other related records associated with the client (for example, all order information). Reconstructing the information could range from being difficult to impossible .
By using a Status field, you aren't physically removing the client from the database. Therefore, reconstructing the data for an inactive client is relatively easy. Indeed, you could create a program that does nothing else but present data for inactive clients.
If you're actually concerned that the dead data is both taking up too much disk space and degrading performance, the Status field is still a good idea. You could write a program that copies the dead data to a backup medium and then physically purges the dead data from the database. In this manner, you could still reconstruct the data if necessary, but the dead data is physically removed from the database. The presence of the Status field simply makes the task easier.
Okay, I'm stepping off my soapbox and back to level ground. Now let's see how to modify a database. (I'll assume that you've made a backup of the Xtreme.mdb database.)
The ModifyDB Project
I would be remiss if I didn't show you the easy way to configure a Visual Basic .NET program for database access. In this section, we'll use the Visual Basic .NET wizards to do almost all the work for us. I can then show you the statements needed to modify the database. Once again, before you start, make sure that you've backed up the Xtreme.mdb database because the code and our experimentation in this section will modify the database.
Using the Server Explorer
First, start a new project and name it ModifyDB. Now select the Server Explorer ( not the Solution Explorer) from the View menu. Figure 25.27 shows what your display should look like.
Figure 25.27. Activating the Server Explorer.
If you look closely, you'll see I already have a data connection to the Xtreme.mdb database. However, that's the original database and I don't want to mess with it. Instead, I want to connect with a copy of the database. Therefore, I need to establish a new data connection.
To add a new data connection, right-click the Data Connections icon in the Server Explorer window (it looks like a small drum next to the word Access in Figure 25.27). This opens up a small menu from which you should select the Add Connection option. Your screen should look about the same as Figure 25.4 shown earlier. Click the Provider tab and select the Microsoft JET 4.0 OLE DB Provider and then click Next . Your screen should now look like Figure 25.6. At this point, you should click on the ellipsis button and navigate to the copy of the database that you want to experiment with. After you've done that, it wouldn't hurt to click the Test button to make sure that everything's set up correctly. Now click the OK button. You should now see the new data connection in the Server Explorer window. This can be seen in Figure 25.28.
Figure 25.28. The Server Explorer after adding a backup connection to the Xtreme.mdb database.
Notice that we've added our new connection to the directory where we're developing the program. So far, so good.
If you click on the expand icon (that is, the plus sign in front of the data connection), you can see the internal structure of the database (tables, views, stored procedures). This can be useful, for example, if you need to know the names of the tables in the database. You can also expand the tables to find the list of fields contained within a table. Useful stuff.
Adding the Data Adapter Object
Now click on the Toolbox tab to bring it to the foreground and then click on the Data tab. Now drag the OleDBDataAdapter object onto the form, which will start the Data Adapter Configuration Wizard (DACW). This time, however, we don't want to dismiss it as we did before. Instead, we want to proceed with the configuration of the data adapter object. Click the Next button to proceed.
The DACW is now asking you to confirm your data connection. You should see the pathname to the Xtreme.mdb database. If you need to change the connection, simply select from the drop-down list. (You could also establish a new connection, if you needed to, by using the Add Connection button.) We're good to go, so click the Next button.
If you've done things correctly thus far, your screen should look similar to Figure 25.8 shown earlier. Accept the Use SQL Statements option and click the Next button. The screen will look like Figure 25.9 now. However, this time we want to add the SQL statement that we want to process. To keep things simple, we'll type in
SELECT * FROM Customer
This is shown in Figure 25.29. Now click the Next button.
Figure 25.29. Adding a query to the Data Adapter Configuration Wizard.
Visual Basic .NET will grind for a few seconds and then display a message saying that it has configured the data adapter. You are told that it has not only set up the SELECT statements, but also the table mappings, plus the INSERT , UPDATE , and DELETE statements. It's these last three that we're most interested in here. Click the Finish button and the OleDbDataAdapter1 object is added to the project.
Creating the DataSet Object
We still need a place to store the data, which is the purpose of our DataSet object. From the main menu, click the Data, Generate Dataset menu sequence. Your screen should look the same as Figure 25.18. Click OK to add the DataSet1 object to the project.
Now all we need is a means by which we can display our data.
Adding the DataGrid and Remaining Controls
From the Toolbox on the Windows Forms tab, double-click on the DataGrid control to create a grid object on the form. The default name of DataGrid1 is fine for now. Add four button controls to the form and name them btnAdd , btnEdit , btnDelete , and btnExit . Your form should look something like that shown in Figure 25.30.
Figure 25.30. Form layout for the ModifyDB project.
You now need to bind the DataGrid control to the database. From the Properties window, move the cursor to the DataSource property. Select the DataSet object that does not have the database table associated with it. For example, if you named the control DataSet1 , select it as the DataSource , not DataSet1.Customers .
Now click in the DataMember property and select Customer. Visual Basic .NET knows about the objects you see in the Properties window because of the code that was generated while we were using the wizards.
In the Form Load event, add the following lines of code:
DataGrid1.CaptionText = "Records Read: " & _ CStr(OleDbDataAdapter1.Fill(DataSet1)) DataGrid1.AlternatingBackColor = Color.Honeydew
The first statement does two things. First, it fills the dataset object with the information from the database via the Fill() call. Second, we use the return value from the Fill() call to display the number of records that are in the dataset object. The second statement sets the alternating color value to a very pale shade of green. Finally, add the code shown in Listing 25.6 to the program.
Listing 25.6 The Remaining ModifyDB Code
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As _ System.EventArgs) Handles btnAdd.Click ModifyDatabase("Add") End Sub Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As _ System.EventArgs) Handles btnEdit.Click modifydatabase("Edit") End Sub Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As _ System.EventArgs) Handles btnDelete.Click ModifyDatabase("Delete") End Sub Private Sub ModifyDatabase(ByVal TryingThis As String) ' Purpose: This routine does all of the updating for the database. Try OleDbDataAdapter1.Update(DataSet1) Catch ThisEx As Exception MessageBox.Show("Error during " & TryingThis & ": " & ThisEx.ToString) End Try End Sub
Could the code be any simpler? Each of the buttons calls the same subroutine, named ModifyDatabase() . All that the subroutine does is call the Update() method of the data adapter object using the dataset object as its argument (I'll explain what this does in a moment).
Using the Program
When you start the program, the data grid is filled in with the Customer table. Figure 25.31 shows a sample run of the program. Now suppose that you want to edit the Contact Last Name field for the first record. Because the data grid allows editing, move the cursor to the desired field. This automatically highlights the current contents of the field. You'll also see a pencil icon appear in the first (non- writeable ) column of the data grid (see Figure 25.31). Now just start typing in the new value. When you're done, click the Edit button. This calls the Update() method to write the new data back into the database.
Figure 25.31. A sample run of the ModifyDB project.
To delete a row, scroll to the record you want to remove and move the cursor to the extreme-left column and click. This will highlight the entire selected row. Now press the Delete key on your keyboard. The row is removed. If you click on the Delete key, the deletion is made permanent by another call to Update() .
Finally, if you want to add a record, scroll to the end of the dataset and place the cursor in the first field and start typing in the new data. When all the fields are filled in, click the Add New button. If you fail to fill in a required field (for example, a primary key field), Visual Basic .NET will let you know.
Obviously, there's a little bit of trickery going on here. All these modifications call the same Update() routine to perform the modifications. The reason this works this way is that any changes you make to the data in the grid are really being performed only in memory. Indeed, if you make a multitude of changes and never click one of the buttons, no permanent change is made to the database. (You'll hear programmers refer to this type of update processing as disconnected database programming.)
It's also true that you don't have to click on a button after each change. You could make hundreds of changes to the data without clicking any of the buttons. However, after you do click a button, the Update() call makes the current state of the dataset object permanent in the database. Likewise, never clicking a button before ending the program leaves the database unchanged.