Database Projects in Visual Studio .NET
By Scott Mitchell
Introduction
I've been using Visual Studio .NET for years developing ASP.NET applications, however it wasn't until about two years ago
that I learned about Database projects in Visual Studio .NET. With a Database project you can manage all of your database's
objects' creation scripts - stored procedures, views, tables, user-defined functions, jobs, and so on - through Visual Studio .NET. This offers
a bevy of advantages, including:
Source control on database objects - if you are using source control (and you most definitely should be),
the scripts managed through the Database project can be added to your source control provider. This means that any
changes to your database objects will be recorded by your source control provider, thereby providing the myriad of
advantages that source control affords (rolling back to older versions, a complete history of changes, etc.).
A centralized development experience - rather than having to poke through SQL Enterprise Manager you can manage your
database-related objects through the same IDE that you are using to manage the pages and components in your ASP.NET application.
An improved text-editor - Visual Studio .NET's text-editor is head and shoulder's above SQL Enterprise Manager's
built-in text-editing experience. Additionally, with SQL Enterprise Manager many of the dialog boxes that are used to
create/edit database objects are modal, thereby making it impossible to examine other facets of the database when creating/editing
a database object. Not so when doing it through Visual Studio .NET.
Ease of deployment - if you need to quickly replicate your database's structure having a Database project
makes it as easy as right-clicking on the Database project's objects and selecting the 'Run' context-menu option.
In this article we'll look at how to add a Database project to your existing ASP.NET application and how to import your
existing database's objects into the Database project. While creating and setting up a database project does take a bit of
time, this small, initial investment pays rich dividends. Since first learning about Database projects I've used them religiously
ever since and can't imagine having to go back to managing database objects through SQL Enterprise Manager. Read on to
learn more!
Adding a Database Project to Your Solution
To add a Database project to your existing ASP.NET application, start by opening the ASP.NET application in Visual Studio .NET.
What we need to do is add a new Project to the ASP.NET application's Solution. Understand that Visual Studio .NET offers
two levels of granularity for project management:
Projects - a project is a set of files that work together to accomplish some task. Projects are typically
divided into different types. There are ASP.NET Application Projects, WinForms Projects, Class Library Projects,
Web Control Library Projects, and so on. Each project usually consists of a set of files - for example, an ASP.NET
Application Project would consist of a variety of ASP.NET pages, code-behind classes, image files, CSS files, Global.asax,
and so on.
Solutions - a Solution is a collection of Projects.
The Solution Explorer lists the current Solution along with the Solution's Projects in a tree. At the root of the tree is
the Solution, directly underneath are the associated Projects. The screenshot to the right shows the Solution Explorer for
a Solution with a single Project.
To add a new Database project to the Solution, right-click on the Solution node in the tree and choose Add --> New Project.
This will bring up the Add New Project dialog box. Under the Project Types section, expand the Other Projects folder and
locate the Database Projects folder (see the screenshot below).
After adding this new project type you'll be prompted to choose the database connection to use for the Database project.
The Add Database Reference dialog box, shown below, lists the datasources saved in the Server Explorer. You can select
one of the existing ones or add a new one by clicking the Add New Reference button. (If you opt to add a new database connection you
will need to specify the provider - OLEDB for SQL Server, OLEDB for Oracle, Microsoft Jet for Access, etc. - along with the
connection details, such as the server, username, password, database name, and so on.) You don't need to choose a database
connection at this point if you don't like, as you can specify one later. To save this choice until later simply click the
Cancel button.
After choosing a database connection (or clicking the Cancel button) the new Database project will be added to the Solution
(see the screenshot below).
The Database project consists of a list of folders and a set of Database References. If you chose a database connection from
the Add Database Reference dialog box the selected database connection will appear in the Database References section. If you
clicked Cancel, there will be no database connection included in this section. You can, at any time, add additional database
connections to the set of Database References by right-clicking on the Database References node and selecting New Database
Reference. Doing so will bring up the Add Database Reference dialog box.
From this point I typically delete the default folders provided by the Database project and replace them with the following:
Sprocs
Tables
Views
UDFs
Jobs
Feel free to rename the folders as I do, to leave them named as-is, or to apply your own naming scheme.
All that remains now is to add our existing database's objects to the Database project. In Part 2
of this article we'll see how to do precisely that!
Adding a Database Project to Your Solution
