Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

May 3, 2010

VS 2010 Database Project: Building and Deployment

Filed under: C#,VB.NET,Visual Studio @ 12:52 am

The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to manage the scripts for your database from within Visual Studio. This includes building the schema scripts from the individual database object scripts and deploying the scripts back to the database.

One of the ways that the new VS 2010 Database project is significantly different from the Database project available in prior versions of Visual Studio is the build and deployment process.

The old VS 2003/2005/2008 Database project was just a container for your scripts. You could right-click on the database in Server Explorer to generate the script to the project. When you made changes to the script, you could right-click on the script and select to apply the script to the database. But you had to manually perform these tasks.

And with the old style Database projects, it was easy to forget to apply your script changes to the database. It also was not easy to keep everything in synchronization. For example, if you later made a table change, it is easy to forget to adjust the related stored procedures as well.

Instead of being a simple repository, the new 2010 Database project is alive. The scripts in your Database project define your database just like the files in your code projects define your application. And the build and deployment process can apply the changes to your actual database.

This post details how to use the VS 2010 Database project and deploy your scripts back to your database.

Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then write the scripts into your project from an existing database as detailed in this prior post. Or write your own scripts in the project.

After you’ve made any changes to those scripts, you will want to deploy them back to the database.

To build the Database project, right-click on the Database project and select Build. OR you can use the Build menu.

When you build the Database project, Visual Studio validates your scripts and writes the database schema to a file with a .dbschema extension.

image_thumb7

To deploy the scripts to your database, right-click on the Database project and select Deploy. Or you can use the Build menu.

When you deploy the Database project, Visual Studio builds a deployment script.

image_thumb9

You can then run that one script to apply all of the information from your database object scripts to the development database.

But if you want to ensure that your development database is always up to date with the scripts in your Database project, you can set your Database project properties so that any changes are automatically deployed to the development database when you run your application (or build the solution).

Double-click on the Properties node of the Database project in Solution Explorer to open the Database project properties. Select the Deploy tab:

image

Set the Deploy action to "Create a deployment script (.sql) and deploy to the database". Then use the Edit button to the right of the Target connection (cut off in the above screen shot) to set the connection to your development database. Once you set these project settings, they will remain set unless you change them.

Each time you deploy the Database project, using the menu options or by just running the application within Visual Studio, Visual Studio will immediately apply all script changes directly to the database.

image

In this example, I added a table script for an Invoice table. When I ran the application, Visual Studio built and deployed the Database project scripts, creating the Invoice table in the database.

Use these features of the Database project any time you want to keep the master copy of your database schema defined in your scripts and ensure that the schema is in sync every time you run your application within Visual Studio.

Enjoy!

34 Comments

  1.   CT — December 15, 2011 @ 11:08 am    Reply

    i believe the real strength is that these projects can be deployed to TFS and tracked in source control.
    i believe that is a very important feature to track the changes and what changes were made for what purpose.

    from a corporate perspective, being able to create deployments scripts is an enormous feature if a production team is deploying the projects over SIT, QA, Prod phases.

    maybe someone has already mentioned it, if you are creating the db for the first time, you can first create the db in sql server studio to a point where you are satisfied, with all procs, permissions etc, and get VS to import all the scripts for you from sql server.

    this project mostly seems to track the changes in a corporate environment and i find it amazingly helpful to create delta scripts, which contain only the changes i made.

  2.   Build Error in DB project VS 2010 — February 2, 2012 @ 6:48 am    Reply

    I have upgrade the VS2008 to VS2010, i am getting the below error in the DB projects.

    The database is not available in the local manchine, it’s presented in seperate server.
    I don’t need to deploy the project, but need to get build successfully.

    Done executing task “SqlBuildTask” — FAILED.
    Done building target “DspBuild” in project “MyOperation.dbproj” — FAILED.
    Done executing task “CallTarget” — FAILED.
    Done building target “DBBuild” in project “MyOperation.dbproj” — FAILED.
    Done building project “MyOperation.dbproj” — FAILED.

    Help me if any one know!!

  3.   Bill — February 24, 2012 @ 9:36 am    Reply

    All was working fine until I introduced a script with 49000 insert statements for static data. we use a post deploy script via SQLcmd. I receive an outofmemoryexception. You might argue that this amount of data should be run in from outside the project, but why should it.

  4.   harshal — June 4, 2012 @ 2:23 am    Reply

    I tried testing connection to database i have created it shows successful but disconnects when i run my window application
    and shows exception unhandled database not found

    i tried re installing visual studio and sql express edition too,
    but show same error

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2021 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs