0

How can I implement custom paging for a GridView bound to an ObjectDataSource without using a stored procedure?

I have a search screen that allows users to apply a number of filters to create a custom search.(Several DropDownList Controls for each one that has selected value other then "All" I need to add a filter to the "where" clause of the SQL statement).

I want to build the SQL query dynamically. Usually I use a store procedure and use ROW_NUMBER() (See here ) But how can I implement paging in c# code level ?

Is there a way to do so 'or is my only option to implement the dynamic SQL inside the stored procedure (See here) ?

Thanks

ProgNet
  • 715
  • 1
  • 10
  • 20

1 Answers1

1

Basically, if you enable paging on your ListView and on the ObjectDataSource, the data source will pass the current page number as well as the page size to your data provider class (the one you configure in the ObjectDataSource).

Here's a working example:

http://netpl.blogspot.com/2009/04/how-to-controll-aspnet-listview-page.html

Specifically, take a look at the data provider class, PersonDataSource. 

public class PersonDataSource
{
    public IEnumerable<Person> Retrieve( int StartRow, int RowCount )
    {
        return DataModel.Instance.Persons.Skip( StartRow ).Take( RowCount );
    }

    public int CountItems()
    {
        return DataModel.Instance.Persons.Count;
    }
 }

You are free to implement the provider, it means that you can retrieve your objects from memory, from the database, you can use an ORM or create a SqlCommand. No restrictions.

I would not implement paging at the application level but rather I would create a paged query. Paging at the application level means that you retrieve data from the database and filter at the application level which is usually worse in terms of performance than filtering at the database level.

If you want to craft your own paging queries then there are at least few ways to do it, with ROW_NUMBER or without.

Wiktor Zychla
  • 47,367
  • 6
  • 74
  • 106
  • Hi,Thank you for your answer.What I want to know is : given the startRowIndex and the MaximumRows values from the ObjectDataSource is there a way to retrieve only the n records of the current page from the database without using a stored procedure ? (I want to reterive only n records at a time .I do not know OR Mapping) – ProgNet Sep 09 '12 at 13:46
  • Sorry I missed your last comment .Thank you for your help :) – ProgNet Sep 19 '12 at 12:41