ASP.NET 2.0 Resources

Powered by Blogger

ASP.NET 2.0: Custom paging and sorting with ObjectDataSource

Today i came across some very interesting arcticles by Scott Mitchell Custom Paging in ASP.NET 2.0 with SQL Server 2005 and Sorting Custom Paged Results. I was looking for such complex info for a long time and those arcticles finally supplied all infromation together at one place. As a bonus you get some basic speed comparisons of different paging and sorting methods.

If you want custom paged and sortable ObjectDataSource you will have define some object properties (see the following ObjectDataSource declaration sample)...

<asp:ObjectDataSource 
    ID="FundListDataSource" 
    runat="server" 
    EnablePaging="True" 
    TypeName="Core.BLL.FundController" 
    SelectCountMethod="GetFundCount"     
    SelectMethod="GetFunds" 
    StartRowIndexParameterName="startrow" 
    MaximumRowsParameterName="maxrows"     
    SortParameterName="sortExpression" 
/>

ID, TypeName, runat and SelectMethod are probably familiar to you. So lets look at the others...

EnablePaging

This property turns on/off paging support for this data source. If you enable paging for ObjectDataSource control you also have to define SelectCountMethod, StartRowIndexParameterName and MaximumRowsParameterName properties.

SelectCountMethod

This property contains method name (must be static, parameterless method of TypeName type) used for geting total count of paged items.

StartRowIndexParameterName

This is the name of parameter for SelectMethod specifying starting page index.

MaximumRowsParameterName

Similar to StartRowIndexParameterName this parameter specifies how many rows to return.

SortParameterName

When using custom sorting for ObjectDataSource, this property contains name of the parameter that specifies row sorting.

This is my Bussiness Layer GetFuns method. It return list of all funds and has three parameters. First is starting data row, second is how many rows to return and the last one is our sorting expression. This method first tries to translate sortExpression from Bussiness Layer notation to Data layer notation (more about it later) and after that retrieves data from underlying Data layer.

public static List<FundInfo> GetFunds(int startrow, 
    int maxrows, string sortExpression)
{
    string translatedSortExpression = null;
    List<FundInfo> funds = null;

    if (!String.IsNullOrEmpty(sortExpression)) { 
        translatedSortExpression = 
            CBO<FundInfo>.TranslateSortParams(sortExpression);
    }

    using (IDataReader dr = 
        DataProviderFactory.CoreProvider.GetFunds(
                                startrow, 
                                maxrows, 
                                translatedSortExpression))
    {
        funds = CBO<FundInfo>.FillList(dr);
    }
    return funds;
}

Translating sortExpression to Data layer notation is quite tricky. Previously mentioned GetFunds method is used to populate web application GridView defined for example as...

<asp:GridView ID="GridView1" runat="server" 
    AllowPaging="True" AutoGenerateColumns="False"
    DataSourceID="FundListDataSource" AllowSorting="True">
    <Columns>
        <asp:BoundField DataField="ID" 
            HeaderText="ID" SortExpression="ID" />
        <asp:BoundField DataField="Name" 
            HeaderText="Name" SortExpression="Name" />
        <asp:BoundField DataField="ISIN" 
            HeaderText="ISIN" SortExpression="ISIN" />
    </Columns>
</asp:GridView>

The sort expressions comming from this Gridview are ID, Name and ISIN but Data Layer works internally with column names FND_ID, FND_NAME and FND_ISIN. Translation from one notation to another is handled by CBO's class static method TranslateSortParams. It uses reflection on FundInfo class properties which are decorated with DatabaseField atributes to translate from one notation to another.

As noted in original arcicles, for large result sets this custom paging is roughly two order of magnitudes faster than the default paging. It's implementation is not so straight forward as default paging and sorting but for large result sets together with correct database indexes it works like a charm. Definitely have a look at those arcticles too!

5 Comments:

  • it is not working i give u the solution first incrase two paramiters in select method and two parameters for maximumRows and startRowIndex
    then set these two parameters as



    and thats it crate a store produre and it is going to work

    mail me on raviraj555@gmail.com if u find it
    bye

    By Blogger ravi, at 11:45 AM  

  • Nice information, many thanks to the author. It is incomprehensible to me now, but in general, the usefulness and significance is overwhelming. Thanks again and good luck! Web Design Company

    By Blogger Pooja, at 10:57 AM  

  • page persistence is not working after sorting. example i click page 10 then sort the column. After that the page return to 1.

    Question: How can i persist the page after sorting? Please reply... thanks in advance.

    By Blogger April Galea, at 1:51 PM  

  • Stephen Stapinski

    Really your blog is very interesting.... it contains great and unique information. I enjoyed to visiting your blog. It's just amazing.... Thanks very much for the share.

    By Blogger rocky case, at 11:18 AM  

  • We have best services of app demo video that increase the credibility of your app and make it easy for download it.

    By Blogger aliya seen, at 1:06 PM  

Post a Comment

<< Home

Created dolly