Creating A New Database Project

By: Armando Prato

Overview

We will now go over creating the initial database project.

Explanation

After starting Visual Studio 2010, create a new project as follows

You will be presented with the following New Project screen. The Name and Solution name will contain default values in them. You can change these as you see fit. If you're running Visual Studio 2010 Professional or greater, you should have a Database option available. Navigate down to the Advanced option under SQL Server. We're going to create a new database project named DemoDB and create a new solution called Tips to house it. You can also choose, if you wish, the root Location on the server where you'd like to create files for the solution and project. We're using SQL Server 2008R2 so we choose SQL Server 2008 Database Project as our project type. After choosing the project type and entering the project and solution names, we click Ok

Now that the solution and it's project has been built, The right hand side of the application should display a couple of new panes. The first will be the Solution Explorer where you can see the various support files that the project will use in building/upgrading a database. The second will be a Schema View pane that has a layout that's similar to what you'd see with Management Studio. While both panes allow for object maintenance, I do the bulk of my object maintenance using the Schema View (more on creating objects in a bit).

In the Solution Explorer, there are several helper files under Properties. These editable files can control the behavior and final output of your database.

• Database.sqlcmdvars can be used to define SQLCMD variables that can be referenced within the project
• Database.sqldeployment can be used specify database options such as recovery model and/or SET options (i.e. SET ANSI_NULLS ON)
• Database.sqlpermissions can be used to maintain user database permissions
• Database.sqlsettings can be used to set options that can control behavior when creating/refactoring your database (i.e. constraint checks, create backup)

Under the Scripts folder, you will note 2 sub folders called Post-Deployment and Pre-Deployment which contain .sql scripts. The act of creating or refactoring a database is referred to as a "deployment". These folders and scripts are known to Visual Studio and are used to control actions that occur before deployment and after deployment. For instance, after your database has been deployed, you may want to insert initial seed data into your tables. You can accomplish this by editing Scripts.PostDeployment.sql to perform the inserts.