Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

May 2, 2010

VS 2010 Database Project: An Introduction

Filed under: C#,VB.NET,Visual Studio @ 7:54 pm

Visual Studio 2010 has a new Database Project. The scripts in this new Database project define your database just like the files in your code projects define your application.

The prior Database project (the one under the "Other Projects" node in VS 2003/2005/2008 as detailed in this prior post), was simply a storage container for your database scripts.

The new Database project is alive. Like the old Database project, it retains your database scripts, but it also has Intellisense, build-time validation, and automatic deployment features. It allows you to keep the definition of the database in your scripts and ensure that the scripts are deployed to your development database.

This is the first in a series of posts on using the new Database project. This post provides the steps for adding a Database Project to any solution in either VB or C#.

1. Right-click on your solution and select Add | New Project. Or select File | New Project from the main menu.

The Add New Project dialog is then displayed.

image

2. Open the Database node in the Installed Templates panel on the left and select SQL Server.

3. Select SQL Server 2008 Database Project from the middle panel, enter a name for the project, and click OK.

NOTE: If you have SQL Server 2005, you can select the SQL Server 2005 Database Project instead.

Visual Studio then creates the new project and adds it to your Solution Explorer:

image

4. Use the Database project to write the scripts for a new database or manage the scripts for an existing database.

For example, use the Tables node to manage your table scripts and the Programmability | Stored Procedures node to manage your stored procedure scripts:

image

These posts provide details on using this new Database project:

Enjoy!

20 Comments

  1.   Eduardo — June 11, 2010 @ 6:05 am    Reply

    Deborah: I’m not sure which part or the post are you referring.
    I’ve changed the deployment setting and setup the target database (scared as hell), but the script do not connect automatically to that DB when I opened them in VS2010.

    Missed something?

  2.   DeborahK — June 11, 2010 @ 9:34 am    Reply

    Hi Edwardo –

    No, the scripts do not connect automatically when you open them in Visual Studio. I complained about this too. So if I want to execute them manually, I have to connect each time.

    But if you set up the project as per the post link I provided, it will automatically connect and execute the changed script when you build/deploy the project.

    Hope this helps.

  3.   Eduardo — June 11, 2010 @ 11:20 am    Reply

    Thanks Deborah, it’s clear now.
    I’m not into automatic deploying scripts, so it’s useless to me.
    But thanks anyway.

  4.   DeborahK — June 11, 2010 @ 8:12 pm    Reply

    Hi Eduard –

    Yea, it didn’t feel right to me at first either. But now that I started deploying the script at compile time, I have gotten to like it.

    Good luck!

  5.   Eduardo — June 15, 2010 @ 2:52 pm    Reply

    And now in top of bad news: The editor can’t connect to SQL 2000!!

  6.   Leon — June 29, 2010 @ 1:26 pm    Reply

    Hi Debs, thanks so much for these very informative articles. I’ve just migrated to VS2010 and the new dbproj throws a few spanners in the works for me but I can see with a different approach it can really make life a lot easier. Your articles have provided an easy and quick way to get up to speed, thanks again!

  7.   steve mandel — December 20, 2010 @ 2:17 am    Reply

    I see where you can execute a stored procedure in order to store it in the database , but in the old database projects you could also run the stored procedure – is this also gone

  8.   DeborahK — December 20, 2010 @ 11:35 am    Reply

    Hi Steve –

    You can highlight the Select statement in your stored procedure from within the Database Project, right-click, and select Execute. It will then run your stored procedure. However, it does not bring up a UI for entry of your parameters. So you can only use this technique when your stored procedure takes no parameters.

    Alternatively, you can run your stored procedure from the Server Explorer window. Then it displays a UI for entry of your parameters.

    Hope this helps.

  9.   Paul — December 28, 2010 @ 2:04 pm    Reply

    Deborah,

    We have been trying to use a database project in our enterprise application (VS 2010 and SQL Server 2008 R2). However, doing a schema compare and write changes results in the generation of horrific scripts.

    For example,
    CREATE TABLE [dbo].[LabGroups] (
    [LabGroupUid] UNIQUEIDENTIFIER NOT NULL,
    [FK_ParentLabGroupUid] UNIQUEIDENTIFIER NULL,
    [FK_LabGroupLeaderUid] UNIQUEIDENTIFIER NULL,
    ALTER TABLE [dbo].[LabGroups]
    ADD [LabGroupCode] NVARCHAR (20) NOT NULL;

    ,
    [LabGroupName] NVARCHAR (120) NOT NULL,
    [IsLab] BIT NOT NULL,
    [Created] DATETIME NOT NULL,
    [CreatedBy] NVARCHAR (50) NOT NULL,
    [Modified] DATETIME NOT NULL,
    [ModifiedBy] NVARCHAR (50) NOT NULL
    );

    We get large regions of the scripts commented randomly and also odd mixtures of CREATE and ALTER as in above … my database guy is spending hours to fix the scripts after every compare.

    Have you seen anything like this?

  10.   Tim Lajaunie — January 11, 2011 @ 7:36 pm    Reply

    Nice article!

    Question: Internally, I plan to use a 2008 database project. However, several of my customers still use 2005. Will a dbschema built with a SQL Server 2008 project be able to update a 2005 database out in the wild with VSDBCMD.exe?

    Thanks.

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