Microsoft Visual Basic 2005 Step by Step (Step by Step (Microsoft))
One Step Further: Writing SQL Statements to Filter Data
You have used the Data Source Configuration Wizard to extract just the tables and fields you wanted from the Students database by creating a custom dataset named StudentsDataSet. In addition to this filtering, however, you can further organize and fine-tune the data displayed by bound controls by using SQL statements and the Visual Studio Query Builder. This section introduces these tools.
For Visual Basic users who are familiar with Microsoft Access or SQL Server, filtering data with SQL statements is nothing new. But the rest of us need to learn that SQL statements are commands that extract, or filter, information from one or more structured tables in a database. The reason for this filtering is simple: Just as Web users are routinely confronted with a bewildering amount of data on the Internet (and use clever search keywords in their browsers to locate just the information they need), database programmers are routinely confronted with tables containing tens of thousands of records that need refinement and organization. The SQL SELECT statement is one traditional mechanism for organizing database information. By chaining together a group of these statements, programmers can create complex search directives, or queries, that extract just the data that is needed from a database.
Realizing the industry-wide acceptance of SQL statements, previous versions of the Visual Studio and Visual Basic IDEs have included mechanisms for using SQL statements. In addition, Visual Studio 2005 provides a tool called Query Builder for those who have less experience with SQL programming and would prefer to use a visual tool to help them construct the queries. In the following exercise, you'll use Query Builder to further organize your dataset by sorting it alphabetically.
Create SQL statements with Query Builder
On the form, click the InstructorTextBox object (the first bound object that you created to display the names of instructors in the Students database).
Click the Add Query command on the Data menu.
The Add Query command is available when a bound object, such as InstructorTextBox, is selected in the Designer. The Search Criteria dialog box appears, as shown in the following illustration:
This dialog box helps you organize and view your queries, which are created by the Query Builder and consist of SQL statements. The table that your query will filter and organize by default (StudentsDataSet.Instructors) is selected in the Select Data Source Table box near the top of the dialog box. You'll recognize the object hierarchy format used by the table name, which is read as “the Instructors table within the StudentsDataSet dataset.” If you had other tables to choose from, they would be listed in the list box displayed when you click the Select Data Source Table arrow.
Type SortInstructors in the New Query Name box.
This text box assigns a name to your query, and forms the basis of toolbar buttons added to the form. (For easy access, the default arrangement is that new queries are assigned to toolbar buttons within the application you are building.)
Click the Query Builder button in the dialog box to open the Query Builder tool.
The Query Builder allows you to create SQL statements by typing them directly into a large SQL statement text box or by clicking list boxes and other visual tools.
In the Instructor row representing the Instructor field in your dataset, click the cell under Sort Type, and then click the arrow to display the Sort Type list box.
Your screen looks like this:
The SQL ORDER BY statement sorts database records based on a key field and sort order number.
To build an SQL ORDER BY statement that sorts records in the Instructor field in ascending order, click Ascending in the Sort Type list box.
Click the SQL statement text box below the grid pane to update the Query Builder window.
The new query is added to the SQL statement box, and your screen looks like this:
Click OK to complete your query.
Visual Studio closes the Query Builder and displays your new query in the Search Criteria Builder dialog box. The name of the query (SortInstructors) is listed, as well as the SQL statements that make up the sort.
Click OK to close the dialog box and configure the InstructorTextBox object to list names in ascending alphabetical order.
This particular SQL statement does not filter the data, but organizes dataset records in a more useful order when the user clicks a SortInstructors button on a new toolbar at the top of the form. The process has also created a SortInstructorsToolStrip object in the component tray below the form. The Designer and component tray look like this now:
Click Start Debugging to run the program.
Visual Studio loads the form and displays the first record for two dataset objects.
Click the SortInstructors button on the new toolbar.
Your new SQL statement sorts the Instructor records in the dataset and displays the records in their new order. The first record is now Barr, Adam, as shown in the following illustration:
Scroll through the list of records, and verify that it is now in ascending alphabetical order. (The last record should be Wilson, Dan.)
Click the Close button to end the program.
You're on your way with building custom queries by using SQL statements and Query Builder. Database programming is a complex topic, but you have already learned much that will help you build datacentric applications—highly personalized collections of data that benefit the user and his or her computing needs—in Visual Basic. You will continue exploring the theme of rich data access in Chapter 19. And in Chapter 20, “Creating Web Sites and Web Pages using Microsoft Visual Web Developer and ASP.NET,” your final project will be displaying database records on a Web site.