How to paginate a gridview and SQL custom query with ROW_NUMBER

Question

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.

naval · Accepted Answer

this code in aspx page

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)


        }

How to paginate a gridview and SQL custom query with ROW_NUMBER

Answers (1)

Related Questions