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 [ http://download.microsoft.com/download/f/1/c/f1cf7b8d-7fb9-4b71-a658-e748e67f9eba/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 [ http://msdn.microsoft.com/vstudio/express/sql/default.aspx ] 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 [ http://msdn2.microsoft.com/en-us/library/bb264564.aspx ] 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
[
http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
] 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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'
GO
Save 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.
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
[ http://go.microsoft.com/fwlink/?linkid=51861 ] 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:
Microsoft SQL Server Express Web site [ http://www.microsoft.com/sql/editions/express/default.mspx ]