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