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