WPF-26 开发简单数据库版本控制引擎

我们开发一个简单的脚本升级引擎，主要解决数据库脚本升级，通过应用程序统一升级不同版本应用程序的SQL，我们通过一张简单图来帮助理解：.

思路很简单，我们可以通过应用程序的版本号和数据库版本号做比较，如果应用程序版本号大于数据库版本号，执行脚本。

我们定义IDBUpgradeStep接口表示不同版本之间执行的SQL语句，指定版本的Step和每个Step执行的SQL

  public interface IDBUpgradeStep  {     bool CanUpgrade(Version version);     IDbConnection DbConnection { set; get; }     Task Run();  }

 //该脚本表示在1.3.1 版本时，我们创建一张Order表    public class DBUpgradeStepVersion1_3_1 : IDBUpgradeStep    {        static readonly Version targetVersion = new Version(1, 3, 1, 0);        public IDbConnection DbConnection        {            get;            set;        }        public bool CanUpgrade(Version version)        {            return version < targetVersion;        }        public async Task Run()        {            //升级数据库            var sql = @"CREATE TABLE ""Order""(                        orderId int,                        orderName varchar(255),                        costProce varchar(255)                        );";            await DbConnection.ExecuteAsync(sql);            System.Diagnostics.Debug.Print(this.GetType().ToString() + " "+ sql);        }    }

    //该脚本表示在1.6.0 版本时，我们为Order表新增TotalAmount字段    public class DBUpgradeStepVersion1_6_0 : IDBUpgradeStep    {        static readonly Version targetVersion = new Version(1, 6, 0, 0);        public IDbConnection DbConnection        {            get;            set;        }        public bool CanUpgrade(Version version)        {            return version < targetVersion;        }        public async Task Run()        {            //升级数据库            var sql = @"Alter Table ""Order"" add TotalAmount money; ";            await DbConnection.ExecuteAsync(sql);            System.Diagnostics.Debug.Print(this.GetType().ToString() + " " + sql);        }    }

    //该脚本表示在1.7.0 版本时，我们为Order表新增Description字段    public class DBUpgradeStepVersion1_7_0 : IDBUpgradeStep    {        static readonly Version targetVersion = new Version(1, 7, 0, 0);        public IDbConnection DbConnection        {            get;            set;        }        public bool CanUpgrade(Version version)        {            return version < targetVersion;        }        public async Task Run()        {            //升级数据库            var sql = @"Alter Table ""Order"" add Description Varchar(255);";            await DbConnection.ExecuteAsync(sql);            System.Diagnostics.Debug.Print(this.GetType().ToString() + " " + sql);        }    }

    //这是最后执行脚本，更新应用程序版本，使应用程序版本和数据库版本一致    public class DBUpgradeStepFinal : IDBUpgradeStep    {        static readonly Version targetVersion = new ApplicationVersion().GetVersion();        #region Members        public bool CanUpgrade(Version version)        {            return version < targetVersion;        }        public IDbConnection DbConnection        {            get;            set;        }        public async Task Run()        {            var sql = @"Update Version set Major=@Major,Minor=@Minor,Build=@Build,Revision=@Revision,LastModifiedDate=@LastModifiedDate";            var param = new            {                Major = targetVersion.Major,                Minor = targetVersion.Minor,                Build = targetVersion.Build,                Revision = targetVersion.Revision,                LastModifiedDate = DateTime.Now            };            await DbConnection.ExecuteAsync(sql, param);        }        #endregion    }

接下来我们定义一个IVersionInfo接口，定义两个具体实现ApplicationVersion获取当前应用程序版本和DBVersion获取当前数据库版本

    public interface IVersionInfo    {        Version CurVersion { get; }    }

    /// <summary>    /// 获取应用程序版本    /// </summary>    public class ApplicationVersion :IVersionInfo    {        public Version CurVersion        {            get            {                return GetVersion();            }        }        #region IVersionInfo Members        public Version GetVersion()        {            return GetType().Assembly.GetName().Version;        }        #endregion    }

    //获取当前数据库的版本    public class DBVersion : IVersionInfo    {        private IDbConnection _dbConnection;        private Version curVer = null;        public DBVersion(IDbConnection dbConnection)        {            _dbConnection =dbConnection;            curVer = GetVersion();        }        public Version CurVersion { get { return curVer; } }        #region IVersionInfo Members        public Version GetVersion()        {            try            {                Version ver = new Version(0, 0, 0, 0);
                var versionList = _dbConnection.Query<dynamic>("SELECT Major,Minor,Build,Revision FROM Version");                var version = versionList.FirstOrDefault();                if (version != null)                {                    int major = version.Major;                    int minor = version.Minor;                    int build = version.Build;                    int revision = version.Revision;                    ver = new Version(major, minor, build, revision);                }                return ver;            }            catch (Exception ex)            {                throw;            }        }        #endregion    }

接下来定义一个DatabaseUpgradeManager类，来判断具体要升级的脚本

    public class DatabaseUpgradeManager    {        private IDbConnection  _dbConnection;        private static readonly Version applicationVersion = new ApplicationVersion().GetVersion();        private List<IDBUpgradeStep> steps = new List<IDBUpgradeStep>();
        /// <summary>        /// Constructor.        /// </summary>        /// <param name="sdfPath">The path of the SDF to be upgraded.</param>        public DatabaseUpgradeManager(IDbConnection dbConnection)        {            _dbConnection = dbConnection;            DBVersionInfo = new DBVersion(dbConnection);        }        /// <summary>        /// Add steps to this CEDatabaseUpgradeManager instance.        /// </summary>        /// <param name="step">The step to be added.</param>        public void AddSteps(IDBUpgradeStep step)        {            step.DbConnection = _dbConnection;            steps.Add(step);        }        /// <summary>        /// Get the count of steps in this instance.        /// </summary>        public int StepCount        {            get { return steps.Count; }        }        /// <summary>        /// Get or set the DBVersion provider.        /// </summary>        public IVersionInfo DBVersionInfo        {            set;            get;        }        /// <summary>        /// Execute the steps.        /// </summary>        /// <returns>Return true if upgrade successfully, otherwise return false.</returns>        public async Task<bool> DoUpgradeAsync()        {            Version dbVersion;            try            {                foreach (IDBUpgradeStep step in this.steps)                {                    dbVersion = DBVersionInfo.CurVersion;                    if (dbVersion >= applicationVersion) break;                    if (step.CanUpgrade(dbVersion))                    {                        await step.Run();                    }                }                return true;            }            catch (Exception ex)            {                return false;            }        }    }

我们使用VS创建一个LocalDB数据库，命名为UpgrateDB，并创建一张Version表，并初始化表：

CREATE TABLE [dbo].[Version] (    [Major]            INT      NOT NULL,    [Minor]            INT      NOT NULL,    [Build]            INT      NOT NULL,    [Revision]         INT      NOT NULL,    [LastModifiedDate] DATETIME NOT NULL);

WPF-26 开发简单数据库版本控制引擎

假如我们现在要Release 2.0 版本，我们修改一下应用程序版本号：

WPF-26 开发简单数据库版本控制引擎

我们在应用程序启动时添加如下代码：

    public partial class MainWindow : Window    {        public MainWindow()        {            InitializeComponent();            Loaded += MainWindow_Loaded;        }        private async void MainWindow_Loaded(object sender, RoutedEventArgs e)        {            await UpgradeDatabaseAsync();        }        public async Task UpgradeDatabaseAsync()        {            var connection = new SqlConnection(@"Server=(localdb)\MSSQLLocalDB;Initial Catalog=UpgrateDB;Integrated Security=true");            DatabaseUpgradeManager clientDBUpgrader = new DatabaseUpgradeManager(connection);            #region upgrate            clientDBUpgrader.AddSteps(new DBUpgradeStepVersion1_3_1());            clientDBUpgrader.AddSteps(new DBUpgradeStepVersion1_6_0());            clientDBUpgrader.AddSteps(new DBUpgradeStepVersion1_7_0());            #endregion            clientDBUpgrader.AddSteps(new DBUpgradeStepFinal());            if (!await clientDBUpgrader.DoUpgradeAsync())            {                Application.Current.Shutdown(1);            }        }    }

我们运行应用程序将执行的脚本打印出来，我们可以看到数据库从版本0升级到版本2.0 过程中的脚本都被执行了，我们还可以通过这种方式实现不同版本脚本升级到最新版本

WPF-26 开发简单数据库版本控制引擎

我们通过上面创建了一个简单的脚本升级工具，其中在我们真实大型产品开发过程中，我们会通过一些开发的工具(或者第三方工具)来对比不同版本数据库对象之间的差异，自动生成脚本，并将脚本嵌入到安装包内，在软件安装过程中写一些逻辑判断以及对其进行升级，包括一些基础配置都是通过安装过程进行配置。

董川民