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 executeQueryLogs(string cText) { using (SqlConnection _sqlConnection = new SqlConnection(config.cprDatabaseConnectionString)) { SqlDataReader sqlDataReader = this.executeQuery(cText, _sqlConnection); if (sqlDataReader != null) { if (sqlDataReader.HasRows) { foreach (CPRRowData cprRowData in this.SerializeCPR(sqlDataReader)) { string ecpr_id = cprRowData.customer; cprRowData.customer = this.config.cprCustomerCode; string jsonConvert = JsonConvert.SerializeObject(cprRowData, this.jsonSerializerSettings); //DataChangesCache.AddItem(GetHashString(jsonConvert), jsonConvert); DataChangesCache.AddItem(String.Format("ECPR_CUD_ID:{0}:{1}", this.tableName, ecpr_id), jsonConvert); if (cprRowData.type == "update" && this.config.diff_tables != null) { System.Threading.Tasks.Task.Factory.StartNew(() => { CPRDataAnalysis.compareDataDiff(this.tableName, cprRowData.data, cprRowData.datetime); }); } } ; } sqlDataReader.Close(); } } }
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); } } }
private void longPoll() { if (Utils.isStartingFromWindowsRestart()) { Thread.Sleep(this.config.wait_before_start_from_windows_restart * 1000); } logger.write("Checking internet/network connection...", Logger.LOGLEVEL.INFO); while (!NetworkMonitoring.IsInternetAvailable()) { logger.write(String.Format("No internet connection. Will try again after {0} secs.", this.config.try_again_after), Logger.LOGLEVEL.ERROR); Thread.Sleep(this.config.try_again_after * 1000); } logger.write("Internet/network connection is succesful.", Logger.LOGLEVEL.INFO); this.logger.ensureConnect(); logger.write("Checking SQL SERVER connection...", Logger.LOGLEVEL.INFO); string errorMessage; while (!Utils.try_connect_to_db(this.config.cprDatabaseConnectionString, out errorMessage)) { logger.write(String.Format("Had an error while trying to connect to SQL SERVER. Will try again after {0} secs.", this.config.try_again_after), Logger.LOGLEVEL.ERROR); logger.write(errorMessage, Logger.LOGLEVEL.ERROR); Thread.Sleep(this.config.try_again_after * 1000); } logger.write("SQL SERVER connection is succesful.", Logger.LOGLEVEL.INFO); logger.write("STARTING ECPR service...", Logger.LOGLEVEL.INFO); this.serviceControlCenter = new ServiceControlCenter(this.config, this.logger); this.serviceControlCenter.addManager(this); this.logger.write(String.Format("Connecting to database {0} ...", this.config.cprDatabase), Logger.LOGLEVEL.INFO); this.sqlConnection = new SqlConnection(connectionString); Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger); System.Timers.Timer ecprTimer = new System.Timers.Timer(); ecprTimer.Elapsed += (sender, e) => Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger); ecprTimer.Interval += (this.config.defaultDatabaseConnectionTimeout * 1000 + 5000); ecprTimer.Enabled = true; if (isReadyToBeStarted) { this.ensureTablesExisting(); this.ensureFieldsExisting(); this.getIgnoreFieldsPK(); } if (sqlConnection.State == ConnectionState.Open) { this.syncControlCenter = new SyncControlCenter(this.config, this.logger); this.fullsyncControlCenter = new FullSyncControlCenter(this.config, this.logger); } if ((syncControlCenter != null && !syncControlCenter.isSubscribed) || (serviceControlCenter != null && !serviceControlCenter.isSubscribed)) { logger.write( String.Format("Cannot subscribed to amqp channels, will try again in {0} secs !!!!!!!", intervalSecs / 1000), Logger.LOGLEVEL.ERROR); } if (!this.config.needToFetchSchemaAndData) { dbSchemaPush(); dbDataPush(); this.config.needToFetchSchemaAndData = true; } if (sqlConnection.State == ConnectionState.Open) { this.isReadyToBeStarted = true; foreach (string tableName in this.config.cprTables.Keys) { try { // Make sure table is exists before subscribe bool tableExists = false; SqlDataReader sqlDataReader = this.doSQL(String.Format("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME ='{0}'", tableName)); if (sqlDataReader != null) { if (sqlDataReader.HasRows) { tableExists = true; } sqlDataReader.Close(); } if (tableExists) { var qN = new QueryNotification( this.config.cprTables[tableName].ToArray(), tableName, this.logger, this.config, this.config.restListAPIJSONMapping, this.config.autoCleanupIfRestart); qN.subscribe(); } else { this.logger.write(String.Format("Cannot subscribed the table {0} for query notification because it doesn't exists in database. Ignored it!", tableName), Logger.LOGLEVEL.ERROR); } } catch (Exception error) { this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR); this.isReadyToBeStarted = false; } } } if (this.config.diff_tables != null && this.isReadyToBeStarted) { this.logger.write("Enable DIFF Tables feature.", Logger.LOGLEVEL.INFO); cprDataAnalysis = new CPRDataAnalysis(this.config, this.logger); cprDataAnalysis.Init(); } this._eventsThread = new Thread(eventsProcess); this._eventsThread.IsBackground = false; this._eventsThread.Start(); this._eventsTimer = new System.Timers.Timer(this.intervalSecs); this._eventsTimer.AutoReset = false; this._eventsTimer.Elapsed += (sender, e) => this._inProcessEvent.Set(); this._eventsTimer.Start(); logger.write("SERVICE STARTED!!!", 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); } }