CODEWITHSUNDEEP
asp.netasp.net-mvcpaginationasp.net-ajax
mko
mko

Reputation: 7335

Paging results in asp.net

I am trying to find a solution in asp.net in order to achieve paging of data so 1) Application does not have to connect to DB on each page index change, in order to retrieve either entire set or just a paged set of results 2) Do not want to store results in Viewstate or Session

For large result sets, I was thinking of storing key values (ID, rownumber) in a result set (array, json) and then populate additional row information on row binding.

Is there a way to do it in asp.net, or I need to consider jQuery for this scenario.

Upvotes: 3

Views: 2004

Answers (3)

igz168
igz168

Reputation: 94

For paged datasets you're generally have 2 options:

  • if you're expecting a small result set (i.e. limit search results to 500 rows or less) then using viewstate is perfectly acceptable. I personally prefer to avoid using session for storing request level data

    • If you're expecting a large result set then you'll need a paged data source. You can write a stored proc that returns a paged dataset (i.e. passing a page index/page size) or use any of the ORM's (ENTITY framework or LINQ TO SQL). Either support paging with take(n) and skip(n).

http://www.mssqltips.com/sqlservertip/1699/sql-server-stored-procedures-to-page-large-tables-or-queries/

http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/sorting-filtering-and-paging-with-the-entity-framework-in-an-asp-net-mvc-application

Regardless of how you render the data (jQuery/ajax or server control) you still have to either get the whole result set in one shot and cache it for subsequent requests (json array or viewstate), or you need a data source the supports paging. If you're concerned with performance then a paged data source is the way to go (the EF example will work just fine for webforms as well, just use your event handlers instead of action methods in mvc).

Upvotes: 1

Jupaol
Jupaol

Reputation: 21365

1) Application does not have to connect to DB on each page index change, in order to retrieve either entire set or just a paged set of results

You need to cache your results. For example:

Example:

Caching Approach 1

    <asp:SqlDataSource runat="server" ID="sdsd"
        SelectCommand="select * from jobs"
        SelectCommandType="Text"
        DataSourceMode="DataSet"
        ConnectionString="<%$ConnectionStrings: PUBSConnectionString %>"
        CacheDuration="30"
        EnableCaching="true"
    >
    </asp:SqlDataSource>
    <asp:DropDownList ID="ddl" runat="server" AutoPostBack="true" DataSourceID="sdsd" DataTextField="job_desc" DataValueField="job_id">
    </asp:DropDownList>

Caching Approach 2

You could always cache your results in code:

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        IEnumerable<employee> res = Enumerable.Empty<employee>();

        if (this.Cache["emp"] == null)
        {
            res = this.GetEmployeesFromDatabase();
            this.Cache["emp"] = res;
        }
        else
        {
            res = this.GetEmployeesFromCache();
        }

        this.grv.DataSource = res;
        this.grv.DataBind();
    }
}

protected IEnumerable<employee> GetEmployeesFromDatabase()
{
    // go to your database to get the objects
    return Enumerable.Empty<employee>();
}

protected IEnumerable<employee> GetEmployeesFromCache()
{
    return this.Cache["emp"] as IEnumerable<employee>;
}

Keep in mind that the returned object must to be a DataSet or DataTable in order to add sorting capabilities to your cache results

2) Do not want to store results in Viewstate or Session

Again you need to use cache

Is there a way to do it in asp.net, or I need to consider jQuery for this scenario.

I don't see how jQuery (by itself) can help you with your requirement, I mean if you use AJAX posts, you would improve the perceived performance of your current page, but that won't help you with the connections to the database unless you use caching

Upvotes: 6

HatSoft
HatSoft

Reputation: 11201

I am not sure whether it covers all your requirement but for paging their is PagedDataSource Class

Encapsulates the paging-related properties of a data-bound control (such as DataGrid, GridView, DetailsView, and FormView) that allow it to perform paging. This class cannot be inherited.

Else I would suggest you look in to the JqGrid

Upvotes: 2

Related Questions