Insert and Update Operations

Team Fly

Page 428

One of the most important topics in database programming is the commitment of the changes made at the client back to the database. The changes involve edited rows, which must update the underlying rows in the table, new rows, which must be inserted into the underlying table, and deletions, which must remove the corresponding rows from the underlying table. There are basically two modes of operation: single updates and multiple updates. A client application running on a local area network as the database server can (and should) submit changes as soon as they occur. If the client application is not connected to the database server at all times, then changes may accumulate at the client and be submitted in batch mode when a connection to the server is available.

From a developer's point of view, the difference between the two modes is how you'll handle update errors. If you submit individual rows to the database and the update operation fails, you can display a warning and let the user edit the data again. You can write code to restore the row to its original state, or not. In any case, it's fairly easy to handle isolated errors. If the application submits a few dozen rows to the database, several of these rows may fail to update the underlying table and you'll have to handle the update errors from within your code. At the very least, you must validate the data as best as you can at the client before submitting them to the database. No matter how thoroughly you validate your data, you can't be sure that they will be inserted into the database successfully.

Another factor you should consider is the nature of the data you work with. Let's consider an application that maintains a database of books and an application that takes orders. The book maintenance application handles publishers, authors, translators, and other data. All users who are entering and correcting titles are working with the same table of authors. If you allow them to work in disconnected mode, the same author name may be entered several times, as no user can see the changes made by any other user. The result is that several rows in the Authors table refer to the same author. This application should be connected: every time a user adds a new author, the table with the author names in the database must be updated, so that other users can see the new author. The same goes for publishers, translators, topics, and so on.

The order-taking application can safely work in a disconnected mode, because orders entered by one user are not aware of, and they don't interfere with, the orders entered by another user. You can install the client application on the notebooks of several salespersons so they can take orders on the go and upload them when they establish a connection between their notebook and the database server (which may happen when they return to the company's offices). There's a small implication here, namely the stock. If you can't make a sale unless the items are in stock, things get quite complicated; the order-taking application can't run in disconnected mode. Incidentally, this is one of the most complicated types of projects you may run into and we will not discuss it in this book. The solution is dictated by the business rules and, in most cases, it's non-trivial.

The order-taking application can be used in a disconnected mode, because each order contains existing products and there will be no update errors. The worst that can happen is that a product's price will change. In this case, a business rule determines whether the sale is made with the old price, or whether the customer should be contacted and confirm the revised price.

Updating the Database with the DataAdapter

The simplest method of submitting changes to the database is to use each DataAdapter's Update method. At the beginning of the chapter we discussed how to submit changes to the database and

Team Fly