Practical .NET

Simplifying Development with Visual Studio Database Projects

Peter learns something by listening to readers: Visual Studio database projects provide a great environment for making changes to your databases. Here's a brief introduction about how they'll make life easier for you and all the steps for setting one up.

• By Peter Vogel
• 12/10/2014

In a series of comments to one of my earlier columns, I was whining about how hard it is to manage database changes as part of a typical development project, especially if you have to work with stored procedures (as I do on my current client's project). A reader, obviously tired with my bellyaching, recommended that I take a look at Visual Studio database projects. I did and I think I'm in love. I've already added a database project for my client's database to the Visual Studio 2013 solution for our current project and I'm using that database project as the central point of control for all of the project's database changes.

Benefits, Caveats and Downloads
Effectively, a database project converts all of the objects in your database (table designs, stored procedures) into text files -- text files that can be searched, modified and stored in source control just like your code files. A database project also includes designers for working with these files. Visual Studio cross-references those files to give you a higher level of IntelliSense support for working with stored procedures than you'll ever get in SQL Server Management Studio.

You also get a staging database where you can make your changes before updating your production or development database and a schema comparison tool for determining, after the fact, what changes you've made. Effectively, you get an isolated database environment for trying out database changes.

Some caveats are worth mentioning. The tools I use here only work with SQL Server 2005 or later. And, while you should get the same results in any version of Visual Studio, I'm working in Visual Studio 2012. It's also entirely possible that my current infatuation with database projects is just a crush and, in a month, I'll find database projects aren't meeting my needs … but I don't think so. Even with all those caveats, my advice is, "Don't be like me." If you're working with a database you should investigate database projects now.

The underlying technology for database projects is bundled into SQL Server Data Tools (SSDT), which is included in all versions of Visual Studio 2013 (including the Express version). However, even if you're using Visual Studio 2013, you should make sure you have the latest version of SSDT by going to the Visual Studio SQL menu and selecting Check for Updates. For earlier versions of Visual Studio you'll need to download SQL Server Data Tools for Visual Studio 2012. If you have an earlier version than Visual Studio 2012 or don't have Visual Studio at all, the download is supposed to install a shell that will allow you to use SSDT.

Creating Your Project
You'll probably want to create your database project as part of a solution that includes the application and class libraries you're working with. To add a database project, from the Visual Studio File menu select Add | New Project to display the Add New Project dialog. In the dialog, select SQL Server from the left-hand list and, when the list in the middle of the dialog displays, select SQL Server Database Project. Give your project a name, click OK and wait for the project to be added to Solution Explorer.

If you're not working with SQL Server 2014 (Visual Studio's default), you'll need to set the version of SQL Server with which you're working. In Project Properties, in the Project Settings tab, select the version of SQL Server that you're using from the Target Platform dropdown list.

Now you need to add the database you're working with to your project. In Solution Explorer, right-click on your database project and select Import | Database to display the Source Database Connection dialog. The dropdown list in that dialog will probably list the database you want but, if you don't see it, click the New Connection button to create a connection to your database (this will bring up the standard database connection dialog). When you leave the dialog, Visual Studio will analyze your database and generate text files corresponding to the tables, stored procedures and other objects in your database.

To run a check on the validity of your objects, from the Visual Studio Build menu select Build <project name>. Be prepared to deal with a number of unresolved references. Some of these may reflect, for want of a better word, "historical anomalies." You might have removed objects from your database but still have stored procedures that refer to them -- you can just delete the stored procedures with the outdated references. More problematic are any references to temporary objects created and destroyed during a process that are, as a result, not in your database when you import it. You'll need to add those temporary objects to your project to resolve those references.

You might also have to add references to other databases to your project. For instance, when you open a stored procedure that refers to tables in another database (for example, the system tables in the master database) you'll find that those table names are marked as unresolved references. You get rid of those messages by adding a database reference to that other database. Right-click on your database project in Solution Explorer and select Add Database Reference. In the resulting Add Database Reference dialog, just add the databases that your stored procedures refer to (there's even a dropdown list specifically for adding references to the master and msdb databases in the same server as your database). Visual Studio will reference those databases when working with your project without adding those databases' objects to your project.

With that, you have a database project. I realize I haven't shown how to use this tool yet -- that's next month's column.