public DataTable GetDataTable(int limit, int page, ref int count, string name, string orderByField = "CR.ID", string orderByType = "DESC") { string strWhere = "1=1"; List <object> param = new List <object>(); #region 添加参数 if (string.IsNullOrWhiteSpace(name) == false) { strWhere += " AND NAME LIKE '%" + name.Replace('\'', ' ') + "%'"; } #endregion string strSql = "SELECT CR.ID,CR.NAME,DB.NAME DBNAME,CHART_TYPE,(CASE IS_SHOW_EXPORT WHEN 1 THEN '是' ELSE '否' END )SHOWEXPORT,(CASE IS_SHOW_DEBUG WHEN 1 THEN '是' ELSE '否' END )SHOWDEBUG,SQL_CODE,(CASE IS_ENABLE WHEN 1 THEN '是' ELSE '否' END )IS_ENABLE,CR.CREATE_TIME,CR.update_time FROM BF_CHART_REPORT CR LEFT JOIN BF_DATABASE DB on CR.DB_ID=DB.ID WHERE " + strWhere; //添加排序 if (string.IsNullOrWhiteSpace(orderByField) == false) { strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : ""); } using (BDBHelper dbHelper = new BDBHelper()) { if (limit == 0 && page == 0) { return(dbHelper.ExecuteDataTableParams(strSql));//不分页查询所有 } //算总记录 if (count == 0) { string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql); count = dbHelper.ExecuteScalarIntParams(sqlCount, param); } return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, param)); } }
public DataTable GetLogDataTable(int limit, int page, int stakId, int nodeId, ref int count, string strMessage = "", string strSql = "", string orderByField = "STFNL.ID", string orderByType = "DESC") { string sql = @"SELECT STFNL.ID,SFL.NAME FLOWNAME,STFNL.LOG_TIME,STFNL.LOG_LEVEL,STFNL.MESSAGE,STFNL.SQL FROM BF_ST_TASK_FLOW_NODE_LOG STFNL LEFT JOIN BF_ST_NODE SNO ON STFNL.NODE_ID = SNO.ID LEFT JOIN BF_ST_FLOW SFL ON STFNL.FLOW_ID = SFL.ID WHERE STFNL.TASK_ID =? AND STFNL.NODE_ID=? "; if (string.IsNullOrWhiteSpace(strMessage) == false) { sql += " and stfnl.message LIKE '%" + strMessage.Replace('\'', ' ') + "%'"; } if (string.IsNullOrWhiteSpace(strSql) == false) { sql += " and stfnl.SQL LIKE '%" + strSql.Replace('\'', ' ') + "%'"; } //添加排序 if (string.IsNullOrWhiteSpace(orderByField) == false) { sql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : ""); } using (BDBHelper dbHelper = new BDBHelper()) { if (limit == 0) { return(dbHelper.ExecuteDataTableParams(sql, stakId, nodeId));//不分页查询所有 } //算总记录 if (count == 0) { string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", sql); count = dbHelper.ExecuteScalarIntParams(sqlCount, stakId, nodeId); } return(dbHelper.ExecuteDataTablePageParams(sql, limit, page, stakId, nodeId)); } }
public DataTable GetDataTable(int limit, int page, ref int count, string name, string typeId, string dbId, int self = 0, string orderByField = "sn.ID", string orderByType = "ASC") { string strWhere = "1=1"; List <object> param = new List <object>(); #region 添加参数 if (string.IsNullOrWhiteSpace(name) == false) { strWhere += " AND SN.NAME LIKE '%" + name.Replace('\'', ' ') + "%'"; } if (string.IsNullOrWhiteSpace(typeId) == false) { int type = Convert.ToInt32(typeId); List <int> types = BF_ST_TYPE.Instance.GetAllChildren(type); if (types.Count > 0) { strWhere += string.Format(" AND SN.TYPE_ID IN ({0})", string.Join(",", types)); } //param.Add(typeId); } if (string.IsNullOrWhiteSpace(dbId) == false) { strWhere += " AND SN.DB_ID = ?"; param.Add(dbId); } if (self > 0) { strWhere += " AND SN.CREATE_UID = ?"; param.Add(SystemSession.UserID); } #endregion string strSql = "select sn.id, sn.NAME, stp.name typeName,sn.LAST_TASK_ID,SFL.name TaskName,db.name dbName,sn.RUN_STATUS,sn.LAST_TASK_IS,sn.LAST_TASK_ST,sn.LAST_TASK_FT,sn.CREATE_TIME "; strSql += ",(select FULL_NAME from BF_USER WHERE ID=SN.CREATE_UID )CREATE_NAME,sn.UPDATE_TIME,(select FULL_NAME from BF_USER WHERE ID=SN.UPDATE_UID )UPDATE_NAME "; strSql += " from BF_ST_NODE sn left join BF_ST_flow sfl on sn.LAST_TASK_ID = SFL.ID left join BF_DATABASE db on SN.DB_ID = DB.ID left join BF_ST_TYPE stp on sn.type_id = stp.id where " + strWhere; //添加排序 if (string.IsNullOrWhiteSpace(orderByField) == false) { strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : ""); } using (BDBHelper dbHelper = new BDBHelper()) { if (limit == 0 && page == 0) { return(dbHelper.ExecuteDataTableParams(strSql, param));//不分页查询所有 } //算总记录 if (count == 0) { string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql); count = dbHelper.ExecuteScalarIntParams(sqlCount, param); } return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, param)); } }
public DataTable GetDataTable(int limit, int page, string strWhere, List <object> value) { string strSql = "select sfl.name flowName,sno.name nodeName,stfnl.log_time,stfnl.log_level,stfnl.message,stfnl.sql from BF_ST_TASK_FLOW_NODE_LOG stfnl left join BF_ST_NODE sno on STFNL.NODE_ID=SNO.ID left join BF_ST_flow sfl on STFNL.FLOW_ID=SFL.ID " + strWhere + " order by id"; using (BDBHelper dbHelper = new BDBHelper()) { if (limit == 0 && page == 0) { return(dbHelper.ExecuteDataTable(strSql));//不分页查询所有 } return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, value)); } }
public DataTable GetDataTable(int limit, int page, string strWhere, List <object> value) { string strSql = "select f.id,f.name,(case f.db_id when 0 then '本地默认数据库' else to_char(DB.NAME) end ) DBNAME,TABLE_NAME,CREATE_TABLE_MODE,(case IS_ALLOW_DELETE when 1 then '是' else '否' end )IS_ALLOW_DELETE,(case IS_ENABLE when 1 then '是' else '否' end )IS_ENABLE,f.UPDATE_TIME,f.REMARK from BF_FORM f left join BF_DATABASE db on f.db_id=db.id where " + strWhere + " order by f.id desc"; using (BDBHelper dbHelper = new BDBHelper()) { if (limit == 0 && page == 0) { return(dbHelper.ExecuteDataTableParams(strSql, value));//不分页查询所有 } return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, value)); } }
public DataTable GetDataTable(int limit, int page, ref int count, int pid, string name, string orderByField, string orderByType) { string strWhere = "1=1"; List <object> param = new List <object>(); if (pid > 0) { strWhere += " AND st.PID=?"; param.Add(pid); } if (string.IsNullOrWhiteSpace(name) == false) { strWhere += " AND st.NAME LIKE '%" + name.Replace('\'', ' ') + "%'"; } string strSql = "select st.id, st.NAME,(select stp.name from BF_ST_TYPE stp where stp.id=st.PID)PNAME,st.REMARK,(select full_name from BF_USER where st.CREATE_UID=ID)createName,st.CREATE_TIME,(select full_name from BF_USER where st.UPDATE_UID=ID)updateName,st.UPDATE_TIME from BF_ST_TYPE st where " + strWhere; //添加排序 if (string.IsNullOrWhiteSpace(orderByField) == false) { strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : ""); } using (BDBHelper dbHelper = new BDBHelper()) { if (limit == 0 && page == 0) { return(dbHelper.ExecuteDataTableParams(strSql, param));//不分页查询所有 } //算总记录 if (count == 0) { string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql); count = dbHelper.ExecuteScalarIntParams(sqlCount, param); } return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, param)); } }
/// <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)); } } }
public DataTable GetDataTable(int limit, int page, ref int count, string daterange = "", string flowName = "", string nodeName = "", string param = "", string statusId = "", string successId = "", string orderByField = "st.ID", string orderByType = "ASC") { string strWhere = "1=1"; List <object> paramList = new List <object>(); #region 添加参数 DateTime endDate = DateTime.Today; DateTime beginDate = endDate.AddDays(-endDate.Day); beginDate = beginDate.AddDays(1 - beginDate.Day); if (string.IsNullOrWhiteSpace(daterange) == false) { Functions.GetIntervalDate(daterange, ref beginDate, ref endDate); } strWhere += " AND ST.REFERENCE_DATE>=? AND ST.REFERENCE_DATE<?"; paramList.Add(beginDate.Date); paramList.Add(endDate.AddDays(1).Date); if (string.IsNullOrWhiteSpace(flowName) == false) { strWhere += " and SF.NAME like '%" + flowName.Replace('\'', ' ') + "%'"; } if (string.IsNullOrWhiteSpace(nodeName) == false) { strWhere += " AND SN.NAME like '%" + nodeName.Replace('\'', ' ') + "%'"; } if (string.IsNullOrWhiteSpace(param) == false) { strWhere += " AND ST.PARAMETER like '%" + param.Replace('\'', ' ') + "%'"; } if (string.IsNullOrWhiteSpace(statusId) == false) { strWhere += " AND st.RUN_STATUS =?"; paramList.Add(statusId); } if (string.IsNullOrWhiteSpace(successId) == false) { strWhere += " AND st.IS_SUCCESS =?"; paramList.Add(successId); } #endregion string strSql = "select st.id,sf.name FLOWNAME,SN.NAME NODENAME,st.IS_MANUAL,st.REFERENCE_DATE,st.RETRY_TIMES,st.RUN_STATUS,st.IS_SUCCESS ,st.START_TIME,st.FINISH_TIME,st.CREATE_TIME,st.REMARK, st.BEGIN_DATE, st.END_DATE, st.PARAMETER "; strSql += "from BF_ST_TASK st left join BF_ST_FLOW sf on ST.FLOW_ID = SF.ID left join BF_ST_NODE sn on ST.NODE_ID = SN.ID where " + strWhere; //排序 if (string.IsNullOrWhiteSpace(orderByField) == false) { strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : ""); } using (BDBHelper dbHelper = new BDBHelper()) { if (limit == 0 && page == 0) { return(dbHelper.ExecuteDataTableParams(strSql, paramList));//不分页查询所有 } //算总记录 if (count == 0) { string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql); count = dbHelper.ExecuteScalarIntParams(sqlCount, paramList); } return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, paramList)); } }
/// <summary> /// 在指定数据库执行查询语句 /// </summary> /// <param name="dbID">数据库ID</param> /// <param name="sql">SQL语句</param> /// <param name="paramList">参数列表</param> /// <param name="rowsCount">记录数(如果传入值等于0,则会重新计算此值,反之不计算)</param> /// <param name="pageSize">分页大小(默认为10,0表示不分页)</param> /// <param name="pageIndex">页号(从1开始)</param> /// <returns>DataTable</returns> public DataTable ExecuteSelectSQL(int dbID, string sql, List <object> paramList, ref int rowsCount, int pageSize = 10, int pageIndex = 1) { if (dbID < 0) { throw new Exception("错误的数据库ID"); } if (string.IsNullOrWhiteSpace(sql)) { throw new Exception("SQL语句不可为空"); } string s = sql.Trim(); if (s.ToUpper().StartsWith("SELECT ") == false) { throw new Exception("只能执行SLECT语句!"); } DataTable dt = null; using (BDBHelper dbHelper = GetBDBHelper(dbID)) { try { if (pageSize <= 0) { if (paramList == null || paramList.Count < 1) { dt = dbHelper.ExecuteDataTable(sql); } else { dt = dbHelper.ExecuteDataTableParams(sql, paramList); } if (dt != null) { rowsCount = dt.Rows.Count; } } else { if (paramList == null || paramList.Count < 1) { dt = dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex); } else { dt = dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList); } if (dt != null && dt.Rows.Count < pageSize && pageIndex <= 1) { rowsCount = dt.Rows.Count; } else if (rowsCount == 0) { if (paramList == null || paramList.Count < 1) { rowsCount = dbHelper.ExecuteScalarInt("SELECT COUNT(*) C FROM (" + sql + ")"); } else { rowsCount = dbHelper.ExecuteScalarIntParams("SELECT COUNT(*) C FROM (" + sql + ")", paramList); } } } } catch (Exception ex) { throw new Exception("在数据库" + dbID + "执行SQL查询出错:" + ex.Message); } } if (pageIndex <= 1 && (dt == null || dt.Rows.Count == 0)) { rowsCount = 0; } return(dt); }