/// <summary> /// 保存到目标数据库 /// </summary> /// <param name="tableMap"></param> /// <param name="dtSource"></param> private void SaveToTargetDB(TableMap tableMap, DataTable dtSource) { // 目标为数据表的情况 string curProcess = ""; DataAccessBroker broker = DataAccessFactory.Instance(this.TargetDataAccessCfg); try { // 事务开始 curProcess = "事务开始"; broker.BeginTransaction(); DatabaseCommand.SaveToTargetDB(broker, tableMap, dtSource); // 事务提交 curProcess = "事务提交"; broker.Commit(); } catch (Exception ex) { // 事务回滚 broker.RollBack(); throw ex; } finally { broker.Close(); } // 写日志 LogManager.Current.WriteCommonLog(this.JobCode, "成功将数据保存到目标表" + tableMap.TargetTable.TableName, this.ThreadName); }
/// <summary> /// 从数据库得到来源数据 /// </summary> /// <returns></returns> public DataTable GetSourceDataFromDB(Hashtable parameters, TableMap tableMap) { DataTable dtSource = null; DataAccessBroker brokerSource = DataAccessFactory.Instance(this.SourceDataAccessCfg); try { string sqlString = tableMap.GetSourceSelectSQL(this.MaxCount); // 替换sql中的宏变量 sqlString = BaseCommand.ReplaceParameters(this.InitialParameters, sqlString); DataSet dataSetSource = brokerSource.FillSQLDataSet(sqlString); // 源数据为空时,不做任何操作 if (dataSetSource == null || dataSetSource.Tables.Count == 0) { return(null); } // 取第一个表 dtSource = dataSetSource.Tables[0]; } finally { brokerSource.Close(); } // 写日志 LogManager.Current.WriteCommonLog(this.JobCode, "从数据表" + tableMap.SourceTable.TableName + "获得" + dtSource.Rows.Count.ToString() + "笔记录。", this.ThreadName); return(dtSource); }
/// <summary> /// 保存header和detail到数据,一笔header与对应的几笔detail作为一个事务 /// </summary> /// <param name="dtHeader"></param> /// <param name="dtDetail"></param> private void SaveHeaderDetailToTarget(ref Hashtable parameters, DataTable dtHeader, DataTable dtDetail, string dataSplitOperator) { List <string> dataIDList = (List <string>)parameters[DDPConst.Param_DataIDList]; int nError = 0; int nSuccess = 0; DataAccessBroker brokerTarget = DataAccessFactory.Instance(this.TargetDataAccessCfg); try { // header sql this.HeaderTableMap.InitialFieldMapByDataTable(dtHeader); string insertSqlHeader = this.HeaderTableMap.GetTargetInsertSQL(brokerTarget.ParameterPrefix.ToString()); // detail sql if (dtDetail.Rows.Count > 0) { this.DetailTableMap.InitialFieldMapByDataTable(dtDetail.Rows[0]); } string insertSqlDetail = this.DetailTableMap.GetTargetInsertSQL(brokerTarget.ParameterPrefix.ToString()); for (int i = 0; i < dtHeader.Rows.Count; i++) { Application.DoEvents(); string recordsCondition = ""; // 一条header DataRow rowHeader = dtHeader.Rows[i]; // 一笔完整的记录一个事务,包括header和detail brokerTarget.BeginTransaction(); try { // 并出关联字段及值,便出检索detail string refFieldSql = ""; string refValueSql = ""; string[] refFieldList = this.DetailTableMap.SourceTable.RefFields.Split(new char[] { ',' }); for (int x = 0; x < refFieldList.Length; x++) { string refField = refFieldList[x]; if (refFieldSql != "") { refFieldSql += "+'|'+"; } refFieldSql += refField; if (refValueSql != "") { refValueSql += "|"; } refValueSql += rowHeader[refField].ToString(); } recordsCondition = refFieldSql + "='" + refValueSql + "'"; // 找到Detail数据 DataRow[] detailRowList = dtDetail.Select(recordsCondition); LogManager.Current.WriteCommonLog(this.JobCode, refValueSql + "对应" + detailRowList.Length.ToString() + "条detail记录。", this.ThreadName); // 将header插入到数据库 DataAccessParameterCollection dpcHeader = this.HeaderTableMap.GetTargetParameters(rowHeader, this.HeaderTableMap.TargetTable.NullFieldNameList); brokerTarget.ExecuteSQL(insertSqlHeader, dpcHeader); // 将detail插入到数据库 for (int j = 0; j < detailRowList.Length; j++) { // detail DataRow rowDetail = detailRowList[j]; DataAccessParameterCollection dpcDetail = this.DetailTableMap.GetTargetParameters(rowDetail, this.DetailTableMap.TargetTable.NullFieldNameList); brokerTarget.ExecuteSQL(insertSqlDetail, dpcDetail); } // 事务提交 brokerTarget.Commit(); LogManager.Current.WriteCommonLog(this.JobCode, recordsCondition + "记录保存数据库成功。", this.ThreadName); nSuccess++; } catch (Exception ex) { // 事务回滚 brokerTarget.RollBack(); LogManager.Current.WriteErrorLog(this.JobCode, recordsCondition + "记录保存数据库出错:" + ex.Message, this.ThreadName); // 从DataIDList中移出此笔记录,这样就不会更新最新时间了 string dataID = this.GetFieldValue(rowHeader, this.HeaderTableMap.SourceTable.PrimaryKeys, dataSplitOperator); dataIDList.Remove(dataID); nError++; // 继续下面的记录 continue; } } } catch (Exception ex) { throw ex; } finally { brokerTarget.Close(); } // 写日志 string errorInfo = "来源共" + dtHeader.Rows.Count.ToString() + "笔记录," + nSuccess.ToString() + "笔保存成功," + nError.ToString() + "笔保存失败。"; LogManager.Current.WriteCommonLog(this.JobCode, errorInfo, this.ThreadName); // 保存到参数里,传给外面 parameters[DDPConst.Param_Info] = errorInfo; // 将错误的记录数保存在参数里 if (nError > 0) { parameters[DDPConst.Param_ErrorCount] = nError; } }
/// <summary> /// 执行 /// </summary> /// <param name="parameters"></param> /// <param name="error"></param> /// <returns></returns> public override ResultCode Execute(ref Hashtable parameters, out string error) { error = ""; // 先将文件名中的参数替换掉 string targetFileName = BaseCommand.ReplaceParameters(this.InitialParameters, this._tableMap.TargetTable.FileName); // 确保目录存在 int nIndex = targetFileName.LastIndexOf("\\"); if (nIndex != -1) { string dir = targetFileName.Substring(0, nIndex); if (Directory.Exists(dir) == false) { Directory.CreateDirectory(dir); } } int count = 0; DataAccessBroker brokerSource = DataAccessFactory.Instance(this.SourceDataAccessCfg); // 将数据保存到目标文件 StreamWriter sw = null; if (String.Compare(this._tableMap.TargetTable.Encoding, "UTF8", true) == 0) { sw = new StreamWriter(targetFileName, false, Encoding.UTF8); //C2-CI指定utf8 } else { sw = new StreamWriter(targetFileName, false); } try { string sqlString = ""; sw.BaseStream.Seek(0, SeekOrigin.Begin); // 判断是否第一行输出总记录数 if (this._tableMap.TargetTable.OneRowRecordsNum == true) { string header = "#"; header = header.PadRight(10, '#'); sw.WriteLine(header); } // 依次 sqlString = this._tableMap.GetSourceSelectSQL(this.MaxCount); IDataReader dataReader = brokerSource.ExecuteSQLReader(sqlString); while (dataReader.Read()) { string line = ""; for (int i = 0; i < dataReader.FieldCount; i++) { if (line != "") { line += this._tableMap.TargetTable.FieldSplitOperator; } line += dataReader[i].ToString(); } sw.WriteLine(line); count++; } sw.Flush(); // 判断是否第一行输出总记录数 if (this._tableMap.TargetTable.OneRowRecordsNum == true) { sw.BaseStream.Seek(0, SeekOrigin.Begin); string header = "#" + count.ToString(); sw.WriteLine(header.PadRight(10), ' '); sw.Flush(); } } finally { if (sw != null) { sw.Flush(); sw.Close(); } brokerSource.Close(); } // 写日志 LogManager.Current.WriteCommonLog(this.JobCode, "从数据表" + this._tableMap.SourceTable.TableName + "获得" + count.ToString() + "笔记录。", this.ThreadName); return(ResultCode.Success); }
/// <summary> /// 根据Data ID List取数据 /// </summary> /// <returns></returns> public DataTable GetSourceByIDList(TableMap tableMap, string fieldNames, List <string> dataIDList, string dataSplitOperator) { DataTable retTable = new DataTable(); DataAccessBroker brokerSource = DataAccessFactory.Instance(this.SourceDataAccessCfg); try { for (int x = 0; x < dataIDList.Count;) { // 每批50个 int count = 50; if (x + count > dataIDList.Count) { count = dataIDList.Count - x; } // 多条数据以or拼起来 List <string> tempDataIDList = new List <string>(); for (int y = 0; y < count; y++) { tempDataIDList.Add(dataIDList[x + y]); } x += count; // 得到where语句 string whereSql = BaseCommand.MakeDataIDWhereSql(fieldNames, tempDataIDList, dataSplitOperator); string sql = "select " + tableMap.SourceTable.FieldNames + " from " + tableMap.SourceTable.TableName + whereSql; DataSet dataSet = brokerSource.FillSQLDataSet(sql); if (dataSet == null || dataSet.Tables.Count == 0) { continue;// 继续下一批50 } DataTable dtTemp = dataSet.Tables[0]; // 第一次时初始化列 if (retTable.Columns.Count == 0) { for (int x1 = 0; x1 < dtTemp.Columns.Count; x1++) { retTable.Columns.Add(dtTemp.Columns[x1].ColumnName, dtTemp.Columns[x1].DataType); } } // 将临时表的数据复制到返回的表中 for (int i = 0; i < dtTemp.Rows.Count; i++) { DataRow row = retTable.NewRow(); DataRow dtRow = dtTemp.Rows[i]; for (int j = 0; j < retTable.Columns.Count; j++) { row[j] = dtRow[j]; } retTable.Rows.Add(row); } } } catch (Exception ex) { throw ex; } finally { brokerSource.Close(); } return(retTable); }