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

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">
             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">

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);
            ErrorCount = 0;
            Searchpattern = "";

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

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

               if(ObjTotalRecords.Value!= DBNull.Value || ObjTotalRecords!=null)

            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

/****** Object:  StoredProcedure [dbo].[ADMJGetCollegeSearch]    Script Date: 06/06/2012 15:43:29 ******/

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

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


at last you will bind the the gridview
grdCourse.DataSource = DS.Tables[0];
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 or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply