Integrating a database project into a Windows installer setup project

Question

I work on a desktop application whose setup installs MSDE and creates a database. Currently the database that the setup creates is a bunch of SQL scripts stored in the C# project, which are hard to maintain. I want to get rid of the SQL scripts and create a Visual Studio database project. I've been trying, with no luck, to find resources on how to integrate the database deployment into my Windows Installer project so that the database gets created at the end of the setup procedure. Also, I've been looking at refactoring our non-existing data access layer into something more manageable and Entity Framework looks very tempting. However, I'm concerned that I'm trying to bite more than I can chew since I would create the Entity Framework model against a database project. In fact, I'm not even sure if that's possible. Any tips and links would be greatly appreaciated.

Thanks,
-- Rubio

Answer 1

You have to package the redist files required by the vsdbcmd.exe in order to work standalone and shell out to vsdbcmd.exe from you installer. All this you have to manually craft inside your installer.

---

GertD @ www.DBProj.com

Answer 2

But I think the question is: Is it possible to have a Sql Server Database project build an ouput script (as produced in Deploy) and have that output included in a installer setup project, so that the installer would be able to run the script and create the database. Packaging and executing Sql Server redistribution files may or may not be needed. 

Answer 3

I think what Gert is saying is that I generate a dbschema file with the VS and use vsdbcmd.exe to deploy it. At least that's what the Action command line parameter seems to be for. Correct me if I'm wrong. I guess I would have to create a custom action that starts vsdbcmd.exe. At least As far as the redist files are concerned, the target machine has to have them so I need to package them as well.

Answer 4

it is very possible to do this. I do this every day.

you need to look into WiX (windows installer xml).

I would separate the vsdb engine stuff into one feature and have the schema components in another.

wix will allow you to define the structure of the content, and you can either use heat (a component of wix) or paraffin from John Bobbins of wintellect to enumerate the output of the solution compilation and import to your MSI's.

heres an excerpt of how paraffin can be used to build the enumerated fragments.

<

 

Exec Command="$(SourceParaffin)\Paraffin.exe -dir $(Output-ITAS_DB)\databaseSchema -groupname $(ApplicationName) $(WiXdbOutput) -norootdirectory -dirref COMPONENTDIR" Condition="$(FeatureList_xxx_xxx_DB) == 'true'"/>

<

 

Exec Command="$(SourceParaffin)\Paraffin.exe -dir $(Output-ITAS_DB)\ReferenceScripts -groupname $(ApplicationName)_ReferenceData $(WiXdbrefDeployOutput) -norootdirectory -dirref DATADIR" Condition="$(FeatureList_xxx_xxx_DB) == 'true'"/>

<

 

Exec Command="$(SourceParaffin)\Paraffin.exe -dir $(Output-ITAS_DB)\VSDBDeploy -groupname $(ApplicationName)_Deploy $(WiXdbDeployOutput) -norootdirectory -dirref DEPLOYDIR" Condition="$(FeatureList_xxx_xxx_DBDEPLOY) == 'true'"/>

Answer 5

I wrote a blog that describes how to create a WiX installer that invokes vsdbcmd.exe to deploy your database. Check it out: http://blogs.msdn.com/dukek/archive/2009/10/19/implementing-a-wix-installer-that-calls-the-gdr-version-of-vsdbcmd-exe.aspx

---

Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)

Answer 6

John, Duke,

I haven't had a chance to learn WiX yet and our installation project is pretty complex, so I doubt that we'll be able to switch to WiX right away. However, I guess the solution is to use a custom action to launch vsdbcmd.exe.

Thanks,
-- Rubio