ASP.NET(c#)GRIDVIEW+SQL存储过程分页

这阵子接了一个单子，客户说数据量比较大，在做后台的时候就想到，传统的分页在数据少时候到是没什么，一旦上100W甚至1000W时候就慢了。于是就弄了一个GridView+SQL存储过程分页，代码如下：
    首先在SQL数据库里的存储过程中新建一个存储过程，在文本中输入以下代码：
CREATE PROCEDURE page_cut
(@TAB NVARCHAR(50),
@FILEDS NVARCHAR(300),
@SORTSTR NVARCHAR(300),
@CURRENTPAGE INT,
@PAGESIZE INT)
AS
DECLARE @EXECSQL NVARCHAR(500)
SELECT @EXECSQL = 'SELECT TOP '+CAST(@PAGESIZE AS NVARCHAR(10))+' '+@FILEDS+' FROM ['+@TAB+'] '
IF @SORTSTR != ''
   BEGIN
      SELECT @EXECSQL=@EXECSQL+'WHERE '+@SORTSTR
   END
IF @CURRENTPAGE = 1
   BEGIN
      SELECT @EXECSQL=@EXECSQL+'ORDER BY [id] DESC'
   END
ELSE
   IF @SORTSTR = ''
      BEGIN
         SELECT @EXECSQL=@EXECSQL+'WHERE [id]<(SELECT MIN(id) FROM ['+@TAB+'] WHERE [id] IN(SELECT
         TOP '+CAST((@CURRENTPAGE-1)*@PAGESIZE AS NVARCHAR(30))+' [id] FROM ['+@TAB+'] ORDER BY
         DESC)) ORDER BY [id] DESC'
      END
   ELSE
      BEGIN
         SELECT @EXECSQL=@EXECSQL+' AND [id]<(SELECT MIN(id) FROM ['+@TAB+'] WHERE [id] IN(SELECT
         TOP '+CAST((@CURRENTPAGE-1)*@PAGESIZE AS NVARCHAR(30))+' [id] FROM ['+@TAB+'] WHERE '+@SORTSTR+'
         ORDER BY [id] DESC) AND '+@SORTSTR+') ORDER BY [id] DESC'
      END
EXEC(@EXECSQL)
GO
调用方法："exec page_cut '表','字段','条件',当前页,每页显示数量"
    在a.aspx中新建一个,然后在.cs中绑定它,把GridView的AllowPaging设为True,PageSize设一下,GridView中由于如果只有一页，那么就不会显示页码栏，所心我们就用一个LABEL来显示分页，然后在GridView下新增一个LABEL，ID为"ShowPager".
    我这假设是用的SQLSERVER200，定义一个函数：
private void GridView_Bind(int currentpage)
{
        if(currentpage < 1) currentpage=1;
        string connString = "Server=(Local);User Id=sa;Password=1;DataBase=db1";
        SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        string sql = "exec page_cut '表','字段','条件',"+currentpage.ToString()+","+GridView.PageSize.ToString();
        SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        GridView.DataSource = ds;
        GridView.DataBind();
        ds.Dispose();
        sda.Dispose();
        sql = "select count(id) from 表";
        SqlCommand cmd = new SqlCommand(sql,conn);
        SqlDataReader sdr = cmd.ExecuteReader();
        int i = 0;
        if(sdr.Read()) i = sdr.GetInt32(0);
        sdr.Close();
        ShowPager.Text = CutPage_Numeric(CurrentPage, i, GridView.PageSize, "GridView");
        conn.Close();
}
    选择GRIDVIEW的PageChanging事件双击代码如下:
    protected void gvList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView.PageIndex = e.NewPageIndex;
        GridView_Bind(e.NewPageIndex+1);
    }
定义CutPage_Numeric方法：
    public string CutPage_Numeric(int currentpage, int totalnum, int pagesize,string obj)
    {
        string resultstr = null;
        int _PageCount,_cutPoint;
        _cutPoint = 10;
        if ((totalnum % pagesize) > 0)
            _PageCount = totalnum / pagesize + 1;
        else
            _PageCount = totalnum / pagesize;
        if (currentpage == 1)
        {
            resultstr += "[ 首页 ]  [ 上一页 ]  ";
            for (int i = 1; i <= _cutPoint; i++)
            {
                if (i > _PageCount) break;
                if (i == currentpage)
                    resultstr += i.ToString() + " ";
                else
                    resultstr += "<a href=/"javascript:__doPostBack('" + obj + "','Page$" + i.ToString() + "')/">" + i.ToString() + "</a>  ";
            }
        }
        else
        {
            resultstr += "[ <a href=/"javascript:__doPostBack('" + obj + "','Page$1')/">首页</a> ] &nbsp";
            resultstr += "[ <a href=/"javascript:__doPostBack('" + obj + "','Page$" + (currentpage - 1) + "')/">上一页</a> ] &nbsp";
        }
        if (currentpage > 1 && currentpage < _cutPoint)
        {
            for (int i = 1; i <= _cutPoint + currentpage; i++)
            {
                if (i > _PageCount) break;
                if (i == currentpage)
                    resultstr += i.ToString() + " ";
                else
                    resultstr += "<a href=/"javascript:__doPostBack('" + obj + "','Page$" + i.ToString() + "')/">" + i.ToString() + "</a>  ";
            }
        }
        if(currentpage >= _cutPoint)
        {
            for (int i = currentpage - _cutPoint + 1; i <= currentpage + _cutPoint; i++)
            {
                if (i > _PageCount) break;
                if (i == currentpage)
                    resultstr += i.ToString() + " ";
                else
                    resultstr += "<a href=/"javascript:__doPostBack('" + obj + "','Page$" + i.ToString() + "')/">" + i.ToString() + "</a>  ";
            }
        }
        if (currentpage < _PageCount)
        {
            resultstr += "[ <a href=/"javascript:__doPostBack('" + obj + "','Page$" + (currentpage + 1) + "')/">下一页</a> ] &nbsp";
            resultstr += "[ <a href=/"javascript:__doPostBack('" + obj + "','Page$" + _PageCount + "')/">尾页</a> ] &nbsp";
        }
        else
        {
            resultstr += "[ 下一页 ]  [ 尾页 ]  ";
        }
        resultstr = "<div style=/"width:100%;height:20px;padding-top:5px;text-align:center;/">" + resultstr + "</div>";
        return resultstr;
    }
OK，再在a.aspx的代码<%@ Page 后加上EnableEventValidation="false"就行了！
    当然，在这里我只是做了一个粗略的介绍，有兴趣大家一起交流！