Load list after page load

What i have to do is to show a list of items from database, the list will have pagination and just 5 items will apper at a time. The problem is that in db are very many items and to load all of them it takes very much time.

Is there a way to load and show 5 items first time when the page is loaded, and after that, (in backend?) somehow to append the others in the same list? I need all loaded at the end.

You can use custom pagination for your purpose.

Check here http://msdn.microsoft.com/en-us/library/bb445504.aspx

Thanks

Hi seiko,

According to your description, I suggest you to paging you data use the T-SQL, and then display it to your client.

There have a sample, please try to refer to it:

In the aspx code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PagingGridView.aspx.cs" Inherits="PagingGridView" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    
    	<table align="center">
			<tr>
					<td align="center">
						<table align="center">
							<tr>
								<td vAlign="top">
									<asp:imagebutton id="PageSizeImb" Runat="server" ImageAlign="AbsBottom" ImageUrl="ImagesPageSize.gif"
										ToolTip="Set page size" OnClick="PageSizeImb_Click"></asp:imagebutton>&nbsp;&nbsp;
									<asp:textbox id="PageSizeTxt" Runat="server" Columns="2" Text="3" Font-Bold="True"></asp:textbox>&nbsp;
									<asp:imagebutton id="FirstPage" Runat="server" ImageAlign="AbsBottom" ImageUrl="Images/first.gif"
										ToolTip="First" CommandName="First" OnCommand="NavigationLink_Click"></asp:imagebutton>
									<asp:imagebutton id="PreviousPage" Runat="server" ImageAlign="AbsBottom" ImageUrl="Images/previous.gif"
										ToolTip="Previous" CommandName="Prev" OnCommand="NavigationLink_Click"></asp:imagebutton>&nbsp;&nbsp;
									<asp:imagebutton id="GoToPageImb" Runat="server" ImageAlign="AbsBottom" ImageUrl="ImagesGoToPage.gif"
										ToolTip="Go to page number" OnClick="GoToPageImb_Click"></asp:imagebutton>&nbsp;
									<asp:textbox id="GoToPageTxt" runat="server" Width="30px" Text="1" Font-Bold="True"></asp:textbox>&nbsp;
									<asp:imagebutton id="NextPage" Runat="server" ImageAlign="AbsBottom" ImageUrl="Images/next.gif" ToolTip="Next"
										CommandName="Next" OnCommand="NavigationLink_Click"></asp:imagebutton>
									<asp:imagebutton id="LastPage" Runat="server" ImageAlign="AbsBottom" ImageUrl="Images/last.gif" ToolTip="Last"
										CommandName="Last" OnCommand="NavigationLink_Click"></asp:imagebutton>&nbsp;&nbsp;
                                    <asp:Label ID="CurrentPage" runat="server" Text="1" Font-Bold="True"></asp:Label>
									<asp:label id="SepLbl" runat="server" Font-Size="X-Small" CssClass="pageLinks" Enabled="False">/</asp:label>
                                    &nbsp;<asp:Label ID="TotalPages" runat="server" Font-Bold="True">1</asp:Label>&nbsp;
								</td>
							</tr>
						</table>
					</td>
			</tr>
			

        <tr>
           <td align=center>
           <asp:GridView  ID="GridView1" runat="server" AutoGenerateColumns="False" >
            <Columns>
                <asp:BoundField DataField="RowNumber" ItemStyle-HorizontalAlign = "Left" HeaderText="RowNumber" />
                <asp:BoundField DataField="Name" ItemStyle-HorizontalAlign= "Left" HeaderText="Name" />
                <asp:BoundField DataField="SalesYTD" ItemStyle-HorizontalAlign = "Left" HeaderText="SalesYTD" />
                <asp:BoundField DataField="PostalCode" ItemStyle-HorizontalAlign = "Left" HeaderText="PostalCode" />
            </Columns>
               <HeaderStyle BackColor="BurlyWood" />
               <RowStyle BackColor="Wheat" />
               <AlternatingRowStyle BackColor="LightGray" />
               
            </asp:GridView>
        </td>
 
    </tr>
    </table>
    </form>
</body>
</html>

In the aspx.cs code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class PagingGridView : System.Web.UI.Page
{

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

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

    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            PageNum = 1;
            PageSize = 3;
            BindGridView();
        }

    }

    private string ConnectionString
    {
        get { return @"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True"; }
    }

    private void BindGridView()
    {

        DataSet objDs = new DataSet();
        System.Data.SqlClient.SqlConnection
        myConnection = new SqlConnection(ConnectionString);
        System.Data.SqlClient.SqlDataAdapter myCommand;
        myCommand = new System.Data.SqlClient.SqlDataAdapter("sp_GridView_RowNumber", myConnection);
        myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

        // PageNum
        myCommand.SelectCommand.Parameters.Add(new SqlParameter("@PageNum", SqlDbType.Int, 4));
        myCommand.SelectCommand.Parameters["@PageNum"].Value = PageNum;

        // PageSize
        myCommand.SelectCommand.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4));
        myCommand.SelectCommand.Parameters["@PageSize"].Value = PageSize;

        // TotalRowsNum
        SqlParameter TotalRowsNum = new SqlParameter("@TotalRowsNum", SqlDbType.Int);
        TotalRowsNum.Value = null;
        TotalRowsNum.Direction = ParameterDirection.Output;
        myCommand.SelectCommand.Parameters.Add(TotalRowsNum);
        myCommand.SelectCommand.Parameters["@TotalRowsNum"].Value = null;

        myConnection.Open();
        myCommand.Fill(objDs);

        GridView1.DataSource = objDs;
        GridView1.DataBind();

        Navigation(Convert.ToInt32(TotalRowsNum.Value.ToString()));

    }

    private void Navigation(int totalRecords)
    {
        Double totalPages = Math.Ceiling(((double)totalRecords / PageSize));

        if ((totalRecords == 1) || (totalPages == 0))
        {
            totalPages = 1;
        }

        if (PageSize > totalRecords)
        {
            PageSize = (int)totalPages;
        }

        GoToPageTxt.Text = PageNum.ToString();
        CurrentPage.Text = PageNum.ToString();
        TotalPages.Text =  totalPages.ToString();
    }


    protected void NavigationLink_Click(Object sender, CommandEventArgs e)
    {
        switch (e.CommandName)
        {
            case "First":
                PageNum = 1;
                break;
            case "Last":
                PageNum = Convert.ToInt16(TotalPages.Text);
                break;
            case "Next":
                PageNum = Convert.ToInt16(CurrentPage.Text) + 1;
                break;
            case "Prev":
                PageNum = Convert.ToInt16(CurrentPage.Text) - 1;
                break;
        }
        BindGridView();
    }

    protected void PageSizeImb_Click(object sender, ImageClickEventArgs e)
    {
        PageNum = 1;
        PageSize = Convert.ToInt16(PageSizeTxt.Text);
        BindGridView();
    }


    protected void GoToPageImb_Click(object sender, ImageClickEventArgs e)
    {
        if (GoToPageTxt.Text != "")
        {
            int maxPage  = Convert.ToInt32(TotalPages.Text);
            int goToPage = Convert.ToInt32(GoToPageTxt.Text);

            if (goToPage <= maxPage)
            {
                PageNum = goToPage;
                BindGridView();
            }
        }
    }


}

In the sql code:

USE [AdventureWorks]
GO
/****** Object:  StoredProcedure [dbo].[sp_GridView_RowNumber]    Script Date: 02/05/2006 10:56:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Socrate
-- Create date: 2/5/2006
-- Description:	implements ROW_NUMBER function
-- =============================================
CREATE PROCEDURE [dbo].[sp_GridView_RowNumber]
(
	@PageNum int,
	@PageSize int,
	@TotalRowsNum int output
)
AS

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

	-- Use ROW_NUMBER function
	WITH Salespeople_AdventureWorks As
	(
		SELECT 'RowNumber' = ROW_NUMBER() OVER(ORDER BY SalesYTD DESC), 'Name' = c.FirstName + ' ' + c.LastName, s.SalesYTD, a.PostalCode
		FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
		JOIN Person.Address a ON a.AddressID = c.ContactID
		WHERE ((TerritoryID IS NOT NULL) AND (s.SalesYTD <> 0))
	)


	-- Query result
	SELECT * 
	FROM Salespeople_AdventureWorks
	WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize             
	ORDER BY SalesYTD DESC

	-- Returns total records number
	SELECT @TotalRowsNum = count(*) 
	FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
	JOIN Person.Address a ON a.AddressID = c.ContactID
	WHERE ((TerritoryID IS NOT NULL) AND (s.SalesYTD <> 0))

END


You can also refer to the article below:
http://www.codeproject.com/Articles/12962/Paging-GridView-with-ROW_NUMBER

Hope it can help you.

If there anything is unclear, please let me know.

Best Regards,
Terry Guo

The pagination is not an option for me. I need them to be faster than pagination system.

Leave a Reply