HTML代码:
<asp:Repeater ID="RPT__List" runat="server">
<HeaderTemplate><table cellpadding="0" cellspacing="0" style="width: 100%;" id="FMListInfo" class="ZebraCrossing">
<thead>
<tr style="height: 22px; text-align: center; background-color: #507CD1; font-weight: bold; color: White;">
<th> ArrivalBill</th>
<th> PEN</th>
<th>ProductNum </th>
<th>Region</th>
<th>Color</th>
<th>CodeName</th>
<th>Quantity</th>
</tr>
</thead>
</HeaderTemplate>
<ItemTemplate>
<tbody>
<tr align="center">
<td><asp:Label ID="lblArrivalBill" runat="server" Text='<%#Eval("ArrivalNum") %>'></asp:Label></td>
<td><asp:Label ID="lblPEN" runat="server" Text='<%#Eval("PEN") %>'></asp:Label></td>
<td><asp:Label ID="lblProductNumber" runat="server" Text='<%#Eval("ProductNum") %>'></asp:Label></td>
<td><asp:Label ID="lblRegion" runat="server" Text='<%#Eval("Region") %>'></asp:Label></td>
<td><asp:Label ID="lblColor" runat="server" Text='<%#Eval("Color") %>'></asp:Label></td>
<td><asp:Label ID="lblCodeName" runat="server" Text='<%#Eval("CodeName") %>'></asp:Label></td>
<td><asp:Label ID="lblTotalQuantity" runat="server" Text='<%#Eval("Quantity") %>'></asp:Label></td>
</tr>
</tbody>
</ItemTemplate><FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
<asp:Panel ID="pl_page" runat="server" HorizontalAlign="Right">
第<asp:Label ID="lblCurrentPage" runat="server" Text=""></asp:Label>页
20条/页 共<asp:Label ID="lblSumPage" runat="server" Text=""></asp:Label>页
共<asp:Label ID="lblSumCount" runat="server" Text=""></asp:Label>条
<asp:LinkButton ID="Lbtn_FirstPage" runat="server" οnclick="Lbtn_FirstPage_Click">首页</asp:LinkButton>
<asp:LinkButton ID="Lbtn_PreviousPage" runat="server" οnclick="Lbtn_PreviousPage_Click">上一页</asp:LinkButton>
<asp:LinkButton ID="Lbtn_NextPage" runat="server" οnclick="Lbtn_NextPage_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="Lbtn_LastPage" runat="server" οnclick="Lbtn_LastPage_Click">尾页</asp:LinkButton>
跳转至<asp:TextBox ID="txtPage" Width="50" runat="server"></asp:TextBox>页
<asp:Button ID="Btn_Go" runat="server" Text="Go" οnclick="Btn_Go_Click" />
</asp:Panel>
.CS代码:
public static int currentPage = 1;//当前页数
public static int pageCount = 20;//每页的条数
public static int pageSum = 0;//总页数
/// <summary>
/// 加载事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
int cityID = int.Parse(Session["CityID"].ToString());
string arrivalID = DDL_ArrivalBill.SelectedValue.ToString();
DataTable dt = bll.GetListByArrrivalNumAndCityID(cityID, arrivalID).Tables[0];
int count = dt.Rows.Count;
if (count > 0)
{
if (count / pageCount == 0)
{
this.lblSumPage.Text = "1";
pageSum = 1;
}
else if (count % pageCount == 0)
{
this.lblSumPage.Text = (count / pageCount).ToString();
pageSum = count / pageCount;
}
else
{
this.lblSumPage.Text = (count / pageCount + 1).ToString();
pageSum = count / pageCount + 1;
}
this.lblCurrentPage.Text = currentPage.ToString();
this.lblSumCount.Text = count.ToString();
}
BindRepeaterOfFMOrder();
}
}
/// <summary>
/// FM库存表
/// </summary>
void BindRepeaterOfFMOrder()
{
int cityID = int.Parse(Session["CityID"].ToString());
string arrivalID = DDL_ArrivalBill.SelectedValue.ToString();
SM_CartridgeBll bll = new SM_CartridgeBll();
this.RPT__FMArrivalOrder.DataSource = bll.GetListByPaging("View_OrderSupplies", "BatchNum", "", currentPage,pageCount, "[BatchNum],[ArrivalNum],[PEN],[ProductNum],[Region],[Color],[CodeName],[Quantity]", "[Quantity]!=0 and CityID=" + cityID + " and [ArrivalID]='" + arrivalID + "'", "");
this.RPT__FMArrivalOrder.DataBind();
}
/// <summary>
/// 首页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Lbtn_FirstPage_Click(object sender, EventArgs e)
{
currentPage = 1;
BindRepeaterOfFMOrder();
}
/// <summary>
/// 尾页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Lbtn_LastPage_Click(object sender, EventArgs e)
{
currentPage = pageSum;
BindRepeaterOfFMOrder();
}
/// <summary>
/// 上一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Lbtn_PreviousPage_Click(object sender, EventArgs e)
{
if (currentPage > 1)
{
currentPage--;
BindRepeaterOfFMOrder();
this.lblCurrentPage.Text = currentPage.ToString();
}
}
/// <summary>
/// 下一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Lbtn_NextPage_Click(object sender, EventArgs e)
{
if (currentPage < pageSum)
{
currentPage++;
BindRepeaterOfFMOrder();
this.lblCurrentPage.Text = currentPage.ToString();
}
}
/// <summary>
/// 跳转至多少页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Btn_Go_Click(object sender, EventArgs e)
{
if (txtPage.Text == "")
{
return;
}
int page = int.Parse(txtPage.Text.Trim());
if (page < 1 || page > pageSum)
{
return;
}
else
{
currentPage = page;
BindRepeaterOfFMOrder();
this.lblCurrentPage.Text = currentPage.ToString();
this.txtPage.Text = string.Empty;
}
}
BLL层:
/// <summary>
/// 分页绑定数据
/// </summary>
/// <param name="tables">表名称,视图</param>
/// <param name="primaryKey">主关键字</param>
/// <param name="sort">排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc</param>
/// <param name="currentPage">当前页码</param>
/// <param name="pageSize">分页尺寸</param>
/// <param name="fields">表或视图中的字段,默认是'*'</param>
/// <param name="filter">过滤语句,不带Where</param>
/// <param name="group">Group语句,不带Group By</param>
/// <returns>DataSet集合</returns>
public DataSet GetListByPaging(string tables, string primaryKey, string sort, int currentPage, int pageSize, string fields, string filter, string group)
{
return dal.GetListByPaging(tables, primaryKey, sort, currentPage, pageSize, fields, filter, group);
}
DAL层:
/// <summary>
/// 分页绑定数据
/// </summary>
/// <param name="tables">表名称,视图</param>
/// <param name="primaryKey">主关键字</param>
/// <param name="sort">排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc</param>
/// <param name="currentPage">当前页码</param>
/// <param name="pageSize">分页尺寸</param>
/// <param name="fields">表或视图中的字段,默认是'*'</param>
/// <param name="filter">过滤语句,不带Where</param>
/// <param name="group">Group语句,不带Group By</param>
/// <returns>DataSet集合</returns>
public DataSet GetListByPaging(string tables, string primaryKey, string sort, int currentPage, int pageSize, string fields, string filter, string group)
{
SqlParameter[] parameters = {
new SqlParameter("@Tables",SqlDbType.VarChar,1000),
new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100),
new SqlParameter("@Sort",SqlDbType.VarChar,200),
new SqlParameter("@CurrentPage",SqlDbType.Int,4),
new SqlParameter("@PageSize",SqlDbType.Int,4),
new SqlParameter("@Fields",SqlDbType.VarChar,1000),
new SqlParameter("@Filter",SqlDbType.VarChar,1000),
new SqlParameter("@Group",SqlDbType.VarChar,1000),
};
parameters[0].Value = tables;
parameters[1].Value = primaryKey;
parameters[2].Value = sort;
parameters[3].Value = currentPage;
parameters[4].Value = pageSize;
parameters[5].Value = fields;
parameters[6].Value = filter;
parameters[7].Value = group;
return SqlHelper.ExecuteDataSet("Proc_Pagination", 1, parameters);
}
分页存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_Pagination]
(
@Tables varchar(1000), --表名称,视图
@PrimaryKey varchar(100), --主关键字
@Sort varchar(200) = NULL, -- 排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
@CurrentPage int = 1, --当前页码
@PageSize int = 10, -- 分页尺寸
@Fields varchar(1000) = '*', --表或视图中的字段,默认是'*'
@Filter varchar(1000) = NULL, --过滤语句,不带Where
@Group varchar(1000) = NULL --Group语句,不带Group By
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = ' <='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)