Ejemplo n.º 1
0
        /// <summary>
        /// 导入数据文件
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="fileName">文件名</param>
        /// <param name="baseDate">基准日期</param>
        /// <param name="message">返回信息</param>
        /// <param name="errorList">错误信息(键:行号,值:错误信息)</param>
        /// <returns></returns>
        public static int ImportDataFile(Entity entity, string fileName, DateTime baseDate, out string message, out Dictionary <int, string> errorList)
        {
            int n           = 0;
            int loadCount   = 0;
            int updateCount = 0;

            message   = string.Empty;
            errorList = new Dictionary <int, string>();
            try
            {
                if (entity == null)
                {
                    message = "配置不存在";
                    return(n);
                }

                if (File.Exists(fileName) == false)
                {
                    message = "文件" + fileName + "不存在";
                    return(n);
                }

                string tableName = entity.TABLE_NAME;
                switch (entity.CREATE_TABLE_MODE)
                {
                case (short)Enums.CreateTableMode.年份后缀:
                    tableName += "_" + baseDate.ToString("yyyy");
                    break;

                case (short)Enums.CreateTableMode.年月后缀:
                    tableName += "_" + baseDate.ToString("yyyyMM");
                    break;

                case (short)Enums.CreateTableMode.年月日后缀:
                    tableName += "_" + baseDate.ToString("yyyyMMdd");
                    break;

                case (short)Enums.CreateTableMode.用户ID后缀:
                    tableName += "_" + SystemSession.UserID;
                    break;
                }

                string sqlQuery  = "SELECT * FROM {0} WHERE 1=0";
                string sqlUnique = "SELECT {0} FROM {1}";
                //字段配置
                Dictionary <string, FieldInfo> dicFields = GetFieldsInfo(entity.FIELDS);
                //自增长主键
                string autoIncrementField = string.Empty;
                //忽略的字段
                Dictionary <string, bool> dicIgnore = new Dictionary <string, bool>();
                //唯一约束字段
                string uniqueField = string.Empty;
                //唯一约束的值
                Dictionary <string, bool> dicUniqueValues = new Dictionary <string, bool>();

                foreach (var kvp in dicFields)
                {
                    if (kvp.Value.IS_AUTO_INCREMENT == 1)
                    {
                        autoIncrementField = kvp.Key;
                    }
                    if (kvp.Value.IS_IMPORT == 0)
                    {
                        dicIgnore.Add(kvp.Key, true);
                    }
                    if (kvp.Value.IS_UNIQUE == 1)
                    {
                        uniqueField = kvp.Key;
                    }
                }

                BDBHelper dbHelper = null;
                try
                {
                    if (entity.DB_ID == 0)
                    {
                        dbHelper = new BDBHelper();
                    }
                    else
                    {
                        BF_DATABASE.Entity db = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(entity.DB_ID);
                        if (db == null)
                        {
                            message = "数据库" + entity.DB_ID + "不存在";
                            return(n);
                        }

                        string dbType = BF_DATABASE.GetDbTypeName(db.DB_TYPE);
                        dbHelper = new BDBHelper(dbType, db.IP, db.PORT, db.USER_NAME, db.PASSWORD, db.DB_NAME, db.DB_NAME);
                    }
                    //表不存在
                    if (entity.CREATE_TABLE_MODE != (short)Enums.CreateTableMode.指定表 && dbHelper.TableIsExists(tableName) == false)
                    {
                        //创建表
                        if (dbHelper.CreateTable(tableName, entity.TABLE_NAME) == false)
                        {
                            message = "创建表" + tableName + "失败";
                            return(n);
                        }
                        //设置自增长
                        if (string.IsNullOrWhiteSpace(autoIncrementField) == false)
                        {
                            if (dbHelper.SetAutoIncrement(tableName, autoIncrementField) == false)
                            {
                                message = "设置表" + tableName + "的字段" + autoIncrementField + "为自增长失败";
                                return(n);
                            }
                        }
                    }

                    //查询需要导入的字段
                    DataTable dtQuery = dbHelper.ExecuteDataTable(string.Format(sqlQuery, tableName));

                    if (dtQuery == null)
                    {
                        message = "无法查询导入的目标表" + tableName;
                        return(n);
                    }
                    //是否包含导入用户ID
                    bool isContainsUid = dtQuery.Columns.Contains("IMPORT_UID");
                    //是否包含导入时间
                    bool isContainsTime = dtQuery.Columns.Contains("IMPORT_TIME");

                    //读取Excel中的数据
                    DataTable tdExcel = new DataTable();
                    foreach (DataColumn col in dtQuery.Columns)
                    {
                        if (dicIgnore.ContainsKey(col.ColumnName.ToUpper()))
                        {
                            continue;
                        }
                        tdExcel.Columns.Add(col.ColumnName, col.DataType);
                    }
                    //读取数据到DataTable
                    if (LoadFileIntoDataTable(fileName, ref tdExcel, out message) == false)
                    {
                        return(0);
                    }
                    if (tdExcel.Rows.Count < 1)
                    {
                        message = "没有数据";
                        return(0);
                    }

                    //要导入的数据
                    DataTable tdLoad   = new DataTable();
                    DataTable tdUpdate = new DataTable();
                    foreach (DataColumn colExcel in tdExcel.Columns)
                    {
                        tdLoad.Columns.Add(colExcel.ColumnName.ToUpper(), colExcel.DataType);
                        tdUpdate.Columns.Add(colExcel.ColumnName.ToUpper(), colExcel.DataType);
                    }
                    if (isContainsUid == true)
                    {
                        tdLoad.Columns.Add("IMPORT_UID", typeof(int));
                        tdUpdate.Columns.Add("IMPORT_UID", typeof(int));
                    }
                    if (isContainsTime == true)
                    {
                        tdLoad.Columns.Add("IMPORT_TIME", typeof(DateTime));
                        tdUpdate.Columns.Add("IMPORT_TIME", typeof(DateTime));
                    }
                    tdUpdate.Columns.Add("EXCEL_ROW_NUM", typeof(int));

                    //唯一约束
                    bool isCheckUnique = false;
                    if (string.IsNullOrWhiteSpace(uniqueField) == false)
                    {
                        sqlUnique = string.Format(sqlUnique, uniqueField, tableName);
                        DataTable tdUnique = dbHelper.ExecuteDataTable(sqlUnique);
                        if (tdUnique != null)
                        {
                            foreach (DataRow rowUnique in tdUnique.Rows)
                            {
                                string key = Convert.ToString(rowUnique[0]);
                                if (dicUniqueValues.ContainsKey(key) == false)
                                {
                                    dicUniqueValues.Add(key, true);
                                    isCheckUnique = true;
                                }
                            }
                        }
                    }

                    //将数据分别写入待导入及待更新
                    int excelRowNumber = 1;
                    //本批次数据重复性验证
                    bool isContainsUniqueField        = tdExcel.Columns.Contains(uniqueField);
                    Dictionary <string, bool> dicTemp = new Dictionary <string, bool>();
                    List <int> errorTemp  = new List <int>();
                    DateTime   importTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    foreach (DataRow dr in tdExcel.Rows)
                    {
                        excelRowNumber++;
                        string key = string.Empty;
                        if (string.IsNullOrWhiteSpace(uniqueField) == false && isContainsUniqueField == true)
                        {
                            key = Convert.ToString(dr[uniqueField]);
                            //不可为空
                            if (string.IsNullOrWhiteSpace(key) == true)
                            {
                                errorTemp.Add(excelRowNumber);
                            }

                            //本批次要导入的数据重复
                            if (dicTemp.ContainsKey(key))
                            {
                                errorTemp.Add(excelRowNumber);
                                continue;
                            }
                            dicTemp.Add(key, true);

                            //和数据库里面原有数据比较
                            if (isCheckUnique == true)
                            {
                                //违反唯一约束
                                if (dicUniqueValues.ContainsKey(key))
                                {
                                    DataRow updateRow = tdUpdate.NewRow();
                                    foreach (DataColumn updateCol in tdExcel.Columns)
                                    {
                                        updateRow[updateCol.ColumnName] = dr[updateCol];
                                    }
                                    if (isContainsUid == true)
                                    {
                                        updateRow["IMPORT_UID"] = SystemSession.UserID;
                                    }
                                    if (isContainsTime == true)
                                    {
                                        updateRow["IMPORT_TIME"] = importTime;
                                    }
                                    updateRow["EXCEL_ROW_NUM"] = excelRowNumber;
                                    tdUpdate.Rows.Add(updateRow);
                                    errorList.Add(excelRowNumber, "数据库中已经存在值:" + key);
                                    continue;
                                }
                            }
                        }

                        //加入待插入
                        DataRow insertRow = tdLoad.NewRow();
                        foreach (DataColumn insertCol in tdExcel.Columns)
                        {
                            insertRow[insertCol.ColumnName] = dr[insertCol];
                        }
                        if (isContainsUid == true)
                        {
                            insertRow["IMPORT_UID"] = SystemSession.UserID;
                        }
                        if (isContainsTime == true)
                        {
                            insertRow["IMPORT_TIME"] = importTime;
                        }
                        tdLoad.Rows.Add(insertRow);
                    }

                    //导入数据
                    if (tdLoad.Rows.Count > 0)
                    {
                        try
                        {
                            loadCount = dbHelper.LoadDataInDataTable(tableName, tdLoad);
                            message   = string.Format("共导入{0}条数据", loadCount);
                        }
                        catch (Exception el)
                        {
                            message = string.Format("尝试导入{0}条数据出错:{1}", tdLoad.Rows.Count, el.Message);
                        }
                    }

                    //更新数据
                    if (tdUpdate.Rows.Count > 0)
                    {
                        if (entity.IS_ALLOW_UPDATE == 1)
                        {
                            errorList = new Dictionary <int, string>();
                            string updateFields = string.Empty;
                            int    ui           = 0;
                            foreach (DataColumn col in tdUpdate.Columns)
                            {
                                if (col.ColumnName == uniqueField || col.ColumnName == "EXCEL_ROW_NUM")
                                {
                                    continue;
                                }
                                if (ui == 0)
                                {
                                    updateFields += col.ColumnName + "=?";
                                }
                                else
                                {
                                    updateFields += " ," + col.ColumnName + "=?";
                                }
                                ui++;
                            }

                            string sqlUpdate = string.Format("UPDATE {0} SET {1} WHERE {2}=?", tableName, updateFields, uniqueField);

                            foreach (DataRow drUpdate in tdUpdate.Rows)
                            {
                                List <object> values = new List <object>();
                                foreach (DataColumn col in tdUpdate.Columns)
                                {
                                    if (col.ColumnName == uniqueField || col.ColumnName == "EXCEL_ROW_NUM")
                                    {
                                        continue;
                                    }
                                    values.Add(drUpdate[col]);
                                }
                                values.Add(drUpdate[uniqueField]);

                                if (dbHelper.ExecuteNonQueryParams(sqlUpdate, values) > 0)
                                {
                                    updateCount++;
                                }
                                else
                                {
                                    errorList.Add(Convert.ToInt32(drUpdate["EXCEL_ROW_NUM"]), "更新数据失败");
                                }
                            }
                        }
                    }

                    foreach (int row in errorTemp)
                    {
                        errorList.Add(row, "表格中数据重复");
                    }

                    if (updateCount > 0)
                    {
                        message += ",更新" + updateCount + "条";
                    }
                    if (errorList.Count > 0)
                    {
                        message += ",失败" + errorList.Count + "条";
                    }
                }
                catch (Exception e)
                {
                    message += "导入数据出错:" + e.Message;
                    BLog.Write(BLog.LogLevel.WARN, "外导数据出错:" + e.ToString());
                    return(loadCount + updateCount);
                }
                finally
                {
                    if (dbHelper != null)
                    {
                        dbHelper.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                message += "导入数据出错:" + ex.Message;
                BLog.Write(BLog.LogLevel.WARN, "外导数据出错:" + ex.ToString());
                return(loadCount + updateCount);
            }

            return(loadCount + updateCount);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// 检测是否可上传,并将异常抛出
        /// </summary>
        /// <param name="id">配置ID</param>
        /// <param name="rowkey">主键值</param>
        /// <param name="yyyymmdd">如果是按日期为后缀的动态表,则需要传入该表日期</param>
        /// <param name="tableName">表名</param>
        public Entity CheckUpload(int id, string rowkey, string yyyymmdd, out string tableName)
        {
            if (id < 1)
            {
                throw new Exception("请通过参数id传入正确的配置ID");
            }
            if (string.IsNullOrWhiteSpace(rowkey))
            {
                throw new Exception("请通过参数rowkey传入附件所属记录的主键值");
            }
            Entity entity = GetEntityByKey <Entity>(id);

            if (entity == null)
            {
                throw new Exception("未找到配置,请通过参数id传入正确的配置ID");
            }
            tableName = entity.TABLE_NAME;
            int ymd = 0;

            switch (entity.CREATE_TABLE_MODE)
            {
            case (short)Enums.CreateTableMode.年份后缀:
                if (string.IsNullOrWhiteSpace(yyyymmdd) || yyyymmdd.Length < 4 || int.TryParse(yyyymmdd, out ymd) == false)
                {
                    throw new Exception("由于目标表以年份为后缀,请通过yyyymmdd传入相应日期");
                }
                tableName += "_" + yyyymmdd.Substring(0, 4);
                break;

            case (short)Enums.CreateTableMode.年月后缀:
                if (string.IsNullOrWhiteSpace(yyyymmdd) || yyyymmdd.Length < 6 || int.TryParse(yyyymmdd, out ymd) == false)
                {
                    throw new Exception("由于目标表以年月为后缀,请通过yyyymmdd传入相应日期");
                }
                tableName += "_" + yyyymmdd.Substring(0, 6);
                break;

            case (short)Enums.CreateTableMode.年月日后缀:
                if (string.IsNullOrWhiteSpace(yyyymmdd) || yyyymmdd.Length != 8 || int.TryParse(yyyymmdd, out ymd) == false)
                {
                    throw new Exception("由于目标表以日期为后缀,请通过yyyymmdd传入相应日期");
                }
                tableName += "_" + yyyymmdd;
                break;

            case (short)Enums.CreateTableMode.用户ID后缀:
                tableName += "_" + SystemSession.UserID;
                break;
            }

            BDBHelper dbHelper = null;

            try
            {
                if (entity.DB_ID == 0)
                {
                    dbHelper = new BDBHelper();
                }
                else
                {
                    BF_DATABASE.Entity db = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(entity.DB_ID);
                    if (db == null)
                    {
                        throw new Exception("数据库配置错误");
                    }
                    string dbType = BF_DATABASE.GetDbTypeName(db.DB_TYPE);
                    dbHelper = new BDBHelper(dbType, db.IP, db.PORT, db.USER_NAME, db.PASSWORD, db.DB_NAME, db.DB_NAME);
                }
                if (dbHelper.TableIsExists(tableName) == false)
                {
                    throw new Exception("目标表" + tableName + "不存在");
                }
                string  sql = string.Format("SELECT * FROM {0} WHERE {1}=?", tableName, entity.KEY_FIELD);
                DataRow dr  = dbHelper.ExecuteDataRowParams(sql, rowkey);
                if (dr == null)
                {
                    throw new Exception(string.Format("在目标表{0}中,没有找到主键{1}值为{2}的记录", tableName, entity.KEY_FIELD, rowkey));
                }
            }
            catch (Exception ex)
            {
                throw new Exception("未知错误:" + ex.Message);
            }
            finally
            {
                if (dbHelper != null)
                {
                    dbHelper.Dispose();
                }
            }

            return(entity);
        }
Ejemplo n.º 3
0
        public string CreateTable(BF_FORM.Entity entity, List <BF_FORM.FieldInfo> FieldList)
        {
            #region 计算表名
            short  tableMode = entity.CREATE_TABLE_MODE;
            string tableName = entity.TABLE_NAME;
            #region 数据库对像
            BDBHelper dbHelper = null;
            if (entity.DB_ID == 0)
            {
                dbHelper = new BDBHelper();
            }
            else
            {
                BF_DATABASE.Entity db = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(entity.DB_ID);
                if (db == null)
                {
                    BLog.Write(BLog.LogLevel.WARN, "查询数据库对像出现异常:数据库ID" + entity.DB_ID);
                    return("");
                }

                string dbType = BF_DATABASE.GetDbTypeName(db.DB_TYPE);
                dbHelper = new BDBHelper(dbType, db.IP, db.PORT, db.USER_NAME, db.PASSWORD, db.DB_NAME, db.DB_NAME);
            }
            #endregion
            //如查指定的表存在就返回
            if (entity.CREATE_TABLE_MODE == (short)Enums.CreateTableMode.指定表 && dbHelper.TableIsExists(tableName))
            {
                return(tableName);
            }

            switch (tableMode)
            {
            case (short)Enums.CreateTableMode.年份后缀:
                tableName += "_" + DateTime.Now.ToString("yyyy");
                break;

            case (short)Enums.CreateTableMode.年月后缀:
                tableName += "_" + DateTime.Now.ToString("yyyyMM");
                break;

            case (short)Enums.CreateTableMode.年月日后缀:
                tableName += "_" + DateTime.Now.ToString("yyyyMMdd");
                break;

            case (short)Enums.CreateTableMode.用户ID后缀:
                tableName += "_" + SystemSession.UserID;
                break;
            }
            #endregion

            #region 建表
            //自增长主键
            string autoIncrementField = string.Empty;
            //唯一约束字段
            string uniqueField = string.Empty;
            //唯一约束的值
            //  Dictionary<string, bool> dicUniqueValues = new Dictionary<string, bool>();

            foreach (var field in FieldList)
            {
                if (field.IS_AUTO_INCREMENT == 1)
                {
                    autoIncrementField = field.EN_NAME;
                }
                if (field.IS_UNIQUE == 1)
                {
                    uniqueField = field.EN_NAME;
                }
            }

            //表不存在
            if (entity.CREATE_TABLE_MODE != (short)Enums.CreateTableMode.指定表 && dbHelper.TableIsExists(tableName) == false)
            {
                //创建表
                if (dbHelper.CreateTable(tableName, entity.TABLE_NAME) == false)
                {
                    BLog.Write(BLog.LogLevel.WARN, "动态建表出现异常:" + tableName);
                    return("");
                }
                //设置自增长
                if (dbHelper.SetAutoIncrement(tableName, autoIncrementField) == false)
                {
                    BLog.Write(BLog.LogLevel.WARN, "设置表" + tableName + "的字段" + autoIncrementField + "为自增长失败");
                    return("");
                }
            }
            #endregion
            return(tableName);
        }