如何使用ROW_NUMBER对gridview和SQL自定义查询进行分页

Question

我有一个页面，它执行一个自定义查询，该查询已保存在数据库中的某个位置。我需要能够在gridview上启用分页。

例如，查询保存在数据库中的方式如下：

select * from requestbases

这将返回10,000行。

使用下面的方法，我使它返回10行。

public DataTable GetGenericResults(string strsql, int pageIndex)
{
   StringBuilder sb = new StringBuilder();
   sb.Append("WITH MyPagedData as ( ");
   int indexFrom = strsql.IndexOf("from");
   sb.Append(strsql.Substring(0, indexFrom));
   sb.Append(", ");
   sb.Append("ROW_NUMBER() OVER(ORDER BY RequestBaseId DESC) as RowNum ");
   sb.Append(strsql.Substring(indexFrom));
   sb.Append(") ");
   sb.Append("SELECT * from MyPagedData where RowNum between @StartIndex and @StartIndex + 10");

   using(var connection = (SqlConnection)_context.Database.Connection)
   {
      var adapter = new SqlDataAdapter(sb.ToString(), connection);
      adapter.SelectCommand.Parameters.Add("@StartIndex", SqlDbType.Int).Value = pageIndex;
      var results = new DataSet();
      adapter.Fill(results, "Results");
      return results.Tables["Results"];
   }
}

这是绑定网格的代码

var datatable = RequestBaseBL.GetGenericResults(query.QuerySql, 0);

if (datatable.Rows.Count > 0)
{
    LblCount.Text = datatable.Rows.Count + " records";
    PanelResults.Visible = true;
    GrvCustomResults.Visible = true;
    GrvCustomResults.DataSource = datatable;
    GrvCustomResults.DataBind();
}

问题在于查询本身返回10行，因此gridview将永远不会显示寻呼机。

<asp:GridView ID="GrvCustomResults" runat="server" Visible="false" AutoGenerateColumns="true">
   <PagerSettings  
             Visible="true"  
             Position="TopAndBottom"  
             PreviousPageText="Previous"  
             NextPageText="Next"  
             Mode="NumericFirstLast" />  
   <HeaderStyle CssClass="gridheader" />

Answer 1

此代码在aspx页面

<asp:Panel runat="server" id="pnlPager" CssClass="pager">
   </asp:Panel>

在此，将在.cs页中使用的方法。该方法用于跟踪pagenum和pagesize的记录

      protected int PageNum
        {
            get { return Convert.ToInt16(ViewState["PageNum"]); }
            set { ViewState["PageNum"] = value; }
        }

        protected int PageSize
        {
            get { return Convert.ToInt16(ViewState["PageSize"]); }
            set { ViewState["PageSize"] = value; }
        }


protected int TotalRecord
    {
        get { return Convert.ToInt16(ViewState["TotalRecords"]); }
        set { ViewState["TotalRecords"] = value; }
    }

这是用于调用存储过程的方法，该过程将发送pagenum，page size

public DataSet GetCollegeSerachData(int PageNum,int PageSize,out int TotalRecords)
        {
            DS = new DataSet();
            ObjDataWrapper = new DataWrapper(ClsCommon.CnnString, CommandType.StoredProcedure);
            TotalRecords=0;
            ErrorCount = 0;
            Searchpattern = "";
            try
            {


                ObjDataWrapper.AddParameter("@PageNum", PageNum);
                ObjDataWrapper.AddParameter("@PageSize", PageSize);

                SqlParameter ObjTotalRecords=(SqlParameter)(ObjDataWrapper.AddParameter("@TotalRowsNum","",SqlDbType.Int,ParameterDirection.Output));



                DS=ObjDataWrapper.ExecuteDataSet("ADMJGetCollegeSearch");
               if(ObjTotalRecords.Value!= DBNull.Value || ObjTotalRecords!=null)
               {
                   TotalRecords=Convert.ToInt32(ObjTotalRecords.Value);
               }

            }
            catch (Exception Ex)
            {
                string err = Ex.Message;
                if (Ex.InnerException != null)
                {
                    err = err + " :: Inner Exception :- " + Ex.InnerException.Message;
                }
                string addInfo = "Error While Executing GetCollegeSerachData in ClsColleges:: -> ";
                ClsExceptionPublisher objPub = new ClsExceptionPublisher();
                objPub.Publish(err, addInfo);
            }
            return DS;
        }

将返回用于绑定存储过程的数据集

这是用于分页的存储过程

GO
/****** Object:  StoredProcedure [dbo].[ADMJGetCollegeSearch]    Script Date: 06/06/2012 15:43:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ADMJGetCollegeListByState]
@PageNum int,
@PageSize int,
@TotalRowsNum int  output


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.



    WITH College_CollegeId As
    (
        SELECT 'RowNumber' = ROW_NUMBER() OVER(ORDER BY collegeid asc),College.*
        FROM College

    )


    -- Query result
    SELECT * 
    FROM College_CollegeId
    WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize             
    ORDER BY collegename asc

    -- Returns total records number
    SELECT @TotalRowsNum = count(*) 
    FROM College

END

最后，您将绑定gridview grdCourse.DataSource = DS.Tables [0];。grdCourse.DataBind（）;
grdCourse.Visible = true;

在网格视图的PageIndexChanging（对象发送者，GridViewPageEventArgs e）处，您将传递

protected void grdCourse_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
           Pagenum = e.NewPageIndex;

          --call this method public DataSet GetCollegeSerachData(int PageNum,int PageSize,out int TotalRecords)


        }