SQL Express Embedding Webcast - Source Code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace EmbeddingDemo_ResourceFile
{
class CheckDbVersion
{
#region Declarations
private SqlConnection _connection = new SqlConnection();
private SqlCommand _command = new SqlCommand();
private String _dbPath = "";
private Version _appVersion = new Version(Properties.Settings.Default.dbVersion);
enum versionCheck
{
Failed = 0,
Equal,
DatabaseIsMoreNew,
DatabaseIsOlder,
DatabaseNotFound
}
#endregion
#region Public Methods
/// <summary>
/// Constructor for CheckDbVersion Class
/// </summary>
/// <remarks>Instantiating the class will set the value for the DataDirectory property
/// based on an application property. If the property is not available, the DataDirectory
/// will be set to either the ClickOnce Data directory or the Assembly location, depending
/// upon the way the application is deployed.
///
/// The DataDirectory property is used to locate the database file.</remarks>
public CheckDbVersion()
{
SetDataPath();
_dbPath = (string)AppDomain.CurrentDomain.GetData("DataDirectory") + Path.DirectorySeparatorChar + Properties.Settings.Default.EmbeddedDbName;
// Set the connection string
_connection.ConnectionString = Properties.Settings.Default.AdventureWorks_Dept_DataConnectionString;
}
/// <summary>
/// VerifyDatabase() is the main Public method called from initiallization code
/// for the application.
/// </summary>
/// <returns>Boolean indicating if the database is valid and at the
/// correct version.
/// True == Database is valid and ready to use.
/// False == Database is invalid and can not be used.</returns>
public bool VerifyDatabase()
{
switch (CheckVersion())
{
default:
{
return false;
}
case (int)versionCheck.Failed:
{
return false;
}
case (int)versionCheck.DatabaseNotFound:
{
return false;
}
case (int)versionCheck.Equal:
{
// Database is ready to use.
return true;
}
case (int)versionCheck.DatabaseIsMoreNew:
{
// Database is ready to use.
return true;
}
case (int)versionCheck.DatabaseIsOlder:
{
// If the database is older than the app, call the update script
// from the application resoureces
bool bResult = RunScript(Properties.Resources.UpgradeScriptResourceFile.ToString());
return bResult;
}
}
}
#endregion
#region Private Methods
/// <summary>
/// Used to set the path where the database will be created.
/// </summary>
/// <remarks>SetDataPath() supports three types of deployments:
/// 1. Custom Location: Use path specified in Settings.
/// 2. ClickOnce: Use default DataDirectory.
/// 3. Manual XCopy: Use assembly location.</remarks>
private void SetDataPath()
{
// Determin the correct path for the database
if (Properties.Settings.Default.CustomDataDirectory == "")
{
if (AppDomain.CurrentDomain.GetData("DataDirectory") == null)
{
// Not ClickOnce and no custom directory, so use the file
// location of the assembly.
string appDir = Assembly.GetExecutingAssembly().Location;
appDir = Path.GetDirectoryName(appDir);
AppDomain.CurrentDomain.SetData("DataDirectory", appDir);
return;
}
// The DataDirectory property is not null and no custom directory is
// specified. Must be ClickOnce, so use the default directory.
return;
}
// Use the specified directory for the database.
// First ensure the custom directory exists. Create it if it doesn't.
String target = Properties.Settings.Default.CustomDataDirectory;
if (!Directory.Exists(target))
{
Directory.CreateDirectory(target);
}
AppDomain.CurrentDomain.SetData("DataDirectory", target);
return;
}
/// <summary>
/// CheckVersion() manages the database creation and versioning
/// </summary>
/// <remarks>If the database does not exist, CheckVersion() will install it
/// from the resource file. Once the database exists, CheckVersion() will verify the version
/// against the dbVersion property of the application and return a versionCheck enumeration.</remarks>
/// <returns>versionCheck</returns>
private int CheckVersion()
{
// Get version information about the database
// First need to check if the database even exists
FileInfo fi = new FileInfo(_dbPath);
if (!fi.Exists)
{
// database file doesn't exist
if (CreateDatabase(fi))
{
return CheckVersion();
}
return (int)versionCheck.DatabaseNotFound;
}
else
{
// the database exists, so check it's version against
// the application version.
String strResults;
// since the database file is present connect to it.
//_connection.ConnectionString = Properties.Settings.Default.AdventureWorks_Dept_DataConnectionString;
if (_connection.State != ConnectionState.Open) _connection.Open();
_command.CommandText = "SELECT value from dbo.AppInfo WHERE property = 'version'";
_command.Connection = _connection;
strResults = (string)_command.ExecuteScalar();
Version v = new Version(strResults);
_connection.Close();
// return a value from the enum
if (v == _appVersion) return (int)versionCheck.Equal;
if (v > _appVersion) return (int)versionCheck.DatabaseIsMoreNew;
if (v < _appVersion) return (int)versionCheck.DatabaseIsOlder;
}
// if it didn't exit yet, then something went wonky
return (int)versionCheck.Failed;
}
/// <summary>
/// Creates the database from resource file
/// </summary>
/// <param name="fi">FileInfo object for db location</param>
private Boolean CreateDatabase(FileInfo fi)
{
// The database does not exist, add it from Embedded Resoureces
Assembly ens = Assembly.GetExecutingAssembly();
// Read the database from resource
Stream s = ens.GetManifestResourceStream("EmbeddingDemo_ResourceFile." + Properties.Settings.Default.EmbeddedDbName);
byte[] dbFile = new byte[s.Length];
s.Read(dbFile, 0, (int)s.Length);
s.Close();
// create a new file and write the data to it.
FileStream fsDatabase = fi.Create();
fsDatabase.Write(dbFile, 0, (int)dbFile.Length);
fsDatabase.Close();
return true;
}
/// <summary>
/// Update script handler
/// </summary>
/// <param name="strFile">passed update script</param>
public bool RunScript(string strFile)
{
// Parse the update script into a set of executable commands
string[] strCommands;
strCommands = ParseScriptToCommands(strFile);
try
{
if (_connection.State != ConnectionState.Open) _connection.Open();
_command.Connection = _connection;
foreach (string strCmd in strCommands)
{
if (strCmd.Length > 0)
{
_command.CommandText = strCmd;
_command.ExecuteNonQuery();
}
}
}
catch (SqlException sql_ex)
{
MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString(), "Run Script");
return false;
}
return true;
}
/// <summary>
/// Parsing function for batch scripts
/// </summary>
/// <param name="strScript">script</param>
public string[] ParseScriptToCommands(string strScript)
{
string[] commands;
commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
return commands;
}
#endregion
}
}
Database Script
USE
[master]
GO
CREATE
DATABASE [AdventureWorks_Dept]
GO
USE
[AdventureWorks_Dept]
GO
CREATE
TYPE [dbo].[Name] FROM [nvarchar](50) NULL
GO
CREATE
SCHEMA [HumanResources] AUTHORIZATION [dbo]
GO
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[AppInfo](
[Property] [nvarchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Value] [nvarchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
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 (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
Update Script
ALTER
TABLE [HumanResources].[Department]
ADD Location char(2)
GO
UPDATE
AppInfo set Value='1.0.0.1' where
Property='Version'
GO