Figure 11.17: Although there was a lot of mouse jockeying involved, the database can be displayed on a form with only one line of code.

Using Queries to Modify Data Results

The interesting thing about databases is that they usually have too much information to be immediately useful. The main thing a user needs to do with a database is screen out information to find exactly what he or she is looking for. Most data management systems use a query to provide this information. The query demo program shows you how queries work in Visual Studio.

Limiting Data with the SELECT Statement

Figure 11.18 demonstrates a new version of the spy statement that enables the user to limit the database results.

333

Figure 11.18: The default version shows the entire database of spies.

The text box below the grid enables the user to type in database manipulation commands in a special language called Structured Query Language (SQL). SQL features a number of commands for data manipulation, but the SELECT command shown here is the most basic and most commonly used command. The asterisk (*) means "all fields", so the command SELECT * from Agents can be read "Display all the fields of the Agents table." As you can see, this is exactly what the program is doing at the moment. You might want a quick list of your spy’s codenames, without any other information. To limit the grid so it only displays codenames, you can type the following command into the text box and then click the Execute Query button:

SELECT CodeName FROM Agents

Figure 11.19 shows what happens when this statement is executed.

334

Figure 11.19: Now the program only shows the spy codenames.

The keyword SELECT indicates that you are going to get a subset of the original table. CodeName is the name of a field you wish to view. The FROM keyword indicates which table you are viewing (there’s only one at the moment), and Agents is the name of that table. SQL keywords are traditionally typed entirely in uppercase letters.

Let’s say you had to quickly set up a mission that involves explosives (like all good missions). You might want to get a list of all your agents and their specialties. You could use this query to generate that list:

SELECT CodeName, Specialty FROM Agents

As you can see from this query, you can indicate more than one field to display at a time. Figure 11.20 shows the results of this second query.

335

Figure 11.20: This query returns a list of the codenames and specialties.

The queries you’ve seen so far indicate how you can limit the number of columns shown in a query result. If you’re only concerned about those spies who can handle explosives, you’re actually more interested in limiting the number of rows (records) that are displayed. You can modify a SELECT statement so it only returns records that satisfy some sort of condition. Try this SQL statement:

SELECT * FROM Agents WHERE Specialty = 'Explosives'

The WHERE statement indicates you want to limit the number of rows displayed. It is followed by a condition. Conditions in SQL are similar to the ones you have used many times in C#, but there are some differences. First, the condition almost always compares a field name to a value. Second, SQL conditions use a single equals sign (=) for equality rather than the double equals (==) used for equality in C#. Finally, string values in SQL are encased in single quotes rather than the double quotes you are used to in C#. The result of this query is shown in Figure 11.21.

336

Figure 11.21: The query returns all the fields, but only the records of the spies who specialize in explosives.

Of course, you can combine both types of queries. If you want a list of the code names and assignment of spies who specialize in explosives, you can use the following query:

SELECT CodeName, Assignment FROM Agents WHERE Specialty = 'Explosives'

The results of this query are illustrated in Figure 11.22.

337