The Code |
Full source code |
UsageGoal |
Prerequisites |
The story |
Showcase |
See also
Goal
To deploy a database schema (including stored procedures, triggers, UDTs etc.) programatically based on Visual Studio 2010 database project.
Prerequisites
- Visual Studio 2010 (Professional or higher, no Express edition)
- .NET Framework 4.0
- Microsoft SQL Server 2008 (maybe 2005 works as well)
- Processor independent - works as x86 or x64.
- To run on user's computer: See prerequisites of VSDBCMD.
The story
Visual Studio 2010 has a new and completely reworked database projects (*.dbproj). One of most interesting features of new database projects is that it can be "build". The "build" process verifies SQL syntax and object dependencies and generates bunch of files to the "sql" directory. The files typicly are:
- *.dbschema - contains complete description of your database in form of XML
- *.deploymanifest - MSBuild file describing how the database schema should be deployed and links to other files
- *_Database.sqlcmdvars - defines custom variables for deployment script
- *_Database.sqldeployment - configures deployment options (which database features to set, which ignore etc.)
- *_Database.sqlsettings - contains database settings like ANSI_PADDING etc.
- *.sql - optionally pre-deploy and post-deploy scripts can be provided in database project
These files are quite useless on its own. Well, it's interesting to have database schema described in a few XML files, but what's the real benefit? The real benefit from these files is
VSDBCMD - a command line tool that can take these files as input and create or update database schema in given target database to be the same as schema described by the *.dbschema file. This tool is interesting when you need to deploy database to production or testing server. Using the tool you can do it automatically as part of some build/deploy process. (Other option is to deploy a database from Visual Studio using database comparison tools offered by new database projects.)
VSDBCMD is installed with Visual Studio to the "%ProgramFiles(x86)%\Microsoft Visual Studio 10.0\VSTSDB\Deploy\" folder. And now the problems start. So, you need Visual Studio to use VSDBCMD. It's OK for web applications or thick clients accessing one central database which is in reach of you as developer, but when database is supposed to run on user computer or on server in client's company, you cannot run VSDBCMD to prepare a database for use (to deploy your schema there) because VSDBCMD is not on user's computer. Another concern is that even when you have VSDBCMD on user's computer, you must run external EXE from your application - what's is kinda ugly. You face similar situation when you want your desktop application to have "Create Database" button you when you want to create a database as part of setup process.
Duke Kamstra describes how to create database as part of setup process using WiX in his article on MSDN blogs. Duke's approach still involves executing external EXE file, but gives one very important information - it's possible to distribute VSDBCMD and it's dependencies to client computer. The dependencies are
another MSDN article about VSDBCMD.
I don't deal with VSDBCMD dependencies in this sample. What I deal with is execution of external EXE application to deploy your database.
My original goal was to find a class I can use to implement functionality of the "Create Database" button in one my hobby application -
Caps Collection Database (actually to create wizard that will collect some information about the database from user and at the end creates a database or connects to existing one). I've asked
on forum and in one mailing list if it was possible to deploy a database (from
.dbproj/.dbschema) programatically. The best answer I was given was VSDBCMD. It still wasn't what I wanted. Fortunately VSDBCMD is written in .NET 4.0 and it's core functionality is in libraries it depends on - namely Microsoft.Data.Schema.dll. Classes from that library are even
documented on MSDN - quite purely, but better than nothing. Well, I was unable to write any working code based on documentation provided, but I've used Reflector to look how VSDBCMD works and I've extracted the most important functionality to a separate library - and that is what this sample is about - performing the same task as VSDBCMD, but without running external EXE application.
Showcase
Database wizard:
Console (note this is my console, not console from any external program, you can, of course, create database silently without any output):
Database created!:
See also