New VSTS Tool Makes Database Dev Easier
The new Visual Studio 2005 Team Edition for Database Professionals, scheduled to ship at the end of this year, lets you compare database schemas, compare table data, generate test data for your database, and more.
- By Jeff Levinson
Recently, Microsoft announced an addition to the Visual Studio Team System line: the Visual Studio 2005 Team Edition for Database Professionals. This addition to Team System, due to be released at the end of 2006, is good news for developers who work with databases. It lets you add new database objects through your project, compare database schemas (even if they aren't part of your Database Project), compare the data contained within a table for differences, reverse-engineer objects from a database to a project, generate test data for your database, and write unit tests against database objects. I'll show you how each of these tools works together to make database development easier.
Comparing Database Schemas
Until now there has been no way to compare database schemas using Microsoft tools; you had to buy an additional product such as ERwin, which would compare schemas for you. The Visual Studio 2005 Team Edition for Database Professionals provides the same functionality that ERwin provides, through its SchemaCompare tool (see Figure 1).
This view is a simple, straightforward comparison between objects. Objects marked with Skip are identical, Update indicates that the definition has changed, and Create indicates that the item doesn't exist in the target database or project. The object definitions are shown at the bottom of the window, with the differences highlighted. You can partially see that there's a second foreign key to the Products table that's not in the database project (see Figure 1). You can filter this view, update the target schema, or write the updates to a text file to analyze or to run later.
Comparing Table Data
One nice feature the Team Edition for Database Professionals offers that ERwin doesn't is the ability to compare data within a table between two different schemas. This DataCompare tool is extremely useful when you're trying to diagnose possible problems between, say, the development and production environments. The comparison is easy to make (between all tables in two databases or just between a select few) and the results are easy to read.
Generating Test Data
One of the major hassles that application developers or DBAs must endure is generating test data to test applications or stored procedures. Or, in the case of SQL Server 2005, the challenge with testing CLR methods is inserting appropriate test data (or any test data, for that matter). The new DataGenerator tool makes this process fairly simple. To generate data, simply add a Data Generation Plan to your solution, select the table(s) to generate data for, and choose the appropriate generator (see Figure 2).
You can select the number of rows to generate, cardinality between tables, and the ratio of records to insert. For the specific table you can select a variety of generator types (more generators are being planned for the release), or you can create your own data generation type.
Database Unit Testing
Another addition you'll appreciate is the Database Unit Testing feature. With Team System you had to write unit tests against your data access code and you could not directly test the database objects themselves. Now you can test any object you can directly invoke—from tables to stored procedures to functions—using the same framework you use for all the other unit test types (see Figure 3).
You create a database unit test through a graphical editor (although you can change the code that it generates). The top of the window allows you to select a current test or add additional tests. You can either code pre- or post-conditions or the test itself through this window. The bottom portion of the test window allows you to specify which conditions you would like to test for.
As of this writing, you can test execution time, non-empty result sets, or a specific row count. Expect this list to change and to become extensible before the release. You can test multiple conditions against multiple result sets. For example, say you have a stored procedure that returns two different result sets. You might want to test the first result set for nine rows (see Figure 3) and the second set for 36 rows. With this framework, you can test for both in the same test simply and easily.
The Visual Studio 2005 Team Edition for Database Professionals will decrease the amount of time it takes to build the database portion of an application, increase the reliability of your database, and provide an easy means of regression testing. You'll also be able to migrate schemas and data easily, and the best news is that you can place them all under source code control. The fact that everything is integrated into a familiar and easy-to-use tool is just the icing on the cake.
Jeff Levinson is the Application Lifecycle Management practice lead for Northwest Cadence specializing in process and methodology. He is the co-author of "Pro Visual Studio Team System with Database Professionals" (Apress 2007), the author of "Building Client/Server Applications with VB.NET" (Apress 2003) and has written numerous articles. He is an MCAD, MCSD, MCDBA, MCT and is a Team System MVP. He has a Masters in Software Engineering from Carnegie Mellon University and is a former Solutions Design and Integration Architect for The Boeing Company. You can reach him at Jeff.Levinson@nwcadence.com.