Click here to Skip to main content
Click here to Skip to main content

Creating a Database Project with Visual Studio

, 25 Aug 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
This article shows how to create a database project to manage database scripts in 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".

Creating new Database project in Visual Studio

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

Here is what it should look like:

View Database Project in Solution Explorer

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.

Add a New Table

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

New Table Script Template

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:

Les 2 Scripts

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

MaBase Project 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.

Connection Properties

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:

Deploy Output

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

DB Created

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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nadege Rouelle
Architect
France France
I'm coding in .Net since 9 years, most with ASP.Net and SharePoint and a little using WPF, MVC, Windows Phone 8 and WinRT technology.
I have learned so much reading others experience and tutorials, or tips so I try to do the same, keeping learning from others of course.
You can also find my blog here : http://sharemstips.wordpress.com/
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 3 PinmemberMember 1076371711hrs 53mins ago 
GeneralMy vote of 5 PinmemberEng.Khalid Jamal27-Oct-14 10:11 
QuestionTo enable IF Exists PinmemberMayank143-Oct-13 2:42 
GeneralMy vote of 3 PinmemberPetr Pechovic26-Aug-11 3:01 
You should use schema view for working with DB artifacts. Not Solution explorer. Because then the engine will manage the diff scripts in better way.
 
Overall in your article you have touched only part of every functionality without really explaining what's behind or what are other options.
 
Missing stuff:
- "Deployment configuration file" and "SQL command variable file" what are they for? How to customize them? How to customize them in team development? How they are related to build configuration? How to create build configuration with specific deployment configuration?
- Creating deployment script and deployment itself. What is necessary to have available. How it is done? What is compared?
- Additional customization of output script, pre-sql-scripts, post-sql-scripts. How they are managed? How to create them?

modified on Friday, August 26, 2011 9:50 AM

Questionit's not working Pinmemberqμάttяό25-Aug-11 5:09 
AnswerRe: it's not working PinmemberNadege Deroussen25-Aug-11 5:16 
GeneralRe: it's not working Pinmemberqμάttяό25-Aug-11 6:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411023.1 | Last Updated 25 Aug 2011
Article Copyright 2011 by Nadege Rouelle
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid