Client 3Updating the Database with a DataSet

The third client that I want to show you is based on client2you can keep all of the work that you put into laying out a nice-looking application and just add a few lines of VB code here and there. client3 looks very much like client2it's a general purpose interactive query processorbut client3 lets you change the result set data displayed in the grid and write those changes back to the database. I'll add two buttons (Commit and Rollback) but those buttons remain disabled until you change a value in the grid (see Figure 18.6).

Figure 18.6. The client3 application.

To start, make room for two buttons near the bottom of the form (you may have to drag the DataGrid to a new location before you can get to the bottom of the grid). Now add the Rollback button to the lower-right corner of the form: Open the Windows Forms tab (in the Toolbox), click the Button tool, and drag out a rectangle where you want the button to live. Change the name of the button to m_rollback and change the Text property to Rollback. When the user clicks the Rollback button, client3 will discard all changes. It's a little misleading to show the user a button named Rollback when he hasn't actually made any changes, so set m_rollback's Enabled property to False. (We'll enable the button as soon as the user makes a change.) Add a second button named m_commit and change its Text property to Commit. When the user clicks this button, client3 will (try to) save all changes back to the database. Again, we'll enable this button when the user changes a value in the grid, so set m_commit's Enabled property to False.

When the user clicks the Rollback button (and the button has been enabled), client3 discards all changes made to the DataSet (m_ds). This would probably be a good time to remind you that the DataGrid is bound to the DataSet and that the binding works in both directions. If you change a value in the DataSet, that change is reflected in the DataGrid (that is, the user sees the new value). If the user changes a value in the grid, that change is reflected in the DataSet. To discard all changes recorded in the DataSet, just call m_ds.RejectChanges(). RejectChanges() throws out any changes that you've made to the DataSet since the most recent call to RejectChanges(), AcceptChanges(), or Update(). You want to invoke RejectChanges() when the Rollback button fires off a Click event. Double-click the Rollback button and add the code shown in Listing 18.5 to the subroutine that Visual Basic creates for you. (I've trimmed the argument list again to make this subroutine fit on the printed page.)

Listing 18.5. Rejecting DataSet Modifications

1 Private Sub m_revert_Click(...) Handles m_revertChanges.Click 2 m_ds.RejectChanges() 3 End Sub

When the user clicks the Commit button (and the button has been enabled), client3 TRies to write all changes back to the database. The code that implements the Commit button is a bit more complex than the code you added for the Rollback function. The Commit button may have to execute INSERT commands (if the user added new rows), DELETE commands (if the user deleted any rows), and UPDATE commands (if the user changed any values). The DataSet keeps track of the modification state for each row in the result set(s) and you can inspect that state by looking at m_ds.Tables(0).Rows(rowNumber).RowState.

Fortunately, you don't have to read through the DataSet yourselfa DataAdapter (m_da) can take care of that for you. You may recall (from the previous client application) that a DataAdapter connects a DataSet to a database. The DataAdapter.Fill() method fills the DataSet each time the user executes a new query. A DataAdapter can move data in the other direction (from the DataSet to the database), too. The DataAdapter.Update() method reads through all of the DataRows in the DataSet, inspects the RowState, and executes an INSERT, DELETE, or UPDATE command (as appropriate) to write the modifications back to the database.

A DataAdapter (specifically, an NpgsqlDataAdapter) can hold a reference to four different DataCommand objects: a SelectCommand, an InsertCommand, an UpdateCommand, and a DeleteCommand. When the user clicks the Commit button, your DataAdapter (m_da) holds a reference to a SelectCommand (the command entered by the user), but the other three command references are empty. Npgsql has a class that can build the INSERT, UPDATE, and DELETE commands for you: the NpgsqlCommandBuilder. An NpgsqlCommandBuilder object performs its magic by examining the layout of a DataRow. For example, if you have an NpgsqlCommandBuilder named builder, you can extract an UPDATE command like this:

updateCommand = builder.GetUpdateCommand( m_ds.Tables(0).Rows(0) )

GetUpdateCommand() reads through each DataColumn in the given row (m_ds.Tables(0).Rows(0)) and builds an UPDATE command from the column names (and data types) that it finds. If m_ds.Tables(0) contained a row from the customers table, for example, GetUpdateCommand() would build a command similar to the one shown in Listing 18.6[1].

[1] The :s_customer_id, :s_customer_name, :w_customer_id, :w_customer_id stuff tells you where the DataAdapter will substitute named parameter values to arrive at the actual command.

Listing 18.6. An Automatic UpdateCommand

UPDATE customers SET customer_id = :s_customer_id, customer_name = :s_customer_name, phone = :s_phone, birth_date = :s_birth_date, balance = :s_balance WHERE customer_id = :w_customer_id AND customer_name = :w_customer_name AND phone = :w_phone AND birth_date = :w_birth_date AND balance = :w_balance

Now that you have most of the pieces of the puzzle, you're ready to write the commit_Click() subroutine. This subroutine (see Listing 18.7) is called when the user clicks the Commit button.

Listing 18.7. Committing DataSet Modifications

1 Private Sub m_commitChanges_Click(...) Handles m_commitChanges.Click 2 Dim builder As Npgsql.NpgsqlCommandBuilder 3 4 builder = New Npgsql.NpgsqlCommandBuilder(m_da) 5 6 m_da.InsertCommand = builder.GetInsertCommand(m_ds.Tables(0).Rows(0)) 7 m_da.DeleteCommand = builder.GetDeleteCommand(m_ds.Tables(0).Rows(0)) 8 m_da.UpdateCommand = builder.GetUpdateCommand(m_ds.Tables(0).Rows(0)) 9 10 m_da.Update(m_ds) 11 m_commitChanges.Enabled = False 12 m_revertChanges.Enabled = False 13 End Sub

There's only one step left to complete this client: Enable the Commit and Rollback buttons when the user changes a value in the DataGrid. An object of type DataSet fires a series of events when you modify one of the values that it holds. We'll create a handler for the Commit_Changed event and tell VB to call that handler whenever your DataSet (m_ds) fires that event. The handler (see Listing 18.8) simply sets the Enabled property for each button.

Listing 18.8. Enabling Commit and Rollback Buttons

1 Private Sub Column_Changed(...) 2 m_commitChanges.Enabled = True 3 m_revertChanges.Enabled = True 4 End Sub

To wire this subroutine into the VB event handling scheme, you must execute an AddHandler command, but knowing when to execute that command is a bit tricky. Look back at Listing 18.4 ("Creating a New DataSet"). That subroutine (m_cmdText KeyUp) takes care of creating a new DataSet and filling that DataSet with a result set built from the command that the user typed in. Because you're creating a new DataSet for each command, you'll have to add the Column_Changed event handler to each DataSet. The new version of m_cmdText_KeyUp is shown in Listing 18.9 (the only difference is that I've added an AddHandler command at line 7).

Listing 18.9. Handling Column_Changed Events

1 Private Sub m_cmdText_KeyUp(...) Handles m_cmdText.KeyUp 2 If (e.Control() And e.KeyCode = Keys.Enter) Then 3 m_cmd.CommandText = m_cmdText.Text 4 m_ds = New DataSet 5 m_da.Fill(m_ds) 6 m_grid.DataSource = m_ds.Tables(0) 7 8 AddHandler m_ds.Tables(0).ColumnChanged, AddressOf Column_Changed 9 End If 10 End Sub

That's it. You have a client application that lets you enter SELECT commands, view the results, change the results, and write those changes back to the database.

Go ahead and try it outtype in a few queries, change the result set, and check out the Commit and Rollback buttons. When you've had enough fun, I'll show you a few problems with the way I've designed client3, and (better yet) I'll show you how to fix them.