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

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.
bemara57Asked:
Who is Participating?
 
JimBrandleyConnect With a Mentor Commented:
Here is a method that works on both Oracle and SQL Server 2005. I have not tried others.

SELECT various columns
  FROM
  (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.

Jim
0
 
orbulatCommented:
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
http://msdn2.microsoft.com/en-us/library/aa479347.aspx
0
 
kyleroiConnect With a Mentor Commented:
MySQL has a limit function that should do what you are looking for here.  
http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

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

CREATE PROCEDURE GAMLS_SEARCH
@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

AS
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)

SELECT
IDENTITY(int, 1,1) AS RowNum,
listnum,
Listprice,
Bedrooms,
City,
Fullbaths,
Lstarea,
Remarks,
photocount
INTO #GAMLS
FROM GAMLS WITH (NOLOCK)
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

SELECT
listnum,
Listprice,
Bedrooms,
City,
Fullbaths,
Lstarea,
Remarks,
photocount
FROM #GAMLS
WHERE RowNum > @recStart
AND RowNum <= @recStart + @recEnd
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
bemara57Author Commented:
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?
0
 
JimBrandleyCommented:
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.

Jim
0
 
jinn_hnnlConnect With a Mentor Commented:
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)
        {
            try
            {
                Connection.Open();
                DataSet ds = new DataSet();
                SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT ANYTHING", Connection);
                myAdapter.Fill(ds,startIndex, noOfRecordAtATime, "MyTable");
                Connection.Close();
                return ds;
            }
            catch
            {
                Connection.Close();
                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,
0
 
MogalManicConnect With a Mentor Commented:
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:
   cmd.CommandText="select..."

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

                while (data.Read() && rowCount < maximumRows)
                {
                    if (startRowIndex-- > 0)   //If NOT page one, then skip previous pages
                        continue;
                    //...Process Results
                    rowCount++;
                }
            }
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.