Figure 11.22: This query limits both the number rows and the number of columns.
In essence, the SELECT statement is used to filter the data to answer some kind of question.
Trap I allowed the user to type in a SELECT statement here simply to keep the code simple. As you can imagine, real users don’t usually know much about SQL, so they probably would really mess this program up. It would be better to build some sort of visual mechanism that creates the SQL statement on the fly than to let the user directly enter SQL. You’ll see some examples of that later in the chapter.
Using an Existing Database
The Query Demo program is based on the existing SimpleSpy database.
I already created the Simple Spy database, so I don’t have to make it again. Simply start a new project, open the server explorer, and the Simple Spy database will still be there. The databases are a function of the entire machine. They are not necessarily tied to any particular project. Drag the Agents table onto the new form, and rename the resulting SqlDataConnection and SqlDataAdapter. Create a new data set from the DataAdapter, and add a data grid to the form. Your form should look something like Figure 11.23.
338
Figure 11.23: The query demo has been attached to the SimpleSpy database.
To finish the connection, set the data grid’s datasource to myDS, and its data member to Agents. Finally, fill the data set in the form’s load method:
private void QueryDemoForm_Load(object sender, System.EventArgs e) {
myAdapter.Fill(myDS); } // end form load
Hint In the last few steps, you have recreated the entire simple spy program. It’s interesting that a procedure that took 20 minutes or more the first time can be done in just a few minutes the second time. With a little practice, you’ll be able to completely hook up a database in a minute or two.
Adding the Capability to Display Queries
The visual layout of the form is not challenging. It contains a data grid called dgSpies, a textbox called txtQuery, and a button called btnQuery. All the action happens in the btnQuery click event:
private void btnQuery_Click(object sender, System.EventArgs e) {
DataSet qDS = new DataSet("results"); myAdapter.SelectCommand.CommandText = txtQuery.Text; myAdapter.Fill(qDS, "results"); dgSpies.SetDataBinding(qDS, "results");
} // end btnQuerey
The code begins by creating a new DataSet object called qDS (for query data set). This new data set has one table, called results. The qDS data set holds the results of the query in its results table. Remember, a data set is a local copy of a database. It must be filled from some sort of data adapter. The new data set gets a subset of the original database. The data adapter class has a SelectCommand property that holds a command for selecting the data. This property holds an SQLCommand object, which has a CommandText property. I can set the CommandText property to a SELECT statement, and this applies to all subsequent fill statements related to the adapter. I use
339
the adapter’s Fill() method to fill qDS with the filtered data. Finally, I used the SetDataBinding() method of the data grid to apply the new Data Set to the data grid and display the results.
Letting the user enter information in a text box is just too risky, so I added an exception handling routine so the program won’t choke up if the user enters a query in some odd format (like “get me all the spies who do sabotage.”)
DataSet qDS = new DataSet("results"); myAdapter.SelectCommand.CommandText =
txtQuery.Text; try {
myAdapter.Fill(qDS, "results"); dgSpies.SetDataBinding(qDS, "results");
}catch (Exception exc){ MessageBox.Show("Something went wrong"); myAdapter.SelectCommand.CommandText =
"Select * from Agents"; myAdapter.Fill(qDS, "results"); dgSpies.SetDataBinding(qDS, "results");
} // end try
}// end btnQuery
Creating a Visual Query Builder
It isn’t reasonable to expect your users to know how to build a SELECT statement. Instead, you may want to prepare some predefined queries for the user, or you might want to build a visual query builder. Figures 11.24 and 11.25 illustrate a program that enables the user to build queries dynamically.
Figure 11.24: The form is just like QueryDemo, but it adds some other controls for building the
340
query.
Figure 11.25: If the user types a value into the textbox and chooses a field from the listbox, only records that satisfy the resulting condition are displayed.
The program displays the SELECT statement as it is being built. The query is automatically updated every time any of the screen elements is changed. This program is very easy to use, and it provides a great deal of power to the user.
Creating the Layout for the Visual Query Builder
The Visual Query program uses a number of controls. The most prominent of these is a data grid, which is bound to a data set as you have done several times in this chapter. To create the bound data grid, follow these steps:
1.Locate your database in the server explorer pane.
2.Drag the Agents table to the form.
3.Rename the resulting DataConnection and DataAdapter Objects.
4.Create a data set from the DataAdapter (using the link at the bottom of the property menu).
5.Rename the resulting instance of the data set.
6.Set the DataSource property of the data grid to the data set.
7.Set the DataMember property of the data grid to the Agents table.
8.Call the data adapter’s fill method to fill the data set in the form’s load event.
Hint It might seem crazy to attach to the same database three different times, but it’s really good practice. Repetition is the best way to ensure you know how to attach to a database. Every data application you write starts out something like this, so it’s a good skill to rehearse.
Figure 11.26 shows the Visual Query form in the designer.
341
Figure 11.26: The Visual Query form features checkboxes, list boxes, and a couple of labels.
The check boxes have predictable names (chkAgentID, for example). The list box is called lstField, and I loaded the field names into the list box in design time. The text box is called txtMatch, and the label holding the query is called (cleverly enough) lblQuery. The only class–level variable is a string called theQuery.
Initializing the Code in the Load Event
As usual, there is some initialization in the form’s load event:
private void VisQueryForm_Load(object sender, System.EventArgs e) {
myAdapter.Fill(myDS, "Agents"); lstField.SelectedIndex = 0;
} // end form load
The load event has only two tasks. First, it fills up the data set from the adapter. Second, it selects element 0 in the list box, so there is always some element selected in the list box.
Building a Query
All the elements on the form cooperate to build a SELECT statement (query). Every time the user changes any element (except for the data grid itself) the component fires off a call to the buildQuery() method. This method is essentially a general−purpose event handler called by most of the screen components.
Hint The actual event that triggers the buildQuery() method changes based on the component type, but they were all pretty easy to guess. For example, I called buildQuery() on the CheckChanged event of the check boxes, the SelectedIndexChanged event of the list box, and the TextChanged event of the text box. When I had a "build query" button, I had its Clicked event attached to the buildQuery() method as well.
342
Building the query is a little more involved than it might seem, so I broke it into functions. The SELECT part of the query comes primarily by examining the status of the check boxes. The getSelect() method builds the theQuery string based on which check boxes are currently checked. The getWhere() method takes theQuery and adds a WHERE clause based on which element in the list box is selected and what value is currently in the text box. By the time getWhere() is finished running, theQuery has a legal SQL SELECT statement that can be used to update the data set.
private void buildQuery(object sender, System.EventArgs e) {
getSelect();
getWhere(); lblQuery.Text = theQuery; runQuery();
} // end buildQuery
After returning from the getWhere() method, the program copies the value of theQuery to the label.
Displaying the Query
It isn’t really necessary to display the query, but I did so for two reasons. Most importantly, the entire program is centered around building a query, so having the query visible all the time gave me a quick indication if my algorithms were correct. (They weren’t at first, but with enough feedback and tweaking, I was able to fix them). While I was testing the program, I took the call to runQuery() out of the buildQuery() method altogether, and only ran runQuery() when I pressed a button. Most of the time, I could tell just by looking at the query whether it would work or not. Once I got the algorithm right, I added the call to runQuery(), because the user will always want to see the results of the query.
There’s nothing wrong with providing feedback to the user. If the user already knows some SQL, the statement will be familiar. If not, this basic form of SQL is close enough to English to make sense even to non−technical users.
Getting the SELECT Clause from the Check Boxes
The SELECT clause is used to determine which fields should be displayed. Check boxes seem like a good option for this kind of input, because any number of them can be selected.
private void getSelect(){
//create the SELECT part of the query from check boxes
theQuery = "SELECT "; bool none = true;
if (chkAgentID.Checked){ theQuery += "AgentID, "; none = false;
}// end if
if (chkCodeName.Checked){ theQuery += "CodeName, "; none = false;
} // end if
if (chkSpecialty.Checked){ theQuery += "Specialty, "; none = false;
} // end if
343
if (chkAssignment.Checked){ theQuery += "Assignment, "; none = false;
}// end if if (none){
theQuery += "*, ";
}// end if
//remove the last comma
theQuery = theQuery.Substring(0, theQuery.Length −2);
theQuery += " FROM Agents"; } // end getSelect
The method begins by setting the value of theQuery to "SELECT ". All of the queries created by this program begin with that word. I then created a Boolean variable called none and initialized it to true. The none variable is used to indicate that none of the check boxes have been checked.
The method then looks at each check box to see if it is checked. If so, none is false (because at least one of the check boxes has been checked) and the name of the current field is appended to theQuery. If, after all the check boxes have been examined, the value of none is still true, I use the asterisk (which stands for all fields) as a field name.
Notice that all the field names end with a comma. SQL requires the list of fields to be separated by commas. It’s easy to put a comma after every field, but SQL does not want a comma after the last field. The problem is determining which field is last. I used a little string manipulation magic to solve this problem. I added a comma after every field name, and then once the field names were finished, I always removed the last two characters from the string, eliminating the last comma and space. (Sneaky, huh? You’ve got to be sly when you’re working with secret agents all the time.) In this program, all queries come from the Agents table, so I just added "FROM Agents" to the end of the query.
To make sure you understand how this is working, run the Visual Query program for a while and take a careful look at the query as you select and deselect check boxes.
Getting the WHERE Clause from the List Box and Text Box
The WHERE clause of an SQL statement is usually used to compare the name of a field with some possible value. To automate the creation of this part of the SQL statement, I used a list box to select field names and a text box to input matching values.
private void getWhere(){
//Create WHERE clause of query from list box if (lstField.SelectedIndex != 0){
theQuery += " WHERE "; theQuery += lstField.Text;
if (lstField.Text != "AgentID"){ theQuery += " = '";
theQuery += txtMatch.Text; theQuery += "'";
}else {
theQuery += " = "; try {
int temp = Convert.ToInt32(txtMatch.Text); theQuery += Convert.ToString(temp);
}catch (Exception exc) { theQuery += "0";
}// end try
344