Database Projects in Visual Studio .NETBy Scott Mitchell
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.
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.
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:
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!