internal bool BulkCopyUpdate()
{
int count = 0, pageSize = 5000;
MDataTable dt = null;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
if (action.DalVersion.StartsWith("08"))
{
pageSize = 1000;
}
count = mdt.Rows.Count / pageSize + 1;
DbBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
bool result = false;
MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary;
string columnName = keyColumn.ColumnName;
for (int i = 0; i < count; i++)
{
dt = mdt.Select(i + 1, pageSize, null);//分页读取
if (dt != null && dt.Rows.Count > 0)
{
#region 核心逻辑
string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DalType);
MDataTable dtData = action.Select(whereIn); //获取远程数据。
dtData.Load(dt); //重新加载赋值。
result = action.Delete(whereIn); //如果存在IsDeleted，会被转Update（导致后续无法Insert）
if (result)
{
dtData.DynamicData = action;
result = dtData.AcceptChanges(AcceptOp.InsertWithID);
}
if (!result)
{
break;
}
#endregion
}
}
if (_dalHelper == null)
{
action.BeginTransation();
}
else
{
action.dalHelper = sourceHelper;//还原。
}
}
return(true);
}
internal bool Auto()
{
bool result = true;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
DalBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
action.dalHelper.IsRecordDebugInfo = false;//屏蔽SQL日志记录 2000数据库大量的In条件会超时。
if ((jointPrimaryIndex != null && jointPrimaryIndex.Count == 1) || (jointPrimaryIndex == null && mdt.Columns.JointPrimary.Count == 1))
//jointPrimaryIndex == null && mdt.Columns.JointPrimary.Count == 1 && mdt.Rows.Count <= 10000
//&& (!action.DalVersion.StartsWith("08") || mdt.Rows.Count < 1001)) //只有一个主键-》组合成In远程查询返回数据-》
{
#region 新逻辑
MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary;
string columnName = keyColumn.ColumnName;
//计算分组处理
int pageSize = 5000;
if (action.DataBaseVersion.StartsWith("08"))
{
pageSize = 1000;
}
int count = mdt.Rows.Count / pageSize + 1;
for (int i = 0; i < count; i++)
{
MDataTable dt = mdt.Select(i + 1, pageSize, null);//分页读取
if (dt != null && dt.Rows.Count > 0)
{
string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DataBaseType);
action.SetSelectColumns(columnName);
MDataTable keyTable = action.Select(whereIn); //拿到数据，准备分拆上市
MDataTable[] dt2 = dt.Split(SqlCreate.GetWhereIn(keyColumn, keyTable.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), DataBaseType.None)); //这里不需要格式化查询条件。
result = dt2[0].Rows.Count == 0;
if (!result)
{
MDataTable updateTable = dt2[0];
updateTable.SetState(2, BreakOp.Null);
updateTable.DynamicData = action;
result = updateTable.AcceptChanges(AcceptOp.Update, _Conn, columnName);
if (!result)
{
sourceTable.DynamicData = updateTable.DynamicData;
}
}
if (result && dt2[1].Rows.Count > 0)
{
MDataTable insertTable = dt2[1];
insertTable.DynamicData = action;
bool keepid = !insertTable.Rows[0].PrimaryCell.IsNullOrEmpty;
result = insertTable.AcceptChanges((keepid ? AcceptOp.InsertWithID : AcceptOp.Insert), _Conn, columnName);
if (!result)
{
sourceTable.DynamicData = insertTable.DynamicData;
}
}
}
}
#endregion
#region 旧逻辑，已不用 分拆处理 本地比较分拆两个表格【更新和插入】-》分开独立处理。
/*
* string columnName = mdt.Columns.FirstPrimary.ColumnName;
* string whereIn = SqlCreate.GetWhereIn(mdt.Columns.FirstPrimary, mdt.GetColumnItems<string>(columnName, BreakOp.NullOrEmpty, true), action.DalType);
* action.SetSelectColumns(mdt.Columns.FirstPrimary.ColumnName);
* dt = action.Select(whereIn);
*
* MDataTable[] dt2 = mdt.Split(SqlCreate.GetWhereIn(mdt.Columns.FirstPrimary, dt.GetColumnItems<string>(columnName, BreakOp.NullOrEmpty, true), DalType.None));//这里不需要格式化查询条件。
* result = dt2[0].Rows.Count == 0;
* if (!result)
* {
* dt2[0].SetState(2, BreakOp.Null);
* dt2[0].DynamicData = action;
* MDataTableBatchAction m1 = new MDataTableBatchAction(dt2[0], _Conn);
* m1.SetJoinPrimaryKeys(new string[] { columnName });
* result = m1.Update();
* if (!result)
* {
* sourceTable.DynamicData = dt2[0].DynamicData;
* }
* }
* if (result && dt2[1].Rows.Count > 0)
* {
* dt2[1].DynamicData = action;
* MDataTableBatchAction m2 = new MDataTableBatchAction(dt2[1], _Conn);
* m2.SetJoinPrimaryKeys(new string[] { columnName });
* result = m2.Insert(!dt2[1].Rows[0].PrimaryCell.IsNullOrEmpty);
* if (!result)
* {
* sourceTable.DynamicData = dt2[1].DynamicData;
* }
* }
*/
#endregion
}
else
{
// action.BeginTransation();
foreach (MDataRow row in mdt.Rows)
{
#region 循环处理
action.ResetTable(row, false);
string where = SqlCreate.GetWhere(action.DataBaseType, GetJoinPrimaryCell(row));
bool isExists = action.Exists(where);
if (action.RecordsAffected == -2)
{
result = false;
}
else
{
if (!isExists)
{
action.AllowInsertID = !row.PrimaryCell.IsNullOrEmpty;
action.Data.SetState(1, BreakOp.Null);
result = action.Insert(InsertOp.None);
}
else
{
action.Data.SetState(2);
result = action.Update(where);
}
}
if (!result)
{
string msg = "Error On : MDataTable.AcceptChanges.Auto." + mdt.TableName + " : [" + where + "] : " + action.DebugInfo;
sourceTable.DynamicData = msg;
Log.Write(msg, LogType.DataBase);
break;
}
#endregion
}
}
action.dalHelper.IsRecordDebugInfo = true;//恢复SQL日志记录
if (_dalHelper == null)
{
action.EndTransation();
}
else
{
action.dalHelper = sourceHelper;//还原
}
}
return(result);
}
/// <summary>
/// 批量更新或插入。
/// </summary>
/// <param name="dt"></param>
/// <param name="excelRow"></param>
/// <returns></returns>
public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null)
{
if (excelRow == null)
{
MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique);
string[] names = null;
if (dtImportUnique != null && dtImportUnique.Rows.Count > 0)
{
names = new String[dtImportUnique.Rows.Count];
for (int i = 0; i < dtImportUnique.Rows.Count; i++)
{
names[i] = dtImportUnique.Rows[i].Get<string>(Config_Grid.Field);
}
}
return dt.AcceptChanges(AcceptOp.Auto, null, names);
}
bool result = true;
//获取相关配置
string[] tables = excelRow.Get<string>(Config_Excel.TableNames).Split(',');
MDataTable configTable = GetExcelInfo(excelRow.Get<string>(Config_Excel.ExcelID));
Dictionary<string, string> rowPrimaryValue = new Dictionary<string, string>();//存档每个表每行的主键值。
Dictionary<string, string> wherePrimaryValue = new Dictionary<string, string>();//存档where条件对应的主键值。
int acceptType = excelRow.Get<int>(Config_Excel.AcceptType);
using (MAction action = new MAction(tables[0]))
{
action.SetAopState(AopOp.CloseAll);
action.BeginTransation();
AppConfig.Debug.OpenDebugInfo = false;
IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend();
foreach (var table in tables)
{
GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存，提前调用，能降低内存。
action.ResetTable(table);
for (int i = 0; i < dt.Rows.Count; i++)
{
action.Data.Clear();
var row = dt.Rows[i];
foreach (var cell in row)//遍历所有数据行
{
if (cell.Struct.TableName != null && cell.Struct.TableName.ToLower() == table.ToLower())//过滤出属于本表的字段。
{
string[] items = cell.ColumnName.Split('.');
string columnName = items[items.Length - 1];
action.Set(columnName, cell.Value);
}
}
#region 检测是否需要插入外键。
MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1");
if (foreignTable != null)
{
foreach (var foreignRow in foreignTable.Rows)
{
string formatter = foreignRow.Get<string>("Formatter");
string fTableName = foreignRow.Get<string>("ForeignTable");
if (string.IsNullOrEmpty(formatter))
{
//获取主键外值键
string key = fTableName + i;
if (rowPrimaryValue.ContainsKey(key))
{
string value = rowPrimaryValue[key];
action.Set(foreignRow.Get<string>("Field"), value);
}
}
else // 从其它自定义列取值。
{
MDataCell cell = row[formatter];
cell = cell ?? row[fTableName + "." + formatter];
if (cell != null)
{
action.Set(foreignRow.Get<string>("Field"), cell.Value);
}
}
}
foreignTable = null;
}
#endregion
#region //获取唯一联合主键,检测是否重复
string where = string.Empty;
List<MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1");
if (rowList != null && rowList.Count > 0)
{
bool isUniqueOr = excelRow.Get<bool>(Config_Excel.WhereType);
List<MDataCell> cells = new List<MDataCell>();
string errText = string.Empty;
int errorCount = 0;
foreach (var item in rowList)
{
var cell = action.Data[item.Get<string>(Config_ExcelInfo.Field)];
if (cell != null)
{
if (cell.IsNullOrEmpty) // 唯一主键是必填写字段
{
errorCount++;
errText += "[第" + (i + 1) + "行数据]：" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空！\r\n";
}
else
{
cells.Add(cell);
}
}
}
if (errorCount > 0)
{
if (!isUniqueOr || errorCount == rowList.Count)
{
result = false;
dt.DynamicData = new Exception(errText);
goto err;
}
}
MDataCell[] item2s = cells.ToArray();
where = action.GetWhere(!isUniqueOr, item2s);
item2s = null;
rowList = null;
}
if (!string.IsNullOrEmpty(where))
{
MDataRow data = action.Data.Clone();
action.SetSelectColumns(action.Data.PrimaryCell.ColumnName);
if (action.Fill(where))//根据条件查出主键ID (数据被清空)
{
string key = table + where;
if (wherePrimaryValue.ContainsKey(key))
{
rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。
}
else
{
rowPrimaryValue.Add(table + i, action.Get<string>(action.Data.PrimaryCell.ColumnName));//记录上一个主键值。
}
action.Data.LoadFrom(data, RowOp.IgnoreNull, false);//还原数据。
if (action.Data.GetState() == 2 && acceptType != 1)//排除掉仅插入选项
{
ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row);
if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where)))
{
continue;//已经存在了，更新，准备下一条。
}
else
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
else
{
continue;//已经存在了，同时没有可更新字段
}
}
else if (action.RecordsAffected == -2)//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
#endregion
if (action.Data.GetState() == 0 || acceptType == 2)//仅更新则跳过插入
{
continue;//没有可映射插入的列。
}
//插入前，调用函数（插入特殊主键等值）
string errMsg;
ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg);
if (excelResult == ExcelResult.Ignore)
{
continue;
}
if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID))
{
result = false;
action.RollBack();
if (string.IsNullOrEmpty(errMsg))
{
errMsg = "[第" + (i + 1) + "行数据]：" + action.DebugInfo;
}
dt.DynamicData = new Exception(errMsg);
excelConfigExtend.OnInsertError(errMsg, dt);
goto err;
}
//插入后事件（可以触发其它事件）
excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1);
string primaryKey = action.Get<string>(action.Data.PrimaryCell.ColumnName);
rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。
if (!wherePrimaryValue.ContainsKey(table + where))
{
wherePrimaryValue.Add(table + where, primaryKey);
}
}
}
err:
action.EndTransation();
excelConfigExtend.Dispose();
}
return result;
}
