Пример #1
0
        /// <summary>
        /// 获取数据库表列表
        /// </summary>
        /// <param name="dbID">数据库ID</param>
        /// <param name="isUsedInImport">是否用于外导表</param>
        /// <returns>表名列表</returns>
        public List <string> GetTableList(int dbID, bool isUsedInImport)
        {
            List <string> list     = new List <string>();
            string        username = "";

            if (dbID < 0)
            {
                return(new List <string>());
            }
            else if (dbID == 0)
            {
                using (BDBHelper dbHelper = new BDBHelper())
                {
                    username = dbHelper.UserName;
                    list     = dbHelper.GetTablesList();
                }
            }
            else
            {
                BF_DATABASE.Entity entity = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(dbID);

                if (entity == null)
                {
                    throw new Exception("数据库" + dbID + "不存在");
                }
                string dbType = GetDbTypeName(entity.DB_TYPE);
                using (BDBHelper dbHelper = new BDBHelper(dbType, entity.IP, entity.PORT, entity.USER_NAME, entity.PASSWORD, entity.DB_NAME, entity.DB_NAME))
                {
                    username = dbHelper.UserName;
                    list     = dbHelper.GetTablesList();
                }
            }

            if (list == null || list.Count < 1)
            {
                return(new List <string>());
            }
            List <string> l = new List <string>();
            int           i = 0;

            foreach (string t in list)
            {
                string name = t.Replace(username.ToUpper() + ".", "");
                //外导时,排除框架所用的表
                if (isUsedInImport == true && name.StartsWith("BF_"))
                {
                    //continue;
                }
                l.Add(name);
                i++;
                //最多返回200张表
                if (i >= 200 && isUsedInImport == false)
                {
                    break;
                }
            }
            return(l);
        }
Пример #2
0
        /// <summary>
        /// 获取指定库的全量表集合
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public List <string> GetAllTableList(int dbID)
        {
            List <string> list     = new List <string>();
            string        username = "";

            if (dbID < 0)
            {
                return(new List <string>());
            }
            else if (dbID == 0)
            {
                using (BDBHelper dbHelper = new BDBHelper())
                {
                    username = dbHelper.UserName;
                    list     = dbHelper.GetTablesList();
                }
            }
            else
            {
                BF_DATABASE.Entity entity = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(dbID);

                if (entity == null)
                {
                    throw new Exception("数据库" + dbID + "不存在");
                }
                string dbType = GetDbTypeName(entity.DB_TYPE);
                using (BDBHelper dbHelper = new BDBHelper(dbType, entity.IP, entity.PORT, entity.USER_NAME, entity.PASSWORD, entity.DB_NAME, entity.DB_NAME))
                {
                    username = dbHelper.UserName;
                    list     = dbHelper.GetTablesList();
                }
            }

            if (list == null || list.Count < 1)
            {
                return(new List <string>());
            }
            else
            {
                return(list);
            }
        }
Пример #3
0
        /// <summary>
        /// 查询报表并返回结果集
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="generatedSQL">解析后的SQL语句</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static DataTable QueryTable(Entity entity, string generatedSQL, List <object> paramList, int pageSize, int pageIndex)
        {
            if (entity == null || string.IsNullOrWhiteSpace(generatedSQL))
            {
                return(new DataTable());
            }

            string sql = generatedSQL;

            if (entity.DB_ID <= 0)
            {
                using (BDBHelper dbHelper = new BDBHelper())
                {
                    if (paramList == null || paramList.Count < 1)
                    {
                        if (pageSize < 1)
                        {
                            return(dbHelper.ExecuteDataTable(sql));
                        }
                        return(dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex));
                    }
                    if (pageSize < 1)
                    {
                        return(dbHelper.ExecuteDataTableParams(sql, paramList));
                    }
                    return(dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList));
                }
            }
            else
            {
                BF_DATABASE.Entity db = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(entity.DB_ID);
                if (db == null)
                {
                    throw new Exception("数据库不存在");
                }

                string dbType = Enums.DBType.Oracle.ToString();
                try
                {
                    dbType = ((Enums.DBType)db.DB_TYPE).ToString();
                }
                catch
                {
                    throw new Exception("未知的数据库类型");
                }

                using (BDBHelper dbHelper = new BDBHelper(dbType, db.IP, db.PORT, db.USER_NAME, db.PASSWORD, db.DB_NAME, db.DB_NAME))
                {
                    if (paramList == null || paramList.Count < 1)
                    {
                        if (pageSize < 1)
                        {
                            return(dbHelper.ExecuteDataTable(sql));
                        }

                        return(dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex));
                    }

                    if (pageSize < 1)
                    {
                        return(dbHelper.ExecuteDataTableParams(sql, paramList));
                    }

                    return(dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList));
                }
            }
        }
Пример #4
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);
        }
Пример #5
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);
        }
Пример #6
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);
        }