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