项目中用到了Quartz，也算是Quartz的一个示例。

源库创建数据同步表SYNC_DATA：

CREATE TABLE SYNC_DATA(ID                             VARCHAR2(36)      NOT NULL,TABLE_NAME                     VARCHAR2(50)      NOT NULL,COMMAND                        VARCHAR2(10)      NOT NULL,TABLE_KEY                      VARCHAR2(50)      NOT NULL,DATA_ID                        VARCHAR2(50)      NOT NULL,FAIL_COUNT                     INT               NOT NULL,CREATE_TIME                    DATE              NOT NULL,UPDATE_TIME                    DATE ,CONSTRAINT sync_data_PK PRIMARY KEY (ID)
)
;COMMENT ON TABLE SYNC_DATA IS '业务数据同步表'
;
COMMENT ON COLUMN SYNC_DATA.ID IS '主键'
;
COMMENT ON COLUMN SYNC_DATA.TABLE_NAME IS '表名'
;
COMMENT ON COLUMN SYNC_DATA.COMMAND IS '命令'
;
COMMENT ON COLUMN SYNC_DATA.TABLE_KEY IS '表主键'
;
COMMENT ON COLUMN SYNC_DATA.DATA_ID IS '数据ID'
;
COMMENT ON COLUMN SYNC_DATA.FAIL_COUNT IS '失败次数'
;
COMMENT ON COLUMN SYNC_DATA.CREATE_TIME IS '创建时间'
;
COMMENT ON COLUMN SYNC_DATA.UPDATE_TIME IS '更新时间'
;

新增修改删除触发器示例(oracle版)：

CREATE OR REPLACE TRIGGER "T_PT_CAMERA_INFO_I"
AFTER INSERT ON "SHINY"."PT_CAMERA_INFO" FOR EACH ROW
BEGININSERT INTO sync_data(ID, TABLE_NAME, COMMAND, TABLE_KEY, DATA_ID, FAIL_COUNT, CREATE_TIME)VALUES(sys_guid(), 'PT_CAMERA_INFO', 'INSERT',  'ID', :new.ID || '', 0,  sysdate);
END;CREATE OR REPLACE TRIGGER "T_PT_CAMERA_INFO_U"
AFTER UPDATE ON "SHINY"."PT_CAMERA_INFO" FOR EACH ROW
BEGINIF (NVL(to_char(:new.ID), 'X') != NVL(to_char(:old.ID), 'X'))OR (NVL(to_char(:new.ASSET_ID), 'X') != NVL(to_char(:old.ASSET_ID), 'X'))OR (NVL(to_char(:new.CAMERA_NO), 'X') != NVL(to_char(:old.CAMERA_NO), 'X'))OR (NVL(to_char(:new.POSITION_CODE), 'X') != NVL(to_char(:old.POSITION_CODE), 'X'))OR (NVL(to_char(:new.POSITION_ID), 'X') != NVL(to_char(:old.POSITION_ID), 'X'))OR (NVL(to_char(:new.CAMERA_NAME), 'X') != NVL(to_char(:old.CAMERA_NAME), 'X'))OR (NVL(to_char(:new.ORG_ID), 'X') != NVL(to_char(:old.ORG_ID), 'X'))OR (NVL(to_char(:new.ADDRESS), 'X') != NVL(to_char(:old.ADDRESS), 'X'))OR (NVL(to_char(:new.CAMERA_IP), 'X') != NVL(to_char(:old.CAMERA_IP), 'X'))OR (NVL(to_char(:new.LONGITUDE), 'X') != NVL(to_char(:old.LONGITUDE), 'X'))OR (NVL(to_char(:new.LATITUDE), 'X') != NVL(to_char(:old.LATITUDE), 'X'))OR (NVL(to_char(:new.SN), 'X') != NVL(to_char(:old.SN), 'X'))OR (NVL(to_char(:new.CAMERA_MODEL), 'X') != NVL(to_char(:old.CAMERA_MODEL), 'X'))OR (NVL(to_char(:new.MANUFACTURER), 'X') != NVL(to_char(:old.MANUFACTURER), 'X'))OR (NVL(to_char(:new.SUPPLIER), 'X') != NVL(to_char(:old.SUPPLIER), 'X'))OR (NVL(to_char(:new.ADD_ID), 'X') != NVL(to_char(:old.ADD_ID), 'X'))OR (NVL(to_char(:new.ADD_TIME, 'yyyy-mm-dd hh24:mi:ss'),'X') != NVL(to_char(:old.ADD_TIME, 'yyyy-mm-dd hh24:mi:ss'),'X'))OR (NVL(to_char(:new.MODIFY_ID), 'X') != NVL(to_char(:old.MODIFY_ID), 'X'))OR (NVL(to_char(:new.MODIFY_TIME, 'yyyy-mm-dd hh24:mi:ss'),'X') != NVL(to_char(:old.MODIFY_TIME, 'yyyy-mm-dd hh24:mi:ss'),'X'))OR (NVL(to_char(:new.USER_NAME), 'X') != NVL(to_char(:old.USER_NAME), 'X'))OR (NVL(to_char(:new.PASS_WORD), 'X') != NVL(to_char(:old.PASS_WORD), 'X'))OR (NVL(to_char(:new.CASE_INSTALL_TYPE), 'X') != NVL(to_char(:old.CASE_INSTALL_TYPE), 'X'))OR (NVL(to_char(:new.AREA_CODE), 'X') != NVL(to_char(:old.AREA_CODE), 'X'))OR (NVL(to_char(:new.MONITOR_TYPE), 'X') != NVL(to_char(:old.MONITOR_TYPE), 'X'))OR (NVL(to_char(:new.POINT_NAME), 'X') != NVL(to_char(:old.POINT_NAME), 'X'))OR (NVL(to_char(:new.CAMERA_IP6), 'X') != NVL(to_char(:old.CAMERA_IP6), 'X'))OR (NVL(to_char(:new.SUBNET_MASK), 'X') != NVL(to_char(:old.SUBNET_MASK), 'X'))OR (NVL(to_char(:new.GATEWAY), 'X') != NVL(to_char(:old.GATEWAY), 'X'))OR (NVL(to_char(:new.MAC_ADDRESS), 'X') != NVL(to_char(:old.MAC_ADDRESS), 'X'))OR (NVL(to_char(:new.ONU_SN), 'X') != NVL(to_char(:old.ONU_SN), 'X'))OR (NVL(to_char(:new.CAMERA_TYPE), 'X') != NVL(to_char(:old.CAMERA_TYPE), 'X'))OR (NVL(to_char(:new.CAMERA_FUN_TYPE), 'X') != NVL(to_char(:old.CAMERA_FUN_TYPE), 'X'))OR (NVL(to_char(:new.FILL_LIGTH_ATTR), 'X') != NVL(to_char(:old.FILL_LIGTH_ATTR), 'X'))OR (NVL(to_char(:new.CAMERA_ENCODE_TYPE), 'X') != NVL(to_char(:old.CAMERA_ENCODE_TYPE), 'X'))OR (NVL(to_char(:new.POWER_TAKE_TYPE), 'X') != NVL(to_char(:old.POWER_TAKE_TYPE), 'X'))OR (NVL(to_char(:new.POWER_TAKE_LENGTH), 'X') != NVL(to_char(:old.POWER_TAKE_LENGTH), 'X'))OR (NVL(to_char(:new.SOUND_ALARM), 'X') != NVL(to_char(:old.SOUND_ALARM), 'X'))OR (NVL(to_char(:new.RESOLUTION), 'X') != NVL(to_char(:old.RESOLUTION), 'X'))OR (NVL(to_char(:new.SOFT_VERSION), 'X') != NVL(to_char(:old.SOFT_VERSION), 'X'))OR (NVL(to_char(:new.LENS_PARAM), 'X') != NVL(to_char(:old.LENS_PARAM), 'X'))OR (NVL(to_char(:new.IS_HAVE_CONSOLE), 'X') != NVL(to_char(:old.IS_HAVE_CONSOLE), 'X'))OR (NVL(to_char(:new.INSTALL_WAY), 'X') != NVL(to_char(:old.INSTALL_WAY), 'X'))OR (NVL(to_char(:new.LINEAR_WAY), 'X') != NVL(to_char(:old.LINEAR_WAY), 'X'))OR (NVL(to_char(:new.RESOURCE_PLACE), 'X') != NVL(to_char(:old.RESOURCE_PLACE), 'X'))OR (NVL(to_char(:new.IMPORT_WATCH), 'X') != NVL(to_char(:old.IMPORT_WATCH), 'X'))OR (NVL(to_char(:new.POSITION_TYPE), 'X') != NVL(to_char(:old.POSITION_TYPE), 'X'))OR (NVL(to_char(:new.COMMUNITY), 'X') != NVL(to_char(:old.COMMUNITY), 'X'))OR (NVL(to_char(:new.STREET), 'X') != NVL(to_char(:old.STREET), 'X'))OR (NVL(to_char(:new.WATCH_SPEC_LOCATION), 'X') != NVL(to_char(:old.WATCH_SPEC_LOCATION), 'X'))OR (NVL(to_char(:new.ROAD_DIRECTION), 'X') != NVL(to_char(:old.ROAD_DIRECTION), 'X'))OR (NVL(to_char(:new.FOUL_LINE), 'X') != NVL(to_char(:old.FOUL_LINE), 'X'))OR (NVL(to_char(:new.FEN_JU), 'X') != NVL(to_char(:old.FEN_JU), 'X'))OR (NVL(to_char(:new.POLICE_STATION), 'X') != NVL(to_char(:old.POLICE_STATION), 'X'))OR (NVL(to_char(:new.CAMERA_DIRECTION), 'X') != NVL(to_char(:old.CAMERA_DIRECTION), 'X'))OR (NVL(to_char(:new.INSTALL_HEIGHT), 'X') != NVL(to_char(:old.INSTALL_HEIGHT), 'X'))OR (NVL(to_char(:new.CROSS_ARM1), 'X') != NVL(to_char(:old.CROSS_ARM1), 'X'))OR (NVL(to_char(:new.CROSS_ARM2), 'X') != NVL(to_char(:old.CROSS_ARM2), 'X'))OR (NVL(to_char(:new.INDOOR_OR_NOT), 'X') != NVL(to_char(:old.INDOOR_OR_NOT), 'X'))OR (NVL(to_char(:new.SPECIAL_PHOTO_PATH), 'X') != NVL(to_char(:old.SPECIAL_PHOTO_PATH), 'X'))OR (NVL(to_char(:new.LOCATION_PHOTO_PATH), 'X') != NVL(to_char(:old.LOCATION_PHOTO_PATH), 'X'))OR (NVL(to_char(:new.REAL_PHOTO_PATH), 'X') != NVL(to_char(:old.REAL_PHOTO_PATH), 'X'))OR (NVL(to_char(:new.NETWORK_PROPERTIES), 'X') != NVL(to_char(:old.NETWORK_PROPERTIES), 'X'))OR (NVL(to_char(:new.POLICE_AREA_CODE), 'X') != NVL(to_char(:old.POLICE_AREA_CODE), 'X'))OR (NVL(to_char(:new.INSTALL_PERSION), 'X') != NVL(to_char(:old.INSTALL_PERSION), 'X'))OR (NVL(to_char(:new.INSTALL_TIME, 'yyyy-mm-dd hh24:mi:ss'),'X') != NVL(to_char(:old.INSTALL_TIME, 'yyyy-mm-dd hh24:mi:ss'),'X'))OR (NVL(to_char(:new.BUILD_PERIOD), 'X') != NVL(to_char(:old.BUILD_PERIOD), 'X'))OR (NVL(to_char(:new.PROJECT_NAME), 'X') != NVL(to_char(:old.PROJECT_NAME), 'X'))OR (NVL(to_char(:new.MANAGER_UNIT), 'X') != NVL(to_char(:old.MANAGER_UNIT), 'X'))OR (NVL(to_char(:new.MANAGER_UNIT_TEL), 'X') != NVL(to_char(:old.MANAGER_UNIT_TEL), 'X'))OR (NVL(to_char(:new.MAINTAIN_UNIT), 'X') != NVL(to_char(:old.MAINTAIN_UNIT), 'X'))OR (NVL(to_char(:new.MAINTAIN_UNIT_TEL), 'X') != NVL(to_char(:old.MAINTAIN_UNIT_TEL), 'X'))OR (NVL(to_char(:new.RECODE_SAVE_TYPE), 'X') != NVL(to_char(:old.RECODE_SAVE_TYPE), 'X'))OR (NVL(to_char(:new.DEVICE_STATE), 'X') != NVL(to_char(:old.DEVICE_STATE), 'X'))OR (NVL(to_char(:new.INDUSTRY_OWN), 'X') != NVL(to_char(:old.INDUSTRY_OWN), 'X'))OR (NVL(to_char(:new.IS_REGISTER_IMOS), 'X') != NVL(to_char(:old.IS_REGISTER_IMOS), 'X'))OR (NVL(to_char(:new.IS_WIFI), 'X') != NVL(to_char(:old.IS_WIFI), 'X'))OR (NVL(to_char(:new.IS_FLASH), 'X') != NVL(to_char(:old.IS_FLASH), 'X'))OR (NVL(to_char(:new.CAMERA_NO_STR), 'X') != NVL(to_char(:old.CAMERA_NO_STR), 'X'))OR (NVL(to_char(:new.CAMERA_VCN_CODE), 'X') != NVL(to_char(:old.CAMERA_VCN_CODE), 'X'))OR (NVL(to_char(:new.FIELD_NO), 'X') != NVL(to_char(:old.FIELD_NO), 'X'))OR (NVL(to_char(:new.KEY_UNIT), 'X') != NVL(to_char(:old.KEY_UNIT), 'X'))OR (NVL(to_char(:new.UNIT_TYPE), 'X') != NVL(to_char(:old.UNIT_TYPE), 'X'))OR (NVL(to_char(:new.SHOW_LEVEL), 'X') != NVL(to_char(:old.SHOW_LEVEL), 'X'))OR (NVL(to_char(:new.PROTOCOL_TYPE), 'X') != NVL(to_char(:old.PROTOCOL_TYPE), 'X'))OR (NVL(to_char(:new.CAMERA_PORT), 'X') != NVL(to_char(:old.CAMERA_PORT), 'X'))OR (NVL(to_char(:new.INTERFACE_TYPE), 'X') != NVL(to_char(:old.INTERFACE_TYPE), 'X'))OR (NVL(to_char(:new.CHANNEL), 'X') != NVL(to_char(:old.CHANNEL), 'X'))OR (NVL(to_char(:new.USER_OBJECT), 'X') != NVL(to_char(:old.USER_OBJECT), 'X'))OR (NVL(to_char(:new.IMG_PATH), 'X') != NVL(to_char(:old.IMG_PATH), 'X'))OR (NVL(to_char(:new.CAMERA_DESC), 'X') != NVL(to_char(:old.CAMERA_DESC), 'X'))OR (NVL(to_char(:new.IS_REGISTER_VCN), 'X') != NVL(to_char(:old.IS_REGISTER_VCN), 'X'))OR (NVL(to_char(:new.IS_DEL), 'X') != NVL(to_char(:old.IS_DEL), 'X'))OR (NVL(to_char(:new.ORDER_VALUE), 'X') != NVL(to_char(:old.ORDER_VALUE), 'X'))OR (NVL(to_char(:new.POLLING_RESULT), 'X') != NVL(to_char(:old.POLLING_RESULT), 'X'))OR (NVL(to_char(:new.SERVER_ID), 'X') != NVL(to_char(:old.SERVER_ID), 'X'))OR (NVL(to_char(:new.SHORT_MSG), 'X') != NVL(to_char(:old.SHORT_MSG), 'X'))OR (NVL(to_char(:new.CAMERA_BELONGS_ID), 'X') != NVL(to_char(:old.CAMERA_BELONGS_ID), 'X'))OR (NVL(to_char(:new.RELATED_CUSTOMS), 'X') != NVL(to_char(:old.RELATED_CUSTOMS), 'X'))OR (NVL(to_char(:new.ADDED_TO_SDE), 'X') != NVL(to_char(:old.ADDED_TO_SDE), 'X'))OR (NVL(to_char(:new.CAMERA_BAK), 'X') != NVL(to_char(:old.CAMERA_BAK), 'X'))OR (NVL(to_char(:new.CAMERA_BELONGS_PK), 'X') != NVL(to_char(:old.CAMERA_BELONGS_PK), 'X'))OR (NVL(to_char(:new.MEMBERBAR_CODE), 'X') != NVL(to_char(:old.MEMBERBAR_CODE), 'X'))OR (NVL(to_char(:new.IS_BRANCH), 'X') != NVL(to_char(:old.IS_BRANCH), 'X'))OR (NVL(to_char(:new.IS_WATCHPOS), 'X') != NVL(to_char(:old.IS_WATCHPOS), 'X'))OR (NVL(to_char(:new.CAMERA_ANGLE), 'X') != NVL(to_char(:old.CAMERA_ANGLE), 'X'))OR (NVL(to_char(:new.CROSS_ARM3), 'X') != NVL(to_char(:old.CROSS_ARM3), 'X'))OR (NVL(to_char(:new.IS_SYS), 'X') != NVL(to_char(:old.IS_SYS), 'X'))OR (NVL(to_char(:new.RECORD_TIME), 'X') != NVL(to_char(:old.RECORD_TIME), 'X'))OR (NVL(to_char(:new.ANALYSIS_NO), 'X') != NVL(to_char(:old.ANALYSIS_NO), 'X'))OR (NVL(to_char(:new.WIFI_STATE), 'X') != NVL(to_char(:old.WIFI_STATE), 'X'))OR (NVL(to_char(:new.FACE_TASK_STATUS), 'X') != NVL(to_char(:old.FACE_TASK_STATUS), 'X'))OR (NVL(to_char(:new.VIDEO_TASK_STATUS), 'X') != NVL(to_char(:old.VIDEO_TASK_STATUS), 'X'))OR (NVL(to_char(:new.BAYONET_TASK_STATUS), 'X') != NVL(to_char(:old.BAYONET_TASK_STATUS), 'X'))OR (NVL(to_char(:new.VQD_URL), 'X') != NVL(to_char(:old.VQD_URL), 'X'))OR (NVL(to_char(:new.SYS_TYPE), 'X') != NVL(to_char(:old.SYS_TYPE), 'X'))OR (NVL(to_char(:new.IS_HAVE_CAPTURE), 'X') != NVL(to_char(:old.IS_HAVE_CAPTURE), 'X'))THENINSERT INTO sync_data(ID, TABLE_NAME, COMMAND, TABLE_KEY, DATA_ID, FAIL_COUNT, CREATE_TIME)VALUES(sys_guid(), 'PT_CAMERA_INFO', 'UPDATE',  'ID', :new.ID || '', 0,  sysdate);END IF;
END;CREATE OR REPLACE TRIGGER "T_PT_CAMERA_INFO_D"
BEFORE DELETE ON "SHINY"."PT_CAMERA_INFO" FOR EACH ROW
BEGININSERT INTO sync_data(ID, TABLE_NAME, COMMAND, TABLE_KEY, DATA_ID, FAIL_COUNT, CREATE_TIME)VALUES(sys_guid(), 'PT_CAMERA_INFO', 'DELETE',  'ID', :old.ID || '', 0,  sysdate);
END;CREATE OR REPLACE TRIGGER "T_VIDEO_VIDEOSOURCE_I"
AFTER INSERT ON "SHINY"."VIPF_VIDEO_VIDEOSOURCE" FOR EACH ROW
BEGININSERT INTO sync_data(ID, TABLE_NAME, COMMAND, TABLE_KEY, DATA_ID, FAIL_COUNT, CREATE_TIME)VALUES(sys_guid(), 'VIPF_VIDEO_VIDEOSOURCE', 'INSERT',  'CAMERA_ID', :new.CAMERA_ID || '', 0,  sysdate);
END;CREATE OR REPLACE TRIGGER "T_VIDEO_VIDEOSOURCE_U"
AFTER UPDATE ON "SHINY"."VIPF_VIDEO_VIDEOSOURCE" FOR EACH ROW
BEGININSERT INTO sync_data(ID, TABLE_NAME, COMMAND, TABLE_KEY, DATA_ID, FAIL_COUNT, CREATE_TIME)VALUES(sys_guid(), 'VIPF_VIDEO_VIDEOSOURCE', 'UPDATE',  'CAMERA_ID', :new.CAMERA_ID || '', 0,  sysdate);
END;CREATE OR REPLACE TRIGGER "T_VIDEO_VIDEOSOURCE_D"
BEFORE DELETE ON "SHINY"."VIPF_VIDEO_VIDEOSOURCE" FOR EACH ROW
BEGININSERT INTO sync_data(ID, TABLE_NAME, COMMAND, TABLE_KEY, DATA_ID, FAIL_COUNT, CREATE_TIME)VALUES(sys_guid(), 'VIPF_VIDEO_VIDEOSOURCE', 'DELETE',  'CAMERA_ID', :old.CAMERA_ID || '', 0,  sysdate);
END;

启动数据同步任务C#代码：

using DataSync.DAL;
using DataSync.Utils;
using Utils;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Timers;
using System.Windows.Forms;
using Quartz.Impl;
using Quartz;
using DataSync.Jobs;
using Models;namespace DataSync
{/// <summary>/// 数据同步服务/// by suxiang/// </summary>partial class DataSyncService : ServiceBase{#region 变量#endregion#region 构造函数public DataSyncService(){InitializeComponent();}#endregion#region OnStart 启动服务protected override void OnStart(string[] args){startScheduler();LogUtil.Log("服务启动成功");}#endregion#region OnStop 停止服务protected override void OnStop(){LogUtil.Log("服务停止成功");Thread.Sleep(100); //等待一会，待日志写入文件}#endregion#region Start 启动服务public void Start(){OnStart(null);}#endregion#region scheduleJobprivate async void startScheduler(){TaskSchedulerEx taskEx = new TaskSchedulerEx(8, 64);StdSchedulerFactory schedulerFactory = new StdSchedulerFactory();IScheduler scheduler = await schedulerFactory.GetScheduler();await scheduler.Start();await LogUtil.Log("任务调度器已启动");if (ConfigUtil.SyncParam == 1) //部署在政务网侧 {scheduleJob(scheduler, "政务网中间库->政务网平台库", ConfigUtil.ZWW_Middle, ConfigUtil.ZWW_PT, taskEx);scheduleJob(scheduler, "政务网平台库->政务网中间库", ConfigUtil.ZWW_PT, ConfigUtil.ZWW_Middle, taskEx);}if (ConfigUtil.SyncParam == 2) //部署在视频网侧{scheduleJob(scheduler, "视频网平台库->视频网中间库", ConfigUtil.SPW_PT, ConfigUtil.SPW_Middle, taskEx);}}private async void scheduleJob(IScheduler scheduler, string taskName, string dbSourceConnectionString, string dbTargetConnectionString, TaskSchedulerEx taskEx){string cronString = "0/5 * * * * ?";IJobDetail jobDetail = JobBuilder.Create<SyncJob>().Build();ITrigger trigger = TriggerBuilder.Create().WithCronSchedule(cronString).Build();SpeedInfo speedInfo = new SpeedInfo();jobDetail.JobDataMap.Put("taskName", taskName);jobDetail.JobDataMap.Put("dbSourceConnectionString", dbSourceConnectionString);jobDetail.JobDataMap.Put("dbTargetConnectionString", dbTargetConnectionString);jobDetail.JobDataMap.Put("speedInfo", speedInfo);jobDetail.JobDataMap.Put("taskEx", taskEx);await scheduler.ScheduleJob(jobDetail, trigger);await scheduleCalcSpeedJob(scheduler, taskName, speedInfo);}private async Task scheduleCalcSpeedJob(IScheduler scheduler, string taskName, SpeedInfo speedInfo){int timeRange = 10;string cronString = "0/" + timeRange + " * * * * ?";IJobDetail jobDetail = JobBuilder.Create<CalcSpeedJob>().Build();ITrigger trigger = TriggerBuilder.Create().WithCronSchedule(cronString).Build();jobDetail.JobDataMap.Put("taskName", taskName);jobDetail.JobDataMap.Put("timeRange", timeRange);jobDetail.JobDataMap.Put("speedInfo", speedInfo);await scheduler.ScheduleJob(jobDetail, trigger);}#endregion}
}

数据同步任务C#代码：

using DataSync.DAL;
using DataSync.Models;
using DataSync.Utils;
using Models;
using Oracle.ManagedDataAccess.Client;
using Quartz;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Utils;namespace DataSync.Jobs
{/// <summary>/// 数据同步任务/// </summary>[DisallowConcurrentExecution]public class SyncJob : IJob{#region 变量属性private readonly string INSERT = "INSERT";private readonly string UPDATE = "UPDATE";private readonly string DELETE = "DELETE";#endregion#region 数据库操作类private OracleHelper _dbSource = null;private OracleHelper _dbTarget = null;#endregion#region 任务参数private string _taskName = null;private string _dbSourceConnectionString = null;private string _dbTargetConnectionString = null;private SpeedInfo _speedInfo = null;private TaskSchedulerEx _task = null;#endregionpublic Task Execute(IJobExecutionContext context){return Task.Run(() =>{try{_taskName = context.JobDetail.JobDataMap["taskName"].ToString();_dbSourceConnectionString = context.JobDetail.JobDataMap["dbSourceConnectionString"].ToString();_dbTargetConnectionString = context.JobDetail.JobDataMap["dbTargetConnectionString"].ToString();_speedInfo = (SpeedInfo)context.JobDetail.JobDataMap["speedInfo"];_task = (TaskSchedulerEx)context.JobDetail.JobDataMap["taskEx"];_dbSource = new OracleHelper(_dbSourceConnectionString);_dbTarget = new OracleHelper(_dbTargetConnectionString);List<SYNC_DATA> list = ServiceHelper.Get<SyncDataDal>().GetList(_dbSourceConnectionString);int oldCount = list.Count;list = DictinctSyncData(list);if (oldCount > 0 && oldCount != list.Count) LogUtil.Log(string.Format("SYNC_DATA集合去重前数量：{0} 去重后数量：{1}", oldCount, list.Count));Dictionary<string, List<SYNC_DATA>> dict = list.ToLookup(a => a.TABLE_NAME).ToDictionary(a => a.Key, a => a.ToList()); //按表分组，对于单张表，必须用单线程，保证处理顺序int syncCount = 0;object lockObj = new object();List<Task> taskList = new List<Task>();foreach (string key in dict.Keys){foreach (SYNC_DATA item in dict[key]){Task task = _task.Run((obj) =>{SYNC_DATA syncData = obj as SYNC_DATA;string targetTableName = syncData.TABLE_NAME;//表名不一至的特殊情况if (ConfigUtil.SyncParam == 1) //部署在政务网侧 {if (syncData.TABLE_NAME == "VIPF_VIDEO_VIDEOSOURCE") targetTableName = "ZZ_VIDEO_VIDEOSOURCE1";}//查询并缓存目标数据库表字段信息集合List<DbColumnInfo> columnList = MemoryCacheUtil.TryGetValue<List<DbColumnInfo>>(_dbSourceConnectionString + targetTableName, () => _dbTarget.GetAllColumns(targetTableName));int row;SyncOneData(_dbSourceConnectionString, (SYNC_DATA)syncData, targetTableName, _dbSource, _dbTarget, columnList, out row);lock (lockObj) { syncCount += row; }_speedInfo.addCount();}, item);taskList.Add(task);}}Task.WaitAll(taskList.ToArray());if (syncCount > 0) LogUtil.Log(string.Format("本次成功同步 {0} 条数据", syncCount));}catch (Exception ex){LogUtil.Error(ex, "数据同步任务 出错");}});}#region 同步一条数据/// <summary>/// 同步一条数据/// </summary>private void SyncOneData(string connStr, SYNC_DATA item, string targetTableName, OracleHelper dbSource, OracleHelper dbTarget, List<DbColumnInfo> columnList, out int row){bool isFault = false;DateTime dt = DateTime.Now;row = 0;try{#region 通过表名查询字段集合string columns = string.Join(",", columnList.ConvertAll<string>(a => a.columns_name));string columnsForInsert = string.Join(",", columnList.ConvertAll<string>(a => ":" + a.columns_name));string columnsForUpdate = string.Join(",", columnList.ConvertAll<string>(a => a.columns_name + "=:" + a.columns_name));#endregionif (item.COMMAND == INSERT || item.COMMAND == UPDATE){//数据查询DataTable sourceData = dbSource.Query(string.Format(@"select * from {0} where {1}", item.TABLE_NAME, CreateWhere(item.TABLE_KEY, item.DATA_ID)));DataTable targetData = dbTarget.Query(string.Format(@"select * from {0} where {1}", targetTableName, CreateWhere(item.TABLE_KEY, item.DATA_ID)));//判断摄像机表是否需要同步if (item.TABLE_NAME == "PT_CAMERA_INFO"){if (!needSync(sourceData, targetData)){ServiceHelper.Get<SyncDataDal>().DeleteById(connStr, item.ID);return;}}if (sourceData != null && sourceData.Rows.Count > 0){bool exists = targetData != null && targetData.Rows.Count > 0;if ((item.COMMAND == INSERT && !exists) || (item.COMMAND == UPDATE && !exists)){#region 插入数据string insertSql = string.Format("insert into {0}({1}) values({2})", targetTableName, columns, columnsForInsert);#region 字段赋值OracleParameter[] paramArr = new OracleParameter[columnList.Count];for (int i = 0; i < columnList.Count; i++){DbColumnInfo columnInfo = columnList[i];paramArr[i] = new OracleParameter(columnInfo.columns_name, GetColumnData(sourceData, columnInfo.columns_name));}#endregionrow = dbTarget.ExecuteSql(insertSql, paramArr);if (row > 0) LogUtil.Log(string.Format("{0} 表 {1} 插入一条数据，{2}", _taskName, targetTableName, CreateWhereLog(item.TABLE_KEY, item.DATA_ID)));#endregion}if ((item.COMMAND == INSERT && exists) || (item.COMMAND == UPDATE && exists)){#region 更新数据string updateSql = string.Format("update {0} set {1} where {2}", targetTableName, columnsForUpdate, CreateWhere(item.TABLE_KEY, item.DATA_ID));#region 字段赋值List<OracleParameter> paramList = new List<OracleParameter>();foreach (DbColumnInfo columnInfo in columnList){paramList.Add(new OracleParameter(columnInfo.columns_name, GetColumnData(sourceData, columnInfo.columns_name)));}#endregionrow = dbTarget.ExecuteSql(updateSql, paramList.ToArray());if (row > 0) LogUtil.Log(string.Format("{0} 表 {1} 更新一条数据，{2}", _taskName, targetTableName, CreateWhereLog(item.TABLE_KEY, item.DATA_ID)));#endregion}}}if (item.COMMAND == DELETE){#region 删除数据string deleteSql = string.Format("delete from {0} where {1}", targetTableName, CreateWhere(item.TABLE_KEY, item.DATA_ID));row = dbTarget.ExecuteSql(deleteSql);if (row > 0) LogUtil.Log(string.Format("{0} 表 {1} 删除一条数据，{2}", _taskName, targetTableName, CreateWhereLog(item.TABLE_KEY, item.DATA_ID)));#endregion}ServiceHelper.Get<SyncDataDal>().DeleteById(connStr, item.ID);}catch (Exception ex){isFault = true;LogUtil.Error(ex, "同步数据失败，表名：" + item.TABLE_NAME.ToUpper() + " 命令：" + item.COMMAND + " 数据ID：" + item.DATA_ID);}try{if (isFault){SYNC_DATA sysSyncBs = ServiceHelper.Get<SyncDataDal>().GetById(connStr, item.ID);sysSyncBs.FAIL_COUNT++;ServiceHelper.Get<SyncDataDal>().Update(connStr, sysSyncBs);LogUtil.Debug("更新业务数据同步表SYNC_DATA，ID：" + item.ID);}}catch (Exception ex){LogUtil.Error(ex, "更新业务数据同步表SYNC_DATA出错");}}#endregion#region 判断是否是主键/// <summary>/// 判断是否是主键/// </summary>private bool IsIdField(string columnName, string idField){if (idField.IndexOf(',') >= 0){string[] idFieldArr = idField.Split(',');return idFieldArr.ToList().Exists(a => a == columnName);}else{return columnName == idField;}}#endregion#region 根据主键生成Where条件语句/// <summary>/// 根据主键生成Where条件语句/// </summary>private string CreateWhere(string idField, string idData){if (idField.IndexOf(',') >= 0){StringBuilder sb = new StringBuilder(" 1=1 ");string[] idFieldArr = idField.Split(',');string[] idDataArr = idData.Split(',');for (int i = 0; i < idFieldArr.Length; i++){sb.AppendFormat(" and {0}='{1}' ", idFieldArr[i], idDataArr[i]);}return sb.ToString();}else{return string.Format("{0}='{1}'", idField, idData);}}#endregion#region 根据字段名从DataTable中获取数据/// <summary>/// 根据字段名从DataTable中获取数据/// </summary>private object GetColumnData(DataTable data, string columnName){for (int i = 0; i < data.Columns.Count; i++){if (data.Columns[i].ColumnName == columnName){return data.Rows[0][data.Columns[i]];}}return DBNull.Value;}#endregion#region 根据主键生成Where条件语句日志信息/// <summary>/// 根据主键生成Where条件语句日志信息/// </summary>private string CreateWhereLog(string idField, string idData){if (idField.IndexOf(',') >= 0){List<string> list = new List<string>();string[] idFieldArr = idField.Split(',');string[] idDataArr = idData.Split(',');for (int i = 0; i < idFieldArr.Length; i++){list.Add(string.Format("{0}:{1}", idFieldArr[i], idDataArr[i]));}return string.Join("，", list.ToArray());}else{return string.Format("{0}:{1}", idField, idData);}}#endregion#region 判断是否需要同步private bool needSync(DataTable sourceTable, DataTable targetTable){HashSet<string> hashSet = new HashSet<string>() { "POLLING_TIME", "CAMERA_STATE" };if (targetTable == null || targetTable.Rows.Count == 0) return true;if (sourceTable != null && sourceTable.Rows.Count > 0 && targetTable != null && targetTable.Rows.Count > 0){foreach (DataColumn column in sourceTable.Columns){if (targetTable.Columns.Contains(column.ColumnName)){if (sourceTable.Rows[0][column.ColumnName] != null && !sourceTable.Rows[0][column].Equals(targetTable.Rows[0][column.ColumnName])){if (!hashSet.Contains(column.ColumnName)){return true;}}}}}return false;}#endregion#region SYNC_DATA集合去重,对于按条件相同的数据,按顺序保留最后一条/// <summary>/// SYNC_DATA集合去重,对于按条件相同的数据,按顺序保留最后一条/// </summary>private List<SYNC_DATA> DictinctSyncData(List<SYNC_DATA> list){List<SYNC_DATA> result = new List<SYNC_DATA>();for (int i = 0; i < list.Count; i++){SYNC_DATA dataI = list[i];bool exists = false;for (int j = i + 1; j < list.Count; j++){SYNC_DATA dataJ = list[j];if (dataI.TABLE_NAME == dataJ.TABLE_NAME&& dataI.TABLE_KEY == dataJ.TABLE_KEY&& dataI.DATA_ID == dataJ.DATA_ID){exists = true;}}if (!exists){result.Add(dataI);}}return result;}#endregion}}