public void Excute()
{
WaitDialogForm sdf = null;
try
{
DataSyncConfig config = DataSyncConfig.GetDataSyncConfig();
if (config != null)
{
DataSyncDirection dataSyncDirection =
SenderMenu.Name.Equals(DataSyncMenuNames.DataSyncPageDoDataSyncGroupInnerDoDataSyncMenu) ? DataSyncDirection.InnerDataSync : DataSyncDirection.OuterDataSync;
sdf = new WaitDialogForm("提示", "正在同步数据......");
dataSyncOperator.SyncData(config, dataSyncDirection);
string filePath = dataCapture.CaptureDirtyFromDBAndSave(config, dataSyncDirection);
//打开数据
SyncDataResource syncDataResource = iocManager.Resolve <SyncDataResource>();
syncDataResource.FullName = filePath;
syncDataResource.Open();
XtraMessageBox.Show(dataSyncDirection == DataSyncDirection.InnerDataSync ? "内网数据同步完成" : "外网数据同步完成");
}
else
{
Logger.Warn("未找到数据同步的配置。/DataOperation/DataCaptureConfig.json");
}
}
catch (Exception ex)
{
EventBus.Default.Trigger(new NonUIExceptionEventData {
UnhandledExceptionEventArgs = new UnhandledExceptionEventArgs(ex, false)
});
}
finally {
sdf.Close();
}
}
private void CreateInsertATableRow(DataSyncDirection dataSyncDirection, DataTable dataTable, int rowNum, DBOutput dBOutput, string dirtyField)
{
string[] cols = dBOutput.Columns.Split(',');
string sql = string.Empty;
if (cols.Contains(dBOutput.KeyColumn))
{
sql = string.Format("Insert into {0}({1},{3}) values({2},-1)", dBOutput.TableName, dBOutput.Columns, string.Join(",", cols.Select(col => ":" + col)), dirtyField);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[cols.Length];
for (int i = 0; i < cols.Length; i++)
{
oracleParameters[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(cols[i], dataTable.Rows[rowNum][cols[i]]);
}
string connStr = GetconStrByDirection(dataSyncDirection);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
else
{
sql = string.Format("Insert into {0}({1},{3},{4}) values({2},-1,:{4})", dBOutput.TableName, dBOutput.Columns, string.Join(",", cols.Select(col => ":" + col)), dirtyField, dBOutput.KeyColumn);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[cols.Length + 1];
for (int i = 0; i < cols.Length; i++)
{
oracleParameters[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(cols[i], dataTable.Rows[rowNum][cols[i]]);
}
oracleParameters[cols.Length] = new Oracle.ManagedDataAccess.Client.OracleParameter(dBOutput.KeyColumn, dataTable.Rows[rowNum][dBOutput.KeyColumn]);
string connStr = GetconStrByDirection(dataSyncDirection);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
}
/// <summary>
/// 建立脏数据sql
/// </summary>
private string CreateDirtyFiledSql(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection)
{
IList <DBOutput> wantDBOutputs = null;
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{
wantDBOutputs = dataSyncConfig.InnerDBOutputs;
}
else
{
wantDBOutputs = dataSyncConfig.OutDBOutputs;
}
StringBuilder sb = new StringBuilder();
//alter table DJQ add dirty number(2); comment on column DJQ.dirty is '0，干净数据 1脏数据';
foreach (var dbOutput in wantDBOutputs)
{
sb.AppendFormat("alter table {0} add {1} number(2) default 1;\n\r comment on column {0}.{1} is '0，干净数据 1脏数据';\n\r", dbOutput.TableName, dataSyncConfig.DirtyField);
}
sb.Append("begin \r\n");
foreach (var dbOutput in wantDBOutputs)
{
sb.AppendFormat("update {0} set {1}=-1; \n\r", dbOutput.TableName, dataSyncConfig.DirtyField);
}
sb.Append(" commit; \r\n end; \r\n");
sb.Append("\r\n");
foreach (var dbOutput in wantDBOutputs)
{
sb.AppendFormat(" create index bmIndex_{0}_{1} on {0}({1}) ; \r\n", dbOutput.TableName, dataSyncConfig.DirtyField);
}
sb.Append("\r\n");
return(sb.ToString());
}
public void Excute()
{
try
{
DataSyncConfig config = DataSyncConfig.GetDataSyncConfig();
if (config != null)
{
DataSyncDirection dataSyncDirection =
SenderMenu.Name.Equals(DataSyncMenuNames.DataSyncPageSqlCreateGroupInnerDataInitMenu) ? DataSyncDirection.InnerDataSync : DataSyncDirection.OuterDataSync;
string result = triggerOperation.DoDBTriggerOperation(config, dataSyncDirection);
TextShowDialog textShowDialog = new TextShowDialog();
textShowDialog.SetText(result);
textShowDialog.ShowDialog();
}
else
{
Logger.Warn("未找到数据同步的配置。/DataOperation/DataCaptureConfig.json");
}
}
catch (Exception ex)
{
EventBus.Default.Trigger(new NonUIExceptionEventData {
UnhandledExceptionEventArgs = new UnhandledExceptionEventArgs(ex, false)
});
}
}
private void DeleteATableRow(DataSyncDirection dataSyncDirection, DataTable dataTable, int rowNum, DBOutput dBOutput, string dirtyField)
{
string sql = string.Format("Delete {0} where {1} =:{1}", dBOutput.TableName, dBOutput.KeyColumn);
string connStr = GetconStrByDirection(dataSyncDirection);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[1];
oracleParameters[0] = new Oracle.ManagedDataAccess.Client.OracleParameter(dBOutput.KeyColumn, dataTable.Rows[rowNum][dBOutput.KeyColumn]);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
public void SyncData(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection)
{
string outerCurrentPath = string.Empty;
IList <DBOutput> OutdBOutputs = null;
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{
//内网同步时，获取外网文件夹下面是否有需要同步的文件
outerCurrentPath = dataPathManager.GetDataCaptureSaveDirectory(DataSyncDirection.OuterDataSync);
OutdBOutputs = dataSyncConfig.OutDBOutputs;
}
else
{
outerCurrentPath = dataPathManager.GetDataCaptureSaveDirectory(DataSyncDirection.InnerDataSync);
OutdBOutputs = dataSyncConfig.InnerDBOutputs;
}
string[] filePaths = Directory.GetFiles(outerCurrentPath).OrderBy(f => Path.GetFileNameWithoutExtension(f)).ToArray();
if (filePaths.Length > 0)
{
foreach (string filePath in filePaths)
{
Logger.Debug("开始同步到" + (DataSyncDirection.InnerDataSync == dataSyncDirection ? "内网" : "外网") + "。同步文件路径：" + filePath);
DataSet ds = DatasetSerialize.DataSetDeserialize(filePath);
if (ds != null && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables.Count; i++)
{
Logger.Debug("开始" + ds.Tables[i].TableName);
string tableName = ds.Tables[i].TableName;
if (tableName.ToUpper().EndsWith("_HIS")) // 同步的时候要注意历史
{
DBOutput deleteDBOutput = GetConfigByTableName(OutdBOutputs, tableName.Replace("_HIS", ""));
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
DeleteATableRow(dataSyncDirection, ds.Tables[i], j, deleteDBOutput, dataSyncConfig.DirtyField);
}
}
DBOutput dBOutput = GetConfigByTableName(OutdBOutputs, tableName);
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
if (IfExistsData(dataSyncConfig, dataSyncDirection, ds.Tables[i].TableName, dBOutput.KeyColumn, ds.Tables[i].Rows[j][dBOutput.KeyColumn]))
{
CreateUpdateATableRow(dataSyncDirection, ds.Tables[i], j, dBOutput, dataSyncConfig.DirtyField);
}
else
{
CreateInsertATableRow(dataSyncDirection, ds.Tables[i], j, dBOutput, dataSyncConfig.DirtyField);
}
}
}
}
//数据同步之后，再将文件移入已同步
File.Move(filePath, dataPathManager.GetDataSyncedSaveDirectory(dataSyncDirection) + "//" + Path.GetFileName(filePath));
}
}
}
/// <summary>
/// 获得脏数据
/// </summary>
/// <returns></returns>
private DataSet CaptureDirtyFromDB(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection)
{
DataSet dataSet = new DataSet();
if (dataSyncConfig != null)
{
IList <DBOutput> dBOutputs = null;
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{ //内网数据同步
if (dataSyncConfig.InnerDBOutputs != null && dataSyncConfig.InnerDBOutputs.Count > 0)
{
dBOutputs = dataSyncConfig.InnerDBOutputs;
}
else
{
Logger.Warn("未发现内网数据同步配置");
}
}
else //外网数据同步
{
if (dataSyncConfig.OutDBOutputs != null && dataSyncConfig.OutDBOutputs.Count > 0)
{
if (dataSyncConfig.OutDBOutputs != null && dataSyncConfig.OutDBOutputs.Count > 0)
{
dBOutputs = dataSyncConfig.OutDBOutputs;
}
else
{
Logger.Warn("未发现外网数据同步配置");
}
}
}
if (dBOutputs != null)
{
foreach (DBOutput dbOutput in dBOutputs)
{
string sql = JointCaptureSql(dbOutput.TableName, dbOutput.Columns, dbOutput.KeyColumn, dataSyncConfig.DirtyField);
string connstr = connectStringCreator.GetOracleConstr(dataSyncDirection);
DataTable dt = OracleHelper.ExecuteDataTable(connstr, sql);
dt.TableName = dbOutput.TableName;
dataSet.Tables.Add(dt);
}
}
}
else
{
Logger.Error("未发现数据同步配置");
}
return(dataSet);
}
private string GetconStrByDirection(DataSyncDirection dataSyncDirection)
{
string conStr = string.Empty;
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{
conStr = connectStringCreator.GetOracleConstr(DataSyncDirection.InnerDataSync);
}
else
{
conStr = connectStringCreator.GetOracleConstr(DataSyncDirection.OuterDataSync);
}
return(conStr);
}
/// <summary>
/// 获取脏数据并且将数据保存在文件中
/// </summary>
/// <returns></returns>
public bool CaptureDirtyFromDBAndSave(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection)
{
try
{
DataSet ds = CaptureDirtyFromDB(dataSyncConfig, dataSyncDirection);
string savePath = dataPathManager.GetDataCaptureSavePath(dataSyncDirection);
DatasetSerialize.DataSetSerialize(savePath, ds);
SetUnDirty(dataSyncConfig, dataSyncDirection);
return(false);
}
catch (Exception ex) {
Logger.Error("获取脏数据，存储在本地出现错误！", ex);
throw ex;
}
}
private bool IfExistsData(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection, string tableName, string keyColumn, object keyValue)
{
string conStr = GetconStrByDirection(dataSyncDirection);
string sql = string.Format("Select Count(1) from {0} Where {1} = :{1}", tableName, keyColumn);
DataTable dataTable = OracleHelper.ExecuteDataTable(conStr, sql, new Oracle.ManagedDataAccess.Client.OracleParameter[] {
new Oracle.ManagedDataAccess.Client.OracleParameter(keyColumn, keyValue)
});
if (Convert.ToInt32(dataTable.Rows[0][0].ToString()) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获取数据同步后需要转存的文件地址
/// </summary>
/// <returns></returns>
public string GetDataSyncedSaveDirectory(DataSyncDirection dataSyncDirection)
{
string rootPath = System.Environment.CurrentDirectory;
string path = string.Empty;
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{
path = rootPath + "/SyncedDataSync/InnerDataSync";
}
else
{
path = rootPath + "/SyncedDataSync/OuterDataSync";
}
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
return(path);
}
/// <summary>
/// 获取当前获取脏数据的后本地保存的路径
/// </summary>
/// <returns></returns>
public string GetDataCaptureSavePath(DataSyncDirection dataSyncDirection)
{
string rootPath = System.Environment.CurrentDirectory;
string path = string.Empty;
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{
path = rootPath + "/CurrentDataSync/InnerDataSync";
}
else
{
path = rootPath + "/CurrentDataSync/OuterDataSync";
}
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
return(path + "/" + Timestamp() + SyncDataResourceMetaData.ExtenstionName);
}
/// <summary>
/// 建立触发器
/// </summary>
/// <param name="dataSyncConfig"></param>
/// <param name="dataSyncDirection"></param>
private string CreateTriggerSql(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection)
{
IList <DBOutput> wantDBOutputs = null;
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{
wantDBOutputs = dataSyncConfig.InnerDBOutputs;
}
else
{
wantDBOutputs = dataSyncConfig.OutDBOutputs;
}
StringBuilder sb = new StringBuilder();
foreach (var dbOutput in wantDBOutputs)
{
sb.AppendFormat("create or replace trigger WHDirty4{0} before insert or update on {0} for each row declare begin if(:new.{1}=-1) then :new.{1}:=0; else :new.{1}:=1; end if; end WHDirty4{0}; \n\r / \n\r", dbOutput.TableName, dataSyncConfig.DirtyField);
}
return(sb.ToString());
}
public string GetOracleConstr(DataSyncDirection dataSyncDirection)
{
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{
return(OracleHelper.GetConnStr(dataSyncSettings.InnerSyncSetting_Address,
dataSyncSettings.InnerSyncSetting_Port,
dataSyncSettings.InnerSyncSetting_SID,
dataSyncSettings.InnerSyncSetting_User,
dataSyncSettings.InnerSyncSetting_Password
));
}
else
{
return(OracleHelper.GetConnStr(dataSyncSettings.OutNetworkSyncSetting_Address,
dataSyncSettings.OutNetworkSyncSetting_Port,
dataSyncSettings.OutNetworkSyncSetting_SID,
dataSyncSettings.OutNetworkSyncSetting_User,
dataSyncSettings.OutNetworkSyncSetting_Password
));
}
}
private void CreateUpdateATableRow(DataSyncDirection dataSyncDirection, DataTable dataTable, int rowNum, DBOutput dBOutput, string dirtyField)
{
string[] cols = dBOutput.Columns.Split(',');
if (cols.Any(c => c.Equals(dBOutput.KeyColumn)))
{
cols.Remove(c => c.Equals(dBOutput.KeyColumn));
}
string sql = string.Format("Update {0} Set {1},{3} Where {2} =:{2}", dBOutput.TableName, string.Join(",", cols.Select(col => " " + col + "=:" + col + " ")),
dBOutput.KeyColumn, dirtyField + "=:" + dirtyField);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[cols.Length + 2];
for (int i = 0; i < cols.Length; i++)
{
oracleParameters[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(cols[i], dataTable.Rows[rowNum][cols[i]]);
}
oracleParameters[cols.Length] = new Oracle.ManagedDataAccess.Client.OracleParameter(dirtyField, (object)-1);
oracleParameters[cols.Length + 1] = new Oracle.ManagedDataAccess.Client.OracleParameter(dBOutput.KeyColumn, dataTable.Rows[rowNum][dBOutput.KeyColumn]);
string connStr = GetconStrByDirection(dataSyncDirection);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
/// <summary>
/// 将数据设置为干净的数据
/// </summary>
private void SetUnDirty(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection)
{
string connstr = connectStringCreator.GetOracleConstr(dataSyncDirection);
if (dataSyncConfig != null)
{
if (dataSyncDirection == DataSyncDirection.InnerDataSync)
{ //设置内网数据为干净数据
foreach (DBOutput dBOutput in dataSyncConfig.InnerDBOutputs)
{
SetUnDirty(dBOutput.TableName, dataSyncConfig.DirtyField, connstr);
}
}
else
{ //设置外网数据为干净数据
foreach (DBOutput dBOutput in dataSyncConfig.OutDBOutputs)
{
SetUnDirty(dBOutput.TableName, dataSyncConfig.DirtyField, connstr);
}
}
}
}
public static void DoDBTriggerOperationCilck(IocManager iocManager, DataSyncDirection dataSyncDirection, TriggerOperation triggerOperation, ILogger logger)
{
try
{
DataSyncConfig config = GetDataSyncConfig();
if (config != null)
{
string result = triggerOperation.DoDBTriggerOperation(config, dataSyncDirection);
TextShowDialog textShowDialog = new TextShowDialog();
textShowDialog.SetText(result);
textShowDialog.ShowDialog();
}
else
{
logger.Warn("未找到数据同步的配置。/DataOperation/DataCaptureConfig.json");
}
}
catch (Exception ex) {
EventBus.Default.Trigger(new NonUIExceptionEventData {
UnhandledExceptionEventArgs = new UnhandledExceptionEventArgs(ex, false)
});
}
}
public static void DataSyncOperationClick(IocManager iocManager, DataSyncDirection dataSyncDirection, DataCapture dataCapture, DataSyncOperator dataSyncOperator, ILogger logger)
{
try
{
DataSyncConfig config = GetDataSyncConfig();
if (config != null)
{
WaitDialogForm sdf = new WaitDialogForm("提示", "正在同步数据......");
dataSyncOperator.SyncData(config, dataSyncDirection);
dataCapture.CaptureDirtyFromDBAndSave(config, dataSyncDirection);
MessageBox.Show(dataSyncDirection == DataSyncDirection.InnerDataSync? "内网数据同步完成" : "外网数据同步完成");
sdf.Close();
}
else
{
logger.Warn("未找到数据同步的配置。/DataOperation/DataCaptureConfig.json");
}
}
catch (Exception ex) {
EventBus.Default.Trigger(new NonUIExceptionEventData {
UnhandledExceptionEventArgs = new UnhandledExceptionEventArgs(ex, false)
});
}
}
public string DoDBTriggerOperation(DataSyncConfig dataSyncConfig, DataSyncDirection dataSyncDirection)
{
return(CreateDirtyFiledSql(dataSyncConfig, dataSyncDirection) + " \r\n \r\n" +
CreateTriggerSql(dataSyncConfig, dataSyncDirection));
}
