20.7. 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
First, create a new Windows Application named DisplayQueryResult. Rename the Form FrmDisplayQueryResult and name its source file FrmDisplayQueryResult.vb, 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 and to create the BooksDataSet.
| 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, right click the BooksDataSet.xsd node in the Solution Explorer and choose View Designer, or click the Edit DataSet with Designer icon ( ) in the Data Sources window. 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. 
| 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). If 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 that modify the query. For example, to create a query that selects only books that are copyright 2006, type 2006 in the Filter column of the Copyright row then press Enter. 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.  
| | | 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. 
| | | 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 '%How to 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 the Form's Design view and add a ComboBox named cboQueries below the DataGridView on the Form. Users will use this control to choose a SELECT query to execute. The results will be displayed in the DataGridView. Add three items to cboQueriesone 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 FrmDisplayQueryResult_Load event handler, which sets the initial SelectedIndex of the cboQueries 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 to 0 causes the ComboBox to display the query that is initially performed when FrmDisplayQueryResult 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 cboQueries. Double click cboQueries in Design view to generate a cboQueries_SelectedIndexChanged event handler (lines 3043) in the FrmDisplay QueryResult.vb file (Fig. 20.43). In the event handler, add a Select Case statement (lines 3442) to invoke the method of TitlesTableAdapter that executes the query associated with the ComboBox's current selection. Recall that method Fill (line 36) executes a SELECT query that selects all rows, method FillWithCopyright2006 (line 38) executes a SELECT query that selects all rows in which the copyright year is 2006 and method FillWithHowToProgramBooks (lines 4041) 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. 1 ' Fig. 20.43: FrmDisplayQueryResult.vb 2 ' Displays the result of a user-selected query in a DataGridView. 3 Public Class FrmDisplayQueryResult 4 ' Click event handler for the Save Button in the 5 ' BindingNavigator saves the changes made to the data 6 Private Sub TitlesBindingNavigatorSaveItem_Click( _ 7 ByVal sender As System.Object, ByVal e As System.EventArgs) _ 8 Handles TitlesBindingNavigatorSaveItem.Click 9 10 Me. Validate() 11 Me.TitlesBindingSource.EndEdit() 12 Me.TitlesTableAdapter.Update(Me.BooksDataSet.Titles) 13 End Sub ' TitlesBindingNavigatorSaveItem_Click 14 15 ' loads data into the BooksDataSet.Titles table, 16 ' which is then displayed in the DataGridView 17 Private Sub FrmDisplayQueryResult_Load(ByVal sender As System.Object, _ 18 ByVal e As System.EventArgs) Handles MyBase .Load 19 ' TODO: This line of code loads data into the 'BooksDataSet.Titles' 20 ' table. You can move, or remove it, as needed. 21 Me.TitlesTableAdapter.Fill(Me.BooksDataSet.Titles) 22 23 ' set the ComboBox to show the default query that 24 ' selects all books from the Titles table 25 cboQueries.SelectedIndex = 0 26 End Sub ' FrmDisplayQueryResult_Load 27 28 ' loads data into the BooksDataSet.Titles table based on 29 ' user-selected query 30 Private Sub cboQueries_SelectedIndexChanged( _ 31 ByVal sender As System.Object, ByVal e As System.EventArgs) _ 32 Handles cboQueries.SelectedIndexChanged 33 ' fill the Titles DataTable with the result of the selected query 34 Select Case cboQueries.SelectedIndex 35 Case 0 36 TitlesTableAdapter.Fill(BooksDataSet.Titles) 37 Case 1 ' books with copyright year 2006 38 TitlesTableAdapter.FillWithCopyright2006(BooksDataSet.Titles) 39 Case 2 ' How to Program books, sorted by Title 40 TitlesTableAdapter.FillWithHowToProgramBooks( _ 41 BooksDataSet.Titles) 42 End Select 43 End Sub ' cboQueries_SelectedIndexChanged 44 End Class' FrmDisplayQueryResult
   |
|
Figure 20.43 also displays the output for FrmDisplayQueryResult. 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. |
