Visual Studio 2010: Database projects
Visual Studio 2008 introduced us database projects. These projects are also present in Visual Studio 2010. I noticed some differences already when building my photo gallery application. Let’s see how these projects work under Visual Studio 2010.
Why database projects?
Database projects are good for more than one reason. Main reason is simple – all database objects are defined in same solution where is code that is using this database. Because database project is project like the others then it is possible to add it to code repository – you get versioning this way.
You can build and deploy database projects so you get SQL-scripts and up to date database on your database server. And… I’ve heard that database projects are also able to incremental deployments.
Structure of database projects
In my last posting about my photo gallery system, Packaging my gallery code, I wrote about how to separate files from web project to other projects. One of there projects was database project called MyGallery2010.Database.
You can see my database project structure on right. Currently I have definitions for tables, primary keys and foreign keys. I think it is enough for now. When project gets some more complex requirements on database then I will add new features.
One difference I noticed is that empty database project is created without deep folder structure in Visual Studio 2010. Visual Studio 2008 database projects created also folders for tables, keys and so on. Visual Studio 2010 let’s you decide how do you want to organize the definitions of your database projects.
The internal structure of database projects is very rich. Take a look at properties section – there are settings for SQL command variables, deployments, permissions and other settings. You can also add database references to Visual Studio database projects.
And one thing more – take a look at Post-Deployment and Pre-Deployment folders. You can add there special SQL-scripts that are run before and after database deployment.
Adding database objects to project
After creating new database project it is easy to start adding definitions for database objects. You can right-click on database project and select database object type from menu as show on following screenshot fragment.
You can also use "Add New Item” menu selection to add new database object definition from database objects menu as shown on following screenshot.
After adding new object definition Visual Studio 2010 creates file for it and adds example definition to it. You can use it to build up your own object definition. Building your database using Visual Studio database projects guarantees pretty well that you pay attention to every object you define. I think it may decrease also bugs in database definitions.
Building and deploying database
Building and deploying database projects is not different from other projects. What is different is output.
You can see that before any deployments database scripts are validated and also object dependencies are analyzed. It means that before deployment the order of changes is already clear and deployment scripts have no errors.