写一个调用的过程：
1 ）aspx页面
<asp:DataList ID="DataList1" runat="server" CssClass="lihoutaikuang" HeaderStyle-HorizontalAlign="Center"
ItemStyle-HorizontalAlign="Center" OnDeleteCommand="DataList1_DeleteCommand"
Width="97%">
<HeaderTemplate>
<tr>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif" height="29">
<b>编号</b></td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif">
<b>登录名称</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif">
<b>显示名称</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif">
<b>性别</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif">
<b>职位</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif">
<b>办公电话</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif">
<b>手机</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif">
<b>电子邮件</b></td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif"> <%--style="display: <%=GetStateValue("A002") %>"--%>
<b>修改</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif" ><%--style="display: <%=GetStateValue("A005") %>"--%>
<b>为用户赋角色</b>
</td>
<td align="center" background="http://www.cnblogs.com/images/houtai_51.gif" ><%--style="display: <%=GetStateValue("A003") %>"--%>
<b>删除</b></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center" class="lihoutaidots" height="35">
<%#Eval("UserID")%>
</td>
<td align="center" class="lihoutaidots">
<%#Eval("UserName")%>
</td>
<td align="center" class="lihoutaidots">
<%#Eval("RealName")%>
</td>
<td align="center" class="lihoutaidots">
<%#Eval("Sex").ToString() == "0" ? "男" : "女" %>
</td>
<td align="center" class="lihoutaidots">
<%#Eval("Pos")%>
</td>
<td align="center" class="lihoutaidots">
<%#Eval("OfficeTel")%>
</td>
<td align="center" class="lihoutaidots">
<%#Eval("MobilePhone")%>
</td>
<td align="center" class="lihoutaidots">
<%#Eval("Email")%>
</td>
<td align="center" class="lihoutaidots"><%-- style="display: <%=GetStateValue("A002") %>"--%>
<a href="<%#Eval("UserID","UserEdit.aspx?UserID={0}") %>">
<img border="0" height="15" src="http://www.cnblogs.com/images/houtai_42.gif" width="16" /></a></td>
<td align="center" class="lihoutaidots"><%-- style="display: <%=GetStateValue("A005") %>"--%>
<a href="<%#Eval("UserID","../Role/UserRoleManage.aspx?UserID={0}") %>">
<img border="0" height="15" src="http://www.cnblogs.com/images/houtai_42.gif" width="16" /></a></td>
<td align="center" class="lihoutaidots"><%-- style="display: <%=GetStateValue("A003") %>"--%>
<asp:ImageButton ID="ImageButton1" runat="server" CommandArgument='<%#Eval("UserID")%>'
CommandName="Delete" ImageUrl="http://www.cnblogs.com/images/houtai_44.gif" OnClientClick="javascript:return confirm('确定要删除此行记录么？')" />
</td>
</tr>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:DataList>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" AlwaysShow="True" CssClass="listpage"
CustomInfoHTML="记录总数：<font color='blue'><b>%RecordCount%</b></font> 总页数：<font color='blue'><b>%PageCount%</b></font> 当前页：<font color='red'><b>%CurrentPageIndex%</b></font>"
FirstPageText="[首页]" HorizontalAlign="Right" InputBoxStyle="width:19px" LastPageText="[尾页]"
meta:resourceKey="AspNetPager1" NextPageText="[下一页]" OnPageChanged="AspNetPager1_PageChanged"
PageSize="10" PrevPageText="[上一页]" ShowCustomInfoSection="Left" ShowNavigationToolTip="True"
Style="font-size: 13px" UrlPaging="True" Width="760">
</webdiyer:AspNetPager>
2 ) aspx.cs 后台绑定数据
#region 绑定数据列表
protected override void BindData()
{
string where = "";
if (!string.IsNullOrEmpty(txtKeyWord.Text.Trim()) && txtKeyWord.Text.Trim() != TextBoxWatermarkExtender1.WatermarkText)
{
where += " UserName like '%" + txtKeyWord.Text.Trim() + "%' ";
}
DataSet ds = DalHelper.Accounts_Users.UserGetList(this.AspNetPager1.PageSize, this.AspNetPager1.CurrentPageIndex - 1, where);
int recountCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
AspNetPager1.RecordCount = recountCount;
DataList1.DataSource = ds.Tables[0].DefaultView;
DataList1.DataBind();
}
#endregion
3 ) DAL数据访问层的方法
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet UserGetList(int PageSize, int PageIndex, string strWhere)
{
SqlParameter[] parameters = {
new SqlParameter("@TableNames", SqlDbType.VarChar, 200),
new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 100),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@CurrentPage", SqlDbType.Int),
new SqlParameter("@Order", SqlDbType.VarChar, 200),
new SqlParameter("@Fields", SqlDbType.VarChar, 200),
new SqlParameter("@Filter", SqlDbType.VarChar, 1000),
new SqlParameter("@Group", SqlDbType.VarChar, 200)
};
parameters[0].Value = " Accounts_Users ";
parameters[1].Value = " UserID ";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = " Accounts_Users.UserID DESC ";
parameters[5].Value = "";
parameters[6].Value = strWhere;
parameters[7].Value = "";
return DbHelperSQL.RunProcedure("UP_GetRecordByPage", parameters, "ds");
}
4 ) 数据库的调用存储过程
ALTER PROCEDURE [dbo].[UP_GetRecordByPage]
@TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空
@Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页，0表示第1页
@Filter VARCHAR(200) = '', --条件，可以为空，不用填 where
@Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'Where 1=1'
ELSE
SET @Filter = 'Where ' + @Filter
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group
IF @Order <> ''
BEGIN
DECLARE @pos1 INT, @pos2 INT
SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
IF CHARINDEX(' DESC', @Order) > 0
IF CHARINDEX(' ASC', @Order) > 0
BEGIN
IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
SET @Operator = '<='
ELSE
SET @Operator = '>='
END
ELSE
SET @Operator = '<='
ELSE
SET @Operator = '>='
SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
SET @pos1 = CHARINDEX(',', @SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX('.', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = '>='
END
DECLARE @type varchar(50)
DECLARE @prec int
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 @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC('
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
Select @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' orDER BY ' + @Order + '
declare @RecordCount int
select @RecordCount = count(1) from ' + @TableNames + ' ' + @Filter + ' ' + @Group +'
SET ROWCOUNT ' + @PageSize + '
Select ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' orDER BY ' + @Order + '
select @RecordCount
')
END
