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