/// <summary> /// Clear data for merge override table /// </summary> /// <param name="tables">override table</param> public void DeleteDestinationOverride(List <string> tables) { foreach (string table in tables) { bool exists = DbHelper.IsTableExists(Destination.ConnectionString(), table); if (exists) { string sql = string.Format("Delete From {0}", table); DbHelper.ExecuteSql(Destination.ConnectionString(), sql); } } }
/// <summary> /// Compare source and destination database version /// 1. Source equal to destination : true /// 2. Source newer than destination, upgrade database and return upgrade result /// 3. Destination newer than source, stop backup task /// </summary> /// <returns>若來源版本相同或升級成功:true、來源版本較舊或升級失敗:false</returns> public bool CheckVersion() { bool sourceIsExists = DbHelper.IsTableExists(Source.ConnectionString(), "WebDBVersion"); bool destinationIsExists = DbHelper.IsTableExists(Destination.ConnectionString(), "WebDBVersion"); if (!sourceIsExists || !destinationIsExists) { this.Log += "Can't get source/destination database version." + Environment.NewLine; return(false); } int sourceVer = (int)(DbHelper.ReadOne(Source.ConnectionString(), "Select TOP 1 VersionNum From WebDBVersion Order By klKey DESC")); int destinationVer = (int)(DbHelper.ReadOne(Destination.ConnectionString(), "Select TOP 1 VersionNum From WebDBVersion Order By klKey DESC")); if (sourceVer - destinationVer > 0) { System.Windows.MessageBoxResult result = System.Windows.MessageBox.Show("Warning: Database upgrade operation has risk.\n\nAre you sure continue?", "Database Upgrade Confirm", System.Windows.MessageBoxButton.OKCancel, System.Windows.MessageBoxImage.Warning); if (result == System.Windows.MessageBoxResult.OK) { if (UpgradeDatabase(sourceVer, destinationVer)) { return(true); } else { this.Log += "An error occurred during upgrade database." + Environment.NewLine; return(false); } } else { this.Log += "Database upgrade cancelled by user." + Environment.NewLine; return(false); } } else if (sourceVer == destinationVer) { return(true); } else { this.Log += "Destination database version is higher than source." + Environment.NewLine; return(false); } }
/// <summary> /// Upgrade database /// </summary> /// <returns>return upgrade result</returns> public bool UpgradeDatabase(int sourceVer, int destinationVer) { // Get installation directory from registry Microsoft.Win32.RegistryKey registry = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Wow6432Node\Wintriss Engineering\Web Inspector"); if (registry != null) { if (registry.GetValueNames().Contains("INSTALLDIR")) { try { string dbUtilityDir = registry.GetValue("INSTALLDIR").ToString() + @"\Database Utility\"; List <string> cmdPathList = new List <string>(); while (destinationVer <= sourceVer) { cmdPathList.AddRange(System.IO.Directory.GetFiles(dbUtilityDir) .Where(f => f.Contains(string.Format("Upgrade_{0}", destinationVer))) .ToList()); destinationVer++; } string conn = Destination.ConnectionString(); foreach (string path in cmdPathList) { DbHelper.ExecuteScript(conn, path); } return(true); } catch (Exception e) { return(false); } } } return(false); }
public void bgwValidateConnection_DoWorkHandler(object sender, DoWorkEventArgs e) { try { List <CheckedListItem> backupTables = e.Argument as List <CheckedListItem>; Smo.Server srvDestination = new Smo.Server(Destination.ServerConnection); if (!srvDestination.Databases.Contains(Destination.Database)) { #region Create Database and Copy Table sechma Smo.Database newdb = new Smo.Database(srvDestination, Destination.Database); newdb.Create(); Smo.Server srvSource = new Smo.Server(Source.ServerConnection); Smo.Database dbSource = srvSource.Databases[Source.Database]; Smo.Transfer transfer = new Smo.Transfer(dbSource); transfer.CopyAllUsers = true; transfer.CopyAllObjects = true; transfer.CopyAllTables = false; transfer.CopyData = false; transfer.CopySchema = true; transfer.Options.WithDependencies = true; transfer.Options.DriAll = true; transfer.Options.ContinueScriptingOnError = false; // Create all table when database not exist foreach (var tbl in dbSource.Tables) { transfer.ObjectList.Add(tbl); } transfer.DestinationServer = Destination.Server; transfer.DestinationDatabase = newdb.Name; transfer.DestinationLoginSecure = Destination.LoginSecurity; if (!Destination.LoginSecurity) { transfer.DestinationLogin = Destination.UserId; transfer.DestinationPassword = Destination.Password; } transfer.TransferData(); #endregion #region Get Source Data and Filter DataSet ds = DbHelper.CopySechmaFromDatabase(Source.ConnectionString()); List <string> sortTables = DbHelper.Fill(Source.ConnectionString(), ds, backupTables.Select(b => b.Name).ToList()); if (ds.Tables["Jobs"].Rows.Count > 0) { ds.Tables["Jobs"].Rows.Cast <DataRow>().LastOrDefault().Delete(); // Always delete last record. ds.Tables["Jobs"].AcceptChanges(); if (IsDateFiltration) { ds.Tables["Jobs"].Rows.Cast <DataRow>().Where(j => (DateTime)j["Date"] < DateFrom || (DateTime)j["Date"] > DateTo).ToList().ForEach(j => j.Delete()); } foreach (var tbl in sortTables) { ds.Tables[tbl].AcceptChanges(); } } #endregion #region Execute SqlBulk Copy foreach (string tbl in sortTables) { DbHelper.ExecuteSqlBulk(Destination.ConnectionString(), ds.Tables[tbl]); this.Progress += 100 / backupTables.Count; if (DbHelper.SqlBulkLog.Count > 0) { this.Log += DbHelper.SqlBulkLog.LastOrDefault() + Environment.NewLine; } } #endregion } else { if (CheckVersion()) { // TODO: now todo here. #region Get Source Data and Filter date range DataSet ds = DbHelper.CopySechmaFromDatabase(Source.ConnectionString()); List <string> sortTables = DbHelper.Fill(Source.ConnectionString(), ds, backupTables.Select(b => b.Name).ToList()); if (ds.Tables["Jobs"].Rows.Count > 0) { ds.Tables["Jobs"].Rows.Cast <DataRow>().LastOrDefault().Delete(); // Always delete last record. ds.Tables["Jobs"].AcceptChanges(); if (IsDateFiltration) { ds.Tables["Jobs"].Rows.Cast <DataRow>().Where(j => (DateTime)j["Date"] < DateFrom || (DateTime)j["Date"] > DateTo).ToList().ForEach(j => j.Delete()); } foreach (var tbl in sortTables) { ds.Tables[tbl].AcceptChanges(); } } #endregion #region Get destination PK list of table exists and modify for merge // filter override table don't modify key List <string> overrideTable = backupTables.Where(b => b.IsOverride == true).Select(b => b.Name).ToList(); foreach (string tbl in sortTables) { if (!overrideTable.Contains(tbl)) { string keycolumn = DbHelper.PrimaryKeyColumn(Destination.ConnectionString(), tbl); string sql = string.Format("Select TOP 1 {0} From {1} Order By {0} DESC", keycolumn, tbl); int? lastkey = (int?)(DbHelper.ReadOne(Destination.ConnectionString(), sql)); if (lastkey != null) { int newkey = (int)lastkey + 1; int row = ds.Tables[tbl].Rows.Count; ds.Tables[tbl].Columns[keycolumn].ReadOnly = false; for (int i = row - 1; i >= 0; i--) { ds.Tables[tbl].Rows[i][keycolumn] = newkey + i; } ds.Tables[tbl].AcceptChanges(); } } } ds.AcceptChanges(); #endregion #region Delete override table data List <string> clearTable = backupTables.Where(b => b.IsOverride == true).Select(b => b.Name).ToList(); DeleteDestinationOverride(clearTable); #endregion #region Execute SqlBulk Copy foreach (string tbl in sortTables) { DbHelper.ExecuteSqlBulk(Destination.ConnectionString(), ds.Tables[tbl]); this.Progress += 100 / backupTables.Count; if (DbHelper.SqlBulkLog.Count > 0) { this.Log += DbHelper.SqlBulkLog.LastOrDefault() + Environment.NewLine; } } #endregion } else { this.Log += "*** Database upgrade failed ***" + Environment.NewLine; e.Result = false; } } } catch (Exception) { this.Log += "An error occurred during backup database." + Environment.NewLine; e.Result = false; } // DONE: 1. Check destination database exsits. // DONE: 2. If No date range, use transfer copy all database. // DONE: 2-1. If use date range, DataTable.Select(); filter Jobs key (klKey) and filter another table has FK by Jobs (fkJobKey, klJobKey) // DONE: 2-2. Use Sqlbulk copy datatable // DONE: 3. If YES Check db version // CheckVersion(); // DONE: 3-1. Source > Destination => upgrade scripts // DONE: 3-2. Source == Destination => Run step 4 for merge data. // DONE: 3-3. Source < Destination => false; alert message and block; // DONE: 4. Deal table releationship PK/FK to create DataSet & Datatable from Source. // DONE: 5. If table of ObservTable selected get record of Destination last PK int. // List<Record> Record.LastKey, Record.TableName, Record.PKColumnName // TODO: 6. DataSet.Fill(); get data and filter date range. // TODO: 7. Use Sqlbulk copy datatable. }