/// <summary> /// 分页读取数据 /// </summary> /// <param name="recordCount">页面个数</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示</param> /// <param name="tableName">从什么表</param> /// <param name="whereConditional">条件</param> /// <param name="selectField">选择哪些字段</param> /// <returns>数据表</returns> public virtual DataTable GetDTByPage(out int recordCount, int pageIndex = 1, int pageSize = 20, string sortExpression = null, string sortDire = null, string tableName = null, string whereConditional = null, string selectField = null) { if (tableName.IndexOf("SELECT", System.StringComparison.OrdinalIgnoreCase) >= 0 || DBProvider.CurrentDbType == RDIFramework.Utilities.CurrentDbType.MySql) { // 统计总条数 string commandText = string.Empty; if (string.IsNullOrEmpty(tableName)) { tableName = this.CurrentTableName; } commandText = tableName; if (tableName.IndexOf("SELECT", System.StringComparison.OrdinalIgnoreCase) >= 0) { commandText = "(" + tableName + ") AS T "; } string whereStatement = string.Empty; if (!string.IsNullOrEmpty(whereConditional)) { whereStatement = string.Format(" WHERE {0} ", whereConditional); } commandText = string.Format("SELECT COUNT(1) AS recordCount FROM {0} {1}", commandText, whereStatement); object returnObject = DBProvider.ExecuteScalar(commandText); recordCount = returnObject != null?int.Parse(returnObject.ToString()) : 0; if (DBProvider.CurrentDbType == RDIFramework.Utilities.CurrentDbType.MySql) { return(DbCommonLibary.GetDTByPage(DBProvider, tableName, recordCount, pageIndex, pageSize, whereConditional, sortExpression, sortDire)); } return(DbCommonLibary.GetDTByPage(DBProvider, recordCount, pageIndex, pageSize, tableName, sortExpression, sortDire)); } // 这个是调用存储过程的方法 return(DbCommonLibary.GetDTByPage(DBProvider, out recordCount, pageIndex, pageSize, sortExpression, sortDire, tableName, whereConditional, selectField)); }
public DataTable GetFileDTByPage(out int recordCount, int pageIndex = 1, int pageSize = 20, string whereConditional = "", string order = "") { this.SelectField = CiFileTable.FieldId + " ," + CiFileTable.FieldFolderId + " ," + CiFileTable.FieldFileName + " ," + CiFileTable.FieldFilePath + " ," + CiFileTable.FieldFileSize + " ," + CiFileTable.FieldReadCount + " ," + CiFileTable.FieldCategory + " ," + CiFileTable.FieldDescription + " ," + CiFileTable.FieldEnabled + " ," + CiFileTable.FieldSortCode + " ," + CiFileTable.FieldCreateUserId + " ," + CiFileTable.FieldCreateBy + " ," + CiFileTable.FieldCreateOn + " ," + CiFileTable.FieldModifiedUserId + " ," + CiFileTable.FieldModifiedBy + " ," + CiFileTable.FieldModifiedOn + " , (SELECT " + CiFolderTable.FieldFolderName + " FROM " + CiFolderTable.TableName + " WHERE " + CiFolderTable.FieldId + " = '" + CiFileTable.FieldFolderId + "') AS FolderFullName "; recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional); return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order)); }
/// <summary> /// 分页查询 /// </summary> /// <param name="searchValue">查询字段</param> /// <param name="departmentId">部门主键</param> /// <param name="roleId">角色主键</param> /// <param name="recordCount">记录数</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示</param> /// <param name="order">排序</param> /// <returns>数据表</returns> public DataTable GetDTByPage(string searchValue, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null) { string whereConditional = PiUserTable.TableName + "." + PiUserTable.FieldDeleteMark + " = 0 " + " AND " + PiUserTable.TableName + "." + PiUserTable.FieldEnabled + " = 1 " + " AND " + PiUserTable.TableName + "." + PiUserTable.FieldIsVisible + " = 1 "; if (!String.IsNullOrEmpty(departmentId)) { /* * 用非递归调用的建议方法 * sqlQuery += " AND " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN ( SELECT " + PiOrganizeTable.FieldId + " FROM " + BaseOrganizeEntity.TableName + " WHERE " + PiOrganizeTable.FieldId + " = " + departmentId + " OR " + PiOrganizeTable.FieldParentId + " = " + departmentId + ")"; */ PiOrganizeManager organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo); string[] organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, departmentId, PiOrganizeTable.FieldParentId); if (organizeIds != null && organizeIds.Length > 0) { whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")" + " OR " + PiUserTable.TableName + "." + PiUserTable.FieldWorkgroupId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + "))"; } } if (!string.IsNullOrEmpty(roleId)) { string tableNameUserRole = PiUserRoleTable.TableName; whereConditional += " AND ( " + PiUserTable.TableName + "." + PiUserTable.FieldId + " IN " + " (SELECT + " + PiUserRoleTable.FieldUserId + " FROM " + tableNameUserRole + " WHERE " + PiUserRoleTable.FieldRoleId + " = '" + roleId + "'" + " AND " + PiUserRoleTable.FieldEnabled + " = 1" + " AND " + PiUserRoleTable.FieldDeleteMark + " = 0)) "; } if (!string.IsNullOrEmpty(searchValue)) { searchValue = "'" + StringHelper.GetSearchString(searchValue) + "'"; whereConditional += " AND (" + PiUserTable.FieldRealName + " LIKE " + searchValue; whereConditional += " OR " + PiUserTable.FieldUserName + " LIKE " + searchValue + ")"; //whereConditional += " AND (" + searchValue + ")"; ; } recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional); this.CurrentTableName = "PIUSER LEFT OUTER JOIN PIUSERLOGON ON PIUSER.ID = PIUSERLOGON.ID "; switch (DBProvider.CurrentDbType) { case CurrentDbType.SqlServer: case CurrentDbType.Access: case CurrentDbType.Oracle: case CurrentDbType.MySql: this.SelectField = PiUserTable.TableName + ".* " + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldFirstVisit + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldPreviousVisit + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLastVisit + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldIPAddress + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldMACAddress + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLogOnCount + "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldUserOnLine; break; case CurrentDbType.DB2: break; } return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order)); }
/// <summary> /// 获取分页DataTable /// </summary> /// <param name="recordCount">记录总数</param> /// <param name="pageIndex">当前页数</param> /// <param name="pageSize">每页显示多少条</param> /// <param name="whereConditional">条件</param> /// <param name="order">排序字段</param> /// <returns>数据表</returns> public virtual DataTable GetDTByPage(out int recordCount, int pageIndex, int pageSize, string whereConditional, string order) { recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional); return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order)); }