Embedding SQL Server Express into Custom Applications
SQL Server Technical Article
Robert Walters
Microsoft Corporation
November 2005
Applies to:
Microsoft SQL Server 2005 Express Edition
Microsoft Visual Studio
Summary: This white paper discusses how to integrate and deploy Microsoft SQL Server Express-based applications by using configuration files and the ClickOnce deployment technology in Microsoft Visual Studio. (23 printed pages)
Click here to download the Word document version of the article, EmbedExprCustApps.doc.
Contents
Introduction
The Template.ini File and Command-Line Switches
Deploying SQL Server 2005 Express Edition by Using a Wrapper
ClickOnce (Visual Studio 2005)
Licensing
Conclusion
Introduction
As today's computer applications continue to grow in complexity and in the amount of information they must store and manage, a stable and efficient database engine is a key ingredient for the overall success of any project. Microsoft SQL Server 2005 Express Edition (SQL Server Express) is a lightweight and "free" database engine. It replaces Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). SQL Server Express is similar to SQL Server 2005 Workgroup Edition, but with some limitations on the memory, database, and schema features. To learn more about the features of SQL Server 2005 Express Edition, see the SQL Server 2005 Express Web site on the Microsoft Web site.
Previously, creating a custom application with MSDE 2000 embedded was a three phase process:
- Creating an MSI package.
- Merging the MSDE 2000 merge modules.
- Running Setup to install the custom application and MSDE 2000.
SQL Server Express no longer uses merge modules to embed the SQL Server Express Database Engine. Instead, it contains a single-setup executable that can be bundled with your custom application. This executable accepts command-line parameters for a smooth installation experience.
The best way to embed SQL Server Express within your application depends on how you plan to deploy the application. Ideally, you would always download the latest SQL Server Express version from the Web. This would help to ensure that your customers are running the latest version. However, it is probably more feasible to install SQL Server Express from an internal file share, or place the Express bits on the same media as your custom application.
This white paper describes the configuration file and command-line parameter support for the sqlexpr.exe setup installation executable. Because merge modules have been deprecated, configuring SQL Server Express this way is the best option for SQL Server 2005. To help you more easily embed SQL Server Express, this white paper also provides instructions for creating a wrapper class that will assist custom application developers in deploying a SQL Server Express solution. In addition, this white paper describes the advantages and disadvantages of using the ClickOnce feature of Microsoft Visual Studio 2005 to deploy your SQL Server Express custom applications.
The Template.ini File and Command-Line Switches
There are two ways to programmatically install, modify, and remove SQL Server Express components. First, you can call setup.exe and pass a series of parameters on the command line. Alternatively, you can configure all the parameters within a single file and pass that file as a command-line parameter to setup.exe. This file is called template.ini and is located in the root directory of SQL Server Express. An example of launching Setup and passing the configuration file is as follows:
start /wait setup.exe /qb /settings c:\template.ini
For those not familiar with the Microsoft Windows command line, start is an application that opens a new console window. The /wait parameter tells the console window to wait until the program finishes execution before terminating itself. A complete list of parameters for the start command can be obtained by passing /? as a parameter. The next parameter in the line of code is setup.exe. This is the name of the application to launch: in this case, SQL Server Express Setup. The rest of the parameters are arguments for the setup program. The setup.exe argument /qb tells Setup to run in quiet mode, which requires no user interaction. This mode does provide some visual status to the user about the status of the installation.
Alternatively, you could specify /qn. The only difference between /qb and /qn is that when you use /qn, there is no visual status reported to the user.
All errors from Setup in SQL Server are recorded in the setup log files. If you call in for support on a setup-related issue, the Product Support specialist will probably want you to find these files. By default, the setup log files are located at this path:
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
If you encounter problems when developing a custom SQL Server Express installation, these files are a good place to start debugging.
The /settings parameter in the command-line code tells Setup to obtain all installation information from the file that is defined in the next parameter. In the code example, the template.ini file is stored in the root of the C: drive.
Important If you have downloaded SQL Server Express, you might not see the application setup.exe. This is because, if you downloaded SQL Server Express from the Web, you probably downloaded a single SQLEXPR.EXE file. If this is the case, you need to call this application from the command line to extract the SQL Server Express files from this compressed executable. To perform this extraction, run SQLEXPR /X from the command line. A dialog box appears, prompting you for a location to extract the files to. The SQL Server Express files will be copied to the location that you specify. These files will include setup.exe and the template.ini file, among many other files and folders.
The template.ini file is a plain text file that can be opened by using a text editor such as Notepad. When you open this file, you see a long commented introduction citing examples of how to use the file. The file itself is well documented, and a lot of the options are explained in great detail within the file itself. For that reason, this white paper does not restate all the options. Instead, following are just a few parameters of interest.
PIDKEY—This parameter is not required for SQL Server Express installations. All other SKUs require this parameter.
ADDLOCAL—This parameter specifies which components to install. If ADDLOCAL is not specified, Setup will fail. A user can specify ADDLOCAL=ALL, which installs all components. For SQL Server Express, following are the only options available for ADDLOCAL.
Table 1
| ADDLOCAL parameter | Description |
|---|---|
| SQL_Engine | Installs SQL Server database, including the SQL Server and SQL Browser services. |
| SQL_Data_Files | Installs core SQL Server databases, including master, the resource database, and tempdb. |
| SQL_Replication | Installs files necessary for replication support in SQL Server Express. |
| Client_Components | Installs components for communication between clients and servers, including network libraries for ODBC and OLE DB. Also installs applications such as the sqlcmd utility (oSQL replacement), SQL Server Configuration Manager, and the Surface Area Configuration tool. |
| Connectivity | Installs software development kits containing resources for model designers and programmers. This includes SQL Server Management Objects (SMO) and Replication Management Objects (RMO). |
REMOVE—This parameter is similar to ADDLOCAL. However, instead of adding components, it either removes a specific component, or completely uninstalls SQL Server Express if you use REMOVE=ALL. The following example removes the client components of an existing SQL Server Express installation.
REMOVE=Client_Components.
You do not have to specify an instance name because the Client_Components are not instance-specific. If you were removing SQL_Replication support, you would also need to add:
INSTANCENAME=<<name of the SQL Server Express Instance>>
UPGRADE—This parameter is used when upgrading from MSDE to SQL Server 2005 Express Edition. When UPGRADE is used, you must also specify the same instance name as the name of the MSDE instance you want to upgrade. This is because it is possible to have up to 16 MSDE instances on a single computer. An example upgrade parameter is:
UPGRADE=SQL_Engine INSTANCENAME=MYMSDE
When writing custom installation applications, it can be difficult to remember these parameter names for all of your projects. To make this easier, you can write a custom wrapper class to encapsulate setting the parameters and to provide a reusable stub for your custom applications. The wrapper class does not expose every option available, but it should give enough direction to suit your own custom installation needs.
Deploying SQL Server 2005 Express Edition by Using a Wrapper
As a custom application developer, you have three options to include SQL Server Express within your application:
- Install SQL Server Express first. Then install the custom application.
- Install the custom application first. Then install SQL Server Express.
- Create a wrapper that combines the two-step process in the first two options into a single step.
Note A SQL Server Express wrapper cannot be MSI-based because Windows Installer does not support multiple instantiation of the Windows Installer service.
The remainder of this section focuses on creating a wrapper for your custom application. In the example code, the wrapper is a simple class that exposes three public methods: IsExpressInstalled, EnumSQLInstances, and InstallExpress. Ideally, you do not have to know if SQL Server Express or any other instance of SQL Server is already installed on the local computer. This example includes them in case you want to give the end user the flexibility of selecting an existing instance of SQL Server Express to install your application against, instead of always creating a new instance.
Note As a best practice, most custom applications should use the same SQL Server Express instance. This is accomplished by using the User Instances feature of SQL Server Express. You can learn more about user instances from the SQL Server Express Edition User Instances white paper on MSDN.
The first step is to create a simple class. This class will contain local variables of most of the command-line switches supported by the SQLEXP.EXE installation executable. These switches will be exposed as properties of the class object.
Important The following code is only to be used a guideline for installing SQL Server Express with your custom application. It is not complete and does not contain robust error-handling routines.
public class EmbeddedInstall
{
#region Internal variables
//Variables for setup.exe command line
private string instanceName = "SQLEXPRESS";
private string installSqlDir = "";
private string installSqlSharedDir = "";
private string installSqlDataDir = "";
private string addLocal = "All";
private bool sqlAutoStart = true;
private bool sqlBrowserAutoStart = false;
private string sqlBrowserAccount = "";
private string sqlBrowserPassword = "";
private string sqlAccount = "";
private string sqlPassword = "";
private bool sqlSecurityMode = false;
private string saPassword = "";
private string sqlCollation = "";
private bool disableNetworkProtocols = true;
private bool errorReporting = true;
private string sqlExpressSetupFileLocation =
System.Environment.GetEnvironmentVariable("TEMP") + "\\sqlexpr.exe";
#endregion
#region Properties
public string InstanceName
{
get
{
return instanceName;
}
set
{
instanceName = value;
}
}
public string SetupFileLocation
{
get
{
return sqlExpressSetupFileLocation;
}
set
{
sqlExpressSetupFileLocation = value;
}
}
public string SqlInstallSharedDirectory
{
get
{
return installSqlSharedDir;
}
set
{
installSqlSharedDir = value;
}
}
public string SqlDataDirectory
{
get
{
return installSqlDataDir;
}
set
{
installSqlDataDir = value;
}
}
public bool AutostartSQLService
{
get
{
return sqlAutoStart;
}
set
{
sqlAutoStart = value;
}
}
public bool AutostartSQLBrowserService
{
get
{
return sqlBrowserAutoStart;
}
set
{
sqlBrowserAutoStart = value;
}
}
public string SqlBrowserAccountName
{
get
{
return sqlBrowserAccount;
}
set
{
sqlBrowserAccount = value;
}
}
public string SqlBrowserPassword
{
get
{
return sqlBrowserPassword;
}
set
{
sqlBrowserPassword = value;
}
}
//Defaults to LocalSystem
public string SqlServiceAccountName
{
get
{
return sqlAccount;
}
set
{
sqlAccount = value;
}
}
public string SqlServicePassword
{
get
{
return sqlPassword;
}
set
{
sqlPassword = value;
}
}
public bool UseSQLSecurityMode
{
get
{
return sqlSecurityMode;
}
set
{
sqlSecurityMode = value;
}
}
public string SysadminPassword
{
set
{
saPassword = value;
}
}
public string Collation
{
get
{
return sqlCollation;
}
set
{
sqlCollation = value;
}
}
public bool DisableNetworkProtocols
{
get
{
return disableNetworkProtocols;
}
set
{
disableNetworkProtocols = value;
}
}
public bool ReportErrors
{
get
{
return errorReporting;
}
set
{
errorReporting = value;
}
}
public string SqlInstallDirectory
{
get
{
return installSqlDir;
}
set
{
installSqlDir = value;
}
}
#endregion
Now that you have set up the local variables and properties for the class object, you can work on the public methods IsExpressInstalled, EnumSQLInstances, and InstallExpress.
Assuming a local server installation, you can simply look to the local registry to see if SQL Server Express or any other instance of SQL Server is installed. The method in the following code enumerates and checks the "Edition" value for keys under:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.X
Where X is an instance of SQL Server.
IsExpressInstalled
public bool IsExpressInstalled()
{
using (RegistryKey Key =
Registry.LocalMachine.OpenSubKey("Software\\Microsoft\\Microsoft SQL
Server\\", false))
{
if (Key == null) return false;
string[] strNames;
strNames = Key.GetSubKeyNames();
//If we cannot find a SQL Server registry key, we
don't have SQL Server Express installed
if (strNames.Length == 0) return false;
foreach (string s in strNames)
{
if (s.StartsWith("MSSQL."))
{
//Check to see if the edition is "Express Edition"
using (RegistryKey KeyEdition =
Key.OpenSubKey(s.ToString() + "\\Setup\\", false))
{
if ((string)KeyEdition.GetValue("Edition") == "Express Edition")
{
//If there is at least one instance of
SQL Server Express installed, return true
return true;
}
}
}
}
}
return false;
}
By using the local registry, you can determine more information about all the SQL Server instances, regardless of edition, that are installed on the local server. Having this information is useful if you want to provide a better installation experience. The method in the following code example will take a reference and populate a string array for instances, editions, and versions. It returns the number of instances of SQL Server that are installed on the local computer.
EnumSQLInstances
public int EnumSQLInstances(ref string[] strInstanceArray, ref string[] strEditionArray, ref string[] strVersionArray)
{
using (RegistryKey Key = Registry.LocalMachine.OpenSubKey("Software\\Microsoft\\Microsoft SQL Server\\", false))
{
if (Key == null) return 0;
string[] strNames;
strNames = Key.GetSubKeyNames();
//If we can not find a SQL Server registry key, we return 0 for none
if (strNames.Length == 0) return 0;
//How many instances do we have?
int iNumberOfInstances = 0;
foreach (string s in strNames)
{
if (s.StartsWith("MSSQL."))
iNumberOfInstances++;
}
//Reallocate the string arrays to the new number of instances
strInstanceArray = new string[iNumberOfInstances];
strVersionArray = new string[iNumberOfInstances];
strEditionArray = new string[iNumberOfInstances];
int iCounter = 0;
foreach (string s in strNames)
{
if (s.StartsWith("MSSQL."))
{
//Get Instance name
using (RegistryKey KeyInstanceName =
Key.OpenSubKey(s.ToString(), false))
{
strInstanceArray[iCounter] =
(string)KeyInstanceName.GetValue("");
}
//Get Edition
using (RegistryKey KeySetup =
Key.OpenSubKey(s.ToString() + "\\Setup\\", false))
{
strEditionArray[iCounter] =
(string)KeySetup.GetValue("Edition");
strVersionArray[iCounter] =
(string)KeySetup.GetValue("Version");
}
iCounter++;
}
}
return iCounter;
}
}
Now you can install SQL Server Express. First, convert the properties of the class into a command line argument that can be passed to the SQLEXPR.EXE installation application. The following method performs this task.
private string BuildCommandLine()
{
StringBuilder strCommandLine = new StringBuilder();
if (!string.IsNullOrEmpty(installSqlDir))
{
strCommandLine.Append("
INSTALLSQLDIR=\"").Append(installSqlDir).Append("\"");
}
if (!string.IsNullOrEmpty(installSqlSharedDir))
{
strCommandLine.Append("
INSTALLSQLSHAREDDIR=\"").Append(installSqlSharedDir).Append("\"");
}
if (!string.IsNullOrEmpty(installSqlDataDir))
{
strCommandLine.Append("
INSTALLSQLDATADIR=\"").Append(installSqlDataDir).Append("\"");
}
if (!string.IsNullOrEmpty(addLocal))
{
strCommandLine.Append(" ADDLOCAL=\"").Append(addLocal).Append("\"");
}
if (sqlAutoStart)
{
strCommandLine.Append(" SQLAUTOSTART=1");
}
else
{
strCommandLine.Append(" SQLAUTOSTART=0");
}
if (sqlBrowserAutoStart)
{
strCommandLine.Append(" SQLBROWSERAUTOSTART=1");
}
else
{
strCommandLine.Append(" SQLBROWSERAUTOSTART=0");
}
if (!string.IsNullOrEmpty(sqlBrowserAccount))
{
strCommandLine.Append("
SQLBROWSERACCOUNT=\"").Append(sqlBrowserAccount).Append("\"");
}
if (!string.IsNullOrEmpty(sqlBrowserPassword))
{
strCommandLine.Append("
SQLBROWSERPASSWORD=\"").Append(sqlBrowserPassword).Append("\"");
}
if (!string.IsNullOrEmpty(sqlAccount))
{
strCommandLine.Append(" SQLACCOUNT=\"").Append(sqlAccount).Append("\"");
}
if (!string.IsNullOrEmpty(sqlPassword))
{
strCommandLine.Append(" SQLPASSWORD=\"").Append(sqlPassword).Append("\"");
}
if (sqlSecurityMode == true)
{
strCommandLine.Append(" SECURITYMODE=SQL");
}
if (!string.IsNullOrEmpty(saPassword))
{
strCommandLine.Append(" SAPWD=\"").Append(saPassword).Append("\"");
}
if (!string.IsNullOrEmpty(sqlCollation))
{
strCommandLine.Append(" SQLCOLLATION=\"").Append(sqlCollation).Append("\"");
}
if (disableNetworkProtocols == true)
{
strCommandLine.Append(" DISABLENETWORKPROTOCOLS=1");
}
else
{
strCommandLine.Append(" DISABLENETWORKPROTOCOLS=0");
}
if (errorReporting == true)
{
strCommandLine.Append(" ERRORREPORTING=1");
}
else
{
strCommandLine.Append(" ERRORREPORTING=0");
}
return strCommandLine.ToString();
}
Now you can create the InstallExpress method.
InstallExpress
public bool InstallExpress()
{
//In both cases, we run Setup because we have the file.
Process myProcess = new Process();
myProcess.StartInfo.FileName = sqlExpressSetupFileLocation;
myProcess.StartInfo.Arguments = "/qb " + BuildCommandLine();
/* /qn -- Specifies that setup run with no user interface.
/qb -- Specifies that setup show only the basic
user interface. Only dialog boxes displaying progress information are
displayed. Other dialog boxes, such as the dialog box that asks users if
they want to restart at the end of the setup process, are not displayed.
*/
myProcess.StartInfo.UseShellExecute = false;
return myProcess.Start();
}
Now, create the sample application that calls this wrapper class.
class Program
{
static void Main(string[] args)
{
EmbeddedInstall EI = new EmbeddedInstall();
if (args.Length > 0)
{
int i = 0;
while (i < args.Length)
{
if ((string)args[i].ToUpper() == "-V")
{
string[] strInstanceArray = new string[0];
string[] strVersionArray = new string[0];
string[] strEditionArray = new string[0];
int iInstances = EI.EnumSQLInstances(ref
strInstanceArray, ref strEditionArray, ref strVersionArray);
if (iInstances > 0)
{
for (int j = 0; j <= iInstances - 1; j++)
{
Console.WriteLine("SQL Server Instance:
\"" + strInstanceArray[j].ToString() + "\" -- " +
strEditionArray[j].ToString() + " (" + strVersionArray[j].ToString() +
")");
}
}
else
{
Console.WriteLine("No instance of SQL Server
Express found on local server.\n\n");
}
return;
}
if ((string)args[i].ToUpper() == "-I")
{
if (EI.IsExpressInstalled())
{
Console.WriteLine("An instance of SQL Server
Express is installed.\n\n");
}
else
{
Console.WriteLine("There are no SQL Server
Express instances installed.\n\n");
}
return;
}
i++;
}
}
Console.WriteLine("\nInstalling SQL Server 2005 Express Edition\n");
EI.AutostartSQLBrowserService = false;
EI.AutostartSQLService = true;
EI.Collation = "SQL_Latin1_General_Cp1_CS_AS";
EI.DisableNetworkProtocols = false;
EI.InstanceName = "SQLEXPRESS";
EI.ReportErrors = true;
EI.SetupFileLocation = "C:\\Downloads\\sqlexpr.exe";
//Provide location for the Express setup file
EI.SqlBrowserAccountName = ""; //Blank means LocalSystem
EI.SqlBrowserPassword = ""; // N/A
EI.SqlDataDirectory = "C:\\Program Files\\Microsoft SQL Server\\";
EI.SqlInstallDirectory = "C:\\Program Files\\";
EI.SqlInstallSharedDirectory = "C:\\Program Files\\";
EI.SqlServiceAccountName = ""; //Blank means Localsystem
EI.SqlServicePassword = ""; // N/A
EI.SysadminPassword = "ThIsIsALoNgPaSsWoRd1234!!"; //<<Supply
a secure sysadmin password>>
EI.UseSQLSecurityMode = true;
EI.InstallExpress();
Console.WriteLine("\nInstalling custom application\n");
//If you need to run another MSI install, remove the following comment lines
//and fill in information about your MSI
/*Process myProcess = new Process();
myProcess.StartInfo.FileName = "";//<<Insert the path to your MSI file here>>
myProcess.StartInfo.Arguments = ""; //<<Insert any command line parameters here>>
myProcess.StartInfo.UseShellExecute = false;
myProcess.Start();*/
}
ClickOnce (Visual Studio 2005)
ClickOnce is a new feature that is part of the Microsoft .NET Framework version 2.0. ClickOnce lets you deploy Windows-based client applications to a computer by placing the application files on a Web or file server that is accessible to the client, and then providing the user with a link. This lets users download and run applications from centrally managed servers without requiring administrator privileges on the client machine.
In this section, the ClickOnce/SQL Server Express experience is illustrated by developing a simple WinForm application. This application uses the AdventureWorks sample database, which can be downloaded from the SQL Server 2005 Express Edition Documentation and Samples Web page in the Microsoft Download Center.
This example demonstrates how to create a single WinForm that will be used to view the departments in the HumanResources.Department table in the AdventureWorks database.
To create a WinForm that displays the Department table:
Launch Visual Studio.
Create a new Windows Application project.
When the Form1 Designer opens, add a reference to the AdventureWorks database.
Right click the Project node in the Solution Explorer pane, and then select both Add and Existing Item. Navigate to the AdventureWorks database and click OK.
The Data Source Configuration Wizard opens.
Under the Tables node, select the Department table, and then continue with the wizard.
When the wizard finishes, you will notice the "AdventureWorks.MDF" database icon in the Solution Explorer pane and a new "AdventureWorks" connection in the Database Explorer. The Database Explorer lets you perform database operations such as creating new tables, querying and modifying existing data, and other database development functions.
Add the DataGridView control to the WinForm. This grid control is located in the toolbox. When you drag the grid control onto the design surface, you have the option of selecting the AdventureWorks dataset that you created when you ran the Data Source Configuration Wizard. This dialog box is shown in Figure 1.
.gif "Bb264562.emsqlexcustapp01(en-US,SQL.90).gif")
Figure 1. DataGridView configuration pop-up
When a data source is configured you should be able to run the application and have the grid control display the values for the Department table, as shown in Figure 2.
.gif "Bb264562.emsqlexcustapp02(en-US,SQL.90).gif")
Figure 2. Sample AdventureWorks application
You can now deploy this application by using ClickOnce.
To deploy the application by using ClickOnce:
To publish the application, from the Build menu, select Publish. The Publish Wizard opens, as shown in Figure 3.
.gif "Bb264562.emsqlexcustapp03(en-US,SQL.90).gif")
Figure 3. Publish Wizard: where to publish the application page
The first page in the wizard specifies where the compiled bits should physically be placed. In this example, as shown in Figure 3, the application is published to the local hard drive.
In the Specify the location to publish this application box, enter C:\deploy\ViewDepartments.
Click Next to display the next page of the Publish Wizard. as shown in Figure 4. This page prompts for the location from which users will install the application.
.gif "Bb264562.emsqlexcustapp04(en-US,SQL.90).gif")
Figure 4. Publish Wizard: how will users install the application page
For this example, select From a CD-ROM or DVD-ROM.
Click Next to display the next page of the Publish Wizard, as shown in Figure 5. This page specifies whether the application will check for updates.
.gif "Bb264562.emsqlexcustapp05(en-US,SQL.90).gif")
Figure 5. Publish Wizard: check for updates page
ClickOnce provides applications with the ability to look for updates at certain times, such as the start of the application or whenever the application developer chooses to call the appropriate update APIs. There are some issues when using this feature with a database. These are discussed later in this document. For this example, select The application will not check for updates.
Click Next to display the last page of the wizard, as shown in Figure 6.
.gif "Bb264562.emsqlexcustapp06(en-US,SQL.90).gif")
Figure 6. Publish Wizard: Ready to publish
Besides displaying the summary information, the wizard notifies you that, because you are writing to a CD or DVD-ROM, Setup will install a shortcut and entry in Add or Remove Programs for your application.
Click Finish.
You can write an application that will live on the application server only and never be installed on the client machine. Regardless, ClickOnce will prompt the user to install any missing prerequisites, such as the .NET Framework 2.0 or SQL Server Express, as shown in Figure 7.
.gif "Bb264562.emsqlexcustapp07(en-US,SQL.90).gif")
Figure 7. Prerequisites not installed when user launches application
Note Whether the application itself is designed to be run on demand from an application server or to be installed locally, SQL Server Express is always installed on the local machine if the custom application requires it.
When the Publish Wizard finishes, new files are placed in the deployment directory. These files include the compressed data files and the setup installer application. You might want to copy these files to a CD and distribute them to your users, to provide them with the necessary information about applications that use SQL Server Express.
Important A user who is not an administrator on the local machine will not be able to install the .NET Framework or SQL Server Express. In this case, system administrators should deploy these components first. System administrators can do this either manually or by using a distributed software management system such as Microsoft Systems Management Server.
Updating ClickOnce Deployments that Use SQL Server Express
In this scenario, assume that the end user has successfully installed your application. The user had all necessary prerequisites installed, and the application is running successfully.
Data in the example application are entered in the original version 1.0 database. Now the custom application developers have come out with a new version, 2.0, of the application. This new version has an additional column named Location in the Departments table. This new column stores the geographical location of the department. When the developer deploys version 2.0, the new version of the database is pushed down to the client, and the previous version is automatically moved to a separate folder named Pre. The developer must now write a database migration script to move all the data from the 1.0 version in the Pre folder to the new database. Because Visual Studio does not have any tools to support this migration, it is completely up to the developer to perform the migration. Otherwise, none of the data that was entered in version 1.0 will be accessible to the application. Additionally, if the developer publishes an interim version (for example, 2.1) to reconcile this migration problem, or if the developer accesses the MDF file by simply viewing the structure in Server Explorer, ClickOnce will see that the date and time stamp has changed and deploy version 2.1 of the database. This moves version 2.0 of the database to the Pre folder and delete version 1.0 of the database. This results in complete data loss and a poor customer experience.
To avoid this, Visual Studio should not include the database files when the application is deployed. Instead, provide installation scripts to create the database. Also, when you perform a ClickOnce update, you must write and call a separate update script. The ViewDepartments example from the previous section is used in this section to help clarify the workaround solution.
ViewDepartments is a single WinForm application that connects to the AdventureWorks database and enumerates the Departments table. When you developed this application, you pointed Visual Studio to the AdventureWorks MDF file, which created a new data source. As the application functions now, if you used ClickOnce to deploy the application, the application would always include the AdventureWorks MDF file and cause the overwrite problems mentioned previously.
To avoid unwanted data loss in your application:
Do not copy the MDF file in the output directory.
In the Solution Explorer pane, click the AdventureWorks database icon, as shown in Figure 8. In the Properties pane, select Do not copy for the Copy to Output Directory property.
.gif "Bb264562.emsqlexcustapp08(en-US,SQL.90).gif")
Figure 8. Copy to Output Directory property
Do not publish the AdventureWorks database files.
Access the Project properties panel by selecting Properties from the Project menu. On the Publish tab, click Application Files. This launches a dialog box that contains a list of all of the files in the solution. As shown in Figure 9, change the publish status to Exclude for the MDF and the LDF files of the AdventureWorks database.
.gif "Bb264562.emsqlexcustapp09(en-US,SQL.90).gif")
Figure 9. Exclude database files
Script the creation of the AdventureWorks database.
You can script a database in many ways. In SQL Server Management Studio, you right-click the database in Object Explorer and can create the entire script there. Or, you can use the Generate SQL Server Scripts Wizard for more scripting options. If you do not have a license for this tool or any other scripting tool, you can easily create a small program that uses the SQL Server Management Objects (SMO) object model to create a script by using the Scripter class.
Note If you installed SQL Server Express and chose to install the developer components, the SMO DLLs are located by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.
Following is a modified AdventureWorks creation script that creates and populates the Departments table.
USE [master]
GO
CREATE DATABASE [AdventureWorks] ON PRIMARY
( NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf' , SIZE = 167936KB
, MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf' , SIZE = 2048KB ,
MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'AdventureWorks', @new_cmptlevel=90
GO
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TYPE [dbo].[Name] FROM [nvarchar](50) NULL
GO
EXEC sys.sp_executesql N'CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]'
GO
CREATE TABLE [HumanResources].[Department](
[DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[GroupName] [dbo].[Name] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT
[DF_Department_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Engineering','Research and Development')
GO
insert into [HumanResources].[Department](Name,Groupname) values('Tool
Design','Research and Development')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Sales','Sales and Marketing')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Marketing','Sales and Marketing')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Purchasing','Inventory Management')
GO
insert into [HumanResources].[Department](Name,Groupname) values('Research
and Development','Research and Development')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Production','Manufacturing')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Production Control','Manufacturing')
GO
insert into [HumanResources].[Department](Name,Groupname) values('Human
Resources','Executive General and Administration')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Finance','Executive General and Administration')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Information Services','Executive General and Administration')
GO
insert into [HumanResources].[Department](Name,Groupname) values('Document
Control','Quality Assurance')
GO
insert into [HumanResources].[Department](Name,Groupname) values('Quality
Assurance','Quality Assurance')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Facilities and Maintenance','Executive General and
Administration')
GO
insert into [HumanResources].[Department](Name,Groupname) values('Shipping
and Receiving','Inventory Management')
GO
insert into [HumanResources].[Department](Name,Groupname)
values('Executive','Executive General and Administration')
GO
--This next table is used to identify the version of the database
CREATE TABLE AdventureWorks..AppInfo
(Property nvarchar(255) NOT NULL,
Value nvarchar(255))
GO
INSERT INTO AdventureWorks..AppInfo Values(‘Version','1.0.0.0')
GO
Because the actual MDF file is not included in this solution, you must define and synchronize versions of the database that the application is connected to. An easy workaround is to add the AppInfo table to the AdventureWorks database. When you start the application, it should first check to see if the versions match. If they don't, the application should either run an upgrade script or fail. This is explained in more detail in step 3.
To implement a version check:
Store the script as an embedded resource within the application.
Before you add the script, you must add a resource file to your project. To do this, right-click the project in the Solution Explorer pane and select Add, and then select New Item. Select Resource File, and then click Add. This launches the Resource File document window shown in Figure 10.
Next, you could either add the SQL scripts as separate strings, or you could add them as text files. For simplicity, store your Create and Update scripts as separate files within this resource. To do this, on the Add resource drop-down menu, click Add existing file. Locate the creation script produced in step 2 and add this file.
.gif "Bb264562.emsqlexcustapp10(en-US,SQL.90).gif")
Figure 10. Resource document window showing our create database script
Create an upgrade script for the AdventureWorks database.
Although you might not have to upgrade your application right away, you should also include the upgrade script to upgrade your database to version 1.0.0.3.
USE [AdventureWorks] GO ALTER TABLE [HumanResources].[Department] ADD Location char(2) GO UPDATE AdventureWorks..AppInfo set Value='1.0.0.3' where Property='Version' GOSave this script as UpgradeAdventureWorks.sql. Add it to the resource file, as described in step 3.
Modify the application to check versions and run any necessary scripts.
Now you should modify your current design to make the version check and script execution happen.
Note In the previous example, the Form_Load method contains code auto-generated when we just assigned the AdventureWorks dataset via the UI:
this.departmentTableAdapter.Fill(this.adventureWorks_DataDataSet.Department);This should be removed or commented out because you want to perform the database version check first. You should also set the value to None for the DataSource property (see Figure 11) that was prepopulated in the grid control when you used the UI to bind the grid to the data source.
.gif "Bb264562.emsqlexcustapp11(en-US,SQL.90).gif")
Figure 11. DataSource property auto-generated by Visual Studio
The following is the complete code for the Form1 class:
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace ViewDepartments
{
public partial class Form1 : Form
{
enum VersionCheck { Failed = 0, Equal, DatabaseIsMoreNew,
DatabaseIsOlder, DatabaseNotFound };
private SqlConnection sqlCon = new SqlConnection();
private SqlCommand sqlCmd = new SqlCommand();
public Form1()
{
InitializeComponent();
if (SetupDatabase() == false)
{
return;
}
PopulateGrid();
}
public bool SetupDatabase()
{
bool bContinue = false;
//Create a connection to SQL Server
try
{
sqlCon.ConnectionString = "Server=.\\sqlexpress;Integrated Security=true";
sqlCon.Open();
}
catch (SqlException sql_ex)
{
MessageBox.Show("Fail to connect to SQL Server Express\n"
+ sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
return bContinue;
}
//Now that you are connected to Express, check the database
versions
switch (CheckVersion())
{
case (int)VersionCheck.Equal:
{
bContinue = true;
break;
}
case (int)VersionCheck.Failed:
{
bContinue = false;
break;
}
case (int)VersionCheck.DatabaseIsOlder:
{
//Run the upgrade script
bContinue =
RunScript(Resource1.UpdateAdventureWorks.ToString());
break;
}
case (int)VersionCheck.DatabaseIsMoreNew:
{
bContinue = false;
break;
}
case (int)VersionCheck.DatabaseNotFound:
{
//Run the creation script
bContinue =
RunScript(Resource1.CreateAdventureWorks.ToString());
break;
}
default:
{
bContinue = false;
break;
}
}
return bContinue;
}
public bool RunScript(string strFile)
{
string[] strCommands;
strCommands = ParseScriptToCommands(strFile);
try
{
if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
sqlCmd.Connection = sqlCon;
foreach (string strCmd in strCommands)
{
if (strCmd.Length > 0)
{
sqlCmd.CommandText = strCmd;
sqlCmd.ExecuteNonQuery();
}
}
}
catch (SqlException sql_ex)
{
MessageBox.Show(sql_ex.Number.ToString() + " " +
sql_ex.Message.ToString());
return false;
}
return true;
}
public int CheckVersion()
{
//Get Version information from application
Version v=new Version(Application.ProductVersion.ToString());
try
{
string strResult;
//Verify that the AdventureWorks Database exists
sqlCmd = new SqlCommand("select count(*) from
master..sysdatabases where name='AdventureWorks'",sqlCon);
strResult = sqlCmd.ExecuteScalar().ToString();
if (strResult == "0")
{
sqlCon.Close();
return (int)VersionCheck.DatabaseNotFound;
}
sqlCmd = new SqlCommand("SELECT value from
AdventureWorks..AppInfo where property='version'", sqlCon);
strResult=(string)sqlCmd.ExecuteScalar();
Version vDb = new Version(strResult);
sqlCon.Close();
if (vDb == v)
return (int)VersionCheck.Equal;
if (vDb > v)
return (int)VersionCheck.DatabaseIsMoreNew;
if (vDb < v)
return (int)VersionCheck.DatabaseIsOlder;
}
catch (SqlException sql_ex)
{
MessageBox.Show(sql_ex.Number.ToString() + " " +
sql_ex.Message.ToString());
return (int)VersionCheck.Failed;
}
catch (Exception system_ex)
{
MessageBox.Show(system_ex.Message.ToString());
return (int)VersionCheck.Failed;
}
return (int)VersionCheck.Failed;
}
public string[] ParseScriptToCommands(string strScript)
{
string[] commands;
commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
return commands;
}
public void PopulateGrid()
{
String strCmd = "Select * from [AdventureWorks].[HumanResources].[Department]";
SqlDataAdapter da;
da = new SqlDataAdapter(strCmd, sqlCon);
DataSet ds = new DataSet();
da.Fill(ds, "Departments");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Departments";
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the
'adventureWorks_DataDataSet.Department' table. You can move or remove this
line as necessary.
//this.departmentTableAdapter.Fill(this.adventureWorks_DataDataSet.
Department);
}
}
}
In the previous code, a call is made to SetDatabase(). This function will first attempt to make a connection to SQL Server Express. When that call succeeds, it calls into the CheckVersion() method. This method verifies that the AdventureWorks database exists. If it does, the CheckVersion() method obtains the version number from the AppInfo table. If the AdventureWorks database does not exist, the creation script that is located in the resource file is executed. If the database version is earlier than the application version, the upgrade script is run.
Note The version that is being compared against the database is coming from the File Version property of the project. This property can be set within the Assembly Information dialog box. This dialog box is accessible from the Application tab in Project Properties.
When you first execute this application against a blank SQL Server Express database, it creates the AdventureWorks database, and you see the four columns of the Departments table. The next time you execute this application, it will be upgraded to include another column in the table named Location.
Licensing
By following certain guidelines, you can freely distribute SQL Server Express with your custom application. First, you must register yourself on the SQL Server Express Web site, where you will also find the complete guide to SQL Server Express licensing. Then, review and accept the End User License Agreement.
Conclusion
For more information: