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));
}
