前台调用:先引用组件:<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
(组件下载:AspNetPager.dll ASPNetPagerHelper.dll)
然后应用组件:
<webdiyer:AspNetPager ID="AspNetPager_log" CssClass="manu" runat="server" PageSize="20"
SubmitButtonStyle="" InputBoxStyle=""
SubmitButtonText="" NumericButtonTextFormatString="{0}"
TextBeforeInputBox="转到第" TextAfterInputBox="页" NumericButtonCount="5"
AlwaysShow="True" ShowInputBox="Always" ShowFirstLast="false"
OnPageChanged="AspNetPager_log_PageChanged" FirstPageText="首页"
LastPageText="尾页" NextPageText="下一页 >" PrevPageText="< 上一页" CustomInfoHTML=""
PageIndexBoxStyle=""
CurrentPageButtonClass="current" TextAfterPageIndexBox=""
TextBeforePageIndexBox="" HorizontalAlign="Center">
</webdiyer:AspNetPager>
后台应用:
protected void AspNetPager_log_PageChanged(object sender, EventArgs e)
{
DBind();
}
private void DBind()
{
int totalrecordscount = 0;
DataTable dt = 数据对象.GetList(AspNetPager_log.CurrentPageIndex, AspNetPager_log.PageSize, "select * from 表 where 条件 排序", out totalrecordscount).Tables[1];
if (dt.Rows.Count > 0)
{
this.Repeater1.DataSource = dt.DefaultView;
ASPNetPagerHelper.ASPNetPager.AspNetPagerSetting(AspNetPager_log, totalrecordscount);
Repeater1.DataBind();
}
}
列表函数:
public DataSet GetList(Int32 iPageCurr, Int32 iPageSize, string sql, out Int32 totalRecords)
{
SqlParameter[] parameters = {
new SqlParameter("@RecordCount", SqlDbType.Int),
new SqlParameter("@PageCurrent", SqlDbType.Int),
new SqlParameter("@Pagesize", SqlDbType.Int),
new SqlParameter("@sql", SqlDbType.VarChar,1000),
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = iPageCurr;
parameters[2].Value = iPageSize;
parameters[3].Value = sql;
DataSet ds = DbHelperSQL.RunProcedure("sp_PageView", parameters, "ds");//存数过程名:sp_PageView (下面将有源码)
totalRecords = Convert.ToInt32(parameters[0].Value.ToString());
//返回DataTable的结果
return ds;
}
执行存数过程方法:
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[sp_PageView]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[sp_PageView]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROC sp_PageView
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@RecordCount int OUTPUT --总记录数
AS
SET NOCOUNT ON
DECLARE @p1 int
DECLARE @PageCount int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@RecordCount OUTPUT--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@RecordCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标
EXEC sp_cursorclose @p1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO