?
Solved

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

Posted on 2007-10-07
7
Medium Priority
?
659 Views
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.
0
Comment
Question by:bemara57
7 Comments
 
LVL 10

Expert Comment

by:orbulat
ID: 20032056
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
 
LVL 22

Accepted Solution

by:
JimBrandley earned 1000 total points
ID: 20032100
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
 
LVL 1

Assisted Solution

by:kyleroi
kyleroi earned 400 total points
ID: 20032142
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:bemara57
ID: 20032204
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
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20032396
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
 
LVL 10

Assisted Solution

by:jinn_hnnl
jinn_hnnl earned 400 total points
ID: 20032617
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
 
LVL 21

Assisted Solution

by:MogalManic
MogalManic earned 200 total points
ID: 20033178
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 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