/// <summary> /// 上传 /// </summary> /// <param name="server"></param> /// <param name="client"></param> /// <param name="tableSynch"></param> /// <returns></returns> private int Upload(OracleDapperDber server, OracleDapperDber client, TableSynch tableSynch) { int synchCount = 0; DataTable dtClient = client.ExecuteDataTable(OracleSqlBuilder.BuildGetNeedSynchTableSQL(tableSynch.TableName, tableSynch.SynchField)); foreach (DataRow dr in dtClient.Rows) { string execSql = string.Empty; if (server.ExecuteScalar <int>(OracleSqlBuilder.BuildHasRecordSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString())) == 0) { execSql = OracleSqlBuilder.BuildInsertSQL(tableSynch.TableName, tableSynch.SynchField, dr); } else { execSql = OracleSqlBuilder.BuildUpdateSQL(tableSynch.TableName, tableSynch.SynchField, tableSynch.PrimaryKey, dr); } if (server.Execute(execSql) > 0) { synchCount += 1; client.Execute(OracleSqlBuilder.BuildUpdateSynchFieldSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString(), tableSynch.SynchField)); } } return(synchCount); }
/// <summary> /// 下达 /// </summary> /// <param name="server"></param> /// <param name="client"></param> /// <param name="tableSynch"></param> /// <returns></returns> private int Download(OracleDapperDber server, OracleDapperDber client, TableSynch tableSynch) { int synchCount = 0; DataTable dtServer = server.ExecuteDataTable(OracleSqlBuilder.BuildGetNeedSynchTableSQL(tableSynch.TableName, tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier)); foreach (DataRow dr in dtServer.Rows) { string execSql = string.Empty; if (client.ExecuteDataTable(OracleSqlBuilder.BuildHasRecordSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString())).Rows[0][0].ToString() == "0") { execSql = OracleSqlBuilder.BuildInsertSQL(tableSynch.TableName, tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier, dr); } else { execSql = OracleSqlBuilder.BuildUpdateSQL(tableSynch.TableName, tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier, tableSynch.PrimaryKey, dr); } if (client.Execute(execSql) > 0) { synchCount += 1; server.Execute(OracleSqlBuilder.BuildUpdateSynchFieldSQL(tableSynch.TableName, tableSynch.PrimaryKey, dr[tableSynch.PrimaryKey].ToString(), tableSynch.SynchField, this._CommonAppConfig.SyncIdentifier)); } } return(synchCount); }
void AnalyticData(SendMessage data) { int syncCount = 0; bool IsMatch = false; //MachineCodes item = ServerConfiguration.Instance.Details.Where(a => a.UpLoadIdentifer == data.UpLoadIdentifier).FirstOrDefault(); foreach (MachineCodes item in ServerConfiguration.Instance.Details.Where(a => a.UpLoadIdentifer == data.UpLoadIdentifier)) { if (data.UpLoadIdentifier == item.UpLoadIdentifer) { IsMatch = true; // 未设置主键名则跳过 if (string.IsNullOrEmpty(item.PrimarKeys)) { grabPerformer_OutputInfo(string.Format("{0} 提取未执行,原因:未设置主键(PrimaryKeys)参数", item.UpLoadIdentifer)); break; } OracleDapperDber selfDber = new OracleDapperDber(ServerConfiguration.Instance.SelfConnStr); using (OracleConnection connection = selfDber.CreateConnection() as OracleConnection) { if (data.DataColumns != null) { // 在数据中创建表 if (connection.ExecuteScalar <int>(CMCS.CommonADGS.Core.OracleSqlBuilder.BuildHasTableSQL(item.DataTableName)) == 0) { connection.Execute(CMCS.CommonADGS.Core.OracleSqlBuilder.BuildTableSQL(item.DataTableName, data.DataColumns)); } } else if (data.DataRows != null) { string execSql = string.Empty; // 生成主键值 string primaryKeyValue = item.UpLoadIdentifer + "-" + CMCS.CommonADGS.Core.OracleSqlBuilder.BuildPrimaryKeyValue(item.PrimarKeys, data.DataRows); if (connection.ExecuteScalar <int>(CMCS.CommonADGS.Core.OracleSqlBuilder.BuildHasRecordSQL(item.DataTableName, primaryKeyValue)) == 0) { execSql = CMCS.CommonADGS.Core.OracleSqlBuilder.BuildInsertSQL(item.DataTableName, primaryKeyValue, item.UpLoadIdentifer, data.DataRows); } else { execSql = CMCS.CommonADGS.Core.OracleSqlBuilder.BuildUpdateSQL(item.DataTableName, primaryKeyValue, item.UpLoadIdentifer, data.DataRows); } syncCount += connection.Execute(execSql); } } } } if (!IsMatch) { grabPerformer_OutputInfo(string.Format("接收到:{0}数据,但未找到对应配置", data.UpLoadIdentifier)); } if (syncCount > 0) { grabPerformer_OutputInfo(string.Format("成功接收:{0}一条数据", data.UpLoadIdentifier)); } }
/// <summary> /// 提取数据 /// </summary> private void Grab() { OracleDapperDber selfDber = new OracleDapperDber(this._ADGSAppConfig.SelfConnStr); using (OracleConnection connection = selfDber.CreateConnection() as OracleConnection) { foreach (AssayGraber assayGraber in this._ADGSAppConfig.AssayGrabers) { try { if (!assayGraber.Enabled) { continue; } // 未设置主键名则跳过 if (string.IsNullOrEmpty(assayGraber.PrimaryKeys)) { OutputInfoMethod(string.Format("{0} 提取未执行,原因:未设置主键(PrimaryKeys)参数", assayGraber.MachineCode)); continue; } DataTable dtlAssay = assayGraber.ExecuteGrab(); // 在数据中创建表 if (connection.ExecuteScalar <int>(OracleSqlBuilder.BuildHasTableSQL(assayGraber.TableName)) == 0) { connection.Execute(OracleSqlBuilder.BuildTableSQL(assayGraber.TableName, dtlAssay)); } int syncCount = 0; foreach (DataRow drAssay in dtlAssay.Rows) { string execSql = string.Empty; // 生成主键值 string primaryKeyValue = assayGraber.MachineCode + "-" + OracleSqlBuilder.BuildPrimaryKeyValue(assayGraber.PrimaryKeys, drAssay); if (connection.ExecuteScalar <int>(OracleSqlBuilder.BuildHasRecordSQL(assayGraber.TableName, primaryKeyValue)) == 0) { execSql = OracleSqlBuilder.BuildInsertSQL(assayGraber.TableName, primaryKeyValue, assayGraber.MachineCode, drAssay); } else { execSql = OracleSqlBuilder.BuildUpdateSQL(assayGraber.TableName, primaryKeyValue, assayGraber.MachineCode, drAssay); } syncCount += connection.Execute(execSql); } OutputInfoMethod(string.Format("{0} 本次提取 {1} 条", assayGraber.MachineCode, syncCount)); } catch (Exception ex) { OutputErrorMethod(string.Format("{0} 提取失败", assayGraber.MachineCode), ex); } } } }
/// <summary> /// 生成列的默认值的SQL /// </summary> /// <param name="columns"></param> /// <param name="serverDber"></param> /// <returns></returns> public static string BuildColumnDefaultSQL(IList <DataColumn> columns, OracleDapperDber serverDber) { StringBuilder sql = new StringBuilder(); foreach (DataColumn item in columns) { sql.AppendFormat(Environment.NewLine + "comment on column {0}.{1} is '{2}'", item.Table.TableName, item.ColumnName, GetColumnComment(item.Table.TableName, item.ColumnName, serverDber)); } return(sql.ToString()); }
/// <summary> /// 生成添加列的SQL /// </summary> /// <param name="columns"></param> /// <returns></returns> public static string BuildAddColumnSQL(IList <DataColumn> columns, OracleDapperDber serverDber) { StringBuilder sql = new StringBuilder(); foreach (DataColumn item in columns) { sql.AppendFormat("alter table {0} add {1} {2} {3}", item.Table.TableName, item.ColumnName, SystemTypeToOracleType(item.DataType, item.MaxLength), DefaultValueChange(GetColumnDefaultValue(item.Table.TableName, item.ColumnName, serverDber))); } return(sql.ToString()); }
private DcDbers() { BeltSampler_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("皮带采样机接口连接字符串")); AutoCupboard_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("智能存样柜接口连接字符串")); PneumaticTransfer_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("气动传输接口连接字符串")); CarJXSampler_Dber = new OracleDapperDber(commonDAO.GetCommonAppletConfigString("汽车机械采样机接口连接字符串")); TurnCarWeighterMutualDber = new OracleDapperDber(string.Format(commonDAO.GetCommonAppletConfigString("翻车衡数据库连接字符串"), DateTime.Now.Year)); }
private DcDbers() { BeltSampler_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("皮带采样机接口连接字符串")); AutoCupboard_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("智能存样柜接口连接字符串")); PneumaticTransfer_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("气动传输接口连接字符串")); WeightBridger_Dber = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("轨道衡数据库连接字符串")); CarJXSampler_Dber1 = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("#1汽车机械采样机接口连接字符串")); CarJXSampler_Dber2 = new SqlServerDapperDber(commonDAO.GetCommonAppletConfigString("#2汽车机械采样机接口连接字符串")); WeightBridger_Dber1 = new AccessDapperDber(string.Format(commonDAO.GetAppletConfigString("轨道衡数据库连接字符串1"), DateTime.Now.Year)); TruckWeight_Dber = new OracleDapperDber(commonDAO.GetAppletConfigString("汽车衡数据库连接字符串")); }
public void TransferAssayQulity(Action <string, eOutputType> output) { string interfaceUrl = commonDAO.GetAppletConfigString("数据同步智仁接口", "接口地址"); if (string.IsNullOrWhiteSpace(interfaceUrl)) { output("未在【小程序参数配置】模块中添加配置“接口地址”", eOutputType.Error); return; } OracleDapperDber thirdDber = new OracleDapperDber(interfaceUrl); int intervalValue = 7; string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "获取集团数据时间间隔(天)"); if (!string.IsNullOrWhiteSpace(configValue)) { Int32.TryParse(configValue, out intervalValue); } DateTime startTime = DateTime.Now.AddDays(-intervalValue); string searchSql = string.Format("select * from V_JK_HUAY where assaycode like 'Z%' and RIQ>=to_date('{0}','yyyy-MM-dd hh24:mi:ss')", startTime.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = thirdDber.ExecuteDataTable(searchSql); if (dt == null || dt.Rows.Count <= 0) { return; } foreach (DataRow item in dt.Rows) { string assaycode = string.Empty; DataTable assaydata = SelfDber.ExecuteDataTable(string.Format("select b.assaycode from cmcstbmake a inner join cmcstbassay b on a.id=b.makeid where a.makecode='{0}'", item["ASSAYCODE"])); if (assaydata != null && assaydata.Rows.Count > 0) { assaycode = assaydata.Rows[0][0].ToString(); } if (string.IsNullOrEmpty(assaycode)) { continue; } string assaySql = string.Format("update cmcstbassay set ASSAYDATE=to_date('{0}','yyyy-MM-dd hh24:mi:ss'),ASSAYPLE='{1}' where ASSAYCODE='{2}'", item["RIQ"], item["HUYY"], assaycode); string qualitySql = string.Format(@"update fultbfuelquality t set t.mt={0},t.mad={1},t.ad={2},t.aar={3},t.aad={4},t.vad={5},t.vdaf={6},t.std={7},t.stad={8},t.had={9},t.hdaf={10},t.qnetarmj={11},t.qbad={12},t.qgrd={13},t.qgrad={14} where t.id in(select FUELQUALITYID from cmcstbassay where ASSAYCODE='{15}')", item["MT"], item["MAD"], item["AD"], item["AAR"], item["AAD"], item["VAD"], item["VDAF"], item["STD"], item["STAD"], item["HAD"], item["HDAF"], item["QNETAR"], item["QBAD"], item["QGRD"], item["QGRAD"], assaycode); if (SelfDber.Execute(assaySql) + SelfDber.Execute(qualitySql) > 0) { output(string.Format("化验编码:{0},煤质信息同步成功!", assaycode), eOutputType.Normal); } } }
/// <summary> /// 测试数据库连接 /// </summary> /// <returns></returns> private bool TestDBConnect() { if (string.IsNullOrEmpty(txtSelfConnStr.Text.Trim())) { MessageBoxEx.Show("请先输入数据库连接字符串", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(false); } try { OracleDapperDber dber = new OracleDapperDber(txtSelfConnStr.Text.Trim()); dber.CreateConnection().Open(); dber.CreateConnection().Close(); return(true); } catch (Exception ex) { MessageBoxEx.Show("数据库连接失败," + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(false); } }
/// <summary> /// ²âÊÔÊý¾Ý¿âÁ¬½Ó /// </summary> /// <returns></returns> private bool TestDBConnect() { if (string.IsNullOrEmpty(txtSelfConnStr.Text.Trim())) { MessageBoxEx.Show("ÇëÏÈÊäÈëÊý¾Ý¿âÁ¬½Ó×Ö·û´®", "²Ù×÷Ìáʾ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(false); } try { OracleDapperDber dber = new OracleDapperDber(txtSelfConnStr.Text.Trim()); using (OracleConnection conn = dber.CreateConnection() as OracleConnection) { conn.Open(); } return(true); } catch (Exception ex) { MessageBoxEx.Show("Êý¾Ý¿âÁ¬½Óʧ°Ü£¬" + ex.Message, "²Ù×÷Ìáʾ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(false); } }
/// <summary> /// 同步数据 /// </summary> /// <param name="output"></param> /// <returns></returns> public void TransferData(List <TableOrView> list, Action <string, eOutputType> output) { string interfaceUrl = commonDAO.GetAppletConfigString("数据同步智仁接口", "接口地址"); if (string.IsNullOrWhiteSpace(interfaceUrl)) { output("未在【小程序参数配置】模块中添加配置“接口地址”", eOutputType.Error); return; } OracleDapperDber thirdDber = new OracleDapperDber(interfaceUrl); foreach (var item in list) { if (string.IsNullOrWhiteSpace(item.Source) || string.IsNullOrWhiteSpace(item.Destination) || string.IsNullOrWhiteSpace(item.Type)) { continue; } if (item.PropertySetDetails == null || item.PropertySetDetails.Count <= 0) { continue; } if (item.Type.ToLower() == "download") { DownLoadData(item, thirdDber, output); } else if (item.Type.ToLower() == "upload") { UpLoadData(item, thirdDber, output); } else { output(string.Format("节点<TableOrView source='{0}' destination={1}>中的类型配置错误,支持类型:upload、download", item.Source, item.Destination), eOutputType.Error); return; } } }
/// <summary> /// 同步数据 /// </summary> private void Synch() { if (!TestPing(this._CommonAppConfig.ServerIp)) { OutputInfoMethod(string.Format("服务器网络已断开"), eOutputType.Error); return; } OracleDapperDber serverDber = new OracleDapperDber(this._CommonAppConfig.ServerConnStr); OracleDapperDber clientDber = new OracleDapperDber(this._CommonAppConfig.ClientConnStr); if (string.IsNullOrEmpty(this._CommonAppConfig.SyncIdentifier)) { OutputInfoMethod(string.Format("同步标识未设置"), eOutputType.Error); return; } foreach (TableSynch tableSynch in this._CommonAppConfig.TableSynchs) { try { if (!tableSynch.Enabled) { continue; } // 未设置表名则跳过 if (string.IsNullOrEmpty(tableSynch.TableName.Trim())) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:未设置表名(TableName)参数", tableSynch.SynchTitle), eOutputType.Error); continue; } // 未设置主键名则跳过 if (string.IsNullOrEmpty(tableSynch.PrimaryKey.Trim())) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:未设置主键(PrimaryKey)参数", tableSynch.TableName), eOutputType.Error); continue; } // 未设置同步标识则跳过 if (string.IsNullOrEmpty(tableSynch.SynchField.Trim())) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:未设置同步标识字段(SynchField)参数", tableSynch.TableName), eOutputType.Error); continue; } //判断两个库表是否存在 if (serverDber.Execute(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:服务器端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error); continue; } if (clientDber.Execute(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:就地端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error); continue; } //判断两个库表字段是否一致 DataTable dt1 = serverDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName)); DataTable dt2 = clientDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName)); if (!OracleSqlBuilder.CompareTableField(dt1, dt2)) { OutputInfoMethod(string.Format("【{0}】两端表{1}列名不一致", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Important); IList <DataColumn> columns = OracleSqlBuilder.CompareTableFieldAndReturn(dt1, dt2); if (columns != null && columns.Count > 0) { clientDber.Execute(OracleSqlBuilder.BuildAddColumnSQL(columns, serverDber)); clientDber.Execute(OracleSqlBuilder.BuildColumnDefaultSQL(columns, serverDber)); OutputInfoMethod(string.Format("【{0}】列已同步", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Important); } } int res = 0; switch (tableSynch.SynchType) { case "上传": res = Upload(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); break; case "下达": res = Download(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); break; case "双向": //先下达 再上传 res = Download(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); res = Upload(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); break; } } catch (Exception ex) { OutputErrorMethod(string.Format("【{0}】 {1}同步失败", tableSynch.SynchTitle, tableSynch.SynchType), ex); } } //删除数据 foreach (CmcsWaitForHandleEvent item in clientDber.Entities <CmcsWaitForHandleEvent>("where DataFlag=0 and EventCode like '%删除%'")) { bool isSuccess = false; eEventCode eventCode; bool a = Enum.TryParse <eEventCode>(item.EventCode, out eventCode); if (!Enum.TryParse <eEventCode>(item.EventCode, out eventCode)) { continue; } switch (eventCode) { case eEventCode.汽车智能化_删除未完成运输记录: isSuccess = clientDber.DeleteBySQL <CmcsUnFinishTransport>("where TransportId=:TransportId", new { TransportId = item.ObjectId }) > 0; if (isSuccess) { OutputInfoMethod(string.Format("事件:{0} ObjectId:{1}", eEventCode.汽车智能化_删除未完成运输记录.ToString(), item.ObjectId), eOutputType.Normal); } break; case eEventCode.汽车智能化_删除入厂煤运输记录: isSuccess = clientDber.DeleteBySQL <CmcsBuyFuelTransport>("where Id=:Id", new { Id = item.ObjectId }) > 0; if (isSuccess) { OutputInfoMethod(string.Format("事件:{0} ObjectId:{1}", eEventCode.汽车智能化_删除入厂煤运输记录.ToString(), item.ObjectId), eOutputType.Normal); } break; case eEventCode.汽车智能化_删除出厂煤运输记录: isSuccess = clientDber.DeleteBySQL <CmcsSaleFuelTransport>("where Id=:Id", new { Id = item.ObjectId }) > 0; if (isSuccess) { OutputInfoMethod(string.Format("事件:{0} ObjectId:{1}", eEventCode.汽车智能化_删除出厂煤运输记录.ToString(), item.ObjectId), eOutputType.Normal); } break; default: break; } if (isSuccess) { item.DataFlag = 1; clientDber.Update(item); } } }
/// <summary> /// 同步数据 /// </summary> private void Synch() { OracleDapperDber serverDber = new OracleDapperDber(this._CommonAppConfig.ServerConnStr); OracleDapperDber clientDber = new OracleDapperDber(this._CommonAppConfig.ClientConnStr); foreach (TableSynch tableSynch in this._CommonAppConfig.TableSynchs) { try { if (!tableSynch.Enabled) { continue; } // 未设置表名则跳过 if (string.IsNullOrEmpty(tableSynch.TableName.Trim())) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:未设置表名(TableName)参数", tableSynch.SynchTitle), eOutputType.Error); continue; } // 未设置主键名则跳过 if (string.IsNullOrEmpty(tableSynch.PrimaryKey.Trim())) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:未设置主键(PrimaryKey)参数", tableSynch.TableName), eOutputType.Error); continue; } // 未设置同步标识则跳过 if (string.IsNullOrEmpty(tableSynch.SynchField.Trim())) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:未设置同步标识字段(SynchField)参数", tableSynch.TableName), eOutputType.Error); continue; } //判断两个库表是否存在 if (serverDber.ExecuteScalar <int>(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:服务器端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error); continue; } if (clientDber.ExecuteScalar <int>(OracleSqlBuilder.BuildHasTableSQL(tableSynch.TableName)) == 0) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:就地端不存在此表{1}", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error); continue; } //判断两个库表字段是否一致 DataTable dt1 = serverDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName)); DataTable dt2 = clientDber.ExecuteDataTable(OracleSqlBuilder.BuildGetNullTableSQL(tableSynch.TableName)); if (!OracleSqlBuilder.CompareTableField(dt1, dt2)) { OutputInfoMethod(string.Format("【{0}】 同步未执行,原因:两端表{1}列名不一致", tableSynch.SynchTitle, tableSynch.TableName), eOutputType.Error); continue; } int res = 0; switch (tableSynch.SynchType) { case "上传": res = Upload(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); break; case "下达": res = Download(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); break; case "双向": //先下达 再上传 res = Download(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次下达 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); res = Upload(serverDber, clientDber, tableSynch); OutputInfoMethod(string.Format("【{0}】 本次上传 {1} 条", tableSynch.SynchTitle, res), eOutputType.Normal); break; } } catch (Exception ex) { OutputErrorMethod(string.Format("【{0}】 {1}同步失败", tableSynch.SynchTitle, tableSynch.SynchType), ex); } } }
private EquAssayDeviceDAO() { _ADGSAppConfig = ADGSAppConfig.GetInstance(); selfDber = new OracleDapperDber(this._ADGSAppConfig.SelfConnStr); }
/// <summary> /// 上报数据 /// </summary> /// <param name="entity"></param> /// <param name="thirdDber"></param> /// <param name="output"></param> private void UpLoadData(TableOrView entity, OracleDapperDber thirdDber, Action <string, eOutputType> output) { //查询本地是否有待上报数据 string sbSearch = "select "; foreach (var item in entity.PropertySetDetails) { sbSearch += string.Format("{0},", item.Source); } sbSearch = sbSearch.Trim(','); sbSearch += string.Format(" from {0} ", entity.Source); if (!string.IsNullOrWhiteSpace(entity.TimeIntervalProperty)) { int intervalValue = 7; string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "数据上报时间间隔(天)"); if (!string.IsNullOrWhiteSpace(configValue)) { Int32.TryParse(configValue, out intervalValue); } DateTime startTime = DateTime.Now.AddDays(-intervalValue); sbSearch += string.Format(" where {0}>=to_date('{1}','yyyy-MM-dd hh24:mi:ss')", entity.TimeIntervalProperty, startTime); } DataTable dt = SelfDber.ExecuteDataTable(sbSearch); if (dt == null || dt.Rows.Count <= 0) { return; } Boolean ishavepk = entity.PropertySetDetails.Count(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true") > 0; foreach (DataRow item in dt.Rows) { //只有更新和新增操作 string strChaXun = string.Format("select * from {0} where 1=1 ", entity.Destination); if (ishavepk) { foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true")) { if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime") { pk.Format = "yyyy-MM-dd hh24:mi:ss"; strChaXun += string.Format("and {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format); } else { strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString()); } } } else { foreach (var pk in entity.PropertySetDetails) { if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime") { pk.Format = "yyyy-MM-dd hh24:mi:ss"; strChaXun += string.Format("and {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format); } else { strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString()); } } } DataTable dtHaveData = thirdDber.ExecuteDataTable(strChaXun); if (dtHaveData == null || dtHaveData.Rows.Count <= 0) { //新增 string insertSql = string.Format(@"insert into {0} (", entity.Destination); string names = ""; string values = ""; foreach (var detail in entity.PropertySetDetails) { names += string.Format("{0},", detail.Destination); if (detail.DesType != null && detail.DesType.ToLower() == "datetime" && !string.IsNullOrWhiteSpace(detail.Format)) { values += string.Format("{0},", item[detail.Source] == DBNull.Value ? "''" : ("'" + DateTime.Parse(item[detail.Source].ToString()).ToString(detail.Format) + "'")); } else { values += string.Format("'{0}',", item[detail.Source] == DBNull.Value ? "" : item[detail.Source].ToString()); } } insertSql += names.Trim(',') + ") values (" + values.Trim(',') + ")"; if (thirdDber.Execute(insertSql) > 0) { output(string.Format("接口上报数据【{0}】已同步,操作:新增", entity.Description), eOutputType.Normal); } } else { //更新 string updateSql = string.Format("update {0} set ", entity.Destination); foreach (var detail in entity.PropertySetDetails) { if (detail.DesType != null && detail.DesType.ToLower() == "datetime" && !string.IsNullOrWhiteSpace(detail.Format)) { updateSql += string.Format("{0}={1},", detail.Destination, item[detail.Source] == DBNull.Value ? "''" : ("'" + DateTime.Parse(item[detail.Source].ToString()).ToString(detail.Format) + "'")); } else { updateSql += string.Format("{0}='{1}',", detail.Destination, item[detail.Source] == DBNull.Value ? "" : item[detail.Source].ToString()); } } //if (entity.Description == "编码接口表") // updateSql = updateSql.Trim(',') + string.Format(" where id='{0}'", dtHaveData.Rows[0]["TRUCKENTERID"].ToString()); //else updateSql = updateSql.Trim(','); string updateWhere = ""; if (ishavepk) { int index = 0; foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true")) { if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime") { pk.Format = "yyyy-MM-dd hh24:mi:ss"; updateWhere += string.Format("{3} {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format, (index == 0 ? "" : "and")); } else { updateWhere += string.Format("{2} {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), (index == 0 ? "" : "and")); } index++; } if (!string.IsNullOrWhiteSpace(updateWhere)) { updateWhere = " where " + updateWhere; } } if (string.IsNullOrWhiteSpace(updateWhere)) { updateWhere = string.Format(" where {0}='{1}'", entity.PropertySetDetails.First().Destination, dtHaveData.Rows[0][entity.PropertySetDetails.First().Destination].ToString()); } updateSql = updateSql + " " + updateWhere; //updateSql = updateSql.Trim(',') + string.Format(" where {0}='{1}'", entity.PropertySetDetails.First().Destination, dtHaveData.Rows[0][entity.PropertySetDetails.First().Destination].ToString()); if (thirdDber.Execute(updateSql) > 0) { output(string.Format("接口取数【{0}】已同步,操作:更新", entity.Description), eOutputType.Normal); } } } }
/// <summary> /// 获取列注释 /// </summary> /// <param name="tableName"></param> /// <param name="columnName"></param> /// <param name="serverDber"></param> /// <returns></returns> public static string GetColumnComment(string tableName, string columnName, OracleDapperDber serverDber) { return(serverDber.ExecuteDataTable(string.Format("select COMMENTS from user_col_comments where Table_Name='{0}' and COLUMN_NAME='{1}'", tableName.ToUpper(), columnName.ToUpper())).Rows[0][0].ToString()); }
/// <summary> /// 获取列默认值 /// </summary> /// <param name="tableName"></param> /// <param name="columnName"></param> /// <param name="serverDber"></param> /// <returns></returns> public static string GetColumnDefaultValue(string tableName, string columnName, OracleDapperDber serverDber) { return(serverDber.ExecuteDataTable(string.Format("SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME ='{0}' AND COLUMN_NAME = '{1}'", tableName.ToUpper(), columnName.ToUpper())).Rows[0][0].ToString()); }
public void TransferBaseOperLog(Action <string, eOutputType> output) { string interfaceUrl = commonDAO.GetAppletConfigString("数据同步智仁接口", "接口地址"); if (string.IsNullOrWhiteSpace(interfaceUrl)) { output("未在【小程序参数配置】模块中添加配置“接口地址”", eOutputType.Error); return; } OracleDapperDber thirdDber = new OracleDapperDber(interfaceUrl); int intervalValue = 7; string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "获取集团数据时间间隔(天)"); if (!string.IsNullOrWhiteSpace(configValue)) { Int32.TryParse(configValue, out intervalValue); } DateTime startTime = DateTime.Now.AddDays(-intervalValue); string searchSql = string.Format("select * from V_JK_COALLOG where RIQ>=to_date('{0}','yyyy-MM-dd hh24:mi:ss') order by RIQ desc", startTime.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = thirdDber.ExecuteDataTable(searchSql); if (dt == null || dt.Rows.Count <= 0) { return; } foreach (DataRow item in dt.Rows) { string sql = ""; if (item["LEIX"].ToString() == "煤矿单位") { #region 煤矿 if (item["DONGZ"].ToString() == "增加") { //先查有没有,没有就新增,有就更新 DataTable dtTemp = SelfDber.ExecuteDataTable(string.Format("select * from fultbmine where name='{0}' or CompanyCode='{1}'", item["NEWNAME"], item["NEWCODE"])); if (dtTemp != null && dtTemp.Rows.Count > 0) { if (dtTemp.Rows.Count > 1) { output(string.Format("矿点:名称【{0}】,编码【{1}】,查询到多条记录,更新失败!请在系统中检查数据合理性!", item["NEWNAME"], item["NEWCODE"]), eOutputType.Error); continue; } else { sql = string.Format("update fultbmine set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]); } } else //新增 { sql = string.Format(@"insert into fultbmine (ID, CREATIONTIME, CREATORUSERID, ISDELETED, CODE, SORT, NAME, ISSTOP, DATAFROM, PARENTID, SYNCFLAG, COMPANYCODE, SHORTNAME, DATAFLAG, SYNCTIME) values ('{0}', sysdate, 1, 0, '{1}', 1, '{2}', 0, '集团接口', '-1', 0, '{1}', '{2}', 0, sysdate)", Guid.NewGuid().ToString(), item["NEWCODE"], item["NEWNAME"]); Log4Neter.Info(sql); } } else if (item["DONGZ"].ToString() == "更新") { sql = string.Format("update fultbmine set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]); } else if (item["DONGZ"].ToString() == "删除") { sql = string.Format("update fultbmine set isstop=1,SYNCTIME=sysdate where name='{0}' and CompanyCode='{1}'", item["OLDNAME"], item["OLDCODE"]); } #endregion } else if (item["LEIX"].ToString() == "运输单位") { #region 运输单位 if (item["DONGZ"].ToString() == "增加") { //先查有没有,没有就新增,有就更新 DataTable dtTemp = SelfDber.ExecuteDataTable(string.Format("select * from fultbtransportcompany where name='{0}' or code='{1}'", item["NEWNAME"], item["NEWCODE"])); if (dtTemp != null && dtTemp.Rows.Count > 0) { if (dtTemp.Rows.Count > 1) { output(string.Format("运输单位:名称【{0}】,编码【{1}】,查询到多条记录,更新失败!请在系统中检查数据合理性!", item["OLDNAME"], item["OLDCODE"]), eOutputType.Error); continue; } else { sql = string.Format("update fultbtransportcompany set name='{0}',code='{1}',SYNCTIME=sysdate where name='{2}' and code='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]); } } else //新增 { sql = string.Format(@"insert into fultbtransportcompany (ID, CREATIONTIME, CREATORUSERID, ISDELETED, CODE, NAME, ISSTOP, DATAFROM, SYNCFLAG, SYNCTIME) values ('{0}', sysdate, 1, 0, '{1}', '{2}', 0, '集团接口', 0, sysdate)", Guid.NewGuid().ToString(), item["NEWCODE"], item["NEWNAME"]); } } else if (item["DONGZ"].ToString() == "更新") { sql = string.Format("update fultbtransportcompany set name='{0}',code='{1}',SYNCTIME=sysdate where name='{2}' and code='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]); } else if (item["DONGZ"].ToString() == "删除") { sql = string.Format("update fultbtransportcompany set isstop=1,SYNCTIME=sysdate where name='{0}' and code='{1}'", item["OLDNAME"], item["OLDCODE"]); } #endregion } else if (item["LEIX"].ToString() == "煤种") { #region 煤种 if (item["DONGZ"].ToString() == "增加") { //先查有没有,没有就新增,有就更新 DataTable dtTemp = SelfDber.ExecuteDataTable(string.Format("select * from fultbfuelkind where name='{0}' or CompanyCode='{1}'", item["NEWNAME"], item["NEWCODE"])); if (dtTemp != null && dtTemp.Rows.Count > 0) { if (dtTemp.Rows.Count > 1) { output(string.Format("煤种:名称【{0}】,编码【{1}】,查询到多条记录,更新失败!请在系统中检查数据合理性!", item["OLDNAME"], item["OLDCODE"]), eOutputType.Error); continue; } else { sql = string.Format("update fultbfuelkind set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]); } } else //新增 { sql = string.Format(@"insert into fultbfuelkind (ID, CREATIONTIME, CREATORUSERID, ISDELETED, CODE, SORT, NAME, ISSTOP, DATAFROM, PARENTID, SYNCFLAG, COMPANYCODE, SHORTNAME, DATAFLAG, SYNCTIME) values ('{0}', sysdate, 1, 0, '{1}', 1, '{2}', 0, '集团接口', '-1', 0, '{1}', '{2}', 0, sysdate)", Guid.NewGuid().ToString(), item["NEWCODE"], item["NEWNAME"]); } } else if (item["DONGZ"].ToString() == "更新") { sql = string.Format("update fultbfuelkind set name='{0}',CompanyCode='{1}',SYNCTIME=sysdate where name='{2}' and CompanyCode='{3}'", item["NEWNAME"], item["NEWCODE"], item["OLDNAME"], item["OLDCODE"]); } else if (item["DONGZ"].ToString() == "删除") { sql = string.Format("update fultbfuelkind set isstop=1,SYNCTIME=sysdate where name='{0}' and CompanyCode='{1}'", item["OLDNAME"], item["OLDCODE"]); } #endregion } if (string.IsNullOrWhiteSpace(sql)) { continue; } if (SelfDber.Execute(sql) > 0) { output(string.Format("基础信息操作记录处理完成,操作内容:{0},操作动作:{1},修改前编码:{2},修改后编码:{3},修改前名称:{4},修改后名称:{5}", item["LEIX"], item["DONGZ"], item["OLDCODE"], item["NEWCODE"], item["OLDNAME"], item["NEWNAME"]), eOutputType.Normal); } } }
/// <summary> /// 同步集团数据 /// </summary> /// <param name="entity"></param> /// <param name="thirdDber"></param> /// <param name="output"></param> private void DownLoadData(TableOrView entity, OracleDapperDber thirdDber, Action <string, eOutputType> output) { //同步集团数据 string sbSearch = "select "; foreach (var item in entity.PropertySetDetails) { sbSearch += string.Format("{0},", item.Source); } sbSearch = sbSearch.Trim(','); sbSearch += string.Format(" from {0} ", entity.Source); if (!string.IsNullOrWhiteSpace(entity.TimeIntervalProperty)) { int intervalValue = 7; string configValue = commonDAO.GetAppletConfigString("数据同步智仁接口", "获取集团数据时间间隔(天)"); if (!string.IsNullOrWhiteSpace(configValue)) { Int32.TryParse(configValue, out intervalValue); } DateTime startTime = DateTime.Now.AddDays(-intervalValue); sbSearch += string.Format(" where {0}>=to_date('{1}','yyyy-MM-dd hh24:mi:ss')", entity.TimeIntervalProperty, startTime.ToString("yyyy-MM-dd HH:mm:ss")); } DataTable dt = thirdDber.ExecuteDataTable(sbSearch); if (dt == null || dt.Rows.Count <= 0) { return; } Boolean ishavepk = entity.PropertySetDetails.Count(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true") > 0; foreach (DataRow item in dt.Rows) { //只有更新和新增操作 string strChaXun = string.Format("select * from {0} where 1=1 ", entity.Destination); if (ishavepk) { foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true")) { strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == null ? "" : item[pk.Source].ToString()); } } else { foreach (var pk in entity.PropertySetDetails) { strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == null ? "" : item[pk.Source].ToString()); } } DataTable dtHaveData = SelfDber.ExecuteDataTable(strChaXun); if (dtHaveData == null || dtHaveData.Rows.Count <= 0) { //新增 string insertSql = string.Format(@"insert into {0} (", entity.Destination); string names = "ID, CREATIONTIME, CREATORUSERID,LASTMODIFICATIONTIME,"; string values = string.Format("'{0}', sysdate,1,sysdate,", Guid.NewGuid().ToString()); if (entity.Description == "矿点同步") { string code = commonDAO.GetMineNewChildCode("000"); if (!string.IsNullOrEmpty(code)) { names += "Code,"; values += "'" + code + "',"; } names += "Sort,"; values += commonDAO.GetMineSort() + ","; } else if (entity.Description == "煤种同步") { string code = commonDAO.GetFuelKindNewChildCode("000"); if (!string.IsNullOrEmpty(code)) { names += "Code,"; values += "'" + code + "',"; } names += "Sort,"; values += commonDAO.GetFuelKindSort() + ","; } if (!string.IsNullOrWhiteSpace(entity.IsSoftDelete) && entity.IsSoftDelete.ToLower() == "true") { names += "ISDELETED,"; values += "0,"; } if (!string.IsNullOrWhiteSpace(entity.TreeParentId)) { names += "parentid,"; values += string.Format("'{0}',", entity.TreeParentId); } foreach (var detail in entity.PropertySetDetails) { names += string.Format("{0},", detail.Destination); values += string.Format("'{0}',", item[detail.Source] == null ? "" : item[detail.Source].ToString()); } if (!string.IsNullOrWhiteSpace(entity.IsHaveSyncTime) && entity.IsHaveSyncTime.ToLower() == "true") { names += "SYNCTIME,"; values += "sysdate,"; } insertSql += names.Trim(',') + ") values (" + values.Trim(',') + ")"; if (SelfDber.Execute(insertSql) > 0) { output(string.Format("接口取数【{0}】已同步,操作:新增", entity.Description), eOutputType.Normal); } } else { //更新 string updateSql = string.Format("update {0} set ", entity.Destination); foreach (var detail in entity.PropertySetDetails) { updateSql += string.Format("{0}='{1}',", detail.Destination, item[detail.Source] == null ? "" : item[detail.Source].ToString()); } if (!string.IsNullOrWhiteSpace(entity.IsHaveSyncTime) && entity.IsHaveSyncTime.ToLower() == "true") { updateSql += "SYNCTIME=sysdate,"; } updateSql = updateSql.Trim(',') + string.Format(" where id='{0}'", dtHaveData.Rows[0]["ID"].ToString()); if (SelfDber.Execute(updateSql) > 0) { output(string.Format("接口取数【{0}】已同步,操作:更新", entity.Description), eOutputType.Normal); } } } }