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