使用DbUp完成数据库更新

class UpdateDBHelper { /// <summary> /// Sql脚本路径格式 /// </summary> private const string SCRIPT_PATH_FORMAT = "./SqlScripts/{0}"; /// <summary> /// 升级数据库 /// 注意：所有需要执行的Sql脚本必须按照严格版本顺序排序 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="message">升级信息</param> /// <returns>升级是否成功</returns> public static bool UpdateDb(List<string> connectionStringList, out string message) { bool updateResult = true; message = string.Empty; SqlConnectionStringBuilder connectionStringBuilder = null; string scriptPath = string.Empty; string dbName = string.Empty; foreach (var connectionString in connectionStringList) { connectionStringBuilder = new SqlConnectionStringBuilder(connectionString); dbName = connectionStringBuilder.InitialCatalog; scriptPath = string.Format(SCRIPT_PATH_FORMAT, dbName); updateResult = UpdateSingleDb(connectionString, scriptPath, out message); if (!updateResult) { break; } else { Console.WriteLine($"{dbName} 升级成功！"); } } return updateResult; } private static bool UpdateSingleDb(string connectionString, string path, out string message) { message = ""; //检查数据库是否存在,如果不存在就创建然后运行脚本 EnsureDatabase.For.SqlDatabase(connectionString); var updateEngineBuilder = DeployChanges.To .SqlDatabase(connectionString) .WithScriptsFromFileSystem(path) .LogToConsole(); updateEngineBuilder.Configure(configure => { configure.ScriptExecutor.ExecutionTimeoutSeconds = 28800; }); var updateEngine = updateEngineBuilder.Build(); if (!PreUpdate(updateEngine, out message)) { return false; } var result = updateEngine.PerformUpgrade();//升级数据库 if (!result.Successful) { message = result.Error.ToString(); return false; } return true; } /// <summary> /// 升级前检查 /// </summary> /// <param name="upgradeEngine"></param> /// <param name="errorMsg"></param> /// <returns></returns> private static bool PreUpdate(UpgradeEngine upgradeEngine, out string errorMsg) { errorMsg = string.Empty; if (!upgradeEngine.TryConnect(out errorMsg)) { return false; } //判断是否有数据库版本信息 var sqlList = upgradeEngine.GetDiscoveredScripts(); if (sqlList.Count == 0) { errorMsg = "升级数据库：找不到数据库版本，无法初始化数据库！"; return false; } //版本控制是否异常 if (upgradeEngine.GetExecutedButNotDiscoveredScripts().Count > 0) { errorMsg = $"版本控制异常,存在 {upgradeEngine.GetExecutedButNotDiscoveredScripts().Count} 个异常版本！"; return false; } return true; } }

使用：

        static void Main(string[] args)
        {
            List<string> connectionStringList = new List<string> { "Data Source=xx.xx.xx.xx,30705;uid=sa;pwd=xxxxxxxxx;Initial Catalog=test3;Pooling=true;Max Pool Size=1000;Min Pool Size=5;Connection Timeout=28800" };
            string updateDbMessage = string.Empty;
            Console.WriteLine("更新数据库 开始。。。。");
            bool updateDbResult = UpdateDBHelper.UpdateDb(connectionStringList, out updateDbMessage);
            if (!updateDbResult)
            {
                Console.WriteLine($"更新数据库 失败!-{updateDbMessage}");
            }
            else
            {
                Console.WriteLine("更新数据库 成功!");
            }
            Console.ReadKey();
        }

 

参考：https://dbup.readthedocs.io/en/latest/

使用DbUp完成数据库更新

标签：net   timeout   span   count   files   严格   als   null   ring