/// <summary>
/// 查询出列名
/// </summary>
/// <param name="connectionName">配置文件中的 name</param>
/// <param name="tableName">表名</param>
/// <param name="type">1.sql server 2.mysql</param>
/// <returns></returns>
public IList <string> GetColumnsName(string connectionName, string tableName, TableType type = TableType.SqlServer)
{
OperationResult <IList <string> > columnsNameResult = null;
if (type == TableType.SqlServer)
{
SynPlanSqlServerBLL sqlServerBLL = new SynPlanSqlServerBLL(connectionName);
columnsNameResult = sqlServerBLL.GetColumnsName(tableName);
}
else if (type == TableType.MySql)
{
SynPlanMySqlBLL mySqlBLL = new SynPlanMySqlBLL(connectionName);
columnsNameResult = mySqlBLL.GetColumnsName(tableName);
}
if (columnsNameResult != null && columnsNameResult.ResultType == OperationResultType.Success)
{
return(columnsNameResult.AppendData);
}
else
{
Log(string.Format("From:{0} 数据库操作失败！原因:{1}", connectionName, columnsNameResult.Message));
return(null);
}
}
/// <summary>
/// 获取 to 表中的最大ID
/// </summary>
/// <param name="connectionName">配置文件中的 name</param>
/// <param name="tableName">表名</param>
/// <param name="pid">表中的主键ID</param>
/// <param name="type">1.sql server 2.mysql</param>
/// <returns></returns>
public int GetMaxID(string connectionName, string tableName, string keyName, TableType type = TableType.MySql)
{
OperationResult <int> maxIDResult = null;
if (type == TableType.SqlServer)
{
SynPlanSqlServerBLL sqlServerBLL = new SynPlanSqlServerBLL(connectionName);
maxIDResult = sqlServerBLL.GetMaxID(tableName, keyName);
}
else if (type == TableType.MySql)
{
SynPlanMySqlBLL mySqlBLL = new SynPlanMySqlBLL(connectionName);
maxIDResult = mySqlBLL.GetMaxID(tableName, keyName);
}
if (maxIDResult != null && maxIDResult.ResultType == OperationResultType.Success)
{
return(maxIDResult.AppendData);
}
else
{
Log(string.Format("From:{0} 数据库操作失败！原因:{1}", connectionName, maxIDResult.Message));
return(-1);
}
}
/// <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));
}
