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