private void BackUpFilteredDb(string copyDbName) { string targetNameForRar = string.Format("{0}{1}.bak", DiskOperations.DataTemp_ForBackUpDirectory, copyDbName); DiskOperations.AssertFileNotExist(targetNameForRar, Utility._Error_ExistTargetBackUpDb); SqlCommandRunner.BackUpDb(copyDbName, targetNameForRar); }
private List <string> FindAllColums(string dbName, string tableName) { List <string> retVal = new List <string>(); string fetchColumns = string.Format(@"select column_name from {0}.information_schema.columns where table_name = '{1}'", dbName, tableName); try { using (SqlDataReader sdr = SqlCommandRunner.ExecuteReader(new SqlCommand(fetchColumns), dbName)) { while (sdr.Read()) { retVal.Add(sdr["column_name"].ToString()); } } } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}/{2},原因是:{3}", _Error_Columns_Fetch, dbName, tableName, e.Message)); } if (retVal.Count == 0) { throw new ApplicationException(string.Format("{0}{1}/{2},原因是:未能筛选出任何列名", _Error_Columns_Fetch, dbName, tableName)); } return(retVal); }
internal void RestoreData(DateTime?fromDay, DateTime?toDay, RestoreStatus theRuningStatusInSession) { //备份源数据库 TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}{1}", Utility._Process_BackUpDb, _DbName)); string targetName = string.Format("{0}{1}{2}.bak", DiskOperations.DbBackUp_ForRestoreDirectory, _DbName, Utility.GetTimeStamp()); SqlCommandRunner.BackUpDb(_DbName, targetName); //删减数据库备份 TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}", Utility._Process_DelNonuseDbBackUp)); theRuningStatusInSession.AddInformationLine(DiskOperations.DelFilesFromDirectory(DiskOperations.DbBackUp_ForRestoreDirectory, _DbName, targetName, _DefaultBackUpDbCount)); //拷贝下载的数据库用于数据还原 string copyDbName = _DbName + _ForRestoreCopyDbSuffix; string downLoadDbBackUpFullName = string.Format("{0}{1}{2}.bak", DiskOperations.DataTemp_ForRestoreDirectory, _DbName, _OrginCopyDbSuffix); TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}{1}", Utility._Process_CopyDb, copyDbName)); SqlCommandRunner.RestoreDbFromFile(copyDbName, DiskOperations.TempDirectory, downLoadDbBackUpFullName); //每一个表的数据还原 foreach (TableTransfer tt in _TablesToTransfer) { tt.RestoreData(fromDay, toDay, theRuningStatusInSession); } //删除无用的数据库拷贝 TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}{1}", Utility._Process_DelNonUseDb, copyDbName)); SqlCommandRunner.DeleteDb(copyDbName); }
private bool RowDataIsSame(int copyId, string tableName) { //先比较所有的列的数目是否相同 int needStoreColumnCount = FindAllColums(_RestoreDbName, tableName).Count; int copyDataColumnCount = FindAllColums(_ForRestoreCopyDbName, tableName).Count; if (needStoreColumnCount != copyDataColumnCount) { throw new ApplicationException(string.Format("{0}{1}", _Error_TowTable_NotSameColumn, tableName)); } //构建每一列值的哈希值,并比较 int[] hashCodeOfneedStoreData = new int[needStoreColumnCount]; int[] hashCodeOfneedCopyData = new int[needStoreColumnCount]; string rowCommand = string.Format("select * from {0} where pkid = {1}", tableName, copyId); try { using (SqlDataReader sdr = SqlCommandRunner.ExecuteReader(new SqlCommand(rowCommand), _RestoreDbName)) { while (sdr.Read()) { for (int i = 0; i < needStoreColumnCount; i++) { hashCodeOfneedStoreData[i] = sdr[i].GetHashCode(); } } } } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}{2},原因是:{3}", _Error_RowRead_Failed, tableName, copyId, e.Message)); } try { using (SqlDataReader sdr = SqlCommandRunner.ExecuteReader(new SqlCommand(rowCommand), _ForRestoreCopyDbName)) { while (sdr.Read()) { for (int i = 0; i < needStoreColumnCount; i++) { hashCodeOfneedCopyData[i] = sdr[i].GetHashCode(); } } } } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}{2},原因是:{3}", _Error_RowRead_Failed, tableName, copyId, e.Message)); } for (int i = 0; i < needStoreColumnCount; i++) { if (hashCodeOfneedStoreData[i] != hashCodeOfneedCopyData[i]) { return(false); } } return(true); }
public string RestoreTableData(DateTime?fromDay, DateTime?toDay) { List <ConstraintInfo> backUpConstraintInfo = SqlCommandRunner.GetConstraintInfo(_MainTable, _RestoreDbName); SqlCommandRunner.DropTable(_MainTable, _RestoreDbName); SqlCommandRunner.CopyTable(_MainTable, _ForRestoreCopyDbName, _RestoreDbName); SqlCommandRunner.RestoreConstraintInfo(backUpConstraintInfo, _MainTable, _RestoreDbName); return(string.Format("--表{0}共计:覆盖所有数据", _MainTable)); }
private void DeleteData(int orginId, string tableName, string dbName) { string sqlCommand = string.Format("delete from {0} where pkid = {1}", tableName, orginId); try { SqlCommandRunner.ExecuteNonQuery(new SqlCommand(sqlCommand), dbName); } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}", _Error_FilterTable, e.Message)); } }
private string FilterTheMainTable(DateTime?fromDay, DateTime?toDay, string dbName) { int allCount = SqlCommandRunner.GetTableRowCount(_MainTable, dbName); int delRowCount; string sqlCommand = DefineTheMainTableFilterCommand(fromDay, toDay); try { delRowCount = SqlCommandRunner.ExecuteNonQuery(new SqlCommand(sqlCommand), dbName); } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}", _Error_FilterTable, e.Message)); } return(string.Format("--表{0}共计:总行数{1},删减{2}行数据", _MainTable, allCount, delRowCount)); }
private void AddData(int copyId, string tableName) { string theColumnsString = MakeColumnsString(FindAllColums(_RestoreDbName, tableName), _RestoreDbName, tableName); StringBuilder addCommand = new StringBuilder(); addCommand.AppendLine(string.Format("SET IDENTITY_INSERT {0} ON", tableName)); addCommand.AppendLine(string.Format(@"insert into {0}({1}) select * from {2}.dbo.{0} where pkid = {3}", tableName, theColumnsString, _ForRestoreCopyDbName, copyId)); addCommand.AppendLine(string.Format("SET IDENTITY_INSERT {0} OFF", tableName)); try { SqlCommandRunner.ExecuteNonQuery(new SqlCommand(addCommand.ToString()), _RestoreDbName); } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1},主键是:{2},原因是:{3}", _Error_AddRow, tableName, copyId, e.Message)); } }
private List <int> FindAllPkids(string tableName, string theDb) { List <int> retVal = new List <int>(); try { using (SqlDataReader sdr = SqlCommandRunner.ExecuteReader(new SqlCommand(string.Format("select pkid from {0}", tableName)), theDb)) { while (sdr.Read()) { retVal.Add(int.Parse(sdr["pkid"].ToString())); } } } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}", _Error_FilterTable, e.Message)); } return(retVal); }
private List <int> FindAllPkidsInOrginTable(DateTime?fromDay, DateTime?toDay, string dbName) { List <int> retVal = new List <int>(); try { using (SqlDataReader sdr = SqlCommandRunner.ExecuteReader(new SqlCommand(DefineTheMainTableSelectCommand(fromDay, toDay)), dbName)) { while (sdr.Read()) { retVal.Add(int.Parse(sdr["pkid"].ToString())); } } } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}", _Error_FilterTable, e.Message)); } return(retVal); }
private string FilterTheOtherTable(List <int> theRemainedMainTablePkids, string dbName, Dictionary <string, List <int> > tablesAndIds) { StringBuilder sb = new StringBuilder(); foreach (KeyValuePair <string, string> theColumnn in DefineProtectedTableFkColumnName()) { int allCount = SqlCommandRunner.GetTableRowCount(theColumnn.Key, dbName); try { string theCommand = string.Format("delete from {0} where {1} not in({2})", theColumnn.Key, theColumnn.Value, MakePkidStrings(theRemainedMainTablePkids)); sb.AppendLine(string.Format("--表{0}共计:总行数{1},删减{2}行数据", theColumnn.Key, allCount, SqlCommandRunner.ExecuteNonQuery(new SqlCommand(theCommand), dbName))); tablesAndIds.Add(theColumnn.Key, FindAllPkids(theColumnn.Key, dbName)); } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}", _Error_FilterTable, e.Message)); } } return(sb.ToString()); }
private string DropNonUseTable(string theReleatedDb) { StringBuilder retVal = new StringBuilder("--丢弃表:"); //先删除所有外键,防止无法删除的表 SqlCommandRunner.DelAllFks(theReleatedDb); //再删除表 foreach (string aTable in SqlCommandRunner.GetAllTables(_DbName)) { if (FindTableTransfer(aTable) == null && FindTableProtected(aTable) == null) { SqlCommandRunner.DropTable(aTable, theReleatedDb); retVal.Append(aTable).Append(","); } } if (retVal.ToString().EndsWith(",")) { return(retVal.Remove(retVal.Length - 1, 1).ToString()); } return(retVal.ToString()); }
private List <int> FindAllPkidsWithMainTableIds(string tableName, string colunName, List <int> allPkidsInOrginTable, string dbName) { string command1 = string.Format("select pkid from {0} where {1} in({2})", tableName, colunName, MakePkidStrings(allPkidsInOrginTable)); List <int> retVal = new List <int>(); try { using (SqlDataReader sdr = SqlCommandRunner.ExecuteReader(new SqlCommand(command1), dbName)) { while (sdr.Read()) { retVal.Add(int.Parse(sdr["pkid"].ToString())); } } } catch (Exception e) { throw new ApplicationException(string.Format("{0}{1}", _Error_FilterTable, e.Message)); } return(retVal); }
internal void BackUpData(DateTime?fromDay, DateTime?toDay, RunningStatus theRuningStatusInSession) { //备份源数据库 TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}{1}", Utility._Process_BackUpDb, _DbName)); string targetName = string.Format("{0}{1}{2}.bak", DiskOperations.DbBackUp_ForBackUpDirectory, _DbName, Utility.GetTimeStamp()); SqlCommandRunner.BackUpDb(_DbName, targetName); //删减数据库备份 TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}", Utility._Process_DelNonuseDbBackUp)); theRuningStatusInSession.AddInformationLine(DiskOperations.DelFilesFromDirectory(DiskOperations.DbBackUp_ForBackUpDirectory, _DbName, targetName, _DefaultBackUpDbCount)); //拷贝数据库用于数据筛选 string copyDbName = _DbName + _OrginCopyDbSuffix; TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}{1}", Utility._Process_CopyDb, copyDbName)); SqlCommandRunner.RestoreDbFromFile(copyDbName, DiskOperations.TempDirectory, targetName); //删除无用的表 TransferService.StartLittleProcess(theRuningStatusInSession, Utility._Process_DelNonUseTable); theRuningStatusInSession.AddInformationLine(DropNonUseTable(copyDbName)); //有用表数据筛选 foreach (TableTransfer tt in _TablesToTransfer) { tt.BackUpData(fromDay, toDay, theRuningStatusInSession); } //将筛选完毕的数据库备份到指定地点用于打包压缩 TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}{1}", Utility._Process_BackUpFiltedTable, copyDbName)); BackUpFilteredDb(copyDbName); //删除无用的数据库拷贝 TransferService.StartLittleProcess(theRuningStatusInSession, string.Format("{0}{1}", Utility._Process_DelNonUseDb, copyDbName)); SqlCommandRunner.DeleteDb(copyDbName); }
internal void TryBackUpErrorClean() { SqlCommandRunner.DeleteDb(_DbName + _OrginCopyDbSuffix); }
internal void TryRestoreErrorClean() { SqlCommandRunner.DeleteDb(_DbName + _ForRestoreCopyDbSuffix); }
public string FilterTableData(DateTime?fromDay, DateTime?toDay) { int allCount = SqlCommandRunner.GetTableRowCount(_MainTable, _OrginDbName); return(string.Format("--表{0}共计:总行数{1},删减0行数据", _MainTable, allCount)); }