This chapter is from the book
This chapter is from the book
This chapter is from the book
Queries
Similar to what we mentioned regarding the Create and Change scripts, you can also create SQL queries manually within the Visual Studio environment. However, except for the most trivial queries, using the Query Designer to design the queries graphically is much more efficient and less error-prone.
We can demonstrate use of the Query Designer by creating a parameter update query that updates the wholesale prices of all of the products in our inventory by a specified percentage.
Open the Solution Explorer and right-click on any folder node other than Database References. From the context menu displayed, select the Add Query menu item. Doing so displays the Add New Item dialog box, shown previously in Figure 8.2.
Select the Database Query template, set the name to UpdateWholesale.dtq, and click on the Open button. The Query Designer is now displayed, as shown in Figure 8.13.
Figure
8.13 The Query Designer opened to create a new database queryFrom the Add Table dialog, add the tblInventory table and then click on the Close button to dismiss the dialog.
We need to change the query type from a Select to an Update query. We do so by selecting the Change Type menu item from the main Query menu, or by clicking on the Change Type button on the Query toolbar, and then selecting the Update menu item.
In the Diagram pane of the Query Designer, click on the checkbox for the WholesalePrice field, as that is the field we're going to update.
In the Grid pane, enter the following formula in the New Value column in the row for the WholesalePrice field that we just added:
WholesalePrice * (1 + ? / 100)
This formula accepts a parameter value, which is the percentage that the wholesale price should be increased by. When the query is executed, the question mark in the formula will be replaced by the parameter value that is provided. The Query Designer should look like that shown in Figure 8.14.
Figure
8.14 The query to update the WholesalePrice field in the tblInventory
tableAlthough we could now run and test our query within the Query Designer, we will (soon) do it from the Solution Explorer.
Close the Query Designer and click on the Yes button when prompted to save the changes to the UpdateWholesale.dtq query.
Double-click on tblInventory in the Server Explorer to display all the current data in that table. These are the values before we run the query script to update the wholesale price.
NOTE
You may want to take a snapshot of the data so that you can easily verify the new prices after running the update query. You can do so by selecting all the rows that are being displayed and then copying and pasting them in a Notepad file (or any other tool of your liking).
Similar to what we showed previously with a script, we can run a query by dragging and dropping it onto a database reference. We can also run it on the default reference by right-clicking on the query that we want to run and selecting the Open menu item.
NOTE
The context menu displayed when you right-click on a query also contains Design and Design On menu items that open the Query Designer against the default or a specified database reference.
Drag and drop the UpdateWholesale query onto the reference for the Novelty database in the Solution Explorer.
The Execute Scripts or Queries dialog box is shown, giving us a chance to confirm that we want to run the script on the specified database. Click on the Yes button to continue and to run the query.
The Define Query Parameters dialog box is shown because our query contains a parameter. Enter a value—say, 10—as the percentage increase for the wholesale prices. Click on the OK button to continue.
A message box is displayed, stating the number of rows affected by the query. Click on OK to dismiss it.
Repeat step 9 to again display the data in the tblInventory table. The table should contain the modified wholesale prices, as shown in Figure 8.15.
)
Figure
8.13)
Figure
8.14){kind=link}
)
Figure
8.15 The data in the tblInventory table, after increasing the WholesalePrice
field by 10 percent