Chapter 12. Installing SQL Server Databases

In this chapter, we will cover the following topics:

• Installing a SQL Server instance with a bootstrapper

• Adding a database to a SQL Server instance

• Creating a table within a SQL Server database

• Inserting data into a database table

• Creating an ODBC data source for a SQL Server instance

Introduction

Data is vital to most applications. So, it's likely that you'll want to set up a database at installation time to store data. The WiX Toolset gives us a mechanism to add a new instance of SQL Server and then add databases, table definitions, and data to it. In this chapter, we'll cover these common tasks and also touch upon how to set up an ODBC data source, which gives a standardized way for applications of various types to connect to the database.

Installing a SQL Server instance with a bootstrapper

Unless you're sure that an instance of SQL Server is already installed on the end user's computer, you'll probably want to install it yourself. A bootstrapper can check for the existence of SQL Server and install it only if it isn't there, saving you the guesswork. In this recipe, we will create a bootstrapper that will install SQL Server 2014 Express. This is a free database offered by Microsoft.

How to do it...

Add PackageGroup for SQL Server 2014 Express to a bootstrapper project, as shown in the following steps:

1. Create a new bootstrapper project and call it SqlServerBootstrapper.

2. Add the UtilExtension namespace to the project by right-clicking on the References node in Solution Explorer and selecting OK after navigating to Add Reference... | Browse | WixUtilExtension.dll | Add.

3. Add NetFxExtension to the project by right-clicking on the References node in Solution Explorer and selecting OK after navigating to Add Reference... | Browse |...

Adding a database to a SQL Server instance

Once we have an instance of SQL Server running on the end user's computer, the next step is to add a database to it. This will give us a place to store our application's data. With WiX, we can define a new database in a declarative style rather than with an external SQL script.

Getting ready

Create a new setup project and name it NewDatabaseInstaller.

How to do it...

The following steps show how to create a new database with the SqlDatabase element:

1. Add SqlExtension to the project by right-clicking on the References node in Solution Explorer and selecting OK after navigating to Add Reference... | Browse | WixSqlExtension.dll | Add.

2. Add the SqlExtension namespace to the Wix element:

   <Wix xmlns="http://schemas.microsoft.com/wix/2006/wi"  
   xmlns:sql="http://schemas.microsoft.com/wix/SqlExtension">

3. Add a Component element that has KeyPath set to yes:

   <ComponentGroup Id="ProductComponents" 
                   Directory="INSTALLFOLDER">
     <Component...

Creating a table within a SQL Server database

After creating a database on the end user's computer, you'll want to define its schema by adding table definitions. WiX gives us a way to execute CREATE TABLE statements within the database that we're installing. In this recipe, we will add a table definition with a few basic fields.

Getting ready

Create a new setup project and name it NewTableInstaller.

How to do it...

To create a table, add a SqlString element that specifies the CREATE TABLE SQL statement:

1. Add SqlExtension to the project by right-clicking on the References node in Solution Explorer and selecting OK after navigating to Add Reference... | Browse | WixSqlExtension.dll | Add.

2. Add the SqlExtension namespace to the Wix element:

   <Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" 
   xmlns:sql="http://schemas.microsoft.com/wix/SqlExtension">

3. Add a Component that has its KeyPath attribute set to yes. It should contain a SqlDatabase element so that a database is set up for us to add a...

Inserting data into a database table

During the installation, you may decide to populate your database tables with some seed data. For example, you may have a list of U.S. states in which you do business that you'd like to add as static data. In this recipe, we will create a database, add a table to it, and then insert new rows into that table.

Getting ready

Create a new setup project and call it InsertingDataInstaller.

How to do it...

Include an INSERT statements in a SqlScript element to add rows of data to a database. The following steps show how to do it.

1. Add SqlExtension to the project by right-clicking on the References node in Solution Explorer and selecting OK after navigating to Add Reference... | Browse | WixSqlExtension.dll | Add.

2. Add the SqlExtension namespace to the Wix element:

   <Wix xmlns="http://schemas.microsoft.com/wix/2006/wi"  
   xmlns:sql="http://schemas.microsoft.com/wix/SqlExtension">

3. We're going to store all our SQL commands in a file. So, use Notepad to create a file...

Creating an ODBC data source for a SQL Server instance

Microsoft Open Database Connectivity (ODBC) is a long-standing and established API for connecting to a database. Although there are alternative .NET-specific libraries, such as Entity Framework, ODBC is more general purpose and fits well into an environment where a variety of languages and technology stacks are used.

Many languages have libraries that can take advantage of ODBC. For example, C# can use either ADO.NET or the classes under the System.Data.Odbc namespace to connect to an ODBC data source. In this recipe, we will set up a data source to connect to a SQL Server instance called MySqlInstance.

Getting ready

Create a new setup project and call it OdbcDataSourceInstaller.

How to do it...

Using the ODBCDataSource element, create a data source for SQL Server, as shown in the following steps:

1. Add a component to the project that will contain our ODBC data source:

   <ComponentGroup Id="ProductComponents" 
                   Directory="INSTALLFOLDER...