Practical .NET

Leveraging a Visual Studio Database Project

Peter shows how a Visual Studio database project can make you more productive when making changes to your database. But a database project also gives you a variety of tools to let you keep track of what those changes are -- which will make moving those changes to your production environment considerably safer.

• By Peter Vogel
• 01/01/2015

In my column last month, "Simplifying Development with Database Projects," I described how to create a Visual Studio database project and I promised that it would make managing changes to your database easier. This column goes into that "making your life easier" part by showing how you might use a database project.

Working with Tables: Not as Great as It Could Be
In a database project, Solution Explorer holds a list of text files for your database's tables, stored procedures and other database components.

Double-clicking any of these opens them in an editor window, just like a code file. For example, if you open a table definition you get a visual designer at the top of the window and a T-SQL editor at the bottom.

However, calling this a "visual" designer is a stretch and is one of my few disappointments with a database project. For example, to add a constraint, you right-click on the list of constraints on the right side of the designer and select Add New Constraint. This will prompt you for a name for your constraint and then add a sample template line to the end of your table definition. After that, you're on your own -- you don't get any IntelliSense support. One other disappointment with tables: Solution Explorer doesn't display a list of column names for a table the way SQL Management Studio does.

You will, however, find it easier to work with tables when accessing them in stored procedures. You can, for example, right-click on a table name in a stored procedure and select Find All References to get a list of all of the places the table is used, both in other stored procedures and in the script file that defines the table. Double-clicking on any of those list items will take you to the stored procedure or the table's definition. This makes changing a column or table name considerably less risky than doing it in SQL Server Manager Studio.

After you've assured yourself that the change is safe, the Visual Studio SQL | Refactor menu choice includes a Rename option that will make the change for you.

If all you want is to get to is a table's definition (for example, if you want to get a list of column names) then it's faster to click on the table name in a stored procedure and press F12. Unfortunately, regardless of whether you pick Show All References or use F12, you're taken to a plain text version of the table definition. If you want see the table in the visual designer, you'll need to double-click the file in Solution Explorer to open it in a second window.

Working with Stored Procedures: Much Better
On the other hand, when writing stored procedures you can expect much better IntelliSense support than you get in SQL Server Management Studio: You'll be prompted through the syntax for SQL statements and given dropdown lists of table names.

Within SQL statements, once you've assigned aliases to your table names, you'll even get lists of column names when you enter a table alias followed by a period (you'll also get these lists if you type in a full table name followed by a period, but, unless you're paid by the keystroke or have very short table names, I can't see anyone doing that). I already find myself shifting to typing in dummy Select clauses just to get to the statement's From clause so that I can set up an alias. I then go back and define the statement's columns in the Select clause using the alias and IntelliSense.

From within a stored procedure window you can also execute your procedure (with or without debugging). For windows containing SQL statements, you can see the execution plan for your query.

All of the changes that you make to your files form what Visual Studio refers to as your "database model." If you pick Deploy from the Build menu, Visual Studio creates a local database that reflects your model. I'm running the development version of my application against that database.

Tracking Changes
At any time you can do a schema comparison between your model and the original database. To do that, right-click on your project in Solution Explorer and select Schema Compare. This displays an editor window with two dropdown lists at the top. The list on the left is set to the local database that Visual Studio creates during deployment. You need to set the list on the right to your original database (or, really, any database that makes sense). Once you've done that, clicking on the Compare button will generate a difference list.

When the comparison is complete you can generate a SQL file of the changes to make to your production database or use Build | Publish to update your original database directly from Visual Studio. The first publish to a database takes forever -- subsequent publishes will go much faster. Also, SQL Server Data Tools will generate what it calls a "preview" of the changes … but won't show you the preview before making the changes. You can, however, review the preview after the publish is complete.

More critically, all of these changes to your text files can now be saved to whatever source control system you're using (I use Subversion when I'm working alone and Team Foundation Services when I'm part of a team), letting you track your changes. If you don't use source control, you can right-click on the project in Solution Explorer and select Snapshot Project, which will create a binary "milestone" for your project that you can compare against.

As I said in my previous column, I'm just starting to integrate database projects into my work. But I'm very happy with what they've done for me so far (and very grateful to the reader who put me on to it).