Debugging Databases with Visual Studio and SQL Server
Moving down, the next function in Server Explorer is Tables. We used this to define the database in earlier articles in this series. But just as you can use QuickWatch and the Immediate window to work with the actual values stored in VB.NET variables, you can use this to work with the values stored in database tables. To do this, just right-click a table and select Show Table Data.
--------
Click Here to display the illustration
--------
And you can do far more than just show the data. You can add and delete rows and change values too. One caution: the data isn't updated until the database is refreshed and the display in Visual Studio is refreshed too. So if you're making a change to a database by running the code, don't expect to see the change automagically in the Show Table Data view. And don't expect to see a change you make in that view to be in the database until the change is saved either. It doesn't work like that.
This is a little more understandable if you pay close attention to the title on the tab: Recipes: Query. Show Table Data is actually just the result of a SQL query statement. You can see what that query is by right-clicking the pane, selecting the Pane menu item, and then clicking SQL. The illustration below shows that Show Table Data is just the result of the SQL query:
You can "refresh" the information by running the query again. That's what Execute SQL does.
--------
Click Here to display the illustration
--------
This is all good information for debugging, but the real payoff comes when you start writing your own SQL queries. The illustration below shows a different query that was entered into a query window and executed.
--------
Click Here to display the illustration
--------
In addition to being able to display exactly what you want to see, you can debug your SQL statements this way. Enter what you think is the correct SQL statement for your code and see if it works. Visual Studio will even write your queries for you when you use the New Query feature of Server Explorer. We'll see more of this in a later article when the SQL gets more complex and harder to write.
As you can see, there's a huge amount of power and flexibility here. And that's one of the problems. There are so many different ways to use it that it's easy to lose sight of "the big picture". In the next article in this series, Visual Basic, Visual Studio, ADO.NET, and SQL Server, the goal is to see how the work we have done so far fits into the overall picture. But two new objects that critical in database programming, DataAdapter and DataSet, are also introduced.
--------
Click Here to display the illustration
--------
And you can do far more than just show the data. You can add and delete rows and change values too. One caution: the data isn't updated until the database is refreshed and the display in Visual Studio is refreshed too. So if you're making a change to a database by running the code, don't expect to see the change automagically in the Show Table Data view. And don't expect to see a change you make in that view to be in the database until the change is saved either. It doesn't work like that.
This is a little more understandable if you pay close attention to the title on the tab: Recipes: Query. Show Table Data is actually just the result of a SQL query statement. You can see what that query is by right-clicking the pane, selecting the Pane menu item, and then clicking SQL. The illustration below shows that Show Table Data is just the result of the SQL query:
SELECT *FROM RecipesYou can "refresh" the information by running the query again. That's what Execute SQL does.
--------
Click Here to display the illustration
--------
This is all good information for debugging, but the real payoff comes when you start writing your own SQL queries. The illustration below shows a different query that was entered into a query window and executed.
--------
Click Here to display the illustration
--------
In addition to being able to display exactly what you want to see, you can debug your SQL statements this way. Enter what you think is the correct SQL statement for your code and see if it works. Visual Studio will even write your queries for you when you use the New Query feature of Server Explorer. We'll see more of this in a later article when the SQL gets more complex and harder to write.
As you can see, there's a huge amount of power and flexibility here. And that's one of the problems. There are so many different ways to use it that it's easy to lose sight of "the big picture". In the next article in this series, Visual Basic, Visual Studio, ADO.NET, and SQL Server, the goal is to see how the work we have done so far fits into the overall picture. But two new objects that critical in database programming, DataAdapter and DataSet, are also introduced.
