Overview
Visual Studio 2008 Team System has new type of database projects that are much different from those we had in Visual Studio 2005. There is a number of extremely useful features that should make you development much easier. Most of this functionality was previously introduced in Visual Studio 2005 Database Edition. It was released much later than Visual Studio 2005 itself, so some developers (including me) accidentally missed its announcement. Now these tools are integrated into Visual Studio 2008 and replace old database projects.
You start from creating new schema or importing schema of existing database into VS database project, which is presented by separate scripts for each database object. Visual Studio creates quite good catalogue structure by default, but if you have large database then you may add your own folders to better organize your project. As there is separate script file for each database object, it becomes much easier to track changes when your Database Project is put under source control.
After you have imported schema of existing DB (or created new one from scratch), you can easily deploy your DB to any instance of SQL Server and work with it while offline using your private SQL server instance. Visual Studio merges all small separate scripts into several large ones, so you can also use those scripts to manually create database.
To navigate among you database objects, besides Solution Explorer, you can use Schema View pane, that displays database objects as SQL Server Management Studio or Server Explorer does. You also can filter objects if you have large database.
Perhaps the most awesome feature of new database project is design-time validation of your scripts. Visual Studio uses your local database to validate scripts as you type them. It is claimed that you must have either Developer or Enterprise edition of SQL Server locally installed, but it seems that most of the features work ok with SQL Express.
To ease the rename of database objects, Visual Studio provides refactoring wizard. It makes it really painless to rename database tables, columns, views and other objects, because it finds all places where old name was used and provides preview screen with changes for you. There are no other refactoring methods than rename now, so it is not quite clear why the only item "Rename" has been put in "Refactoring" menu. Perhaps there will be some more however...
Another new powerful feature is database unit tests. Visual Studio automates creation of unit tests for database object just as it done for C# and VB code. Good compliment for unit tests is new feature that allows you to load test data in your database. It loads randomly generated values in tables you specify. You can set-up it to load data before executing unit tests. It also can be used for performance testing to foresee how your DB will function on large volumes of data.
And finally, there is handy tool for comparing schemas and data between databases. It is buried in UI, however, and not so easy to find. See screenshots below for help.
Creating Database Project
Here is where you start from.
You can select to create database project using wizard. There is nothing special that can be made only in the wizard, but it allows setup your project quicker, especially if you are not too familiar with this new type of projects yet. Wizard asks you for options of your database and allows you to import schema of existing database.
Project Structure
Here is what we have after wizard finishes. Each database object creation script is placed in a separate file.
Design-time validation
I've intentionally made mistake in a column name. Visual Studio immediately shows error message in Error List pane. Unfortunately, error is not highlighted in code editor, but when you click on error message you get positioned on the correct place in code (this works not always, however).
I love this feature!
Another good use of validation is to check existing database for mistakes. You can import it's schema into new database project and build it to start validation.
Schema View Pane
You can make some changes to schema here and easily navigate through you database.
Refactoring
Yet one very powerful feature. If you had to rename a column in a large DB you would appreciate this new rename helper.
Comparing databases
No need to use 3rd party utilities anymore. You can compare databases or database projects in Visual Studio now. Note the toolbar button that the red arrow pointing at - it starts compare wizard.
Here is how results of comparison look like.
Loading Database with test data
Loading database with test data is very useful for unit tests and also can be used for performance testing. To load you DB with test data you add new item, Data Generation Plan, to you project.
Then you specify what tables to fill with data, how many records to add and start data load by clicking on toolbar button.
Unit Tests
To create Unit Tests for your stored procedure you select it in Schema View and select Create Unit Tests in context menu.
Wizard asking you for a list of procedures to generate unit tests for and details abut project that will host your unit tests.
If you selected to create new project, Visual Studio asks you for it's properties. Note that you can automatically deploy database to your database server and load it with a test data as it is configured in your Data Generation Plan.
And here is how result looks like. You can go on and customize your Unit Tests as you need.
Conclusion
Undoubtedly, new database projects are a great feature. What is cool is that you can set-up really solid solution for your development environment with Visual Studio 2008 and Team Foundation Server. And new features of database projects add missing parts to that. You now can test code in your database objects by running unit tests in nightly builds. And you can validate that only valid code comes to Source Controls using Continuous Integration. That all was possible with C# and VB code previously, but now you need not worry for your stored procedures too.
Even if you do not want to use database project as part of a solution, you may want to use some of its useful features on your stand-alone database:
- You can validate stored procedures, functions and other objects in your database.
- You may want to load you database with test data for performance testing.
- You also can use compare wizard to see changes between your databases.
- And, finally, you can use renaming wizard to easily rename you database objects if you need to.
Here you will find official documentation (that is quite good).