In this part we’ll create our database after we ask the connection settings from the end user and update our web.config file with the correct credentials and connection settings.
In part 2 we’ll already provided a dialog where the user can enter the server’s IP address, database name, user and password. We’ll use these credentials further in this part.
Create the database
First we’ll have to create the database scripts. I’ve created 2 scripts, one to create all tables, stored procedures, primary and foreign keys, … and a second one to prefill some default tables (like a default admin user, …). I’m not going into the creation of the scripts, I suppose you’ll now how to do that and otherwise you’ll be able to Google or Bing for it.
Right click your setup project in your solution explorer and add a new folder ‘Database’. Add the 2 scripts to that folder (in this demo CreateTables.sql and FillTables.sql)
Add a new .wxs file in the project and name it ‘Database.wxs’ (right click project, add, select installer file, click add). Just as with the IIS configuration in part 3 we’ll have to tell WIX compiler that we’re goiing to use the WiX Util and WiX SQL library. We’ll have to add the namespaces at the top of the file as shown below.
The first items we’ll have to add are the 2 SQL files we’ve created. Add 2 “Binary” elements that point to our files.
Next step will be to use the Util library to create an user element that will connect to the SQL server. The name and password parameter we’ll set to the variables we’ve used in our custom dialog.
Next element to add is a “DirectoryRef” element, this will tell WiX to execute the content of that element when installing the application. In our project we’ll set the ID to the ‘INSTALLFOLDER’.
In this element we’re going to add the ‘Component’ element that contains the ‘SQLDatabase’ element. In the ‘SQLDatabase’ element we can specify the SQL server name or IP address and database name we asked our end user in our custom dialog. Set those items to the variables we used in our dialog.
Set the ‘CreateOnInstall’ parameter to yes to have WiX install the database, choose yes by parameter ‘DropOnINstall’ if you want the database be dropped when your website is uninstalled by Add/remove programs. The ‘User’ parameter we set to the ‘User’ element we’ve created before. Last but not least is the ‘ContinueOnError’ parameter that I’ve set to no to avoid the installation to continue when a database error occurs.
The above is actually enough to create our database. Off course we want to create also our tables and pre fill them. To do that we’ve have to add 2 ‘SQlScript’ elements to the ‘SQLDatabase’ element that will point to ‘Binary’ elements we’ve created before (that on their part point to the scripts we’ve added).
Update the MS Build file
Because we created an extra file to include in the build process we must be sure that the file is added in our build.
We have to update the item groups that contain the list of files to include as shown above.
Now open up the Developer Command Prompt for Visual Studio 2012 again and change the prompt to the setup project folder and type the next statement:
You’ll see the build process will return an error. We added a reference to the WiX Util and Database library in our ‘.wsx’ file but we didn’t tell the candle.exe and light.exe tools to take that into account. Add in both command the –ext WixUtilExtension and –ext WixSqlExtension and run the script again.
Now you should have a successful build. Run the installer and enter the connection credentials for an existing SQL (Express) server and you’ll see your database will be created.
If the installer receives an error during the database installation you’ll get an error message when installing and all already installed files will be reverted.
Next parts
This concludes the installation of the SQL database. Next part we’ll handle updating our web.config file to have the correct SQL connection string.
Install the .NET 4.5 framework if that isn’t installed already
Install the MVC 4 framework if that isn’t installed already.
Create a folder and copy all needed files to run the application (done)
Create a new database on an existing SQL server and prefill the database with the correct tables and values. (the connection details and database name should be entered by the end user running the installer) (done)
Create a new website in IIS 7.5 (create website and application pool running under .NET 4.5) (done)
Alter the config file so the correct connection settings are used (entered by the end user)
