Querying the Books Database

Now that you have seen how to display an entire database table in a DataGridView, we demonstrate how to execute specific SQL SELECT queries on a database and display the results. Although this example only queries the data, the application could be modified easily to execute other SQL statements. Perform the following steps to build the example application, which executes custom queries against the Titles table of the Books database.

Step 1: Creating the Project

Create a new Windows Application named DisplayQueryResult. Rename the Form DisplayQueryResultForm and name its source file DisplayQueryResult.cs, then set the Form's Text property to Display Query Result.

Step 2: Adding a Data Source to the Project

Perform the steps in Section 20.6.1 to include the Books database as a data source in the project.

Step 3: Creating a DataGridView to Display the Titles Table

Drag the Titles node from the Data Sources window onto the Form to create a DataGridView that will display the entire contents of the Titles table.

Step 4: Adding Custom Queries to the TitlesTableAdapter

Recall that invoking a TableAdapter's Fill method populates the DataSet passed as an argument with the entire contents of the database table that corresponds to that TableAdapter. To populate a DataSet member (i.e., a DataTable) with only a portion of a table (e.g., books with copyright dates of 2006), you must add a method to the TableAdapter that fills the specified DataTable with the results of a custom query. The IDE provides the TableAdapter Query Configuration Wizard to perform this task. To open this wizard, first right click the BooksDataSet.xsd node in the Solution Explorer and choose View Designer. You can also click the Edit DataSet with Designer icon (). Either of these actions opens the Dataset Designer (Fig. 20.35), which displays a visual representation of the BooksDataSet (i.e., the tables AuthorISBN, Authors and Titles and the relationships among them). The Dataset Designer lists each table's columns and the autogenerated TableAdapter that accesses the table. Select the TitlesTableAdapter by clicking its name, then right click the name and select Add Query... to begin the TableAdapter Query Configuration Wizard (Fig. 20.36).

Figure 20.35. Viewing the BooksDataSet in the Dataset Designer.

Figure 20.36. TableAdapter Query Configuration Wizard to add a query to a TableAdapter.

(This item is displayed on page 1024 in the print version)

Step 5: Choosing How the TableAdapter Should Access the Database

On the first screen of the wizard (Fig. 20.36), keep the default option Use SQL Statements and click Next.

Step 6: Choosing a Query Type

On the next screen of the wizard (Fig. 20.37), keep the default option SELECT which returns rows and click Next.

Figure 20.37. Choosing the type of query to be generated for the TableAdapter.

Step 7: Specifying a SELECT Statement for the Query

The next screen (Fig. 20.38) asks you to enter a query that will be used to retrieve data from the Books database. Note that the default SELECT prefixes Titles with "dbo.". This prefix stands for "database owner" and indicates that the table Titles belongs to the database owner (i.e., you). In cases where you need to reference a table owned by another user of the system, this prefix would be replaced by the owner's username. You can modify the SQL statement in the text box here (using the SQL syntax discussed in Section 20.4), or you can click Query Builder... to design and test the query using a visual tool.

Figure 20.38. Specifying a SELECT statement for the query.

Step 8: Building a Query with Query Builder

Click the Query Builder... button to open the Query Builder (Fig. 20.39). The top portion of the Query Builder window contains a box listing the columns of the Titles table. By default, each column is checked (Fig. 20.39(a)), indicating that each column should be returned by the query. The middle portion of the window contains a table in which each row corresponds to a column in the Titles table. To the right of the column names are columns in which you can enter values or make selections to modify the query. For example, to create a query that selects only books that are copyright 2006, enter the value 2006 in the Filter column of the Copyright row. Note that the Query Builder modifies your input to be "= '2006'" and adds an appropriate WHERE clause to the SELECT statement displayed in the middle of Fig. 20.39(b). Click the Execute Query button to test the query and display the results in the bottom portion of the Query Builder window. For more Query Builder information, see msdn2.microsoft.com/library/ms172013.aspx.

Figure 20.39. Query Builder after adding a WHERE clause by entering a value in the Filter column.

(This item is displayed on page 1026 in the print version)

(a)

(b)

Step 9: Closing the Query Builder

Click OK to close the Query Builder and return to the TableAdapter Query Configuration Wizard (Fig. 20.40), which now displays the SQL query created in the preceding step. Click Next to continue.

Figure 20.40. The SELECT statement created by the Query Builder.

(This item is displayed on page 1027 in the print version)

Step 10: Setting the Names of the Autogenerated Methods That Perform the Query

After you specify the SQL query, you must name the methods that the IDE will generate to perform the query (Fig. 20.41). Two methods are generated by defaulta "Fill method" that fills a DataTable parameter with the query result and a "Get method" that returns a new DataTable filled with the query result. The text boxes to enter names for these methods are prepopulated with FillBy and GetdataBy, respectively. Modify these names to FillWithCopyright2006 and GeTDataWithCopyright2006, as shown in Fig. 20.41. Finally, click Finish to complete the wizard and return to the Dataset Designer (Fig. 20.42). Note that these methods are now listed in the TitlesTableAdapter section of the box representing the Titles table.

Figure 20.41. Specifying names for the methods to be added to the TitlesTableAdapter.

Figure 20.42. Dataset Designer after adding Fill and Get methods to the TitlesTableAdapter.

Step 11: Adding an Additional Query

Repeat Steps 410 to add another query that selects all books whose titles end with the text "How to Program" and sorts the results by title in ascending order (see Section 20.4.3). In the Query Builder, enter LIKE '%Howto Program' in the Title row's Filter column. To specify the sort order, select Ascending in the Sort Type column of the Title row. In the final step of the TableAdapter Query Configuration Wizard, name the Fill and Get methods FillWithHowToProgramBooks and GeTDataForHowToProgramBooks, respectively.

Step 12: Adding a ComboBox to the Form

Return to Design view and add below the DataGridView a ComboBox named queriesComboBox to the Form. Users will use this control to choose a SELECT query to execute, whose result will be displayed in the DataGridView. Add three items to queriesComboBoxone to match each of the three queries that the TitlesTableAdapter can now perform:

SELECT ISBN, Title, EditionNumber, Copyright FROM Titles SELECT ISBN, Title, EditionNumber, Copyright FROM Titles WHERE (Copyright = '2006') SELECT ISBN, Title, EditionNumber, Copyright FROM Titles WHERE (Title LIKE '%How to Program') ORDER BY Title

Step 13: Customizing the Form's Load Event Handler

Add a line of code to the autogenerated DisplayQueryResultForm_Load event handler, which sets the initial SelectedIndex of the queriesComboBox to 0. Recall that the Load event handler calls the Fill method by default, which executes the first query (the item in index 0). Thus, setting the SelectedIndex causes the ComboBox to display the query that is initially performed when DisplayQueryResultForm first loads.

Step 14: Programming an Event Handler for the ComboBox

Next you must write code that will execute the appropriate query each time the user chooses a different item from queriesComboBox. Double click queriesComboBox in Design view to generate a queriesComboBox_SelectedIndexChanged event handler (lines 4261) in the DisplayQueryResult.cs file (Fig. 20.43). Then to the event handler add a switch statement that invokes the method of titlesTableAdapter that executes the query associated with the ComboBox's current selection (lines 4760). Recall that method Fill (line 50) executes a SELECT query that selects all rows, method FillWithCopyright2006 (lines 5354) executes a SELECT query that selects all rows in which the copyright year is 2006 and method FillWithHowToProgramBooks (lines 5758) executes a query that selects all rows that have "How to Program" at the end of their titles and sorts them in ascending order by title. Each method fills BooksDataSet.Titles with only those rows returned by the corresponding query. Thanks to the data binding relationships created by the IDE, refilling booksDataSet.Titles causes the TitlesDataGridView to display the selected query's result with no additional code.

Figure 20.43. Displaying the result of a user-selected query in a DataGridView.

Figure 20.43 also displays the output for DisplayQueryResultForm. Figure 20.43(a) depicts the result of retrieving all rows from the Titles table. Figure 20.43(b) demonstrates the second query, which retrieves only rows for books with a 2006 copyright. Finally, Fig. 20.43(c) demonstrates the third query, which selects rows for How to Program books and sorts them in ascending order by title.