private void ensure_ecpr_diff_table_is_created() { var createEcprDiffTableQuery = @" SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON CREATE TABLE [dbo].[ecpr_diff]( [table] [varchar](max) NOT NULL, [change_id] [int] NOT NULL, [column] [varchar](max) NOT NULL, [original] [varchar](max) NULL, [new] [varchar](max) NULL, [changed_on] [datetime] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] SET ANSI_PADDING OFF "; if (CPRDataAnalysis.executeNonQuery(createEcprDiffTableQuery)) { CPRDataAnalysis.logger.write("ecpr_diff table is created.", Logger.LOGLEVEL.INFO); } }
private void ensure_clones_diff_tables() { foreach (var tableInfo in CPRDataAnalysis.config.diff_tables) { var cloneQuery = String.Format("SELECT * INTO ecpr_{0}_clone FROM {0}", tableInfo.Key); if (CPRDataAnalysis.executeNonQuery(cloneQuery)) { CPRDataAnalysis.logger.write(String.Format("{0} is cloned to ecpr_{0}_clone.", tableInfo.Key), Logger.LOGLEVEL.INFO); } } }
public static void compareDataDiff(string tableName, Dictionary <string, object> data, string changed_on) { if (CPRDataAnalysis.sqlConnection.State == ConnectionState.Closed) { try { CPRDataAnalysis.sqlConnection.Open(); } catch (Exception) { return; } } var needStop = DateTime.Compare(CPRDataAnalysis.config.diff_stop, DateTime.UtcNow); if (needStop <= 0) { return; } try { var sqlDataReaderCount = Convert.ToInt32(CPRDataAnalysis.executeQueryScalar("SELECT COUNT(*) FROM ecpr_diff")); if (sqlDataReaderCount >= CPRDataAnalysis.config.diff_max) { return; } int change_id = Convert.ToInt32(CPRDataAnalysis.executeQueryScalar("SELECT COALESCE(MAX(change_id), 0) + 1 as maxChangeID FROM ecpr_diff")); if (CPRDataAnalysis.config.diff_tables.ContainsKey(tableName)) { string pk_val = (string)CPRDataAnalysis.config.diff_tables[tableName]["pk"]; var data_val = data[pk_val]; string originTableQuery = String.Format("SELECT * FROM ecpr_{0}_clone where {1}={2}", tableName, pk_val, data_val); string insertToEcprDiffTable = "INSERT INTO ecpr_diff ([table], change_id, [column], original, new, changed_on) VALUES ('{0}', {1}, '{2}', '{3}', '{4}', '{5}')"; List <string> insertToEcprDiffTableList = new List <string>(); SqlDataReader sqlDataReader = CPRDataAnalysis.executeQuery(originTableQuery); if (sqlDataReader != null && sqlDataReader.HasRows) { while (sqlDataReader.Read()) { foreach (var d in data) { var originVal = sqlDataReader[d.Key]; var changed_on_dt = new DateTime(1970, 1, 1, 0, 0, 0).ToLocalTime(); changed_on_dt = changed_on_dt.AddSeconds(Convert.ToInt64(changed_on)); if (!originVal.Equals(d.Value)) { insertToEcprDiffTableList.Add(String.Format(insertToEcprDiffTable, tableName, change_id, d.Key, originVal, d.Value, changed_on_dt.ToString("MM/dd/yyyy hh:mm:ss tt"))); } } change_id++; } sqlDataReader.Close(); foreach (var item in insertToEcprDiffTableList) { CPRDataAnalysis.executeNonQuery(item); } } } }catch (Exception error) { CPRDataAnalysis.logger.write(error.Message, Logger.LOGLEVEL.INFO); } }