ASP.NET存储过程分页

ASP.NET存储过程分页

存储过程：

createPROCEDURE pagelist
@tablenamenvarchar(50),
@fieldnamenvarchar(50)="*",
@pagesizeint,--每页显示记录条数
@currentpageint,--第几页
@orderidnvarchar(50),--主键排序
@sortint,--排序方式，1表示升序，0表示降序排列
@rowcountint output,--总记录数，共有几条信息
@pagecountint output--总页数，共有多少页
AS
declare@countsqlnvarchar(50)
declare@sqlnvarchar(200)
declare@subsqlnvarchar(100)--notin子sql语句
declare@tmpOrderidnvarchar(50)
--返回总记录数，并赋值给输出参数@rowcount
set@countsql="select@totalcount=count(*) from "+@tablename
exec sp_executesql@countsql,N"@totalcountint out",@rowcount output

--判断字段名是否为空
if@fieldnameisnullor@fieldname=""
set@fieldname="* "

--判断是否排序及排序方式
if@orderidisnullor@orderid=""
set@tmpOrderid=""
else
begin
if@sort=0
set@tmpOrderid="orderby "+@orderid+" desc"
else
set@tmpOrderid="orderby "+@orderid+" asc"
end

--计算页数
if@rowcount%@pagesize>0
set@pagecount=(@rowcount/@pagesize)+1;
else
set@pagecount=@rowcount/@pagesize;

--分页算法实现
set@subsql="selecttop"+str(@pagesize*(@currentpage-1))+" "+@orderid+"from "+@tablename+" "+@tmpOrderid
set@sql="selecttop"+str(@pagesize)+" "+@fieldname+"from "+@tablename+" where "+@orderid+"not in ("+@subsql+")"+@tmpOrderid
exec(@sql)

 

.cs文件：

protectedvoid Page_Load(object sender,EventArgs e)
    {
        int CurrentPage,RowCount,PageCount;
        int PageSize =2;
        if (Request["CurrentPage"] ==null||Convert.ToInt32(Request["CurrentPage"])<1)
        {
            CurrentPage=1;
        }
        else
        {
            CurrentPage=Convert.ToInt32(Request["CurrentPage"]);
        }

        //数据库操作
       SqlConnection sqlconn = newSqlConnection(ConfigurationManager.AppSettings["ConnStr"].ToString());
        SqlCommand cmd = newSqlCommand("pagelist", sqlconn);
        cmd.CommandType =CommandType.StoredProcedure;
        cmd.Connection = sqlconn;
        SqlParameter[] prams ={
                               newSqlParameter("@tablename",SqlDbType.NVarChar,50),
                               newSqlParameter("@fieldname",SqlDbType.NVarChar,50),
                               newSqlParameter("@pagesize",SqlDbType.Int),
                               newSqlParameter("@currentpage",SqlDbType.Int),
                               newSqlParameter("@orderid",SqlDbType.NVarChar,50),
                               newSqlParameter("@sort",SqlDbType.Int),
                               newSqlParameter("@rowcount",SqlDbType.Int),
                               newSqlParameter("@pagecount",SqlDbType.Int)};
        prams[0].Value ="news";//表名
       prams[1].Value ="*";//字段名
       prams[2].Value =PageSize;//每页显示条数
       prams[3].Value=CurrentPage;//当前页数
       prams[4].Value ="id";//主键
       prams[5].Value =1;//排序方式,0表示降序，1表示升序
       prams[6].Direction =ParameterDirection.Output;//总记录数
       prams[7].Direction =ParameterDirection.Output;//总页数
       foreach(SqlParameterpramin prams)
        {
            cmd.Parameters.Add(pram);
        }
        sqlconn.Open();
        SqlDataAdapter sda = newSqlDataAdapter();
        DataSet ds = newDataSet();
        sda.SelectCommand = cmd;
        sqlconn.Close();
        sda.Fill(ds);
        //数据库操作结束

        RowCount = (int)cmd.Parameters["@rowcount"].Value;
        PageCount =(int)cmd.Parameters["@pagecount"].Value;
       
        if(CurrentPage>PageCount)
        {
            Response.Redirect("CutPage.aspx?CurrentPage="+Convert.ToString(PageCount));
            Response.End();
        }
        this.lblCurrent.Text= Convert.ToString(CurrentPage);
        this.lblPageTotal.Text= Convert.ToString(PageCount);
        this.lblRowsTotal.Text= Convert.ToString(RowCount);
        this.lblPageSize.Text= Convert.ToString(PageSize);

        this.hlFirst.NavigateUrl="CutPage.aspx?CurrentPage=1";
        this.hlPrev.NavigateUrl="CutPage.aspx?CurrentPage=" + Convert.ToString(CurrentPage-1);
        this.hlNext.NavigateUrl="CutPage.aspx?CurrentPage=" + Convert.ToString(CurrentPage+1);
        this.hlLast.NavigateUrl="CutPage.aspx?CurrentPage=" + Convert.ToString(PageCount);
        if(Convert.ToInt32(CurrentPage) ==1)
        {
            this.hlPrev.Enabled =false;
            this.hlFirst.Enabled=false;
        }
        if(Convert.ToInt32(CurrentPage) == PageCount)
        {
            this.hlNext.Enabled=false;
            this.hlLast.Enabled=false;
        }

        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
    }

aspx文件：

<asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        </div>
        第<asp:Label ID="lblCurrent" runat="server"></asp:Label>页 | 每页<asp:LabelID="lblPageSize"
            runat="server"></asp:Label>条 | 共有<asp:LabelID="lblPageTotal" runat="server"></asp:Label>页| 共有<asp:Label ID="lblRowsTotal"
            runat="server"></asp:Label>条信息 |
        <asp:HyperLink ID="hlFirst" runat="server">首页</asp:HyperLink>
       |
        <asp:HyperLink ID="hlPrev" runat="server">上一页</asp:HyperLink>
        |
        <asp:HyperLink ID="hlNext" runat="server">下一页</asp:HyperLink>
        |
        <asp:HyperLink ID="hlLast" runat="server">尾页</asp:HyperLink><br/>