Client 2An Interactive Query Processor

The second client application that we'll build is an interactive query processor (see Figure 18.3). Enter a PostgreSQL command in the top window, press Ctrl+Return, and the query results appear in the DataGrid in the bottom window. VB.NET makes it very easy to build a simple application of this sort.

Figure 18.3. The client2 application.

This application creates an NpgsqlConnection object named m_conn, an NpgsqlCommand object (named m_cmd) linked to m_conn, an NpgsqlDataAdapter (named m_da) linked to m_cmd, and a DataSet (m_ds) that gets filled by m_da. The only code that you have to write is the function that fires when the user presses Ctrl+Enter in the text control.

To build this application, create a new VB.NET project (I've named mine client2) and make sure that you have Form1 open in design mode. Be sure to add a reference to Npgsql.dll to your project as I described earlier in this chapter (see the section titled "Creating an Npgsql-enabled VB.NET Project"). Next, open the Data tab in the Toolbox window to expose the Npgsql tools.

The first object that you'll need in this application is an NpgsqlConnection. Double-click the NpgsqlConnection tool (on the Data tab of the Toolbox window) and Visual Studio adds an object named NpgsqlConnection1 to the form. Visual Studio automatically assigns a unique name to each object that you add to a project, but those names can get awfully tedious: Click the Name property (in the Design section of the Properties window) and change the name of this NpgsqlConnection object to m_conn. Make sure that you set the ConnectionString property using either of the methods I described in the previous section (you can use the Data Connection Properties helper dialog or just type in the connection string yourself). client2 uses this object to interact with the PostgreSQL server.

Next, double-click the NpgsqlCommand tool (also in the Data tab) and change the name of the new object to m_cmd. In a typical application, you know the text of the command that you want to execute at the time you write the application and you can set the CommandText property accordingly. In this case, the user provides the text of each command so you can't set the CommandText property now (you'll change the CommandText property every time the user types in a new command). When you ask the m_cmd object to execute itself, it needs an NpgsqlConnection object in order to communicate with the PostgreSQL serversounds like a job for m_conn (the NpgsqlConnection object you created in the previous step). To link m_cmd to m_conn, change m_cmd's Connection property to m_conn.

At this point, you have a connection to the database and a command object that knows how to execute itself using that connection. If you wanted to do things the hard way, you could ask m_cmd to execute itself and then read through the result set, adding each row to the DataGrid yourself. Instead, I'll take the easy way out and use a DataSet to do the tough work. I mentioned earlier in this chapter that you can bind a DataSet to a UI control (such as a DataGrid) and the control will display (some or all of) the data in the DataSet. To add a DataSet to the form, double-click the DataSet tool (again, in the Data tab). The DataSet tool pops up a dialog that asks you to choose one of the typed DataSets in the project, or an untyped DataSet. You don't have any typed DataSets in this project so you'll have to create an untyped DataSet (you can't use a typed DataSet unless you know, at design-time, which tables you'll use in an application). Change the name of this object to m_ds. (Be sure you change the Name property, not the DataSetName property.)

So far, none of the objects that you've added present any sort of visible interface to the user (the form looks empty). client2 displays the result of each query in a DataGrid control. To create the DataGrid, open the Windows Forms tab (in the Toolbox window), click the DataGrid tool, and drag out a rectangle across the bottom half of the form. Change the name of the DataGrid object to m_grid. You'll also want to change the Anchor property from its default value (Top,Left) to Top,Bottom,Left, Right. That tells the DataGrid to resize itself if the user enlarges (or shrinks) the form. You want the DataGrid control to display the rows and columns inside of the m_ds DataSet, so set m_grid's DataSource property to m_ds. That binds the DataGrid and the DataSet together. The binding works in both directions. As you might expect, the values displayed in the DataGrid will change if you modify the content of the DataSet. What you may find surprising is that you can change the values in the DataGrid and the rows and columns in the DataSet reflect those changes. I'll show you how to write those changes back to the database in the next client.

You'll also need a place for the user to type in SQL commandsyou could use a multi-line TextBox control for this, but I'll use a RichTextBox instead, since it's a tad bit easier. Click the RichTextBox tool (in the Windows Forms tab) and drag out a rectangle across the top half of the form. Change the name of this object to m_cmdText and set the Dock property to Top. (That tells the RichTextBox to resize itself horizontally, but keep the same height if the user stretches out the form.) You may also want to blank out the Text property (or better yet, set it to SELECT * FROM to give a hint to the user).

Here's a quick review of the objects that you've created so far. You have an NpgsqlCommand object (m_cmd) linked to an NpgsqlConnection object (m_conn). When you execute a command, m_cmd interacts with the database using the m_conn connection. You have a RichTextBox (m_cmdText) where the user can enter PostgreSQL commands. You can't link m_cmdText and m_cmd at design-time, so you'll have to write some code to move the command text from m_cmdText into m_cmdI'll show that code to you in a moment. You also have a DataGrid (m_grid) bound to a DataSet (m_ds). The DataGrid displays the row and column values inside of the DataSet. You're missing one very important componentyou don't have anything that links the DataSet (m_ds) to the NpgsqlCommand (m_cmd). You need an adapter; specifically, an NpgsqlDataAdapter.

As I mentioned earlier in this chapter, an NpgsqlDataAdapter class links a DataSet to the database. An NpgsqlDataAdapter object contains a link to a SELECT command (stored inside of an NpgsqlCommand object). To add an NpgsqlDataAdapter to the form, open the Data tab (in the Toolbox) and double-click the NpgsqlDataAdapter tool. Change the name of this object to m_da. To link m_da to a command object, change the SelectCommand property to m_cmd. When the DataAdapter needs a result set from the database, it executes the SelectCommand.

When the user types in a command and presses Ctrl+Enter, you want to copy the command text from the RichTextBox (m_cmdText) to the NpgsqlCommand (m_cmd) and then ask the DataAdapter (m_da) to fill the DataSet (m_ds). To intercept the Ctrl+Enter event, switch to the Code view (right-click on the form and choose View Code), choose m_cmdText from the Class Name list box (that's up at the very top of the Code View window), and select Key Up in the Method Name list box (also at the top of the Code View window). Visual Studio inserts the code for a skeleton subroutine named m_cmdText.KeyUp() and moves your cursor into that code. Add the following code to the m_cmdTextKey() subroutine:

If (e.Control() And e.KeyCode = Keys.Enter) Then
 m_cmd.CommandText = m_cmdText.Text
 m_da.Fill(m_ds)
End If

Your subroutine should look like Listing 18.2 (I've trimmed out the argument list to fit on the printed pagedon't change the argument list that Visual Studio created for you).

Listing 18.2. Filling a DataSet

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_da.Fill(m_ds)
5 End If
6 End Sub

The code at line 3 copies the text that the user entered from the RichTextBox (m_cmdText) into the NpgsqlCommand object (m_cmd). The call to m_da.Fill() (see line 4) fills the DataSet (m_ds) with a result set. When you call the Fill() method, the DataAdapter examines its own SelectCommand property to find the appropriate DataCommand object. In this case, m_da.SelectCommand is a reference to the m_cmd command (which holds a reference to the m_conn connection). The DataAdapter asks m_cmd to execute itself and then loads the result set into the DataSet.

Go ahead and run this program. Type a SELECT command into the RichTextBox and press Ctrl+Enter. After a short pause (the client has to connect to the database, execute the command that you entered, and read the result set), the DataGrid changes its appearance (see Figure 18.4), but it doesn't display the result set. Click the + in the DataGrid and then choose Table to see the result set.

Figure 18.4. A confusing DataGrid.

It's rather annoying that you have to click the DataGrid to see the result set, but there's a good reason that the DataGrid behaves that way. I mentioned earlier that a DataSet can hold many result sets, not just one. Since the DataGrid is bound to the DataSet, it doesn't know which result set you want to view. That's why you have to choose. Of course, in this application, this application never adds more than one result set to the DataSet, so this behavior isn't very intuitive. Fortunately, this problem is easy to fix. Instead of binding the DataGrid to the entire DataSet, you can bind the grid to a single result set, but you can't bind the grid until you have a result set. That means you have to write some more code: Change the m_cmdText.KeyUp() subroutine so that it looks like Listing 18.3.

Listing 18.3. Binding to a Single Result Set

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_da.Fill(m_ds)
5 m_grid.DataSource = m_ds.Tables(0)
6 End If
7 End Sub

The only change here is that I've added line 5. By binding the DataGrid (m_grid) to a single result set (m_ds.Tables(0)), the grid knows which result set you want to view and automatically displays that result set as soon as its ready.

Run this client again and you'll see that it behaves better. But there's still one problem. Execute two (or more) queries and the result sets are co-mingled. Figure 18.5 shows the problemI've executed two commands (SELECT * FROM customers and SELECT * FROM tapes). The first four grid rows display the first result set and the last five rows display the second result set. When you Fill() a DataSet, the old data is not removed. Instead, the Fill() method searches through the DataSet to find a result set with the same name and adds the new data to that result set. Since we're creating anonymous (unnamed) result sets in this application, the DataSet makes up a name. The problem is that the DataSet always comes up with the same name (Table), even if the shape of the new result set differs. When you Fill() the DataSet the first time, it ends up in a result set named Table. When you add the second result set, it's also named Table and the DataSet merges the new data into the existing Table. If the shape of the new result set differs (in other words, if the columns are different), the DataSet adds NULL values to each result set to morph them into the same shape.

Figure 18.5. Another confusing DataGrid.

This problem is easy to fix, too. In fact, you can solve the problem in two different ways. First, you could assign a unique name to each result set by passing the name to the Fill() method. For example, the following code snippet increments a counter and assigns the counters current value (in string form) to each result set:

...
 m_queryCounter += 1
 m_da.Fill(m_ds, m_queryCounter.ToString())
...

That method works, but the DataSet grows and grows as you execute more queries. Instead, you can discard the old DataSet and create a new one as you execute each query. Listing 18.4 shows a slightly modified version of m_cmdText.KeyUp():

Listing 18.4. Creating a New DataSet

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
4 m_da.Fill(m_ds)
5 m_grid.DataSource = m_ds.Tables(0)
6 End If
7 End Sub

This version creates a new DataSet for each query. The old DataSet is simply lost (and eventually reclaimed by the VB garbage collector). When you run this program, you can execute query after query and you get a new DataSet each time. Since you bind the DataGrid to each new DataSet, the grid displays the result set returned by the most recent query.