How to paginate a gridview and SQL custom query with ROW_NUMBER

I have a page that executes a custom query that its saved somewhere on the database.
I need to be able to enable pagination on the gridview.

For example purposes the query saved on the database its like this:

select * from requestbases

This returns 10,000 rows.

With the method below I make it return 10 rows.

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"];
   }
}

And this is the code to bind the grid

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();
}

The problem is that the query itself returns 10 rows, so the gridview will never show a pager.

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

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

this code in aspx page

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

Here the method that will use in the .cs page
This is used to track the record of the pagenum and 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; }
    }

This is the method is used for the call the store procedure that will send the 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;
        }

that will return the dataset that will used to bind the store procedure

here the store procedure that will used for pagination

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

at last you will bind the the gridview
grdCourse.DataSource = DS.Tables[0];
grdCourse.DataBind();
grdCourse.Visible = true;

at the PageIndexChanging(object sender, GridViewPageEventArgs e) of the grid view you will pass the

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

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


        }

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply