using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ProjectBase.Data.Pager
{
///
/// 根据各种不同数据库生成不同分页语句的辅助类 PagerHelper
///
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 属性对象
///
/// 待查询表或自定义查询语句
///
public string TableName
{
get { return tableName; }
set { tableName = value; }
}
///
/// 需要返回的列
///
public string FieldsToReturn
{
get { return fieldsToReturn; }
set { fieldsToReturn = value; }
}
///
/// 排序字段名称
///
public string FieldNameToSort
{
get { return fieldNameToSort; }
set { fieldNameToSort = value; }
}
///
/// 页尺寸,就是一页显示多少条记录
///
public int PageSize
{
get { return pageSize; }
set { pageSize = value; }
}
///
/// 当前的页码
///
public int PageIndex
{
get { return pageIndex; }
set { pageIndex = value; }
}
///
/// 是否以降序排列结果
///
public bool IsDescending
{
get { return isDescending; }
set { isDescending = value; }
}
///
/// 检索条件(注意: 不要加 where)
///
public string StrWhere
{
get { return strwhere; }
set { strwhere = value; }
}
///
/// 表或Sql语句包装属性
///
internal string TableOrSqlWrapper
{
get
{
bool isSql = tableName.ToLower().Contains("from");
if (isSql)
{
return string.Format("({0}) AA ", tableName);//如果是Sql语句,则加括号后再使用
}
else
{
return tableName;//如果是表名,则直接使用
}
}
}
#endregion
#region 构造函数
///
/// 默认构造函数,其他通过属性设置
///
public PagerHelper()
{
}
///
/// 完整的构造函数,可以包含条件,返回记录字段等条件
///
/// 自定义查询语句
/// 需要返回的列
/// 排序字段名称
/// 页尺寸
/// 当前的页码
/// 是否以降序排列
/// 检索条件
/// 连接字符串
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
///
/// 不依赖于存储过程的分页(Oracle)
///
/// 如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql
///
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;
}
///
/// 不依赖于存储过程的分页(SqlServer)
///
/// 如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql
///
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;
}
///
/// 不依赖于存储过程的分页(Access)
///
/// 如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql
///
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;
}
///
/// 不依赖于存储过程的分页(MySql)
///
/// 如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql
///
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;
}
///
/// 不依赖于存储过程的分页(SQLite)
///
/// 如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql
///
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;
}
///
/// 获取对应数据库的分页语句(指定数据库类型)
///
/// 数据库类型枚举
/// 如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql
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;
}
///
/// 获取对应数据库的分页语句(从配置文件读取数据库类型:ComponentDbType)
///
/// 数据库类型枚举
/// 如果isDoCount为True,返回总数统计Sql;否则返回分页语句Sql
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;
}
}
///
/// 数据库类型
///
public enum DatabaseType { SqlServer, Oracle, Access, MySql, SQLite }
}