Join David Gassner for an in-depth discussion in this video Testing SQL queries, part of ASP.NET Essential Training.
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.
- Storing data with SQL Server
- Using the GridView control to present and edit dynamic data
- Creating a data entry system
- Attaching external CSS files
- Creating pages to log in and authenticate visitors
- Installing Internet Information Services (IIS) on Windows XP and Windows Vista
- Deploying an ASP.NET website on IIS
Skill Level Beginner
Q: When trying to create a new database, after pasting the text into the SQL Management Suite and executing the query, the database is not created and the following message appears:
Database 'mybookstore' does not exist. Make sure that the name is entered correctly.
What is causing this error?
A: The database must be explicitly created before the script is run. Start by right-clicking on the Databases item in the left panel, then follow the prompts to create the database. Then retry the query.
Q: I am running into problems installing the latest version of ASP.NET. Has the installation procedure changed since this tutorial was recorded?
A: The installation process for the newest version of ASP.NET and its associated tools is a little different than in ASP.NET 3.5, which was used to record this course. You can download Microsoft Web Platform Installer 2.0 from:
<a href="http://www.microsoft.com/web/downloads/platform.aspx" target="blank">http://www.microsoft.com/web/downloads/platform.aspx</a>
Microsoft Web Platform Installer 2.0 includes everything you need:
Visual Web Developer 2010?
SQL Server Express
You must have one of the following operating systems:
Windows Vista SP1?
Windows XP SP2+?
Windows Server 2003 SP1+?
Windows Server 2008?
Windows Server 2008 R2
You must have administrator privileges on your computer to run the Web Platform Installer.
Q: This course was updated on 2/13/2013. What changed?
A: Since this course was recorded, Microsoft has released both ASP.NET 4.5, the latest version of the server-side web application server, and Visual Studio Express 2012 for Web (the successor product to Visual Web Developer). Both have been adapted to work on Windows 8. There will be some visual changes and some functional changes, but most of the server-side code shown in the course is the same. This update provides a map for those working with the latest software so they can navigate their way through the course.
In particular, we added <em>What's new</em> movies for both ASP.NET 4 and 4.5, a movie explaining the significance of the update, a movie on installing SQL Server Express 2012, and one on exporting database scripts in SQL Server Management Studio 2012, as well as updates to visuals throughout the course.
Q: In the chapter on user authentication, an authentication error results when I try to use the Login component or register a new user. How do I fix this?
A: This is a known error that can occur when using the original release of Visual Studio 2012 Express for Web. Update your copy of Visual Studio for Web to at least maintenance release 1, and then try the exercise again.