private UpgradeSQL loadUpgradeSQL() { UpgradeSQL result = null; string fileName = "Upgrade.json"; var streamInfo = readAppFile(fileName); if (streamInfo == null) { streamInfo = readAppResource(fileName); } if (streamInfo != null) { StreamReader sr = new StreamReader(streamInfo.Stream); try { result = UpgradeSQL.loadJson(sr.ReadToEnd()); } catch (JsonException e) { Trace.TraceError(e.Message); Trace.TraceError(e.StackTrace); Debug.WriteLine(e); } } return(result); }
public static void DoUpgradeDb() { var dbVerItem = SettingItemUtils.GetDbVersion(); int v = Convert.ToInt32(dbVerItem.Value); Trace.TraceInformation("current database version {0}", v); UpgradeSQL upgradeSQL = GlobalSetting.Instance.GetUpgradeSQL(); int newerVersion = upgradeSQL.SQLS.Count; Trace.TraceInformation("newer database version {0}", newerVersion); if (newerVersion > v) { Trace.TraceInformation("upgrade database version from {0} to {1}", v, newerVersion); using (var dbConn = getConnection()) { dbConn.Open(); using (var transaction = dbConn.BeginTransaction()) { for (int i = v; i < newerVersion; i++) { List <string> sqls = upgradeSQL.SQLS[i]; foreach (string sql in sqls) { Trace.TraceInformation("run sql {0}", sql); var cmd = new SqliteCommand(sql, dbConn, transaction); cmd.ExecuteNonQuery(); } } Trace.TraceInformation("write current database version to db"); var verSQL = string.Format("INSERT OR REPLACE INTO SETTING(KEY, VALUE) VALUES ('db.version', '{0}');", newerVersion); var verCmd = new SqliteCommand(verSQL, dbConn, transaction); verCmd.ExecuteNonQuery(); transaction.Commit(); } } } else { Trace.TraceInformation("database version is already the latest"); } }