ActiveRecord, ObjectDataSource, and GridView Custom Paging

• Posted by justin on February 26, 2008
• 1 comments

Updated 4/25/2008: Add sample source code

Problem: How to efficiently implement paging in GridView using an ObjectDataSource

For small amounts of data the built-in paging for the GridView worked fairly well and takes practically no work to do it. However, once you get to a decent amount of data, it becomes incredible slow and provide for a horrible user experience. The problem is that basically the GridView and ObjectDataSource are returning all of the records each time from the database and only showing the ones for the current page. This is a very inefficient use of bandwidth.

Solution:

To get around this problem, you can implement paging with the ObjectDataSource. When you implement paging with the ObjectDataSource, it automatically passes in the values for the startIndex and maximumRows to your select statement and needs to have a SelectCount method defined so that it can get the total number.

Sounds really easy right? Once you figure out how to get the RowCount for the query it is. For simple queries you can use the CountQuery class in ActiveRecord. However for more complicated queries that use a DetachedCriteria, there is currently no overload for CountQuery that take a DetachedCriteria. Hopefully this will be in a future release since there was a patch submitted on March 5^th for this feature.

Luckily both NHibernate and ActiveRecord are open source so I was able to look thru their code and tests. In ActiveRecord it is well structure with a directory in their ActiveRecord Framework project called Queries.

In my research it became clear that a Projection query was what I needed but I still was not sure

So I finally found a ScalarProjectionQuery class that took a DetachCriteria. Below is an example call to the method. query is a DetachedCriteria

ScalarProjectionQuery<WorkOrder, int> proj = new ScalarProjectionQuery<WorkOrder, int>(Projections.RowCount(), query);

Int count = proj.Execute();

To implement the actual paging for the ObjectDataSource , I used the ActiveRecord SlicedFindAll method for my model along with the Sql 2005 Dialect.

Gotcha:

With turning on paging for the ObjectDataSource is that it automatically passes the startIndex and maximumRows parameters into your defined Select method as the last 2 parameters and if you use the designer to configure the ObjectDataSource, it adds those parameters to the parameter collection defined in the html code. This in turn causes the startIndex and maximumRows to be passed twice into the SelectMethod .

Sample Code:

ObjectDataSource Definition:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="FindUserBlogs"
        TypeName="MyClassThatMyFindMethodIsIn" OldValuesParameterFormatString="original_{0}"
        OnSelecting="ObjectDataSource1_Selecting" EnableViewState="False" EnablePaging="True"
        SelectCountMethod=" FindUserBlogsCount">
        <SelectParameters>
            <asp:Parameter Name="userID" Type="Int32" />
        </SelectParameters>
    </asp:ObjectDataSource>

ActiveRecord Method Definition:

public static Blog[]  FindUserBlogs (int userID, int startRowIndex, int maximumRows) {
     // use SlicedFindAll to start at a specific row and limit the results }

public static int FindUserBlogsCount (int userID) {
    //query is a DetachCriteria    
     ScalarProjectionQuery<Blog, int> proj =