Deploy database programatically

Question

I have a desktop application which uses SQL Server database (User Instance or server-side - depends on user decision). I want to allow user to create a new database. The functionality is supposed to work as follows:

1. Use specifies kind of database (Create a new mdb file and attach it to User Instance / create a new database on server / use an empty database)
2. The application creates a database and connects to it (or just connects to an empty database)
3. The application creates tables, stored procedures, triggers etc. in database

Is there any way to use new dbproj in Visual Studio 2010 to achieve this. What I want is to somehow programatically create database objects using files dbproj generates to SQL folder when it is "built".

Yes, I know that I can generate CREATE script from existing database in Management Studio and run it against the database. But it requires manually re-generating the script whenever change is made to a database while in dbprojs everything works in an automatic way.

Any suggestions appreciated.

---

Đ.

Answer 1

You have a commandlinutil vsdbcmd.exe who can be used to deploy databases from the commandline, msbuild or included in a setup application

Read this post about how to make a WiX installation deploing databases with vsdbcmd

http://blogs.msdn.com/dukek/archive/2009/10/19/implementing-a-wix-installer-that-calls-the-gdr-version-of-vsdbcmd-exe.aspx

 

Answer 2

Thank you for replay.

I don't want to be dependent on external exe that may or may not be present on client computer.

A also do not want to create a new database as part of setup but from application itself (in runtime).

---

Đ.

Answer 3

I think the trick is to embed the vsdbcmd in the wix project, but I dont remeber if its doine in the article. Doing so yo dont depend on  an external exe and you do deploy in runtime.

 

Answer 4

Hi Donny,

There is not an easy way to programmatically creature database objects using the database projects model.  You can programmatically deploy a model, but that model must be hydrated from SQL scripts.  I think what you want is SMO. With SMO you can programmatically define a database. See here: http://msdn.microsoft.com/en-us/library/ms162557(v=SQL.100).aspx

Thanks,

---

Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill

Answer 5

Yes, I know about SMO but creating tables etc. via SMO is like writing CREATE TABLE in Visual Basic instead of T-SQL. That's not what I want. The only advantage of SMO for me is, that it can be used to run scripts containing "GO" (I actually use it for change scripts).  Another concern about SMO is if it will work on client computer?

Deploying model using SQL scripts is way I'll go if I will not find anything better.

I looked at vsdbcmd recomended by Mattias. Maybe I can use classes vsdbcmd is based on (e.g. SchemaDeployment ). But same concern as with SMO - how to make it work on client computer?

---

Đ.

Answer 6

If you use VSDBCMD you will run into the same dependencies with SMO. We dont use SMO in VSDBCMD, but do use a component deliverred in SMO, the batch parser. It sounds like the client from your orginal post will already have SMO.

Take a look at my blog here which outlines the use and prereqs of vsdbcmd:

http://blogs.msdn.com/bahill/archive/2009/02/21/deploying-your-database-project-without-vstsdb-installed.aspx

Thanks,

 

---

Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill

Answer 7

The client box does not have SMO installed (unless it's part of .NET Framework).

So (according to your blog) to my application needs

• %\ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Deploy
• HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0 (or 10.0?)
• SQL CE 3.5 SP1 x86, Why? Do I need x64 for 64bit application?
• SMO (because I use it for change scripts)
• SQL server native client
• SQL Server System CLR types

I can't imagine telling user: "If you want 'create a new database' functionality you must install this, this and this" :-(

It's shame that Microsoft does not support any easy way of database deployment :-(.

Anyway, Barclay, thank you for your suggestions. I'll probably go some VSDBCMD-based way.

---

Đ.

Answer 8

OK, so, I wanted to start experimenting with VSDBCMD, but I've quickly ended with mysterious error:

*** SQL00256 d:\Users\Honza\Documents\Programy\Caps\CapsData\sql\debug\CapsDa
ta_Database.sqldeployment  (0,0) The deployment property CollationPrefere
nce could not be used to configure deployment.
*** The deployment configuration file could not be loaded. Deployment cannot co
ntinue

Google is quite silent about this error and I have no idea where it comes from. Using VS 2010 Ultimate Trial RTM. Win7 Ult x64. My command:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC>vsdbcmd /a:Deploy /cs:"Da
ta Source=.\SQLEXPRESS2008;Initial Catalog=xyz;Integrated Security=True" /dsp:SQ
L /dd /manifest:"d:\Users\Honza\Documents\Programy\Caps\CapsData\sql\debug\CapsD
ata.deploymanifest"

xyz is an empty database on SQL Server 2008 Express x64 (running on the same machine).
You can download content of my SQL folder here: http://dzonny.cz/misc/temp/Caps_SQLDeploy.zip .
Any help appreciated :-).

---

Đ.

Answer 9

The deployment option in your Database.sqldeployment settings collation is not valid for 2010.  This file needs to be upgraded. This should have occurred when you upgraded your project if you are running RTM.  You can force the upgrade by loading the Database.sqldeployment file in your project and resave it.

Thanks,

Answer 10

Thanks Barcly.

I use VS 2010 RTM, but the database project have originally been created with Beta 2. After resaving the *.sqldeployment file this particular error was solved.

I had to turn on various database options (CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS, ANSI_NULLS, QUOTED_IDENTIFIER  - which I even don't know they are good for) and finally I was able to to deploy to an empty database using VSDBCMD :-).

Now I can proceed with invoking it programaticallly...

---

Đ.

Answer 11

Thanks to Matias and Barclay I was able to deal with VSDBCMD.

After I made VSDBCMD to work for me I proceeded with Reflector and learned how it works. Based on that knowledge I was able to write a small library which can easily programatically deploy a database schema.

I haven't dealt with dependencies yet. I have to run it on a clean computer (only .NET 4 installed) and see how to make it work. It take same effor as making VSDBCMD working on client computer.

See how to deploy database programatically .

---

Đ.