Client 4A More Robust Query Processor

In client4, I'll show you how to handle three different problems that you'll eventually find in the client3 application:

• If the user executes a command that fails for some reason (syntax error, table doesn't exist, and so on), client3 tHRows an unhandled exception and the program gets so embarrassed that it terminates.
• If the user executes a command other than SELECT, the command completes, but the application throws another one of those embarrassing unhandled exceptions.
• If the user clicks the Commit button and something goes wrong during the Update(), some changes may be recorded to the database and others may not.

It's easy to replicate the first problemjust type in a nonsense command and press Ctrl+Enter. It's just as easy to fix: Wrap the code that executes the command in a TRy/Catch block. Of course, if something goes wrong (that is, if the Catch clause actually catches an exception), you'll want to display the error message somewhere. I'll add a StatusBar control to the bottom of the client4 window (client4 starts out as a carbon copy of client3) and display the error message there. To add a StatusBar, open the Windows Forms tab (in the Toolbox) and double-click the StatusBar tool. Visual Studio automatically places the StatusBar at the bottom of the form. (You may have to scooch the Commit and Rollback buttons a bit to make room for the StatusBar.) Change the name of the StatusBar to m_statusBar and set the Text property to Ready.

With a StatusBar in place, you're ready to Catch the exception that's thrown when a user executes an invalid command. In this application, you're never executing the user's command directlyinstead, a DataAdapter executes the command when you call the Fill() method (to fill up a DataSet). That means that you want to wrap the invocation of the Fill() method (since the exception is thrown from inside of that method). Listing 18.10 shows a new version of the m_cmdText_KeyUp() subroutine.

Listing 18.10. Intercepting Execution Errors

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
6 Try
7 m_da.Fill(m_ds)
8 m_grid.DataSource = m_ds.Tables(0)
9 AddHandler m_ds.Tables(0).ColumnChanged, AddressOf Column_Changed
10 m_statusBar.Text = "Ready"
11 Catch ex As Exception
12 m_statusBar.Text = ex.Message
13 End Try
14 End If
15 End Sub

I've moved three steps into the TRy clause: the call to Fill() (that's the one most likely to throw an exception), binding the DataGrid to the DataSource, and adding an event handler to the DataSet. You want all three steps in the try clause because you don't want to bind the DataGrid or add an event handler if the Fill() invocation fails. I've also added code (see line 10) to set the StatusBar text to indicate that the command succeeded. That's important because a previous command may have placed an error message in the StatusBar. The code inside of the Catch clause (line 12) copies the error message text (it's inside of the ex Exception) into the StatusBar.

That takes care of the first problem without adding too much code. Try it outif you execute a command that generates an error, you'll see the message appear in the status bar at the bottom of the window.

The second problem is just as easy to replicate and just as easy to fix. As long as you have client4 up and running, try executing a non-query command (ANALYZE customers would be a good choice). When you execute a non-query command, client4 does something very strange: The status bar displays the cryptic message "Cannot find table 0." Where in the world is that message coming from? Take a look at Listing 18.10 again, particularly at line 9. Notice that I'm hooking up an event handler that gets invoked when m_ds.Tables(0) (the first table in the m_ds DataSet) fires off a ColumnChanged event. You see the problem. If you don't execute a SELECT command, the DataSet doesn't have a first table (it doesn't hold any tables). It's the code at line 9 that's throwing the exception. Since the exception is thrown within the try clause, the Catch clause catches it and copies the error message ("Cannot find table 0") to the StatusBar. Makes perfect sense now, doesn't it?

To fix this problem, you'll need a way to differentiate between SELECT commands and all other commands. The Npgsql classes don't provide an obvious way to tell the difference. It would be nice if the NpgsqlCommand object contained a CommandType property that exposed this information that you're looking for, but it doesn't. (Actually, an NpgsqlCommand object does have a CommandType property, but it's used for something completely different.) If you think back to the error message we're dealing with, you may see the solution. The error message states "Cannot find table 0" when you've execute a command other than SELECT. That means that, if you execute a command other than SELECT, the DataSet won't contain any tables. If m_ds.Tables.Count > 0, you've executed a SELECT command. If m_ds.Tables.Count = 0, you've executed some other command.

Listing 18.11 shows a modified version of the m_cmdText_KeyUp() subroutine (the last version, I promise).

Listing 18.11. Intercepting Execution Errors

1 Private Sub m_cmdText_KeyUp(...) Handles m_cmdText.KeyUp
2 If (e.KeyCode = Keys.Enter And e.Control) Then
3 m_cmd.CommandText = m_cmdText.Text
4 m_ds = New DataSet
5
6 Try
7 m_da.Fill(m_ds)
8
9 If (m_ds.Tables.Count = 0) Then
10 m_grid.Hide()
11 m_statusBar.Text = "Ok"
12 Else
13 m_grid.DataSource = m_ds.Tables(0)
14 m_grid.Show()
15 m_statusBar.Text = "Ready"
16 AddHandler m_ds.Tables(0).ColumnChanged, AddressOf Column_Changed
17 End If
18
19 Catch ex As Exception
20 m_statusBar.Text = ex.Message
21 End Try
22 End If
23 End Sub

At line 9, this subroutine inspects the m_ds.Tables.Count property. If it finds that there are no tables in the result set, it hides the DataGrid (line 10) and writes Ok into the StatusBar. If the result set does contain a table, the Else clause binds the DataGrid to the DataSet, displays the DataGrid (in case it's been hidden by a prior command), writes Ready into the StatusBar, and wires up the ColumnChanged event handler.

The last problem is a bit more complex to replicate and a bit more complex to resolve. To see the problem, fire up client4 and execute the command SELECT * FROM customers. (That command should return at least three or four rows.) Now start another PostgreSQL client application (you could start a second instance of client4 if you like) and execute the command UPDATE customers SET balance = 16.16 WHERE customer_id = 3. The first session is displaying the content of the m_ds DataSet (that is, it's displaying the result of the SELECT * FROM customers query). The second session has just modified one of the rows in the customers table. Now go back to the first session, change the phone_number for customer_id 3, and click Commit. The m_commitChanges_Click() subroutine tries to Update() the DataSet based on the contents of the DataGrid. But the DataGrid holds stale datayou've modified the balance for customer_id 3 in another session. If the Update() method succeeds, you'll lose that modification. Instead, the Update() method throws an exception (Concurrency Violation: the UpdateCommand affected 0 rows) when it tries to update customer_id 3. To understand why the Update() method fails, look back to the UPDATE command shown in Listing 18.6 ("An Automatic Update Command"). That UPDATE command was generated by an NpgsqlCommandBuilder object and it fails because the WHERE clause refers to every column in the table. The WHERE clause looks something like this:

 WHERE
 customer_id = 3 AND
 customer_name = 'Panky, Henry' AND
 phone = '555-1221' AND
 birth_date = '1968-01-21' AND
 balance = 0.00

The WHERE clause won't match any of the rows in the customers table because you've changed the balance in another session (which explains the message UpdateCommand affected 0 rows).

The obvious way to fix this problem is to wrap the invocation of m_da.Update(m_ds) inside of a try/Catch block. That works fine if you've only modified a single row in the DataGrid. But what happens if you've modified two (or more) rows in the DataGrid and one of the UPDATE statements fails? Some changes are written to the database and others aren't. The obvious way to fix that problem is to wrap the call to m_da.Update(m_ds) in a transaction, wrap the transaction in a try/Catch block, and ROLLBACK the transaction if you catch an exception. That ensures that the database is consistent (all modifications are written to the database or none of the modifications are written to the database), but there's still a problem. The DataSet is keeping track of the modification state of each row in the DataTable. When you call the Update() method, it walks through each row in the DataTable, eyeballs the modification state, and executes an INSERT, UPDATE, or DELETE command if the modification state demands. If the INSERT, UPDATE, or DELETE command succeeds, Update() resets the modification state to indicate that the row is Unchanged and moves on to the next row. If one of the INSERT, UPDATE, or DELETE commands fails, Update() throws an exception and you ROLLBACK the entire transaction. The modification states stored in the DataSet are completely befuddled. Update() has changed some modification states to Unchanged even though those rows have not in fact been committed.

As I said earlier, the fix for this problem is a bit complex. The important point is that you must retain the modification states stored in the DataSet just in case you have to ROLLBACK an aborted transaction. Listing 18.12 shows a modified version of the m_commitChanges_Click()I'll walk you through the changes in a moment.

Listing 18.12. Intercepting Execution Errors

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.UpdateCommand = builder.GetUpdateCommand(m_ds.Tables(0).Rows(0))
8 m_da.DeleteCommand = builder.GetDeleteCommand(m_ds.Tables(0).Rows(0))
9
10 If (m_conn.State <> ConnectionState.Open) Then
11 m_conn.Open()
12 End If
13
14 Dim transact As Npgsql.NpgsqlTransaction
15
16 transact = m_conn.BeginTransaction()
17
18 m_da.InsertCommand.Transaction = transact
19 m_da.UpdateCommand.Transaction = transact
20 m_da.DeleteCommand.Transaction = transact
21
22 Dim changes As DataTable = m_ds.Tables(0).GetChanges()
23
24 Try
25 m_da.Update(changes)
26 transact.Commit()
27
28 m_ds.AcceptChanges()
29 m_commitChanges.Enabled = False
30 m_revertChanges.Enabled = False
31 Catch ex As Exception
32 transact.Rollback()
33 MessageBox.Show(ex.Message, "Update Failed")
34 End Try
35 End Sub

The first eight lines of this version are identical to the previous version: You're creating an NpgsqlCommandBuilder object and extracting the required INSERT, UPDATE, and DELETE, commands from it. The first change appears at lines 1012. If the database connection (m_conn) is not open, the call to m_conn.Open() opens it. (You need an open connection to create a new transaction and there's no guarantee that the connection is still open when you reach this subroutine.)

At line 14, you're creating a new transaction (an object of type NpgsqlTransaction). As you can see, you create a transaction by calling the BeginTransaction() method defined by an open NpgsqlConnection.

The BeginTransaction() method returns a reference to a transaction object and the code at lines 18 through 20 binds the INSERT, UPDATE, and DELETE commands to that transaction. When you invoke the m_da.Update() method, it will execute the INSERT, UPDATE, and DELETE commands within the transaction that you've created.

The transaction part of this subroutine is straightforward. To COMMIT the transaction, you'll call transact.Commit(). To ROLLBACK the transaction, you'll call TRansact.Rollback(). But we haven't solved the modification state mangling problem yet. Remember, you must retain the modification states stored in the DataSet just in case you have to ROLLBACK the transaction. The easiest way to retain the modification states for each row is to make a copy of each row before you call Update(). You could copy the rows by cloning (or copying) the DataTable that you find in the DataSet (m_ds.Tables(0)), but that's actually more than you need. You only need to copy the DataRows that have changed. The DataTable class provides a method, GetChanges(), that returns a new DataTable that contains a copy of all DataRows with a modification state of Added, Deleted, or Modified. The code you see at line 22 invokes the GetChanges() method and assigns the result to a new DataTable named changes.

At line 25, I've changed the call to m_da.Update(). In the previous version, I called the Update() method with a DataSet argument (m_ds). That applied all modifications found in the DataSet. In this version, I'm asking Update() to apply all modifications found in the changes DataTable. If the Update() succeeds, you can COMMIT the transaction (line 26). At this point, the Update() method has reset all of the modification states in the changes DataTable, but the modification states in the DataSet (m_ds) still claim that some rows have been modified. The call to AcceptChanges() (line 28) resets all of the modification states in m_ds. So far, so good.

If the call to Update() fails, you can ROLLBACK the transaction (see line 32). Since the Update() method applied all modifications found in the changes DataTable (as opposed to the m_ds DataSet), the modifications states in the DataSet are still correct. (They still tell you which rows have been modified.) The user can (try to) correct the problem (probably in a concurrent session) and try to COMMIT his changes again.