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