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.   D. Lambert — May 3, 2010 @ 9:37 am    Reply

    Deborah –

    Has this functionality changed since the VS2008 version? I’m using VS2008 DB projects to generate deployment scripts, and I end up hand-editing them to deal with some of the assertions it drops in the top of the script.

    Specifically, in my shop, db scripts have to go through a DBA staff for promotion through environments, and the QA and higher environments are locked down enough that I can’t deploy directly to those DB’s. I end up having to deploy against a schema-equivalent DB that I maintain, and then strip out the server-checking code so this script can be promoted to other environments.

    It would be immensely helpful if I had more control over the generation of this script so that I could make it more environment-neutral.

    Any hope of this in VS2010?

  2.   DeborahK — May 3, 2010 @ 9:47 am    Reply

    Hi D. Lambert –

    If you are using the the Database project under the “Other Projects” node in VS 2008, then this Database project is its replacement. And yes, it is new.

    But if you are referring to the Microsoft SQL Server project under the Database Projects node, I have never used that and don’t know if/how it was changed for VS 2010.

    Hope this helps.

  3.   James Sheldon — May 3, 2010 @ 5:14 pm    Reply

    Deborah – These are very helpful!

    Do you know if you can drop tables using these types of projects. Wasn’t able to see any way to do that.

    Thanks,

    James

  4.   DeborahK — May 4, 2010 @ 12:54 am    Reply

    Hi James –

    Do you have a Data menu with a Schema compare option? It can compare your existing database to another database and it builds in appropirate Drop commands.

    Hope this helps.

  5.   Tom H. — May 5, 2010 @ 11:38 am    Reply

    Hi Deborah,

    Thanks for the informative post!

    Does this method handle DML type scripts? For example, if we need to add a day to a datetime column in one of the tables based on some kind of criteria.

    Also, can it handle upgrades that jump versions? Or do I need to always be pointing to a server that is on the version that my code started at when I began my work?

    Thanks!

  6.   Glen — May 17, 2010 @ 3:06 pm    Reply

    The new database project structure, though sounding good, is the worst thing to happen to our development process, and is significantly holding back our adoption of VS2010!

  7.   DeborahK — May 17, 2010 @ 7:36 pm    Reply

    Hi Glen –

    Can you provide more detail on specifically the issues you have?

  8.   Joe Bloggs — May 20, 2010 @ 2:17 pm    Reply

    Thanks for the information but what I don’t understand is how to post updates that are made to one script (eg a stored procedure) to the DB. The impression I get is that you have to post all changes to the DB via the Deploy process. But if you have multiple people working on the DB or you are working on multiple files and only want to post changes made to one file can you do this like you could in VS2008?

  9.   DeborahK — May 24, 2010 @ 10:29 am    Reply

    Hi Joe –

    You can right-click and select Execute SQL. However, unless this is a new stored procedure, you will get the message “There is alredy an object named ‘xxx’ in the database.

    So you need to change “Create” to “Alter” at the top of your stored procedure before you execute it and then change it back after you execute it.

    It is not a very nice process, but it works.

    Hope this helps.

  10.   En — May 27, 2010 @ 2:39 am    Reply

    Why was the create proc template changed, removing the following:

    “IF EXISTS (SELECT * FROM sysobjects WHERE type = ‘P’ AND name = ‘mysproc’)
    BEGIN
    DROP Procedure mysproc
    END

    GO

    This made deploying so much easier!

RSS feed for comments on this post. TrackBack URI

Leave a comment

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