Creating a Database Project with Visual Studio

Introduction

Important: This kind of project can only be used with Premium or Ultimate version. It does not exist in Professional or Express edition.

First, why should you use this kind of project?

Simple! This kind of project automatically generates scripts to deploy your database. You just have to create a file for each element you want to create (table, stored procedures, triggers ...) and Visual Studio would generate a script file that will execute every query in the correct order. You can use it with SQL Server 2005, 2008 or 2008 R2.

We will see in this article how to create a Visual Studio project and deploy it to a database server.

Creating the Visual Studio Project

Open your Visual Studio and go to "New Project -> Database -> SQL Server -> SQL Server 2008 Database Project".

Give a name to the project and click on the OK button.

Here is what it should look like:

Now, we create our first table. In the treeview, do a right click on Tables -> Add -> Table and check that Table is selected in the new window.

About the filename, I simply use the table name. So, a new file opens with a template to help you create a table:

You can now modify it like this:

CREATE TABLE [dbo].[MaTableA]
(
ID_MaTableA int NOT NULL PRIMARY KEY IDENTITY(1,1),
Name nvarchar(256) NOT NULL,
)

Then we create a second table to have some realistic database schema:

CREATE TABLE [dbo].[MaTableB]
(
ID_MaTableB int NOT NULL PRIMARY KEY IDENTITY(1,1),
Name nvarchar(256) NOT NULL,
ID_MaTableA int NOT NULL FOREIGN KEY REFERENCES MaTableA(ID_MaTableA)
)

We can see our two scripts in the project:

Let's deploy our project. First, we need to configure the connection string. Right click on the project and select Properties.

Deployment information should be configured in Deploy tab. What do we have there?

• Configure deployment settings: allow to specify if parameters should be used for the project or only for our current environment.
• Deploy action: allows to determine which action should be done when we deploy: "Create deployment script" or "Create deployment script and deploy it".
• Deployment script name: allows to specify the script name.
• Target database settings: Here, we will configure the target database. Visual Studio will use this database to generate a script to deploy the new things only. First, we configure the connection strings, then we specify the database name.
• Deployment configuration file: File containing deployment parameters
• SQL command variable file

So, let's see how to configure the connection. Click on Edit on the right of Target Connection.

Add server name, choose the authentication and add database name. It does not matter if the database does not exist. If Visual Studio doesn't find it, it will add the creation of the database in the deployment script. Test the connection (test failed if database does not exist) and click OK. Change deployment configuration to have the script generated and deployed.

So now, we are ready to deploy. Right click on the database project and select Deploy.

To check that everything went right, open the Output window, you should see something like that:

Using the Server Explorer, we go to the database server:

Our database is here and has the two tables we've defined. The SQL script is in the directory sql\Debug of our project. Go there to know what it looks like.

In the treeview of the project, we can also find pre and post deployment scripts. These scripts would be executed respectively before and after the script generated by the project. The first one can allow to initialize some parameters whereas the second one can allow to add data.

Here is the end of this first article. This first part is a simple approach of this kind of project. We will see more features in the second part.

Points of Interest

This kind of project is really useful for managing database script and to work in a team. Every member of a team can make changes and merge their scripts with other team members in a very useful way. And you can keep versioning on each object.

History

• 25^th August, 2011: First version
• 25^th August, 2011: Update: Added Visual Studio version