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