private void SysnTargetTable(XmlTableMap tbMap)
{
//同步数据并且获取该表的主键ID列表
var resList = SynTableData(tbMap);
//同步所有子表数据
SysnSubTargetTable(tbMap.TargetTable, resList);//同步子表
}
public void ReLoadTargetSourceTableMapDic()
{
string fileName = Application.StartupPath + "\\tableMap.xml";
if (System.IO.File.Exists(fileName))
{
_xmlTableMap = XmlTableMap.Create(fileName);
}
else
{
LogWriter.Info("未找到映射配置文件tableMap.xml");
}
}
/// <summary>
/// 数据表数据同步
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="targetTable"></param>
/// <returns>表主键(目标数据与源数据的id都相同)</returns>
private List <string> SynTableData(XmlTableMap tableMap)
{
List <string> tableForeignKeyValList = new List <string>();
var sourceDicList = SourceTableStore.GetTableData(tableMap.SourceTable, tableMap.SourceConnStr);
//string sql = "";
foreach (var sourceDic in sourceDicList)
{
//sql += _targetTableStore.GetExcuteSql(sourceDic, targetTableName) + " ";
if (_targetTableStore.ExcuteSql(sourceDic, tableMap))
{
tableForeignKeyValList.Add(sourceDic.GetValue("ID"));
}
}
//SQLHelper.CreateSqlHelper(EnumConn.DataInterface).ExecuteNonQuery(sql);
return(tableForeignKeyValList);
}
public bool ExcuteSql(Dictionary <string, object> sourceDic, XmlTableMap tableMap)
{
string sql = GetExcuteSql(sourceDic, tableMap);
if (!string.IsNullOrEmpty(sql))
{
try
{
SQLHelper.CreateSqlHelper(EnumConn.DataInterface).ExecuteNonQuery(sql);
}
catch (Exception ex)
{
LogWriter.Info(string.Format("在对表{0}进行增改数据操作的时候出错,数据为【{1}】,错误内容为{2}", tableMap.TargetTable, JsonHelper.ToJson(sourceDic), ex.Message));
return(false);
}
}
return(true);
}
public string GetExcuteSql(Dictionary <string, object> sourceDic, XmlTableMap tableMap)
{
var sqlHelper = SQLHelper.CreateSqlHelper(EnumConn.DataInterface);
string sql = "";
string id = sourceDic.GetValue("ID");
var targetDt = sqlHelper.ExecuteDataTable(string.Format("select top 1 * from {0} where id = '{1}'", tableMap.TargetTable, id));
//检查targetDt是否有ModifyDate,如果存在则修复sourceDic(不存在该字段就补,该字段数据为空就补当前时间)
FixSourceDicModifyDate(sourceDic, targetDt);
if (targetDt.Rows.Count == 0)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
foreach (DataColumn dc in targetDt.Columns)
{
dic.SetValue(dc.ColumnName, sourceDic.GetValue(dc.ColumnName));
}
foreach (var map in tableMap.SpecialFieldMaps)
{
dic.SetValue(map.TargetField, sourceDic.GetValue(map.SourceField));
}
////设置外键值
//var tableModel = GetTargetTableModel(tableMap.TargetTable);
//if (tableModel != null && !tableModel.IsMain)
//{
// //没有映射到外键字段值得时候再取
// if (string.IsNullOrEmpty(dic.GetValue(tableModel.ForeignKeyField)))
// {
// if (string.IsNullOrEmpty(sourceDic.GetValue("ID")))
// LogWriter.Info(string.Format("【{0}】的查询结果必须包含ID,否则其子表的外键字段无法赋值", tableMap.SourceTable));
// dic.SetValue(tableModel.ForeignKeyField, "");
// }
//}
//由TableModel进行字段默认值进行补充
SetDefaultValueFromWebConfigSetting(dic, targetDt.Columns, tableMap.TargetTable);
dic.SetValue("SynDate", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
sql = dic.CreateInsertSql(sqlHelper, tableMap.TargetTable, dic.GetValue("ID"));
}
else
{
DataRow targetDr = targetDt.Rows[0];
DateTime targetModifyDate = DateTime.MinValue;
DateTime sourceModifyDate = DateTime.MaxValue;
//如果目标存在ModifyDate才取值，并判断是否更新，否则总是更新
if (targetDt.Columns.Contains("ModifyDate"))
{
DateTime.TryParse(targetDr["ModifyDate"].ToString(), out targetModifyDate);
DateTime.TryParse(sourceDic["ModifyDate"].ToString(), out sourceModifyDate);
}
//旧数据要更新
if (targetModifyDate < sourceModifyDate)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
foreach (DataColumn dc in targetDt.Columns)
{
//源无此列，则赋值目标
if (!sourceDic.ContainsKey(dc.ColumnName))
{
dic.SetValue(dc.ColumnName, targetDr[dc.ColumnName]);
}
else
{
dic.SetValue(dc.ColumnName, sourceDic.GetValue(dc.ColumnName));
}
}
foreach (var map in tableMap.SpecialFieldMaps)
{
dic.SetValue(map.TargetField, sourceDic.GetValue(map.SourceField));
}
//由TableModel进行字段默认值进行补充
SetDefaultValueFromWebConfigSetting(dic, targetDt.Columns, tableMap.TargetTable);
dic.SetValue("SynDate", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
sql = dic.CreateUpdateSql(sqlHelper, tableMap.TargetTable, dic.GetValue("ID"));
}
}
return(sql);
}
