Looking for an efficient and portable paging method (MSSQL, Oracle, MySQL, etc..)

Posted on 2007-10-07
Last Modified: 2012-06-27
I'm trying to find an efficient and portable way to implement paging for records returned to a GridView in ASP.NET 2.0 and C#. What I mean by portable is something I can use with an MS SQL Server, Access, Oracle, MySQL, etc.. So I don't want to use SQL 2005's ROWNUMBER().

One way of doing it is returning all records to the GridView and let it handle the paging, but that's not efficient since I really have to pull all records out. Another way of doing it is assigning an AUTONUMBER to the table I'm querying, but I don't have that level of freedom with the database to just change the whole schema. I've heard of creating a temporary table and filling it up, but that sounds pretty inefficient for every time some calls the page.

Something I thought of is maybe sorting on the primary key in the table and doing something like "SELECT primarykey, column2, column3 FROM table ORDER BY primarykey LIMIT 10", then keeping track of the last primarykey returned, so the next page would query "SELECT primarykey, column2, column3 FROM table ORDER BY primarykey WHERE primarykey > @lastprimarykey LIMIT 10". Is this a good theory or is there any better ideas on this?? I would appreciate any feedback or experiences.
Question by:bemara57
    LVL 10

    Expert Comment

    basically u can fill the dataset and bind with gridview, then u can use pagination and sorting by attending the event handler easily

    but if u want to use DAL / BLL, but at presentation layer, the logic is just similar
    u can refer to this
    LVL 22

    Accepted Solution

    Here is a method that works on both Oracle and SQL Server 2005. I have not tried others.

    SELECT various columns
      (SELECT primary_key, ROW_NUMBER() OVER (ORDER BY sort_col1, sort_col2, primary_key) rnum
         FROM your_table
        WHERE a.parent = ? AND ...) pageset
     INNER JOIN your_table a ON pageset.primary_key = a.primary_key
     WHERE rnum >= ? AND rnum <= ?
    ORDER BY rnum

    The First WHERE clause needs to contain whatever you would normally use to select the set of rows to return.

    The second WHERE clause defines the start and end rows for the page to retrieve. For example, if the pagesize is 5, and you want the second page, send 6 and 10.

    Substitute :p1 :p2, etc for parameters for Oracle, and @rnum, etc for SQL Server parameters. You will need to do a SELECT COUNT(*) first to determine the number of pages.

    IMPORTANT: In the first ORDER BY clause, you can sort by anything you want for the first one or more columns, but the last need to be the primary key col from the table.

    LVL 1

    Assisted Solution

    MySQL has a limit function that should do what you are looking for here.

    I wrote the following stored proc in MSSQL 2005 to get paged results, you could modify it to suit your needs possibly.

    @recStart INT,
    @recEnd INT,
    @propCat CHAR(20),
    @county0 CHAR(50),
    @county1 CHAR(50),
    @county2 CHAR(50),
    @county3 CHAR(50),
    @county4 CHAR(50),
    @county5 CHAR(50),
    @county6 CHAR(50),
    @cattype0 CHAR(20),
    @cattype1 CHAR(20),
    @cattype2 CHAR(20),
    @cattype3 CHAR(20),
    @cattype4 CHAR(20),
    @cattype5 CHAR(20),
    @cattype6 CHAR(20),
    @cattype7 CHAR(20),
    @minprice MONEY = NULL,
    @maxprice MONEY = NULL,
    @bedrooms INT = NULL,
    @bathRooms INT = NULL,
    @count INT OUTPUT,
    @pic INT

    DECLARE @Sminprice MONEY,
            @Smaxprice MONEY,
            @Sbedrooms INT,
            @SbathRooms INT

    Select @Sminprice = Coalesce(@minprice,0.00),
           @Smaxprice = Coalesce(@maxprice,999999999.99),
           @Sbedrooms = Coalesce(@bedrooms,0),
           @SbathRooms = Coalesce(@bathrooms,0)

    IDENTITY(int, 1,1) AS RowNum,
    WHERE LstFormType = @propCat
    AND LstPropertyType IN (@catType0,@catType1,@catType2,@catType3,@catType4,@catType5,@catType6,@catType7)
    AND lstarea IN (@county0,@county1,@county2,@county3,@county4,@county5,@county6)
    And listprice between @sminprice and @smaxprice
    and Bedrooms >= @sbedrooms
    and fullBaths >= @sbathrooms
    and photocount > @pic
    ORDER BY listprice

    SELECT @count = COUNT(*) FROM #GAMLS

    WHERE RowNum > @recStart
    AND RowNum <= @recStart + @recEnd

    Author Comment

    It seems each method is really propietary to the database.. one for Oracle and MSSQL 2005, one for MSSQL 2000, one for MySQL, etc. I am open to making a specific query per database, but if they are each long and complicated I'd rather not because of maintenance nightmares. Let me bounce one more idea before I bite the bullet and do make a complicated query per database.

    What if I make the query as simple as "SELECT columns FROM table" and load all the records into a .NET DataTable and cache it strategically. The webpage has to reflect real time data, but I am almost willing to push it back to a cached snapshop of 5 minutes.. So every 5 minutes, the DataTable gets loaded up from the database and the presentation layer always accesses that cached DataTable. The DataTable would have to get loaded up by perhaps a scheduled task (yuk!) or possibly a background thread that gets spawned by a "lucky" end user that invokes an outdated DataTable. If the presentation layer gets its source from a DataTable, everything should be automatically paginated and sort enabled right? Any thoughts on this method?
    LVL 22

    Expert Comment

    Here are two more possibilities you might consider:
    1. If the total number of different sets you need to page is relatively small, you could pass page number and page size to a stored procedure, and write one for each provider you need to support.

    2. I just completed a project where I needed to convert hindreds of different selects to paged selects, and they needed to support both Oracle and SQL server. I did not want to rewrite all of them, so instead I built a method to assemble them at run-time as needed. It works just fine.

    LVL 10

    Assisted Solution

    By using DataAdapter

    Most of the solutions here are decent. You can also do the paging by yourself. Look at this example:

            public  DataSet AnyMethod(int startIndex, int noOfRecordAtATime)
                    DataSet ds = new DataSet();
                    SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT ANYTHING", Connection);
                    myAdapter.Fill(ds,startIndex, noOfRecordAtATime, "MyTable");
                    return ds;
                    return null;

    The idea in this example is letting dataAdapter to only fetch the data from start index till the next noOfrecordAtATime item.
     myAdapter.Fill(ds,startIndex, noOfRecordAtATime, "MyTable");

    The startIndex value you can store it somewhere in your aspx page (hidden field for example)

    Hope this helps,
    LVL 21

    Assisted Solution

    The most PORTABLE method of implementing paging is to use C# code.  This way your select statement can impliment any criteria and ANY sort order the customer wants.  Here is a sample:

      using (IDataReader data = cmd.ExecuteReader())
                    int rowCount = 0;

                    while (data.Read() && rowCount < maximumRows)
                        if (startRowIndex-- > 0)   //If NOT page one, then skip previous pages
                        //...Process Results

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now