【ASP.Net】存数过程分页

前台调用：先引用组件：<%@ 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;
        }

sp_PageView.sql

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]
GO

SET 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