Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

May 2, 2010

VS 2010 Database Project: Using an Existing Database

Filed under: C#,VB.NET,Visual Studio @ 8:20 pm

The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to work with scripts for an existing database directly within Visual Studio.

You can automatically build scripts for your existing tables, stored procedures, indexes, constraints, and so on. You can then check these scripts into and out of source code control. You can edit the scripts to change the table schemas or stored procedures. You can then build the project to validate the scripts and then deploy the scripts back to the database.

Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then follow the steps below to create the scripts in the project from an existing database.

1. Right-click on the Database project name in Solution Explorer and select Import Database Objects and Settings.

The Import Database Wizard is displayed.

image

2. Select the database connection to the existing database, any desired properties, and click on the Start button to start the wizard.

The database objects are then scripted directly into your Database project:

image

3. Click on the Finish button to finish.

View the Database project in Solution Explorer to see your added database objects.

image

Notice that this process added the three tables and three stored procedures from my database.

You can then check these files in and out of source code control just like any other project files. You can double-click on any script to edit it.

When you are ready to apply the changes to your development database, you can deploy the scripts as detailed in this post.

NOTE: If your database has been around a while, it is possible that some of the stored procedures may have gotten out of synchronization with the tables. For example, if your DBA renamed the CustomerInfo table to Customer, it is possible that your associated stored procedures were not all modified to match. Unless your code calls all of the stored procedures, you may not have noticed the problem. Since the Database project is "alive", once you follow this process to create the associated scripts into your Database project, any mismatches are defined as application warnings:

image

This helps you ensure that your stored procedures and tables are always in synchronization.

If you want these types of warnings to be treated as application errors and prevent execution of the application from within Visual Studio, you can change these warnings to errors. Double-click on the Properties under the Database project in Solution Explorer and click on the Build tab. Then check the "Treat Warnings as Errors" checkbox. Your warnings will immediately become application errors.

Use the techniques detailed here any time you are working with an existing database and want to add the scripts to your solution.

Enjoy!

1 Comment

  1.   Mark@LFT — September 24, 2010 @ 11:49 pm    Reply

    I have been attempting to use this optionin my app as it seemed a great way to control by app dbs, however I ran into a problem, my app uses two databases, with the db2 refering to db1 in views and sprocs. According to what I have read, this is easily solved by adding a reference from db2 to db1. This does fix the previous problem, however it introduces many more. There are several tables in on both dbs with the same name i.e. KeyTable, Setting etc. This causes the app to create many errors.

    This seems like a major oversight in the design of the db projects, or maybe I am missing something?

    Do you have any views on this?

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