예제 #1
0
        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);
        }
예제 #3
0
        /// <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;
        }