namespace JYK.Controls.PageManage
{
/**//// <summary>
/// 生成分页控件需要的SQL语句
/// </summary>
public class PageSQL
{
/**//// <summary>
/// 分页控件的实例
/// </summary>
public JYKPage myPage = null;
生成分页用的SQL语句的模版#region 生成分页用的SQL语句的模版
生成首页的SQL语句#region 生成首页的SQL语句
/**//// <summary>
/// 生成首页的SQL语句
/// </summary>
public virtual void CreateFirstPageSQL()
{
//第一页的SQL语句，
//select top PageSize * from table where order by
System.Text.StringBuilder sql = new StringBuilder(100);
sql.Append("set nocount on; ");
sql.Append("select top ");
sql.Append(myPage.PageSize );
sql.Append(" ");
sql.Append(myPage.TableShowColumns);
sql.Append(" from ");
sql.Append(myPage.TableName );
sql.Append(" ");
//查询条件
if (myPage.TableQuery.Length > 0)
{
sql.Append(" where ");
sql.Append(myPage.TableQuery);
}
sql.Append(" order by ");
sql.Append(myPage.TableOrderColumns );
sql.Append(" set nocount off; ");
//保存
myPage.SQLGetFirstPage = sql.ToString();
sql.Length = 0;
}
#endregion
生成任意页的SQL语句#region 生成任意页的SQL语句
/**//// <summary>
/// 生成任意页的SQL语句的模版
/// </summary>
public virtual void CreateNextPageSQL()
{
//指定页号的SQL语句的模版
//SQL 2005 数据库，使用 Row_Number()分页
//set nocount on;
//with t_pager as (
// select *,rn = ROW_NUMBER() OVER (ORDER BY id desc) FROM test_indexorder
// )
//SELECT id,name,content,co1,co2,co3,co4,co5 from t_rn WHERE rn between 19007 and 19057;
System.Text.StringBuilder sql = new StringBuilder(500);
sql.Append("set nocount on; ");
sql.Append("with t_pager as (select myIndex = ROW_NUMBER() OVER (ORDER BY ");
sql.Append(myPage.TableOrderColumns);
sql.Append(" ),* from ");
sql.Append(myPage.TableName);
//查询条件
if (myPage.TableQuery.Length > 0)
{
sql.Append(" where ");
sql.Append(myPage.TableQuery);
}
sql.Append(" ) select ");
sql.Append(myPage.TableShowColumns );
sql.Append(" from t_pager where myIndex between {0} and {1} ");
sql.Append(" set nocount off; ");
//保存
myPage.SQLGetNextPage = sql.ToString();
sql.Length = 0;
}
#endregion
生成最后一页的SQL语句#region 生成最后一页的SQL语句
/**//// <summary>
/// 最后一页的SQL语句。依据算法而定，不是所有的情况都需要实现
/// </summary>
public virtual void CreateLastPageSQL()
{
}
#endregion
#endregion
生成SQL语句模版、获取总记录数，计算页数。#region 生成SQL语句模版、获取总记录数，计算页数。
public void CreateSQL()
{
//重新生成SQL语句模版
CreateFirstPageSQL();
CreateNextPageSQL();
CreateLastPageSQL();
//获取总记录数，计算页数。
System.Text.StringBuilder sql = new StringBuilder();
sql.Append("select count(1) from ");
sql.Append(myPage.TableName);
if (myPage.TableQuery.Length > 0)
{
sql.Append(" where ");
sql.Append(myPage.TableQuery);
}
//总记录数
string tmpCount = myPage.DAL.RunSqlGetFirstColValue(sql.ToString());
if (tmpCount != null)
{
//计算页数
Int32 intCount = Int32.Parse(tmpCount);
myPage.PageRecordCount = intCount;
Int32 tmpPageCount = intCount / myPage.PageSize;
tmpPageCount += intCount % myPage.PageSize == 0 ? 0 : 1;
myPage.PageCount = tmpPageCount;
}
}
#endregion
获取分页用的SQL语句#region 获取分页用的SQL语句
/**//// <summary>
/// 传入页号，返回指定页号的SQL语句
/// </summary>
/// <param name="PageIndex">页号</param>
public string GetSQL(Int32 PageIndex)
{
if (PageIndex == 1)
{
//指定页号
myPage.PageIndex = 1;
return myPage.SQLGetFirstPage;
}
else
{
if (PageIndex < 1)
PageIndex = 1;
if (PageIndex > myPage.PageCount)
PageIndex = myPage.PageCount;
//指定页号
myPage.PageIndex = PageIndex;
Int32 p1 = myPage.PageSize * (PageIndex - 1) + 1;
Int32 p2 = p1 + myPage.PageSize - 1;
return string.Format(myPage.SQLGetNextPage, p1, p2);
}
}
#endregion
}
}
namespace JYK.Controls.PageManage
{
/**//// <summary>
/// 负责绘制分页控件的显示内容
/// </summary>
public class PageUI
{
/**//// <summary>
/// 分页控件的实例
/// </summary>
public JYKPage myPage = null;
/**//// <summary>
/// 客户端ID
/// </summary>
public string BtnClientID = "";
/**//// <summary>
/// 添加UI
/// </summary>
public virtual void AddPageUI()
{
AddPageDataInfo();
AddPageNavi();
AddPageText();
}
添加记录数等的显示#region 添加记录数等的显示
/**//// <summary>
/// 添加记录数等的显示
/// </summary>
public virtual void AddPageDataInfo()
{
string str = myPage.PageUIAllCount.Replace("{0}", myPage.PageRecordCount.ToString());
LiteralControl lc1 = new LiteralControl();
lc1.ID = "p_data1";
lc1.Text = str;
myPage.Controls.Add(lc1);
str = myPage.PageUIAllPage.Replace("{0}", myPage.PageIndex.ToString()).Replace("{1}", myPage.PageCount.ToString());
lc1 = new LiteralControl();
lc1.ID = "p_data2";
lc1.Text = str;
myPage.Controls.Add(lc1);
str = myPage.PageUIAllPageCount.Replace("{0}", myPage.PageSize.ToString());
lc1 = new LiteralControl();
lc1.ID = "p_data3";
lc1.Text = str;
myPage.Controls.Add(lc1);
}
#endregion
修改当前页号#region 修改当前页号
/**//// <summary>
/// 修改当前页号
/// </summary>
public virtual void UpdatePageIndex()
{
string str = myPage.PageUIAllPage.Replace("{0}", myPage.PageIndex.ToString()).Replace("{1}", myPage.PageCount.ToString());
LiteralControl lc1 = (LiteralControl)myPage.FindControl("p_data2");
lc1.Text = str;
}
#endregion
添加上一页等#region 添加上一页等
/**//// <summary>
/// 添加上一页等
/// </summary>
public virtual void AddPageText()
{
BtnClientID = myPage.ClientID + "_dh";
System.Text.StringBuilder str = new StringBuilder();
首页#region 首页
str.Append("<a id=\"P_First\" href=\"");
str.Append(GetAHref(1));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >");
str.Append(myPage.PageUIFirst);
str.Append("</a> ");
LiteralControl lc1 = new LiteralControl();
lc1.ID = "p_First";
lc1.Text = str.ToString();
myPage.Controls.Add(lc1);
str.Length = 0;
#endregion
上一页#region 上一页
str.Append("<a id=\"P_Prev\" href=\"");
str.Append(GetAHref(-3));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >");
str.Append(myPage.PageUIPrev);
str.Append("</a> ");
lc1 = new LiteralControl();
lc1.ID = "P_Prev";
lc1.Text = str.ToString();
myPage.Controls.Add(lc1);
str.Length = 0;
#endregion
下一页#region 下一页
str.Append("<a id=\"P_Next\" href=\"");
str.Append(GetAHref(-2));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >");
str.Append(myPage.PageUINext);
str.Append("</a> ");
lc1 = new LiteralControl();
lc1.ID = "P_Next";
lc1.Text = str.ToString();
myPage.Controls.Add(lc1);
str.Length = 0;
#endregion
末页#region 末页
str.Append("<a id=\"P_Last\" href=\"");
str.Append(GetAHref(myPage.PageCount));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >");
str.Append(myPage.PageUILast);
str.Append("</a> ");
lc1 = new LiteralControl();
lc1.ID = "P_Last";
lc1.Text = str.ToString();
myPage.Controls.Add(lc1);
str.Length = 0;
#endregion
GO#region GO
str.Append(" <a id=\"P_GO\" href=\"javascript:_GO()\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >");
str.Append(myPage.PageUIGO);
str.Append("</a> ");
JYKTextBox txt = new JYKTextBox();
txt.ID = "Txt_GO";
txt.MaxLength = 3;
txt.Columns = 1;
myPage.Controls.Add(txt);
lc1 = new LiteralControl();
lc1.ID = "P_Go";
lc1.Text = str.ToString();
myPage.Controls.Add(lc1);
str.Length = 0;
#endregion
}
#endregion
//导航
页面导航#region 页面导航
/**//// <summary>
/// navigation
/// </summary>
public virtual void AddPageNavi()
{
string str = GetNavi();
LiteralControl lc1 = new LiteralControl();
lc1.ID = "p_no";
lc1.Text = str;
myPage.Controls.Add(lc1);
}
#endregion
修改#region 修改
/**//// <summary>
/// 修改
/// </summary>
public virtual void UpdatePageNavi()
{
Int32 pIndex = myPage.PageIndex;
LiteralControl lc1 = (LiteralControl)myPage.FindControl("p_no");
Int32 NoCount = myPage.NaviCount; //一组页号的数量
//Int32 cp = myPage.PageIndex / NoCount * NoCount;
if (pIndex % NoCount == 0 || pIndex % NoCount == NoCount - 1)
{
lc1.Text = GetNavi();
}
else
{
string str = lc1.Text.Replace(">-", ";>[");
str = str.Replace("-<", "]<");
str = str.Replace("[" + pIndex + "]", "-" + pIndex + "-");
lc1.Text = str;
}
}
#endregion
重新计算导航页号#region 重新计算导航页号
/**//// <summary>
/// 重新计算导航页号
/// </summary>
public virtual void ReloadPageNavi()
{
string pIndex = myPage.PageIndex.ToString();
LiteralControl lc1 = (LiteralControl)myPage.FindControl("p_no");
lc1.Text = GetNavi();
}
#endregion
生成导航的html#region 生成导航的html
/**//// <summary>
/// 生成导航的html
/// </summary>
/// <returns></returns>
private string GetNavi()
{
System.Text.StringBuilder str = new StringBuilder();
Int32 pIndex = myPage.PageIndex;
Int32 NoCount = myPage.NaviCount ; //一组页号的数量
Int32 cp = pIndex / NoCount; // *NoCount;
if (pIndex != NoCount && cp != 0)
{
//前导页
str.Append(" <a id=\"P_aa\" href=\"");
str.Append(GetAHref(-99));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >");
str.Append("");
str.Append("</a> ");
str.Append(" ");
}
Int32 i = 0;
Int32 pCount = myPage.PageCount;
cp *= NoCount;
//没有想到更好的解决办法
if (pIndex % NoCount == 0)
cp -= NoCount;
for (i = 1; i <= NoCount; i++)
{
if (pIndex == cp + i)
{
str.Append(" <a id=\"P_b" + i.ToString() + "\" href=\"");
str.Append(GetAHref(cp + i));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >-");
str.Append(cp + i);
str.Append("-</a> ");
}
else
{
str.Append(" <a id=\"P_b"+ i.ToString() +"\" href=\"");
str.Append(GetAHref(cp + i));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >[");
str.Append(cp + i);
str.Append("]</a> ");
}
if (cp + i >= pCount)
{
i = 9999;
}
}
if (cp + i < pCount)
{
//后导页
str.Append(" <a id=\"P_zz\" href=\"");
str.Append(GetAHref(-88));
str.Append("\" class=\"");
str.Append(myPage.CssClass);
str.Append("\" >");
str.Append("");
str.Append("</a> ");
str.Append(" ");
}
return str.ToString();
}
#endregion
}
}
namespace JYK.Controls.PageManage
{
/**//// <summary>
/// 依据PageSQL提供的SQL语句，通过数据访问函数库到数据库里提取数据
/// </summary>
public class PageGetData
{
/**//// <summary>
/// 分页控件的实例
/// </summary>
public JYKPage myPage = null;
/**//// <summary>
/// 返回DataTable记录集
/// </summary>
/// <param name="PageIndex">页号。从1开始计数</param>
/// <returns></returns>
public DataTable GetDataTable(Int32 PageIndex)
{
string sql = myPage.ManagerPageSQL.GetSQL(PageIndex);
return myPage.DAL.RunSqlDataTable(sql);
}
//public T GetDataList<T>(Int32 PageIndex)
//{
// string sql = myPage.ManagerPageSQL.GetSQL(PageIndex);
// return myPage.DAL.RunSqlDataTable(sql);
//}
}
}
