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