/// <summary>
/// 拼凑分页sql
/// </summary>
/// <param name="dbServer"></param>
/// <param name="sql"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="err"></param>
/// <returns></returns>
private string SqlForPage(DbServerOutput dbServer, string sql, int pageIndex, int pageSize, ref ErrorInfo err)
{
string rownNum = "ROWNUM ";//初始化编号
switch (dbServer.DbTypeName.ToUpper())
{
case "DB2":
rownNum = string.Format(" ROW_NUMBER() OVER( PARTITION BY 1 ) ");
break;
case "ORACLE":
rownNum = "ROWNUM ";
break;
case "SQLSERVER":
string nullSql = "SELECT * FROM ( " + sql + " ) T WHERE 1<>1";
DataTable dt = _dbServerApp.ExecuteGetTable(dbServer.Id, nullSql, ref err);
rownNum = dt.Columns[0].Caption;
rownNum = string.Format("ROW_NUMBER() OVER (ORDER BY {0} DESC)", rownNum);
break;
}
int startNum = (pageIndex - 1) * pageSize;
string nowsql = string.Format(@"
SELECT *
FROM (SELECT {1} N, T.*
FROM ({0}) T)
WHERE N > {2} AND N <= {3}", sql, rownNum, startNum, startNum + pageSize);
return(nowsql);
}
/// <summary>
/// 执行sql得到一个DataTable
/// </summary>
/// <param name="dbserver"></param>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable ExecuteGetTable(DbServerOutput dbserver, string sql)
{
DataTable newTable = new DataTable();//初始化查询结果表
var _session = GetSession(dbserver);
if (_session != null)
{
try
{
_session.Open();
var dr = _session.ExecuteReader(sql);
newTable.Load(dr);//加载数据到datatable
_session.Closed();
}
catch (Exception ex)
{
_session.Closed();
throw new Exception("sql执行失败：" + ex.Message);
}
finally
{
_session.Closed();
_session.Dispose();
}
}
return(newTable);
}
/// <summary>
/// 执行sql，根据dbserver
/// </summary>
/// <param name="dbserver"></param>
/// <param name="sql"></param>
/// <returns></returns>
public int Execute(DbServerOutput dbserver, string sql)
{
int obj = 0;//初始化
var _session = GetSession(dbserver);
if (_session != null)
{
try
{
_session.Open();
obj = _session.Execute(sql);
}
catch (Exception ex)
{
_session.Closed();
throw new Exception("sql执行失败：" + ex.Message);
}
finally
{
_session.Closed();
_session.Dispose();
}
}
return(obj);
}
/// <summary>
/// 获取数据库连接字符串
/// </summary>
/// <param name="dbserver"></param>
/// <returns></returns>
public string GetConnectStr(DbServerOutput dbserver)
{
string aesPwd = dbserver.Password;
try
{
var p = EncryptHelper.AesDecrpt(dbserver.Password);
aesPwd = p;
}
catch
{
}
switch (dbserver.DbTypeName.ToUpper())
{
case "DB2":
return(string.Format("Server={0}:{1};Database={2};UID={3};PWD={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, aesPwd));
case "ORACLE":
return(string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, aesPwd));
case "MYSQL":
//return string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, dbserver.Password);
break;
case "SQLSERVER":
//return string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, dbserver.Password);
break;
default: //默认oracle
return(string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", dbserver.Ip, dbserver.Port, dbserver.DataCase, dbserver.User, aesPwd));
}
return("");
}
/// <summary>
/// 传入sql及分页信息得到一个DataTable
/// </summary>
/// <param name="dbServer"></param>
/// <param name="sql"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public DataTable ExecuteGetTable(DbServerOutput dbServer, string sql, int pageIndex, int pageSize)
{
#region 设置分页
string rownNum = "ROWNUM ";//初始化编号
switch (dbServer.DbTypeName.ToUpper())
{
case "DB2":
rownNum = string.Format(" ROW_NUMBER() OVER( PARTITION BY 1 ) ");
break;
case "ORACLE":
rownNum = "ROWNUM ";
break;
case "SQLSERVER":
string nullSql = "SELECT * FROM ( " + sql + " ) T WHERE 1<>1";
DataTable dt = ExecuteGetTable(dbServer, nullSql);
rownNum = dt.Columns[0].Caption;
rownNum = string.Format("ROW_NUMBER() OVER (ORDER BY {0} DESC)", rownNum);
break;
}
int startNum = (pageIndex - 1) * pageSize;
string nowsql = string.Format(@"
SELECT *
FROM (SELECT {1} N, T.*
FROM ({0}) T)
WHERE N > {2} AND N <= {3}", sql, rownNum, startNum, startNum + pageSize);
#endregion
#region 执行sql
DataTable newTable = new DataTable();//初始化查询结果表
var _session = GetSession(dbServer);
if (_session != null)
{
try
{
_session.Open();
var dr = _session.ExecuteReader(nowsql);
newTable.Load(dr);//加载数据到datatable
_session.Closed();
}
catch (Exception ex)
{
_session.Closed();
throw new Exception("sql执行失败：" + ex.Message);
}
finally
{
_session.Closed();
_session.Dispose();
}
}
#endregion
return(newTable);
}
/// <summary>
/// 根据ID获取某个数据库
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public DbServerOutput GetDbServer(long id)
{
try
{
var resDbServer = new DbServerOutput();
var db = _dbServerRepository.Get(id);
if (db != null)
{
resDbServer = db.MapTo <DbServerOutput>();
resDbServer.DbTagName = db.DbTag == null ? "" : db.DbTag.Name;
resDbServer.DbTypeName = db.DbType == null ? "" : db.DbType.Name;
}
return(resDbServer);
}
catch (Exception ex)
{
throw new UserFriendlyException("操作错误，对象或已被删除！");
}
}
/// <summary>
/// 根据数据库信息获取链接
/// </summary>
/// <param name="dbserver"></param>
/// <returns></returns>
public IDbSession GetSession(DbServerOutput dbserver)
{
IDbSession _session = null;
try
{
if (dbserver != null)
{
var conStr = dbserver == null ? null : GetConnectStr(dbserver);
DatabaseType dbType;
switch (dbserver.DbTypeName.ToUpper())
{
case "DB2":
dbType = DatabaseType.Db2;
break;
case "ORACLE":
dbType = DatabaseType.Oracle;
break;
case "MYSQL":
dbType = DatabaseType.MySql;
break;
case "SQLSERVER":
dbType = DatabaseType.SqlServer;
break;
default:
dbType = DatabaseType.Oracle;
break;
}
_session = new DefaultSession(conStr, dbType);
}
return(_session);
}
catch (Exception ex)
{
throw new Exception("创建数据库连接失败：" + ex.Message);
}
}
/// <summary>
/// 获得DataReader
/// </summary>
/// <param name="dbserver"></param>
/// <param name="sql"></param>
/// <returns></returns>
public IDataReader ExecuteDataReader(DbServerOutput dbserver, string sql)
{
IDataReader dr = null;
var _session = GetSession(dbserver);
if (_session != null)
{
try
{
_session.Open();
dr = _session.ExecuteReader(sql);
return(dr);
}
catch (Exception ex)
{
_session.Closed();
_session.Dispose();
throw new Exception("sql执行失败：" + ex.Message);
}
}
return(dr);
}
/// <summary>
/// 根据传入code执行报表，返回datatable
/// </summary>
/// <param name="code"></param>
/// <param name="queryParams"></param>
/// <param name="err"></param>
/// <returns></returns>
public DataTable GetDataTableFromCode(string code, string queryParams, ref ErrorInfo err)
{
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(code))
{
var ent = _reportRepository.FirstOrDefault(p => p.Code == code);
if (ent != null)
{
string dbType = "";//数据库种类
#region 获取数据库
DbServerOutput dbServer = new DbServerOutput();
//中心库
if (ent.DbServerId == null)
{
var connection = ConnectionMsg.GetCurConnection();//获取当前承载库连接信息
dbType = connection.DbType.ToString();
dbServer = null;
}
else
{
dbServer = _dbServerApp.GetDbServer(ent.DbServerId.Value);
dbType = dbServer.DbTypeName;
if (dbServer == null)
{
err.IsError = true;
err.Message = "未找到当前code对应的数据库编号[" + ent.DbServerId.Value + "]！";
return(dt);
}
}
#endregion
//替换全局变量
string sql = ReplaceGlobalVar(ent.Sql);
//替换变量（内置或自定义、外置）
sql = ReplaceDefaultValue(sql, queryParams);
//当IsPlaceholder==true时,拼凑查询筛选条件
if (ent.IsPlaceholder == null || !ent.IsPlaceholder.Value)
{
sql = AnalysisParam(dbType, sql, queryParams);
}
if (dbServer == null)
{
dt = DbHelper.ExecuteGetTable(sql);
}
else
{
dt = _dbServerApp.ExecuteGetTable(dbServer.Id, sql);//执行sql
}
}
else
{
err.IsError = true;
err.Message = "未找到编号为[" + code + "]的报表！";
}
}
else
{
err.IsError = true;
err.Message = "传入的code代码不能为空！";
}
return(dt);
}
/// <summary>
/// 根据code代码及传入条件拼凑和执行sql
/// </summary>
/// <param name="code"></param>
/// <param name="rows"></param>
/// <param name="page"></param>
/// <param name="queryParams"></param>
/// <param name="sidx"></param>
/// <param name="sord"></param>
/// <param name="err"></param>
/// <returns></returns>
public string ExcuteReportSql(string code, int rows, int page, string queryParams, string sidx, string sord, ref ErrorInfo err)
{
if (!string.IsNullOrEmpty(code))
{
var ent = _reportRepository.FirstOrDefault(p => p.Code == code);
if (ent != null)
{
string dbType = ""; //数据库种类
int records = 0; //总数量
DataTable endTable = new DataTable(); //查询结果
#region 获取数据库
DbServerOutput dbServer = new DbServerOutput();
//中心库
if (ent.DbServerId == null)
{
var connection = ConnectionMsg.GetCurConnection();//获取当前承载库连接信息
dbType = connection.DbType.ToString();
dbServer = null;
}
else
{
dbServer = _dbServerApp.GetDbServer(ent.DbServerId.Value);
dbType = dbServer.DbTypeName;
if (dbServer == null)
{
err.IsError = true;
err.Message = "未找到当前code对应的数据库编号[" + ent.DbServerId.Value + "]！";
return("");
}
}
#endregion
//替换全局变量
string sql = ReplaceGlobalVar(ent.Sql);
//替换变量（内置或自定义、外置）
sql = ReplaceDefaultValue(sql, queryParams);
//当IsPlaceholder==true时,拼凑查询筛选条件
if (ent.IsPlaceholder == null || !ent.IsPlaceholder.Value)
{
sql = AnalysisParam(dbType, sql, queryParams);
}
sql = SqlForOrder(sql, sidx, sord); //生成排序sql
string sqlPage = SqlForPage(dbType, sql, page, rows, ref err); //拼凑分页sql
//获取总数
string sqlC = string.Format(@"select count(1) from ({0})", sql);
if (dbServer != null)
{
//记录总数
object num = _dbServerApp.ExecuteScalar(ent.DbServerId.Value, sqlC, ref err);
records = Convert.ToInt32(num);
endTable = _dbServerApp.ExecuteGetTable(ent.DbServerId.Value, sqlPage);
}
else//根据承载库查询
{
try
{
object num = DbHelper.ExecuteScalar(sqlC);
records = Convert.ToInt32(num);
endTable = DbHelper.ExecuteGetTable(sqlPage);
}
catch (Exception ex)
{
err.IsError = true;
err.Message = "查询数据错误：" + ex.Message;
return("");
}
}
//拼凑json串
string result = "{\"records\":" + records + ",\"page\":" + page + ",\"total\":" + Math.Ceiling(decimal.Divide(records, rows)) + ",\"rows\":" + JSON.DecodeToStr(endTable) + "}";
return(result);
}
else
{
err.IsError = true;
err.Message = "未找到编号为[" + code + "]的报表！";
}
}
else
{
err.IsError = true;
err.Message = "传入的代码不能为空！";
}
return("");
}