/// <summary> /// 分页查询 /// </summary> /// <param name="searchValue">查询字段</param> /// <param name="companyId">公司主键</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 GetDataTableByPage(string searchValue, string companyId, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null) { string whereClause = BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldDeletionStateCode + " = 0 " + " AND " + BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldEnabled + " = 1 "; if (!String.IsNullOrEmpty(companyId)) { whereClause += " AND (" + BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldCompanyId + " = " + companyId + ")"; } if (!String.IsNullOrEmpty(departmentId)) { whereClause += " AND (" + BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldDepartmentId + " = " + departmentId + ")"; } if (!string.IsNullOrEmpty(searchValue)) { searchValue = "'" + StringUtil.GetSearchString(searchValue) + "'"; whereClause += " AND (" + BaseStaffEntity.FieldRealName + " LIKE " + searchValue; whereClause += " OR " + BaseStaffEntity.FieldUserName + " LIKE " + searchValue; whereClause += " OR " + BaseStaffEntity.FieldQuickQuery + " LIKE " + searchValue + ")"; // whereClause += " OR " + BaseStaffEntity.FieldSimpleSpelling + " LIKE " + searchValue + ")"; } recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereClause); this.CurrentTableName = "BaseStaff"; return(DbLogic.GetDataTableByPage(DbHelper, this.CurrentTableName, this.SelectFields, pageIndex, pageSize, whereClause, order)); }
public DataTable GetDataTableByPage(BaseUserInfo userInfo, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy) { DataTable result = null; recordCount = 0; string connectionString = string.Empty; connectionString = ConfigurationHelper.AppSettings("OpenMasDbConnection", BaseSystemInfo.EncryptDbConnection); if (!string.IsNullOrEmpty(connectionString)) { if (SecretUtil.IsSqlSafe(conditions)) { using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SqlServer, connectionString)) { recordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbParameters); result = DbLogic.GetDataTableByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbParameters, orderBy); } } else { if (System.Web.HttpContext.Current != null) { // 记录注入日志 FileUtil.WriteMessage("userInfo:" + userInfo.Serialize() + " " + conditions, System.Web.HttpContext.Current.Server.MapPath("~/Log/") + "SqlSafe" + DateTime.Now.ToString(BaseSystemInfo.DateFormat) + ".txt"); } } } return(result); }
/// <summary> /// 获取分页数据(防注入功能的) /// </summary> /// <param name="recordCount">记录条数</param> /// <param name="tableName">数据来源表名</param> /// <param name="selectField">选择字段</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示多少条</param> /// <param name="conditions">查询条件</param> /// <param name="dbParameters">查询参数</param> /// <param name="orderBy">排序字段</param> /// <returns>数据表</returns> public DataTable GetDataTableByPage(BaseUserInfo userInfo, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, List <KeyValuePair <string, object> > dbParameters, string orderBy) { DataTable result = null; // 判断是否已经登录的用户? var userManager = new BaseUserManager(userInfo); recordCount = 0; // 判断是否已经登录的用户? if (userManager.UserIsLogOn(userInfo)) { if (SecretUtil.IsSqlSafe(conditions)) { recordCount = DbLogic.GetCount(DbHelper, tableName, conditions, DbHelper.MakeParameters(dbParameters)); result = DbLogic.GetDataTableByPage(DbHelper, tableName, selectField, pageIndex, pageSize, conditions, DbHelper.MakeParameters(dbParameters), orderBy); } else { if (System.Web.HttpContext.Current != null) { // 记录注入日志 DotNet.Utilities.FileUtil.WriteMessage("userInfo:" + userInfo.Serialize() + " " + conditions, System.Web.HttpContext.Current.Server.MapPath("~/Log/") + "SqlSafe" + DateTime.Now.ToString(BaseSystemInfo.DateFormat) + ".txt"); } } } return(result); }
/// <summary> /// 获取分页数据(防注入功能的) /// 宋彪 2014-06-25 构造List<KeyValuePair<string, object>>比IDbDataParameter[]方便一些 /// dbHelper.MakeParameters(dbParameters)--》IDbDataParameter[] /// </summary> /// <param name="recordCount">记录条数</param> /// <param name="dbHelper">dbHelper</param> /// <param name="tableName">数据来源表名</param> /// <param name="selectField">选择字段</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示多少条</param> /// <param name="conditions">查询条件</param> /// <param name="dbParameters">查询参数</param> /// <param name="orderBy">排序字段</param> /// <returns>数据表</returns> public static IDataReader GetDataReaderByPage(IDbHelper dbHelper, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy) { IDataReader result = null; recordCount = 0; if (null != dbHelper) { recordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbParameters); result = DbLogic.GetDataReaderByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbParameters, orderBy); } return(result); }
/// <summary> /// 获取分页数据(防注入功能的) /// 宋彪 2014-06-25 构造List<KeyValuePair<string, object>>比IDbDataParameter[]方便一些 /// 宋彪 2015-11-13 增加输出最大记录数量,增加是否输出分页数的方法 /// dbHelper.MakeParameters(dbParameters)--》IDbDataParameter[] /// </summary> /// <param name="recordCount">记录条数</param> /// <param name="dbHelper">dbHelper</param> /// <param name="tableName">数据来源表名</param> /// <param name="selectField">选择字段</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示多少条</param> /// <param name="conditions">查询条件</param> /// <param name="dbParameters">查询参数</param> /// <param name="orderBy">排序字段</param> /// <param name="maxOutPut">最大输出数量</param> /// <param name="showRecordCount">是否显示分页数量</param> /// <returns>数据表</returns> public static DataTable GetDataTableByPage(IDbHelper dbHelper, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy, int?maxOutPut = null, bool?showRecordCount = true) { DataTable result = null; recordCount = 0; if (null != dbHelper) { if (showRecordCount == true) { recordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbParameters); recordCount = recordCount > maxOutPut ? (int)maxOutPut : recordCount; } result = DbLogic.GetDataTableByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbParameters, orderBy); } return(result); }
/// <summary> /// 获取分页数据(防注入功能的) /// </summary> /// <param name="recordCount">记录条数</param> /// <param name="tableName">数据来源表名</param> /// <param name="selectField">选择字段</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示多少条</param> /// <param name="conditions">查询条件</param> /// <param name="dbParameters">查询参数</param> /// <param name="orderBy">排序字段</param> /// <returns>数据表</returns> public DataTable GetDataTableByPage(BaseUserInfo userInfo, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, List <KeyValuePair <string, object> > dbParameters, string orderBy) { DataTable result = null; int myRecordCount = 0; var dt = new DataTable(BaseModuleEntity.TableName); var parameter = ServiceInfo.Create(userInfo, MethodBase.GetCurrentMethod()); ServiceUtil.ProcessUserCenterReadDb(userInfo, parameter, (dbHelper) => { // 判断是否已经登录的用户? var userManager = new BaseUserManager(userInfo); // 判断是否已经登录的用户? if (userManager.UserIsLogOn(userInfo)) { if (SecretUtil.IsSqlSafe(conditions)) { myRecordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbHelper.MakeParameters(dbParameters)); result = DbLogic.GetDataTableByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbHelper.MakeParameters(dbParameters), orderBy); } else { if (System.Web.HttpContext.Current != null) { // 记录注入日志 FileUtil.WriteMessage("userInfo:" + userInfo.Serialize() + " " + conditions, System.Web.HttpContext.Current.Server.MapPath("~/Log/") + "SqlSafe" + DateTime.Now.ToString(BaseSystemInfo.DateFormat) + ".txt"); } } } }); recordCount = myRecordCount; return(result); }
/// <summary> /// 分页查询 /// </summary> /// <param name="searchValue">查询字段</param> /// <param name="companyId">公司主键</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 GetDataTableByPage(string searchValue, string companyId, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null) { string whereClause = BaseUserEntity.TableName + "." + BaseUserEntity.FieldDeletionStateCode + " = 0 " + " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = 1 " + " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldIsVisible + " = 1 " + " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " > 0 "; if (!String.IsNullOrEmpty(companyId)) { whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " = '" + companyId + "')"; } if (!String.IsNullOrEmpty(departmentId)) { /* * 用非递归调用的建议方法 * sqlQuery += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN ( SELECT " + BaseOrganizeEntity.FieldId + " FROM " + BaseOrganizeEntity.TableName + " WHERE " + BaseOrganizeEntity.FieldId + " = " + departmentId + " OR " + BaseOrganizeEntity.FieldParentId + " = " + departmentId + ")"; */ /* * BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.UserInfo); * string[] ids = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId); * if (ids != null && ids.Length > 0) * { * whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")" + " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSubCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")" + " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(ids) + ")" + " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(ids) + "))"; + } */ whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " = " + departmentId + ")"; } if (!string.IsNullOrEmpty(roleId)) { string tableNameUserRole = UserInfo.SystemCode + "UserRole"; whereClause += " AND ( " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN " + " (SELECT " + BaseUserRoleEntity.FieldUserId + " FROM " + tableNameUserRole + " WHERE " + BaseUserRoleEntity.FieldRoleId + " = " + roleId + "" + " AND " + BaseUserRoleEntity.FieldEnabled + " = 1" + " AND " + BaseUserRoleEntity.FieldDeletionStateCode + " = 0)) "; } if (!string.IsNullOrEmpty(searchValue)) { searchValue = "'" + StringUtil.GetSearchString(searchValue) + "'"; whereClause += " AND (" + BaseUserEntity.FieldRealName + " LIKE " + searchValue; whereClause += " OR " + BaseUserEntity.FieldUserName + " LIKE " + searchValue; whereClause += " OR " + BaseUserEntity.FieldQuickQuery + " LIKE " + searchValue; whereClause += " OR " + BaseUserEntity.FieldSimpleSpelling + " LIKE " + searchValue + ")"; } recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereClause); this.CurrentTableName = "BaseUser"; if (this.ShowUserLogOnInfo) { this.CurrentTableName = BaseUserEntity.TableName + " LEFT OUTER JOIN " + BaseUserLogOnEntity.TableName + " ON " + BaseUserEntity.TableName + ".Id = " + BaseUserLogOnEntity.TableName + ".Id "; } switch (DbHelper.CurrentDbType) { case CurrentDbType.SqlServer: case CurrentDbType.Access: this.SelectFields = BaseUserEntity.TableName + ".* "; if (this.ShowUserLogOnInfo) { this.SelectFields += "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldFirstVisit + "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldPreviousVisit + "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldLastVisit + "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldIPAddress + "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldMACAddress + "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldLogOnCount + "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldUserOnLine; } break; case CurrentDbType.Oracle: case CurrentDbType.MySql: case CurrentDbType.DB2: break; } return(DbLogic.GetDataTableByPage(DbHelper, this.CurrentTableName, this.SelectFields, pageIndex, pageSize, whereClause, 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 GetDataTableByPage(out int recordCount, int pageIndex, int pageSize, string whereConditional, string order) { recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereConditional); return(DbLogic.GetDataTableByPage(DbHelper, this.CurrentTableName, pageIndex, pageSize, whereConditional, order)); }
/// <summary> /// 获取分页DataTable /// </summary> /// <param name="recordCount">记录总数</param> /// <param name="pageIndex">当前页数</param> /// <param name="pageSize">每页显示多少条</param> /// <param name="whereClause">条件</param> /// <param name="order">排序字段</param> /// <returns>数据表</returns> public virtual IDataReader GetDataReaderByPage(out int recordCount, int pageIndex, int pageSize, string whereClause, IDbDataParameter[] dbParameters, string order) { recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereClause, dbParameters, this.CurrentIndex); return(DbLogic.GetDataReaderByPage(DbHelper, this.CurrentTableName, this.SelectFields, pageIndex, pageSize, whereClause, dbParameters, order, this.CurrentIndex)); }
/// <summary> /// 获取权限审核 /// </summary> /// <param name="userInfo">用户</param> /// <param name="startDate">开始日期</param> /// <param name="endDate">结束日期</param> /// <param name="companyId">公司主键</param> /// <param name="userId">用户主键</param> /// <param name="result">权限主键</param> /// <param name="recordCount">记录数</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">每页显示条数</param> /// <returns>数据表</returns> public DataTable PermissionMonitor(BaseUserInfo userInfo, DateTime startDate, DateTime endDate, string companyId, string userId, string permissionId, out int recordCount, int pageIndex = 0, int pageSize = 20) { DataTable result = null; recordCount = 0; int myRecordCount = 0; var parameter = ServiceInfo.Create(userInfo, MethodBase.GetCurrentMethod()); ServiceUtil.ProcessUserCenterReadDb(userInfo, parameter, (dbHelper) => { string whereClause = string.Empty; List <KeyValuePair <string, object> > dbParameters = new List <KeyValuePair <string, object> >(); if (startDate != null) { if (!string.IsNullOrEmpty(whereClause)) { whereClause += " AND "; } whereClause += BasePermissionEntity.FieldCreateOn + " >= " + DotNet.Utilities.DbHelper.GetParameter(BaseSystemInfo.ServerDbType, "startDate"); dbParameters.Add(new KeyValuePair <string, object>("startDate", startDate)); } if (endDate != null) { if (!string.IsNullOrEmpty(whereClause)) { whereClause += " AND "; } whereClause += BasePermissionEntity.FieldCreateOn + " <= " + DotNet.Utilities.DbHelper.GetParameter(BaseSystemInfo.ServerDbType, "endDate"); dbParameters.Add(new KeyValuePair <string, object>("endDate", endDate)); } string tableName = BasePermissionEntity.TableName; if (userInfo != null) { tableName = userInfo.SystemCode + "Permission"; } myRecordCount = DbLogic.GetCount(dbHelper, tableName, whereClause, dbHelper.MakeParameters(dbParameters)); result = DbLogic.GetDataTableByPage(dbHelper, tableName, "*", pageIndex, pageSize, whereClause, dbHelper.MakeParameters(dbParameters), BasePermissionEntity.FieldCreateOn + " DESC"); if (!result.Columns.Contains("ResourceCategoryName")) { result.Columns.Add("ResourceCategoryName".ToUpper()); } if (!result.Columns.Contains("PermissionName")) { result.Columns.Add("PermissionName".ToUpper()); } if (!result.Columns.Contains("PermissionCode")) { result.Columns.Add("PermissionCode".ToUpper()); } if (!result.Columns.Contains("ResourceName")) { result.Columns.Add("ResourceName".ToUpper()); } if (!result.Columns.Contains("CompanyName")) { result.Columns.Add("CompanyName".ToUpper()); } foreach (DataRow dr in result.Rows) { string id = dr["PermissionId"].ToString(); BaseModuleEntity moduleEntity = BaseModuleManager.GetObjectByCache(userInfo, id); if (moduleEntity != null) { dr["PermissionName"] = moduleEntity.FullName; dr["PermissionCode"] = moduleEntity.Code; } if (dr["ResourceCategory"].ToString().Equals(BaseUserEntity.TableName)) { id = dr["ResourceId"].ToString(); BaseUserEntity userEntity = BaseUserManager.GetObjectByCache(id); if (userEntity != null) { dr["ResourceName"] = userEntity.RealName; dr["CompanyName"] = userEntity.CompanyName; dr["ResourceCategoryName"] = "用户"; } } else if (dr["ResourceCategory"].ToString().Equals(BaseOrganizeEntity.TableName)) { id = dr["ResourceId"].ToString(); BaseOrganizeEntity organizeEntity = BaseOrganizeManager.GetObjectByCache(id); if (organizeEntity != null) { dr["ResourceName"] = organizeEntity.FullName; dr["ResourceCategoryName"] = "网点"; } } else if (dr["ResourceCategory"].ToString().Equals(BaseRoleEntity.TableName)) { id = dr["ResourceId"].ToString(); BaseRoleEntity roleEntity = BaseRoleManager.GetObjectByCache(userInfo, id); if (roleEntity != null) { dr["ResourceName"] = roleEntity.RealName; dr["ResourceCategoryName"] = "角色"; } } } }); recordCount = myRecordCount; return(result); }