123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- namespace ProjectBase.Data.Pager
- {
- /// <summary>
- /// 根据各种不同数据库生成不同分页语句的辅助类 PagerHelper
- /// </summary>
- public class PagerHelper
- {
- #region 成员变量
- private string tableName;//待查询表或自定义查询语句
- private string fieldsToReturn = "*";//需要返回的列
- private string fieldNameToSort = string.Empty;//排序字段名称
- private int pageSize = 10;//页尺寸,就是一页显示多少条记录
- private int pageIndex = 1;//当前的页码
- private bool isDescending = false;//是否以降序排列
- private string strwhere = string.Empty;//检索条件(注意: 不要加 where)
- #endregion
- #region 属性对象
- /// <summary>
- /// 待查询表或自定义查询语句
- /// </summary>
- public string TableName
- {
- get { return tableName; }
- set { tableName = value; }
- }
- /// <summary>
- /// 需要返回的列
- /// </summary>
- public string FieldsToReturn
- {
- get { return fieldsToReturn; }
- set { fieldsToReturn = value; }
- }
- /// <summary>
- /// 排序字段名称
- /// </summary>
- public string FieldNameToSort
- {
- get { return fieldNameToSort; }
- set { fieldNameToSort = value; }
- }
- /// <summary>
- /// 页尺寸,就是一页显示多少条记录
- /// </summary>
- public int PageSize
- {
- get { return pageSize; }
- set { pageSize = value; }
- }
- /// <summary>
- /// 当前的页码
- /// </summary>
- public int PageIndex
- {
- get { return pageIndex; }
- set { pageIndex = value; }
- }
- /// <summary>
- /// 是否以降序排列结果
- /// </summary>
- public bool IsDescending
- {
- get { return isDescending; }
- set { isDescending = value; }
- }
- /// <summary>
- /// 检索条件(注意: 不要加 where)
- /// </summary>
- public string StrWhere
- {
- get { return strwhere; }
- set { strwhere = value; }
- }
- /// <summary>
- /// 表或Sql语句包装属性
- /// </summary>
- internal string TableOrSqlWrapper
- {
- get
- {
- bool isSql = tableName.ToLower().Contains("from");
- if (isSql)
- {
- return string.Format("({0}) AA ", tableName);//如果是Sql语句,则加括号后再使用
- }
- else
- {
- return tableName;//如果是表名,则直接使用
- }
- }
- }
- #endregion
- #region 构造函数
- /// <summary>
- /// 默认构造函数,其他通过属性设置
- /// </summary>
- public PagerHelper()
- {
- }
- /// <summary>
- /// 完整的构造函数,可以包含条件,返回记录字段等条件
- /// </summary>
- /// <param name="tableName">自定义查询语句</param>
- /// <param name="fieldsToReturn">需要返回的列</param>
- /// <param name="fieldNameToSort">排序字段名称</param>
- /// <param name="pageSize">页尺寸</param>
- /// <param name="pageIndex">当前的页码</param>
- /// <param name="isDescending">是否以降序排列</param>
- /// <param name="strwhere">检索条件</param>
- /// <param name="connectionString">连接字符串</param>
- public PagerHelper(string tableName, string fieldsToReturn, string fieldNameToSort,
- int pageSize, int pageIndex, bool isDescending, string strwhere)
- {
- this.tableName = tableName;
- this.fieldsToReturn = fieldsToReturn;
- this.fieldNameToSort = fieldNameToSort;
- this.pageSize = pageSize;
- this.pageIndex = pageIndex;
- this.isDescending = isDescending;
- this.strwhere = strwhere;
- }
- #endregion
- /// <summary>
- /// 不依赖于存储过程的分页(Oracle)
- /// </summary>
- /// <param name="isDoCount">如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql</param>
- /// <returns></returns>
- private string GetOracleSql(bool isDoCount)
- {
- string sql = "";
- if (string.IsNullOrEmpty(this.strwhere))
- {
- this.strwhere = " (1=1) ";
- }
- if (isDoCount)//执行总数统计
- {
- sql = string.Format("select count(*) as Total from {0} Where {1} ", this.TableOrSqlWrapper, this.strwhere);
- }
- else
- {
- string strOrder = string.Format(" order by {0} {1}", this.fieldNameToSort, this.isDescending ? "DESC" : "ASC");
- int minRow = pageSize * (pageIndex - 1);
- int maxRow = pageSize * pageIndex;
- string selectSql = string.Format("select {0} from {1} Where {2} {3}", fieldsToReturn, this.TableOrSqlWrapper, this.strwhere, strOrder);
- sql = string.Format(@"select b.* from
- (select a.*, rownum as rowIndex from({2}) a) b
- where b.rowIndex > {0} and b.rowIndex <= {1}", minRow, maxRow, selectSql);
- }
- return sql;
- }
- /// <summary>
- /// 不依赖于存储过程的分页(SqlServer)
- /// </summary>
- /// <param name="isDoCount">如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql</param>
- /// <returns></returns>
- private string GetSqlServerSql(bool isDoCount)
- {
- string sql = "";
- if (string.IsNullOrEmpty(this.strwhere))
- {
- this.strwhere = " (1=1) ";
- }
- if (isDoCount)//执行总数统计
- {
- sql = string.Format("select count(*) as Total from {0} Where {1} ", this.TableOrSqlWrapper, this.strwhere);
- }
- else
- {
- // With Paging AS
- // ( SELECT ROW_NUMBER() OVER (order by SortCode desc) as RowNumber, * FROM T_ACL_User )
- // SELECT * from Paging Where RowNumber Between 1 and 20
- string strOrder = string.Format(" order by {0} {1}", this.fieldNameToSort, this.isDescending ? "DESC" : "ASC");
- int minRow = pageSize * (pageIndex - 1) + 1;
- int maxRow = pageSize * pageIndex;
- sql = string.Format(@"With Paging AS
- ( SELECT ROW_NUMBER() OVER ({0}) as RowNumber, {1} FROM {2} Where {3})
- SELECT * FROM Paging WHERE RowNumber Between {4} and {5}", strOrder, this.fieldsToReturn, this.TableOrSqlWrapper, this.strwhere,
- minRow, maxRow);
- }
- return sql;
- }
- /// <summary>
- /// 不依赖于存储过程的分页(Access)
- /// </summary>
- /// <param name="isDoCount">如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql</param>
- /// <returns></returns>
- private string GetAccessSql(bool isDoCount)
- {
- string sql = "";
- if (string.IsNullOrEmpty(this.strwhere))
- {
- this.strwhere = " (1=1) ";
- }
- if (isDoCount)//执行总数统计
- {
- sql = string.Format("select count(*) as Total from {0} Where {1} ", this.TableOrSqlWrapper, this.strwhere);
- }
- else
- {
- string strTemp = string.Empty;
- string strOrder = string.Empty;
- if (this.isDescending)
- {
- strTemp = "<(select min";
- strOrder = string.Format(" order by [{0}] desc", this.fieldNameToSort);
- }
- else
- {
- strTemp = ">(select max";
- strOrder = string.Format(" order by [{0}] asc", this.fieldNameToSort);
- }
- sql = string.Format("select top {0} {1} from {2} ", this.pageSize, this.fieldsToReturn, this.TableOrSqlWrapper);
- //如果是第一页就执行以上代码,这样会加快执行速度
- if (this.pageIndex == 1)
- {
- sql += string.Format(" Where {0} ", this.strwhere);
- sql += strOrder;
- }
- else
- {
- sql += string.Format(" Where [{0}] {1} ([{0}]) from (select top {2} [{0}] from {3} where {5} {4} ) as tblTmp) and {5} {4}",
- this.fieldNameToSort, strTemp, (this.pageIndex - 1) * this.pageSize, this.TableOrSqlWrapper, strOrder, this.strwhere);
- }
- }
- return sql;
- }
- /// <summary>
- /// 不依赖于存储过程的分页(MySql)
- /// </summary>
- /// <param name="isDoCount">如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql</param>
- /// <returns></returns>
- private string GetMySqlSql(bool isDoCount)
- {
- string sql = "";
- if (string.IsNullOrEmpty(this.strwhere))
- {
- this.strwhere = " (1=1) ";
- }
- if (isDoCount)//执行总数统计
- {
- sql = string.Format("select count(*) as Total from {0} Where {1} ", this.TableOrSqlWrapper, this.strwhere);
- }
- else
- {
- //SELECT * FROM 表名称 LIMIT M,N
- string strOrder = string.Format(" order by {0} {1}", this.fieldNameToSort, this.isDescending ? "DESC" : "ASC");
- int minRow = pageSize * (pageIndex - 1);
- int maxRow = pageSize * pageIndex;
- sql = string.Format("select {0} from {1} Where {2} {3} LIMIT {4},{5}",
- fieldsToReturn, this.TableOrSqlWrapper, this.strwhere, strOrder, minRow, maxRow);
- }
- return sql;
- }
- /// <summary>
- /// 不依赖于存储过程的分页(SQLite)
- /// </summary>
- /// <param name="isDoCount">如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql</param>
- /// <returns></returns>
- private string GetSQLiteSql(bool isDoCount)
- {
- string sql = "";
- if (string.IsNullOrEmpty(this.strwhere))
- {
- this.strwhere = " (1=1) ";
- }
- if (isDoCount)//执行总数统计
- {
- sql = string.Format("select count(*) as Total from {0} Where {1} ", this.TableOrSqlWrapper, this.strwhere);
- }
- else
- {
- //SELECT * FROM 表名称 LIMIT M,N
- string strOrder = string.Format(" order by {0} {1}", this.fieldNameToSort, this.isDescending ? "DESC" : "ASC");
- int minRow = pageSize * (pageIndex - 1);
- int maxRow = pageSize * pageIndex;
- sql = string.Format("select {0} from {1} Where {2} {3} LIMIT {4},{5}",
- fieldsToReturn, this.TableOrSqlWrapper, this.strwhere, strOrder, minRow, maxRow);
- }
- return sql;
- }
- /// <summary>
- /// 获取对应数据库的分页语句(指定数据库类型)
- /// </summary>
- /// <param name="dbType">数据库类型枚举</param>
- /// <param name="isDoCount">如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql</param>
- public string GetPagingSql(DatabaseType dbType, bool isDoCount)
- {
- string sql = "";
- switch (dbType)
- {
- case DatabaseType.Access:
- sql = GetAccessSql(isDoCount);
- break;
- case DatabaseType.SqlServer:
- sql = GetSqlServerSql(isDoCount);
- break;
- case DatabaseType.Oracle:
- sql = GetOracleSql(isDoCount);
- break;
- case DatabaseType.MySql:
- sql = GetMySqlSql(isDoCount);
- break;
- case DatabaseType.SQLite:
- sql = GetSQLiteSql(isDoCount);
- break;
- }
- return sql;
- }
- /// <summary>
- /// 获取对应数据库的分页语句(从配置文件读取数据库类型:ComponentDbType)
- /// </summary>
- /// <param name="dbType">数据库类型枚举</param>
- /// <param name="isDoCount">如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql</param>
- public string GetPagingSql(bool isDoCount)
- {
- //AppConfig config = new AppConfig();
- string databaseType = System.Configuration.ConfigurationManager.AppSettings["ComponentDbType"];
- DatabaseType dbType = GetDataBaseType(databaseType);
- return GetPagingSql(dbType, isDoCount);
- }
- private DatabaseType GetDataBaseType(string databaseType)
- {
- DatabaseType returnValue = DatabaseType.SqlServer;
- foreach (DatabaseType dbType in Enum.GetValues(typeof(DatabaseType)))
- {
- if (dbType.ToString().Equals(databaseType, StringComparison.OrdinalIgnoreCase))
- {
- returnValue = dbType;
- break;
- }
- }
- return returnValue;
- }
- }
- /// <summary>
- /// 数据库类型
- /// </summary>
- public enum DatabaseType { SqlServer, Oracle, Access, MySql, SQLite }
- }
|