/// <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)); }