asked on

fetch data from the SQL and displays in the gridview using pagination in Asp.net

public DataSet GetNotStartedSearchCompliance()
        {
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@UserName", UserName);
            param[1] = new SqlParameter("@ActID", ActID);
            param[2] = new SqlParameter("@UnitID", UnitID);
            param[3] = new SqlParameter("@Month", Month);
            param[4] = new SqlParameter("@Year", Year);
            param[5] = new SqlParameter("@Search", Search);
            param[6] = new SqlParameter("@CountryID", CountryID);
            return objHelper.GetDataSet("USP_GetNotStarted_Search_ComplianceAssessment", param);
        }

Getting the resultset from the procedure and having in a dataset and then directly binding to the Gridview.
When the resultset gives some 200/300 records ,where in the Gridview 10 records per page will be appeared and so we would get 20 pages(for 200 records) which is taking huge amount of time .Any alternate solution like binding only 10 records in the first page and clicking on the second record should fetch another 10 records which will increase our performance.

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kyle Abrahams, PMP

Are you fetching the data source again every time?

It should bind relatively quickly if you're only having 10 records per page.

Please see below to compare your code to the proper way to do paging:
http://www.c-sharpcorner.com/UploadFile/rohatash/gridview-paging-sample-in-Asp-Net/

mannevenu26

ASKER

I have cleared that how to write a procedure that displays records per page but my doubt is how to pass the pageindex and page size from the three tired architecture to my stored prcodure.Where will I get pageIndex and Pagesize.Please find my sample code below

 private void BindNotStartedCompliance()
    {
        DataSet NotStartedDataSet = new DataSet();
        NotStartedDataSet = null;
        try
        {
 objTransaction.UserName = Session["UserID"].ToString();
            objTransaction.ActID = Convert.ToInt32(ddlActs.SelectedValue.ToString());
            objTransaction.UnitID = Convert.ToInt32(ddlUnit.SelectedValue.ToString());
            objTransaction.Month = Convert.ToInt32(ddlMonth.SelectedValue);
            objTransaction.Year = Convert.ToInt32(ddlYear.SelectedValue);
            objTransaction.CountryID = Convert.ToInt32(Session["CountryID"].ToString());

            if (txtSearch.Text.Trim() != String.Empty)
            {
                objTransaction.Search = txtSearch.Text.Trim();
                NotStartedDataSet = objTransaction.GetAllNotStartedSearchCompliance();
                NotStartedDataSet.Tables[0].DefaultView.RowFilter = "UnitId = '" + ddlUnit.SelectedValue + "'";
            }
            else
            {
                NotStartedDataSet(gridview) = objTransaction.GetAllNotStartedCompliance();
            }
}
catch()
{
}
my Business Logic
-----------------------------
 public DataSet GetAllNotStartedCompliance()
        {
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@UserName", UserName);
            param[1] = new SqlParameter("@ActID", ActID);
            param[2] = new SqlParameter("@UnitID", UnitID);
            param[3] = new SqlParameter("@Month", Month);
            param[4] = new SqlParameter("@Year", Year);
            param[5] = new SqlParameter("@CountryID", CountryID);
            return objHelper.GetDataSet("USP_GetAllNotStartedComplianceAssessment", param);
        }
Onclicking of pageIndex :
--------------------------------------

 protected void gvNotStarted_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvNotStarted.PageIndex = e.NewPageIndex;
     
            BindNotStartedCompliance();
        
     }
    }

Open in new window

mannevenu26

ASKER

Created the procedure with two extra params like pageIndex and page size which gives 10 records successfully.
But in the Grid it is only 10 those 10 records without pagination then how can I click the 2nd page to get 11 to 20 records.

Shaun Kline

To handle paging using the SQL Server method, you will need to perform a query that returns the count of rows that your query should have and store that count. From there, you would need to handle paging through custom code. This method is best used when your query returns 1000s of rows. Here is an article from MSDN that explains it: https://msdn.microsoft.com/en-us/library/bb445504.aspx

If your are only returning a small set of rows, you should consider Kyle's response to your question.