/// <summary>
/// 数据库表数据列表
/// </summary>
/// <param name="databaseLinkId">数据库连接主键</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataTable GetTableDataList(string databaseLinkId, string tableName)
{
try
{
DatabaseLinkEntity databaseLinkEntity = databaseLinkIBLL.GetEntity(databaseLinkId);
return(databaseTableService.GetTableDataList(databaseLinkEntity, tableName));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 创建数据库表
/// </summary>
/// <param name="databaseLinkEntity"></param>
/// <param name="tableName"></param>
/// <param name="tableRemark"></param>
/// <param name="colList"></param>
/// <returns></returns>
public string CreateTable(string databaseLinkId, string tableName, string tableRemark, List <DatabaseTableFieldModel> colList)
{
try
{
DatabaseLinkEntity databaseLinkEntity = databaseLinkIBLL.GetEntity(databaseLinkId);
return(databaseTableService.CreateTable(databaseLinkEntity, tableName, tableRemark, colList));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 开启事务
/// </summary>
/// <param name="entity">数据库连接信息</param>
/// <returns></returns>
public IRepository BeginTrans(string databaseLinkId)
{
try
{
DatabaseLinkEntity entity = GetEntity(databaseLinkId);
return(databaseLinkService.BeginTrans(entity));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 数据库表数据列表
/// </summary>
/// <param name="databaseLinkId">数据库连接主键</param>
/// <param name="field">表明</param>
/// <param name="switchWhere">条件</param>
/// <param name="logic">逻辑</param>
/// <param name="keyword">关键字</param>
/// <param name="pagination">分页参数</param>
/// <returns></returns>
public DataTable GetTableDataList(string databaseLinkId, string tableName, string field, string logic, string keyword, Pagination pagination)
{
try
{
DatabaseLinkEntity databaseLinkEntity = databaseLinkIBLL.GetEntity(databaseLinkId);
return(databaseTableService.GetTableDataList(databaseLinkEntity, tableName, field, logic, keyword, pagination));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 给定查询语句查询字段
/// </summary>
/// <param name="databaseLinkId">数据库连接主键</param>
/// <param name="strSql">表名</param>
/// <returns></returns>
public List <string> GetSqlColName(string databaseLinkId, string strSql)
{
try
{
DatabaseLinkEntity databaseLinkEntity = databaseLinkIBLL.GetEntity(databaseLinkId);
return(databaseTableService.GetSqlColName(databaseLinkEntity, strSql));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 根据指定数据库执行sql语句
/// </summary>
/// <param name="databaseLinkId">数据库主键</param>
/// <param name="sql">sql语句</param>
/// <param name="dbParameter">参数</param>
public void ExecuteBySql(string databaseLinkId, string sql, object dbParameter = null)
{
try
{
DatabaseLinkEntity entity = GetEntity(databaseLinkId);
databaseLinkService.ExecuteBySql(entity, sql, dbParameter);
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 根据数据库执行sql语句,查询数据->datatable（分页）
/// </summary>
/// <param name="databaseLinkId">数据库主键</param>
/// <param name="sql">sql语句</param>
/// <param name="dbParameter">参数</param>
/// <param name="pagination">分页参数</param>
/// <returns></returns>
public DataTable FindTable(string databaseLinkId, string sql, object dbParameter, Pagination pagination)
{
try
{
DatabaseLinkEntity entity = GetEntity(databaseLinkId);
return(databaseLinkService.FindTable(entity, sql, dbParameter, pagination));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 获取列表数据
/// </summary>
/// <returns></returns>
public DataTable GetSqlData(string databaseLinkId, string sql)
{
try
{
DatabaseLinkEntity entity = databaseLinkIBLL.GetEntity(databaseLinkId);
return(databaseLinkService.FindTable(entity, sql, null));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 保存自定义查询（新增、修改）
/// </summary>
/// <param name="keyValue">主键值</param>
/// <param name="departmentEntity">部门实体</param>
/// <returns></returns>
public bool SaveEntity(string keyValue, DatabaseLinkEntity databaseLinkEntity)
{
try
{
cache.Remove(cacheKey, CacheId.database);
cache.Remove(cacheKey + "dic", CacheId.database);
return(databaseLinkService.SaveEntity(keyValue, databaseLinkEntity));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 数据库表数据列表
/// </summary>
/// <param name="databaseLinkEntity">库连接信息</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataTable GetTableDataList(DatabaseLinkEntity databaseLinkEntity, string tableName)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT * FROM " + tableName);
return(this.BaseRepository(databaseLinkEntity.F_DbConnection, databaseLinkEntity.F_DbType).FindTable(strSql.ToString()));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 数据表字段列表
/// </summary>
/// <param name="databaseLinkEntity">数据库连接信息</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public IEnumerable <DatabaseTableFieldModel> GetTableFiledList(DatabaseLinkEntity databaseLinkEntity, string tableName)
{
try
{
if (databaseLinkEntity == null)
{
return(new List <DatabaseTableFieldModel>());
}
return(this.BaseRepository(databaseLinkEntity.F_DbConnection, databaseLinkEntity.F_DbType).GetDBTableFields <DatabaseTableFieldModel>(tableName));
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 删除自定义查询条件
/// </summary>
/// <param name="keyValue">主键</param>
public void VirtualDelete(string keyValue)
{
try
{
DatabaseLinkEntity entity = new DatabaseLinkEntity()
{
F_DatabaseLinkId = keyValue,
F_DeleteMark = 1
};
this.BaseRepository().Update(entity);
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 数据表列表
/// </summary>
/// <param name="databaseLinkId">数据库连接主键</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public List <DatabaseTableModel> GetTableList(string databaseLinkId, string tableName)
{
try
{
DatabaseLinkEntity databaseLinkEntity = null;
if (databaseLinkId == "systemdb")// 本地数据库
{
databaseLinkEntity = new DatabaseLinkEntity()
{
F_DatabaseLinkId = "systemdb"
};
}
else
{
databaseLinkEntity = databaseLinkIBLL.GetEntity(databaseLinkId);
}
List <DatabaseTableModel> list = (List <DatabaseTableModel>)databaseTableService.GetTableList(databaseLinkEntity);
if (!string.IsNullOrEmpty(tableName))
{
list = list.FindAll(t => t.name.Contains(tableName));
}
return(list);
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 数据库表数据列表
/// </summary>
/// <param name="databaseLinkEntity">库连接信息</param>
/// <param name="strSql">表名</param>
/// <returns></returns>
public List <string> GetSqlColName(DatabaseLinkEntity databaseLinkEntity, string strSql)
{
try
{
var dt = this.BaseRepository(databaseLinkEntity.F_DbConnection, databaseLinkEntity.F_DbType).FindTable(strSql);
List <string> res = new List <string>();
foreach (DataColumn item in dt.Columns)
{
res.Add(item.ColumnName);
}
return(res);
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 创建数据库表
/// </summary>
/// <param name="databaseLinkEntity"></param>
/// <param name="tableName"></param>
/// <param name="tableRemark"></param>
/// <param name="colList"></param>
/// <returns></returns>
public string CreateTable(DatabaseLinkEntity databaseLinkEntity, string tableName, string tableRemark, List <DatabaseTableFieldModel> colList)
{
try
{
if (databaseLinkEntity == null)
{
return("当前数据库不存在");
}
List <DatabaseTableModel> tableList = (List <DatabaseTableModel>)GetTableList(databaseLinkEntity);
if (tableList.Find(t => t.name == tableName) != null)
{
return("表名重复");
}
else
{
IRepository db;
if (databaseLinkEntity.F_DatabaseLinkId == "systemdb")
{
db = this.BaseRepository().BeginTrans();
databaseLinkEntity.F_DbType = DbFactory.GetIDatabaseType().ToString();
}
else
{
db = this.BaseRepository(databaseLinkEntity.F_DbConnection, databaseLinkEntity.F_DbType).BeginTrans();
}
try
{
string sql = "";
switch (databaseLinkEntity.F_DbType)
{
case "SqlServer":
sql = GetSqlServerCreateTableSql(tableName, tableRemark, colList);
db.ExecuteBySql(sql);
break;
case "MySql":
sql = GetMySqlCreateTableSql(tableName, tableRemark, colList);
db.ExecuteBySql(sql);
break;
case "Oracle":
GetOracleCreateTableSql(tableName, tableRemark, colList, db);
break;
}
db.Commit();
}
catch (Exception)
{
db.Rollback();
throw;
}
return("建表成功");
}
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 数据库表数据列表
/// </summary>
/// <param name="databaseLinkEntity">库连接信息</param>
/// <param name="tableName">表名</param>
/// <param name="field">字段</param>
/// <param name="logic">逻辑</param>
/// <param name="keyword">关键字</param>
/// <param name="pagination">分页参数</param>
/// <returns></returns>
public DataTable GetTableDataList(DatabaseLinkEntity databaseLinkEntity, string tableName, string field, string logic, string keyword, Pagination pagination)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM " + tableName + " WHERE 1=1");
if (!string.IsNullOrEmpty(keyword) && !string.IsNullOrEmpty(logic) && logic != "Null" && logic != "NotNull")
{
strSql.Append(" AND " + field + " ");
switch (logic)
{
case "Equal": //等于
strSql.Append(" = ");
break;
case "NotEqual": //不等于
strSql.Append(" <> ");
break;
case "Greater": //大于
strSql.Append(" > ");
break;
case "GreaterThan": //大于等于
strSql.Append(" >= ");
break;
case "Less": //小于
strSql.Append(" < ");
break;
case "LessThan": //小于等于
strSql.Append(" >= ");
break;
case "Like": //包含
strSql.Append(" like ");
keyword = "%" + keyword + "%";
break;
default:
break;
}
strSql.Append("@keyword ");
}
if (logic == "Null")
{
strSql.Append(" AND " + field + " is null ");
}
else if (logic == "NotNull")
{
strSql.Append(" AND " + field + " is not null ");
}
if (databaseLinkEntity.F_DatabaseLinkId == "systemdb")
{
return(this.BaseRepository().FindTable(strSql.ToString(), new { keyword = keyword }, pagination));
}
else
{
return(this.BaseRepository(databaseLinkEntity.F_DbConnection, databaseLinkEntity.F_DbType).FindTable(strSql.ToString(), new { keyword = keyword }, pagination));
}
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
