/// <summary>
/// 分页SQL调用方法 MaxTop MinTop 只允许主键排序
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") {
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
sql.CountSql = strSql.ToString();
//顺序写法：
//SELECT TOP 页大小 *
//FROM table1
//WHERE id >=(
//SELECT ISNULL(MAX(id),0)
//FROM (
//SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id
//) A)
//ORDER BY id
//降序写法：
//SELECT TOP 页大小 *
//FROM table1
//WHERE id <=(
//SELECT ISNULL(MIN(id),0)
//FROM (
//SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id Desc
//) A)
//ORDER BY id Desc
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("top {0} ", pageSize);
strSql.AppendFormat("{0} ", fieldList);
if (pageIndex == 1) {
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
} else {
if (!tableName.IsNullEmpty() && orderBy.EndsWith(" desc", true, null)) strSql.AppendFormat("from {0} where {1} < (select isnull(min({1}),0) from (select top {2} {1} from {3} ", tableName, pk, pageSize * (pageIndex - 1), tableName);
else if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} where {1} > (select isnull(max({1}),0) from (select top {2} {1} from {3} ", tableName, pk, pageSize * (pageIndex - 1), tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
strSql.Append(") tempMaxTop ) ");
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
}
sql.DataSql = strSql.ToString();
return sql;
}
/// <summary>
/// 分页SQL调用方法 只允许主键排序
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") {
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
sql.CountSql = strSql.ToString();
//select * from Student
//where Id in (
//select top 10 Id
//from(select top 3200010 Id from Student order by Id)t
//order by Id desc)
//order by Id
//SELECT * FROM (
//SELECT TOP 页面容量 * FROM (
//SELECT TOP 页面容量*当前页码 * FROM
//表 WHERE 条件 ORDER BY 字段A ASC
//)AS TEMPTABLE1 ORDER BY 字段A DESC
//) AS TEMPTABLE2 ORDER BY 字段A ASC
StringBuilder orderByExt = new StringBuilder();
foreach (string order in orderBy.Split(',')) {
string order2 = order.Trim();
if (order2.EndsWith(" desc", true, null)) orderByExt.AppendFormat("{0} {1},", order2.Left(order2.Length - 5), "asc");
else orderByExt.AppendFormat("{0} {1},", order2.EndsWith(" asc", true, null) ? order2.Left(order2.Length - 4) : order2, "desc");
}
orderByExt.RemoveLastChar(",");
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
if (pageSize == 1) {
strSql.AppendFormat("top {0} ", pageSize);
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
} else {
if (!tableName.IsNullEmpty()) strSql.AppendFormat("{1} from (select top {0} {1} from (select top {3} {1} from {2} ", pageSize, fieldList, tableName, pageSize * pageIndex);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
strSql.AppendFormat(") as Top1 {0}) as Top2 {1} ", "order by " + orderByExt, "order by " + orderBy);
}
sql.DataSql = strSql.ToString();
return sql;
}
/// <summary>
/// 获取当前页的记录集，用WebList2装载
/// </summary>
/// <returns></returns>
public IList <WebList2> GetWebList2(WebList2Format lstFormat)
{
//初始化SQL
SetSql();
int tmpPageIndex = PageIndex;
//获取记录集
string sql = PagerSql.GetSQLByPageIndex(tmpPageIndex);
IList <WebList2> lst = Dal.ExecuteFillWebList2(sql, lstFormat);
return(lst);
}
/// <summary>
/// 获取当前页的记录集，用DataTable装载
/// </summary>
/// <returns></returns>
public DataTable GetDataTable()
{
//初始化SQL
SetSql();
int tmpPageIndex = PageIndex;
//获取记录集
string sql = PagerSql.GetSQLByPageIndex(tmpPageIndex);
DataTable dt = Dal.ExecuteFillDataTable(sql);
return(dt);
}
/// <summary>
/// 绑定控件
/// </summary>
public void DataBind(Int32 tmpPageIndex, PageArgs e)
{
//查找显示数据的控件
PageIndex = tmpPageIndex;
if (tmpPageIndex < 0)
{
tmpPageIndex = 0;
}
//获取提取记录的SQL
string sql = PagerSql.GetSQLByPageIndex(tmpPageIndex);
//this.ManagerPageSQL.GetPageSQL = sql;
//获取DataTable
DataTable dt = Dal.ExecuteFillDataTable(sql);
//调用外部事件
OnPreGridBind(this, e);
if (ShowDataControl == null)
{
//没有设置控件实例，通过ID查找控件，然后绑定
string ctrlIDs = ShowDataControlIDs;
if (ctrlIDs == null) //没有设置显示数据的控件的ID值
{
return;
}
//一个分页控件可以绑定多个显示数据的控件
string[] ctrlID = ctrlIDs.Split(',');
foreach (string a in ctrlID)
{
DataBind(base.Page.FindControl(a), dt);
}
}
else
{
//设置了 显示数据的对象，直接绑定
DataBind(ShowDataControl, dt);
}
//触发（调用）外部的事件
OnGridBinded(this, e);
//this.ManagerPageUI.AddPageUI(); //添加页面显示
}
/// <summary>
/// 计算总记录数，和页数
/// </summary>
private void ComputeRecordCount()
{
//计算总记录数，和页数
string sql = PagerSql.GetRecordCountSQL; //获取SQL语句
string strRecordCount = Dal.ExecuteString(sql); //执行SQL语句
if (Dal.ErrorMessage.Length > 0)
{
Functions.MsgBox("计算总记录数时出现异常，请查看属性设置情况！", true);
return;
}
int intRecordCount = Int32.Parse(strRecordCount);
RecordCount = intRecordCount; //设置总记录数，必须先设置，否则无法计算总页数
PagerSql.ComputePageCount(); //计算页数int intPageCount =
//this.PagerSQL.PageCount = intPageCount; //设置总页数
}
/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") {
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
sql.CountSql = strSql.ToString();
//SELECT TOP 页大小 *
//FROM table1
//WHERE id NOT IN
//(
//SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
//)
//ORDER BY id
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("top {0} ", pageSize);
strSql.AppendFormat("{0} ", fieldList);
if (pageIndex == 1) {
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
} else {
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} where ({1} not in ( select top {2} {1} from {3} ", tableName, pk, pageSize * (pageIndex - 1), tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
strSql.Append(")) ");
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
}
sql.DataSql = strSql.ToString();
return sql;
}
/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") {
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
sql.CountSql = strSql.ToString();
//'select ' + @Fields + ' from (
//select ' + @Fields + ', ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as rownum
//from ' + @Tables + @strWhere + @strGroupBy + ') as tmpTable
//where rownum > ' + CONVERT(nvarchar(10), @startRowIndex) +
//' and rownum <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ')'
//SELECT TOP 页大小 *
//FROM
//(
//SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 where ? group by ?
//) A
//WHERE RowNumber > 页大小*(页数-1)
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty()) {
strSql.Append("from ( ");
strSql.AppendFormat("select {0}, row_number() over (order by {1}) as rownum ", fieldList, orderBy);
strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
strSql.AppendFormat(") as tmpTable where rownum > {0} and rownum <= {1}", (pageIndex - 1) * pageSize, (pageIndex - 1) * pageSize + pageSize);
}
sql.DataSql = strSql.ToString();
return sql;
}
/// <summary>
/// SQL 语句的初始化
/// </summary>
private void SetSql()
{
//获取URL 里面的分页参数
string tmpPageIndex = System.Web.HttpContext.Current.Request.QueryString["page"];
Int32 intPageIndex = 1;
if (!string.IsNullOrEmpty(tmpPageIndex))
{
if (Functions.IsInt(tmpPageIndex))
{
intPageIndex = Int32.Parse(tmpPageIndex);
}
}
PageIndex = intPageIndex;
//生成SQL语句和获取记录总数
PagerSql.CreateSQL();
//统计总记录数和总页数
ComputeRecordCount();
}
/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") {
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
sql.CountSql = strSql.ToString();
//SELECT * FROM (
//SELECT MY_TABLE.*,ROWNUM AS MY_ROWNUM FROM (
// 括号里写实际的需要查询的SQL语句**/
//) AS MY_TABLE WHERE ROWNUM <=200/**这里是一页中的最后一条记录**/
//) WHERE MY_ROWNUM>=10 /**这里是一页中的第一条记录**/
//select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
//select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
//select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where rn>9980;
strSql.Clear();
strSql.Append("select ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} (select {1},rownum as my_rownum from (", tableName, fieldList);
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
strSql.AppendFormat(") as my_table where rownum<{0}) where my_rownum>{1}", pageSize * (pageIndex - 1) + pageSize, pageSize * (pageIndex - 1));
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
sql.DataSql = strSql.ToString();
return sql;
}
/// <summary>
/// 分页SQL调用方法
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <param name="tableName">表名称</param>
/// <param name="pk">主键</param>
/// <param name="fieldList">字段列表</param>
/// <param name="where">where条件 and or 开始</param>
/// <param name="groupBy">分组条件</param>
/// <param name="orderBy">排序条件</param>
/// <returns>分页SQL</returns>
public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") {
PagerSql sql = new PagerSql();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.AppendFormat("count({0}) as total ", pk);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
sql.CountSql = strSql.ToString();
strSql.Clear();
strSql.Append("select ");
//if (distinct) strSql.Append("distinct ");
strSql.AppendFormat("{0} ", fieldList);
if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
strSql.AppendFormat("limit {1} offset {0} ", (pageIndex - 1) * pageSize, pageSize);
sql.DataSql = strSql.ToString();
return sql;
}
private void QuickFirst()
{
if (PagerRunKind == PagerRunKind.Auto)
{
//定义一个事件里的参数
var e = new PageArgs();
switch (PagerTurnKind)
{
case PagerTurnKind.PostBack:
#region PostBack
e.OldPageIndex = 1;
e.CurrentPageIndex = 1;
//触发（调用）外部的事件
OnPageChange(this, e);
//自动提取数据的方式，显示第一页的数据
//生成SQL语句和获取记录总数
PagerSql.CreateSQL();
//统计总记录数和总页数
ComputeRecordCount();
//绑定第this.PageIndex页的数据
DataBind(PageIndex, e);
#endregion
break;
case PagerTurnKind.URLRewriter:
case PagerTurnKind.URL:
#region URL
//这是为不存储
PagerSql.SaveLocation = SaveViewStateLocation.NoSave;
if (IsBindControl)
{
//初始化SQL
SetSql();
//函数里实现下面几个功能
//获取URL 里面的分页参数
//生成SQL语句和获取记录总数
//统计总记录数和总页数
//绑定 数据
DataBind(PageIndex, e);
}
#endregion
break;
case PagerTurnKind.PostBackURL:
case PagerTurnKind.AjaxForWebControl:
#region PostBackURL
e.OldPageIndex = 1;
e.CurrentPageIndex = 1;
//触发（调用）外部的事件
OnPageChange(this, e);
//自动提取数据的方式，显示第一页的数据
//生成SQL语句和获取记录总数
PagerSql.CreateSQL();
//统计总记录数和总页数
ComputeRecordCount();
//绑定第this.PageIndex页的数据
DataBind(PageIndex, e);
#endregion
break;
//其他的分页方法暂时不处理
}
//判断是否ajax分页
switch (PagerTurnKind)
{
case PagerTurnKind.AjaxForWebControl:
//ajax
DataControltoHtml();
break;
}
}
ManagerPageUI.AddPageUI(); //添加页面显示
}
