In my last post on WiX I described how to set up a simple installation project that installed four files and created a shortcut in the start menu. Today we will look at installing and running SQL against databases during installation. Installing, or configuring a database is not an unusual task during installation especially with programs that roll out with SQL Express.
When we left off we had a wxs file that looked like this:
Lets look at how we add SQL support. The first thing we need to do is add references to the WixSqlExtension and WixUtilExtension DLLs. You do this just like you would add any other DLL to a visual studio project, by using the refferences folder.
The second thing we need to do is add references the the XML schema namespaces we are going to use. Add the util and SQL references as follows:
We now need to do three things:
- Create a User element to provide credentials to the database.
- Create SqlDatabase and SqlScripts elements to define the database and link to the script file.
- Create a Binary element to reference a SQL script file.
The User element is a generic element used for storing user information that is consumed by a variety of other elements (SQL, services, etc).
In the above code the Name and Password attributes are set to properties, variables that can be set by command line, the UI (which I'll be making a post about in the future) and custom actions.
The SqlDatabase element specifies the SQL server, database and user, essentially the connection string. This is where the User is linked in by specifying its ID against the User attribute. As well as this you can specify whether to create and drop the database during certain install events. Specifically the options are:
* CreateOnInstall
* CreateOnReinstall
* CreateOnUninstall
* DropOnInstall
* DropOnReinstall
* DropOnUninstall
This gives a lot of control over how the database is created or recreated during install and uninstall events. Care needs to be considered when dropping databases that users may have stored information in.
The SqlDatabase element contains one to many SqlScript elements. SqlScript simply links the SqlDatabase with a script file and specifies on which instalation events it should be run (Execute and Rollback versions of the SqlDatabase options).
Finally we need to specify the SQL file that we need to run. The Binary element allows us to embed files that can be used by the installer but are not meant to be installed.
A Binary simply has an Id (referenced by SqlScript) and a SourceFile attribute which references the local file on your machine. In this case our test.sql file contains a simple command to create a table:
Obviously this is a trivial example. We could easily invoke scripts to construct many tables, and populate them, more importantly we can use properties to conditionally select which files to run.
So lets look what we have done. We've added user credentials, created a way for WiX to communicate with a database, and specified a file to run against this database. The end result merged with our previous wxs files looks like this:
Now all we need to do is run the installer. because we haven't provided a UI we have to specify the properties manually when running the msi file. To get more control over the install we use the MSIEXEC.EXE tool. The following shows how to execute our .msi:
msiexec /i WixTest.msi /log log1.log SQLUSER="SA" SQLPASSWORD="Password" SQLSERVER="(local)\SQLExpress"
The /i flag tells the Microsoft Installer which file to run, /log specifies where to log to, and finally the properties are set. This give sys-admins a lot of power as they don't need to use a UI to install your application across a network of computers. To uninstall run the same as above but with /uninstall instead of /i.
To conclude WiX offers a powerful way to create new, and update existing databases during installation in a transactional manner.