//设置DataBase查询对象
//设置同步时 From 表与 To 表不一样的数据
//判定是否联合查询
//Dictionary<FromColumnName,ToColumnName> 匹配后,追加自定义列字段,移除不需要更新的列
//批量同步 分离出哪些要更新哪些要插入
//记录批量异常后的单次语句
// from 数据来源表 to 写入的目标表名
public void Init()
{
log4net.Config.XmlConfigurator.Configure();
//获取配置文件里的更新列表
IList <SynPlanInfo> synPlanInfoList = GetSynPlanConfigInfo();
for (int i = 0; i < synPlanInfoList.Count; i++)
{
SynPlanInfo spInfo = synPlanInfoList[i];
//获取From 表结构
IList <string> fromCloumnsName = new List <string>();
//获取To 表结构
IList <string> toCloumnsName = new List <string>();
//筛选列名
Dictionary <string, string> updateColumns = new Dictionary <string, string>();
/*
* 当IsJoin==1,表示 from 源表需要联合查询
* 会调用spInfo中的sql语句
* 将无法获得 from 和 to 的表结构
* 将直接调用params所有字段,key作为from的结构体,value作为to的结构体
* 请保证配置匹配正确
* fromtable配置无效,分页无效
*/
if (spInfo.IsJoin == 1)
{
if (spInfo.Params.Count == 0)
{
Log(string.Format(spInfo.FromTable + "数据列不能为空！数据终止"));
return;
}
updateColumns = ScreenColumn(spInfo.FromTable, fromCloumnsName, toCloumnsName, spInfo.Params, spInfo.Forget);
}
else //普通更新方式
{
fromCloumnsName = GetColumnsName(spInfo.FromConnectionName, spInfo.FromTable, (TableType)spInfo.FromType);
toCloumnsName = GetColumnsName(spInfo.ToConnectionName, spInfo.ToTable, (TableType)spInfo.ToType);
//筛选列名 将To 表的不匹配的字段字典里移除
updateColumns = ScreenColumn(spInfo.FromTable, fromCloumnsName, toCloumnsName, spInfo.Params, spInfo.Forget);
}
//判定to表最大ID
var item = updateColumns.ElementAt(0);
int maxID = GetMaxID(spInfo.ToConnectionName, spInfo.ToTable, item.Value, (TableType)spInfo.ToType);
//同步数据 fromCloumnsName[0]：数据源主键ID
SynData(spInfo, updateColumns, item.Key, maxID);
}
}
/// <summary>
/// 获取WebConfig.synplans 配置信息
/// </summary>
/// <returns></returns>
public IList <SynPlanInfo> GetSynPlanConfigInfo()
{
IList <SynPlanInfo> synPlanInfoList = new List <SynPlanInfo>();
var synPlanSection = ConfigurationManager.GetSection("synplans") as XmlNode;
XmlNodeList gradesNode = synPlanSection.ChildNodes;
for (int i = 0; i < gradesNode.Count; i++)
{
SynPlanInfo synPlanInfo = new SynPlanInfo();
XmlNode spiderNode = gradesNode[i];
for (int j = 0; j < spiderNode.Attributes.Count; j++)
{
string name = spiderNode.Attributes[j].Name;
string value = spiderNode.Attributes[name].Value;
switch (name)
{
case "ispsh":
synPlanInfo.IsPush = Convert.ToInt32(value); break;
case "size":
synPlanInfo.Size = Convert.ToInt32(value); break;
case "isjoin":
synPlanInfo.IsJoin = Convert.ToInt32(value); break;
case "sql":
synPlanInfo.sql = value; break;
}
}
Dictionary <string, string> dictParams = new Dictionary <string, string>();
Dictionary <string, string> dictForget = new Dictionary <string, string>();
XmlNodeList childNode = spiderNode.ChildNodes;
for (int m = 0; m < childNode.Count; m++)
{
XmlNode pageNode = childNode[m];
if (pageNode.Name == "fromtable" || pageNode.Name == "totable")
{
for (int n = 0; n < pageNode.Attributes.Count; n++)
{
string name = pageNode.Attributes[n].Name;
string value = pageNode.Attributes[name].Value;
switch (name)
{
case "fromconnection":
synPlanInfo.FromConnectionName = value; break;
case "fromtable":
synPlanInfo.FromTable = value; break;
case "toconnection":
synPlanInfo.ToConnectionName = value; break;
case "totable":
synPlanInfo.ToTable = value; break;
case "fromtype":
synPlanInfo.FromType = Convert.ToInt32(value); break;
case "totype":
synPlanInfo.ToType = Convert.ToInt32(value); break;
}
}
}
else
{
XmlNodeList paramsNodes = pageNode.ChildNodes;
string fromColumnName = string.Empty;
string toColumnName = string.Empty;
for (int n = 0; n < paramsNodes.Count; n++)
{
XmlNode paramsNode = paramsNodes[n];
for (int k = 0; k < paramsNode.Attributes.Count; k++)
{
string name = paramsNode.Attributes[k].Name;
string value = paramsNode.Attributes[name].Value;
switch (name)
{
case "key":
fromColumnName = value; break;
case "value":
toColumnName = value; break;
}
}
if (pageNode.Name == "params")
{
dictParams.Add(fromColumnName, toColumnName);
}
else
{
dictForget.Add(fromColumnName, toColumnName);
}
}
}
}
synPlanInfo.Params = dictParams;
synPlanInfo.Forget = dictForget;
synPlanInfoList.Add(synPlanInfo);
}
return(synPlanInfoList);
}
/// <summary>
/// 同步数据 Synchronous data
/// </summary>
/// <param name="connectionName"></param>
/// <param name="tableName"></param>
/// <param name="keyName"></param>
/// <param name="type"></param>
/// <returns></returns>
public void SynData(SynPlanInfo spInfo, Dictionary <string, string> columnNames, string keyName, int maxID)
{
if (spInfo.IsPush != 1)
{
Log(string.Format("因web.Config中 {0} isPush设置 不同步,已跳过更新 ", spInfo.FromTable));
return;
}
//从from 里拿数据
Log(string.Format("开始执行 从{0} 至 {1} 数据同步开始 时间:{2}", spInfo.FromTable, spInfo.ToTable, DateTime.Now));
SynPlanSqlServerBLL sqlServerBLL = new SynPlanSqlServerBLL(spInfo.FromConnectionName);
SynPlanMySqlBLL mySqlBLL = new SynPlanMySqlBLL(spInfo.ToConnectionName);
int startCount = 1;
int endCount = 0;
int size = spInfo.Size;
int SNUMBER = 0; //计数器
string addFromColumnFlag = string.Empty; //insert from字段
string addToColumnFlag = string.Empty; //insert to写入字段
string addToPColumnFlag = string.Empty; //insert to存储过程字段
//update 时 sql 参数
string updateToPValueFlag = string.Empty; //update 参数
string updateToPKeyFlag = string.Empty; //update WHERE 主键
#region insert 与 update 的 条件参数
for (int i = 0; i < columnNames.Count; i++)
{
var item = columnNames.ElementAt(i);
if (i == 0)
{
updateToPKeyFlag = item.Value + "=@" + item.Value;
}
else
{
updateToPValueFlag += item.Value + "=@" + item.Value + ",";
}
}
if (!string.IsNullOrEmpty(updateToPValueFlag))
{
updateToPValueFlag = updateToPValueFlag.Substring(0, updateToPValueFlag.Length - 1);
}
//insert时 sql 参数
foreach (var item in columnNames)
{
addFromColumnFlag += item.Key + ",";
}
if (!string.IsNullOrEmpty(addFromColumnFlag))
{
addFromColumnFlag = addFromColumnFlag.Substring(0, addFromColumnFlag.Length - 1);
}
foreach (var item in columnNames)
{
addToColumnFlag += item.Value + ",";
addToPColumnFlag += "@" + item.Value + ",";
}
if (!string.IsNullOrEmpty(addFromColumnFlag))
{
addToColumnFlag = addToColumnFlag.Substring(0, addToColumnFlag.Length - 1);
addToPColumnFlag = addToPColumnFlag.Substring(0, addToPColumnFlag.Length - 1);
}
#endregion
while (true)
{
startCount = SNUMBER * size + 1;
endCount = (SNUMBER + 1) * size;
DataTable fromData = null;
#region 获取 from 数据
if ((TableType)spInfo.FromType == TableType.SqlServer) //从sql server中获取数据
{
OperationResult <DataTable> tableResult = null;
if (spInfo.IsJoin == 1) //判定是否调用联合查询
{
//调用后的table必须和配置文件相同
tableResult = sqlServerBLL.GetList(spInfo.sql);
}
else
{
tableResult = sqlServerBLL.GetList(spInfo.FromTable, keyName, addFromColumnFlag, startCount, endCount);
}
if (tableResult.ResultType == OperationResultType.Success)
{
fromData = tableResult.AppendData;
}
else
{
//打不开数据库
Log(string.Format(" 数据库{0}无法打开！程序终止！ ", spInfo.FromConnectionName));
break;
}
}
else if ((TableType)spInfo.FromType == TableType.SqlServer) //从mySql中获取数据
{
OperationResult <DataTable> tableResult = null;
if (spInfo.IsJoin == 1) //判定是否调用联合查询
{
//调用后的table必须和配置文件相同
tableResult = sqlServerBLL.GetList(spInfo.sql);
}
else
{
//mysql limit下标是从0开始
tableResult = mySqlBLL.GetList(spInfo.FromTable, keyName, addFromColumnFlag, startCount - 1, size);
}
if (tableResult.ResultType == OperationResultType.Success)
{
fromData = tableResult.AppendData;
}
else
{
//打不开数据库
Log(string.Format(" 数据库{0}无法打开！程序终止！ ", spInfo.FromConnectionName));
break;
}
}
#endregion
#region 批量同步
//更新列表
DataTable updateTable = new DataTable();
updateTable = fromData.Copy();
updateTable.Rows.Clear();
//插入列表
DataTable insertTable = new DataTable();
insertTable = fromData.Copy();
insertTable.Rows.Clear();
for (int i = 0; i < fromData.Rows.Count; i++)
{
// from 表 ID
int id = Convert.ToInt32(fromData.Rows[i][keyName]);
if (id > maxID)
{
insertTable.ImportRow(fromData.Rows[i]);
}
else
{
updateTable.ImportRow(fromData.Rows[i]);
}
}
// from updateTable的列名和 InsertTable列名修改成 to 表里的列名
foreach (var item in columnNames)
{
var cs = insertTable.Columns[item.Key];
if (cs != null)
{
insertTable.Columns[item.Key].ColumnName = item.Value;
updateTable.Columns[item.Key].ColumnName = item.Value;
}
}
if ((TableType)spInfo.ToType == TableType.SqlServer) //写入sql server中
{
//insert mysql
OperationResult <bool> isOkAddResult =
sqlServerBLL.InsertData(spInfo.ToTable, addToColumnFlag, addToPColumnFlag, insertTable, columnNames);
if (isOkAddResult.ResultType != OperationResultType.Success || isOkAddResult.AppendData == false)
{
Log(string.Format("批量插入时出现异常,正在过滤异常数据！请留意日志文件"));
//此数据未能成功同步（插入）数据库进入单条插入
DataTable singleTable = new DataTable(); //存放信息table
for (int i = 0; i < insertTable.Rows.Count; i++)
{
singleTable.ImportRow(insertTable.Rows[i]);
OperationResult <bool> singleResult =
sqlServerBLL.InsertData(spInfo.ToTable, addToColumnFlag, addToPColumnFlag, singleTable, columnNames);
if (singleResult.ResultType != OperationResultType.Success || singleResult.AppendData == false)
{
Log(string.Format("从:{0} 至:{1} 数据主键ID:{2} 插入失败！"
, spInfo.FromTable, spInfo.ToTable, singleTable.Rows[0][keyName]));
}
singleTable.Rows.Clear(); //执行完清理
}
}
//update sql server
OperationResult <bool> isOkUpdateResult =
sqlServerBLL.UpdateData(spInfo.ToTable, updateToPKeyFlag, updateToPValueFlag, updateTable, columnNames);
if (isOkUpdateResult.ResultType != OperationResultType.Success || isOkUpdateResult.AppendData == false)
{
Log(string.Format("批量更新时出现异常,正在过滤异常数据！请留意日志文件"));
//此数据未能成功同步（更新）数据库进入单条更新
DataTable singleTable = new DataTable(); //存放信息table
for (int i = 0; i < updateTable.Rows.Count; i++)
{
singleTable.ImportRow(updateTable.Rows[i]);
OperationResult <bool> singleResult =
sqlServerBLL.UpdateData(spInfo.ToTable, updateToPKeyFlag, updateToPValueFlag, singleTable, columnNames);
if (singleResult.ResultType != OperationResultType.Success || singleResult.AppendData == false)
{
Log(string.Format("从:{0} 至:{1} 数据主键ID:{2} 插入失败！"
, spInfo.FromTable, spInfo.ToTable, singleTable.Rows[0][keyName]));
}
singleTable.Rows.Clear(); //执行完清理
}
}
}
else if ((TableType)spInfo.ToType == TableType.MySql) //写入mysql中
{
//inser tmysql
OperationResult <bool> isOkAddResult =
mySqlBLL.InsertData(spInfo.ToTable, addToColumnFlag, addToPColumnFlag, insertTable, columnNames);
if (isOkAddResult.ResultType != OperationResultType.Success || isOkAddResult.AppendData == false)
{
Log(string.Format("批量插入时出现异常,正在过滤异常数据！请留意日志文件"));
//此数据未能成功同步（插入）数据库进入单条插入
DataTable singleTable = new DataTable();//存放信息table
for (int i = 0; i < insertTable.Rows.Count; i++)
{
singleTable.ImportRow(insertTable.Rows[i]);
OperationResult <bool> singleResult =
mySqlBLL.InsertData(spInfo.ToTable, addToColumnFlag, addToPColumnFlag, singleTable, columnNames);
if (singleResult.ResultType != OperationResultType.Success || singleResult.AppendData == false)
{
Log(string.Format("从:{0} 至:{1} 数据主键ID:{2} 插入失败！"
, spInfo.FromTable, spInfo.ToTable, singleTable.Rows[0][keyName]));
}
singleTable.Rows.Clear(); //执行完清理
}
}
//update mysql
OperationResult <bool> isOkUpdateResult =
mySqlBLL.UpdateData(spInfo.ToTable, updateToPKeyFlag, updateToPValueFlag, updateTable, columnNames);
if (isOkUpdateResult.ResultType != OperationResultType.Success || isOkUpdateResult.AppendData == false)
{
Log(string.Format("批量更新时出现异常,正在过滤异常数据！请留意日志文件"));
//此数据未能成功同步（更新）数据库进入单条更新
DataTable singleTable = new DataTable();//存放信息table
for (int i = 0; i < updateTable.Rows.Count; i++)
{
singleTable.ImportRow(updateTable.Rows[i]);
OperationResult <bool> singleResult =
mySqlBLL.UpdateData(spInfo.ToTable, updateToPKeyFlag, updateToPValueFlag, singleTable, columnNames);
if (singleResult.ResultType != OperationResultType.Success || singleResult.AppendData == false)
{
Log(string.Format("从:{0} 至:{1} 数据主键ID:{2} 插入失败！"
, spInfo.FromTable, spInfo.ToTable, singleTable.Rows[0][keyName]));
}
singleTable.Rows.Clear();//执行完清理
}
}
}
#endregion
if (fromData == null && fromData.Rows.Count == 0)
{
break;
}
}
Log(string.Format("开始执行 从{0} 至 {1} 数据同步结束 时间:{2}", spInfo.FromTable, spInfo.ToTable, DateTime.Now));
}
