Asp.net - Testing sql queries - Lynda

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 have 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 are going to query. The table structure of this database connects the titles table to both the publishers and the authors table through keys or joints. Double click on the titles table first and you will see that titles tables added in the background. Then double click on publishers and on authors. You will see the publishers and authors tables added and you will also see that connections are made between the tables automatically. These connections are made because the names of the columns in the table match each other and Visual Web Developer picks that up and decides that it can join the columns during the queries 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 would like to display the title of each book, so I'll click the check box next to title and I would also like the books price and scrolling down a bit, the books publication date. You can vertically expand the size of the panels if you are not able to see all of the columns. Now I would also like to include the publisher's name so I'll check that option and I'll also select the authors 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 pubdate 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 you 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 will see the data that's retrieved from the database. Now let's make a little change. Instead of retrieving data in its natural sort order, I would 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 listing the books from least expensive to most expensive. If you select more than one column in your sort, you will also need to select a Sort Order. Now take a look at the resulting SQL and you will 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 then automatically add them into your code. Instead you will 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 are fairly new to SQL you will find that this is a great learning tool. And even for those web developers who are fairly experienced with the SQL language, you will find that creating SQL code with simple joins and so on is made much speedier by the use of this visual tool.