0

I have more than 30,000 rows in a table. It takes a lot of time to load all the data in the gridview. So I want to display 100 rows at a time. When I click next page button, another 100 rows should be displayed. When I click previous page button, previous 100 rows should be displayed. If I type page 5 in a text box, then I want to jump over to the 5th lot of rows.

I also want to display how many pages there will be. Can we implement this concept in vb.net [winform] gridview. I am using database PostgreSQL.

Can anybody give me a hint or some concept?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
nightfire001
  • 759
  • 3
  • 20
  • 50

1 Answers1

1

Look at OFFSET and LIMIT in PostgreSQL.
Your query for the 5th page could look like this:

SELECT * 
FROM   tbl
ORDER  BY id
OFFSET 400
LIMIT  100;

id is the primary key in my example, therefore an index is in place automatically. If you access the table a lot in this fashion, performance may profit from using CLUSTER.

Total number of pages:

SELECT ceil(1235::real / 100)::int
FROM   tbl;

If you wanted the number rounded down, just simplify to:

SELECT 1235  / 100
FROM   tbl;

With both numbers being integer the result will be an integer type and fractional digits truncated automatically. But I think you need to round up here.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Brandstetter : Thank you for the script. I have got one problem in displaying total number of pages. The ceil() function is not returning exact value as I want. For eg, if ceil(42.04), then instead of returning 42, it gives 43. Is there any other method for returning 42 instead of 43? – nightfire001 Nov 01 '11 at 09:57
  • There is the brother of `ceil`: [`floor`](http://www.postgresql.org/docs/9.1/interactive/functions-math.html). You could also simply cast to integer, this will truncate fractional digits. But I chose `ceil` for a purpose. If you calculate `42.04`, you will have to show `43` pages - the last one mostly empty, but still needed. – Erwin Brandstetter Nov 01 '11 at 11:59