Testing SQL queries

After defining your database connection, you can then use Visual Web Developer to create and test SQL queries. To do this, go to the Database Explorer panel. I've previously configured my Database Explorer panel so that it slides in from the right. Then go to the Database Connection, right-click, and select New Query. In order to query the database, you define which tables you're going to query. The table structure of this database connects the Titles table to both the Publishers and the Authors Table through keys, or joins. Double-click on the Titles table first and you'll see the Titles table added in the background. Then double-click on Publishers and on Authors. You'll see the Publishers and Authors tables added, and you'll also see that connections are made between the tables automatically. These connections are made because the names of the columns in the tables match each other, and Visual Web Developer picks that up and decides that it can join the columns during the query's execution. Now close the Add Table dialog and take another look at the diagram of the query. You can drag the table icons around as you need to and also resize or even close the different panes of the query interface.

The next step is to indicate which columns of the tables you want to include in your query results. For example, I'd like to display the title of each book, so I'll click the checkbox next to Title. And I'd also like the book's price, and, scrolling down a bit, the book's publication date. You can vertically expand the size of the panels if you're not able to see all of the columns. Now, I'd also like to include the publisher's name, so I'll check that option, and I'll also select the author's first name and last name. Take a look at the SQL code that's being generated in the third pane, the SQL pane. It's a select statement, which retrieves the title, price, and pub date from titles, the pub name column from publishers, and the author's last name and first name, and then joins the tables on the appropriate columns. Now, to test your query, go to the toolbar and look for the icon with the exclamation mark. This is the Execute SQL button. Click the button and then in the bottom pane, which is your results pane, you'll see the data that's retrieved from the database. Now let's make a little change. Instead of retrieving the data in its natural sort order, I'd like to sort it in order by price. So I'll go to the row that represents the price and I'll change the sort type to Ascending so that I'm sorting the books from least expensive to most expensive. If you select more than one column in your sort, you'll also need to select a Sort Order. Now take a look at the resulting SQL, and you'll see that an Order By clause has been added to the SQL statement that causes the data to be returned in that order. Once again, execute the SQL statement, drag upwards so that you can see the results, and you should see that the data is returned in ascending order by price. The purpose of the query interface is to allow you to model and generate SQL statements. You can't actually take these SQL statements and automatically add them into your code.

Instead, you'll just need to copy and paste the SQL code that's been generated and use it in your code as needed. But Visual Web Developer 2008 Express Edition helps you do this easily. If you're fairly new to SQL, you'll find that this is a great learning tool. And even for those web developers who are fairly experienced with the SQL language, you'll find that creating SQL code with simple joins and so on is made much speedier by the use of this visual tool.