DbUp可以帮我们创建数据库,通过脚本文件创建表、添加数据;并可通过新创建脚本文件升级现有数据库
每个脚本文件只会执行一次,如果需要修改表结构或添加数据,添加新sql脚本文件,而不要修改原文件
脚本文件0001、0002、0003....按顺序执行
安装:
dbup-core (如果使用.NET Framework框架,安装dbup)
dbup-sqlserver (sqlserver数据库扩展,也可选择其他数据库扩展包)
项目截图:
助手类:
public 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/