| The Query Builder consists of four panes. The functions of the Query Builder are accessed via context menus , since the builder is running as a modal form. The builder can also be accessed other ways nonmodally, such as for designing views. When it is displayed nonmodally, a toolbar appears with the functions accessible from there. The top pane is the diagram pane. Here you can see your tables and joins represented graphically. The context menu for the diagram pane appears as in Figure 6.6. Figure 6.6. Diagram pane context menu.  You can access the context menu by clicking the right mouse button over the diagram pane, but, of course, you already knew that! The menu functions are: -
Run You can run most queries to test them. Do not run queries that make changes to data, as you will not be able to reverse them. For select queries, the output appears in the output pane on the bottom of the designer. -
Add Table Opens the Add Table dialog, shown in Figure 6.7. You may choose from tables, views, or functions that return data. Note that you may add the same table more than once to create a self-join . The system automatically creates an alias for the subsequent instances. You may add tables by clicking the Add button or by double-clicking the table or view name . You can change the group by options and select aggregate functions and other options. You don't want to use Group By because it may render the query read-only and the DataAdapter may not be able to create the other three update queries from it. If all you needed were to read the data, then this would be acceptable. Some types of Group By queries are acceptable and it will be able to create the Update statements from them. Figure 6.7. The Add Table dialog.  -
Select All Selects the SQL statement. -
Group By Selecting this option changes the query into a Group By query (see Figure 6.8). These queries are useful for summarizing data. Figure 6.8. Query Builder with Group By selected.  -
Change Type This option allows you to change the query type from select to other types, such as insert or update. You really don't want to do this at this point because you want to use a select query that returns data. See Figure 6.9. Figure 6.9. The Change Type submenu.  -
Show Panes, Hide Pane Turns the display of the four panes of the query builder on or off. -
Verify SQL Syntax When the cursor is over the SQL Text pane this option appears. It allows you to have SQL Server check the syntax of the query. This is useful for validating that a query will compile before using it inside a program. Also, if you type in a query manually this will cause SQL Server to parse the query and fill in the diagram pane for you. -
Navigation Items These items appear when you are over the Results pane and the query has been run. They allow the typical next , previous, and so forth. This is self-explanatory. The Advanced options (see Figure 6.10) button allows you to customize how the wizard will create the Update, Insert, and Delete statements. (Why do they insist on calling anything that requires an additional dialog box "advanced"? Which "nonadvanced" users are going to be configuring a DataAdapter?) The dialog is shown in Figure 6.10. Figure 6.10. The Advanced SQL Generation Options dialog.  By default, the Adapter will create Insert, Update, and Delete SQL statements based on the Select statement you provided. If you intend the DataAdapter to read data only, you can save some overhead by not creating the SQL statements. To do this, clear the first check box. This will also disable the other two check boxes, as they have no meaning if you are only reading from the database.  |
