From: | "Francisco Figueiredo Jr(dot)" <francisco(at)npgsql(dot)org> |
---|---|
To: | Szymon Guz <mabewlun(at)gmail(dot)com> |
Cc: | Mike Christensen <mike(at)kitchenpc(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Working with pages of data (LIMIT/OFFSET keyword) |
Date: | 2010-06-17 17:38:26 |
Message-ID: | AANLkTinz063bZBRh4OSYP9SqWtTHFSafYQ-MIimDopuZ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I would go with 2).
Npgsql supports multiple resultsets. You can pass a query separated by
semicolon ';' or you can use a procedure call which return a setof
refcursor.
On both ways, you will need to call NextResult in your Datareader just
like with SQLServer.
You can check our user manual: http://manual.npgsql.org to get more
info about how to use refcursors
I hope it helps.
Please, let me know if you need more info.
On Wed, Jun 16, 2010 at 16:44, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:
>
>
> 2010/6/16 Mike Christensen <mike(at)kitchenpc(dot)com>
>>
>> I'm generating a query on the fly to return a set of data, however I
>> only want to display 30 rows at a time to the user. For this reason,
>> I use the LIMIT 30 OFFSET x clause on the select statement. However,
>> I also want to know the total rows that match this query had there
>> been no limit, that way I can display to the user the total count and
>> the number of pages, and have Next/Prev buttons in my UI. I can think
>> of the following ways to do this:
>>
>> 1) When the page loads, execute two totally separate queries. One
>> that does the COUNT, and then another query immediately after to get
>> the desired page of data. I don't like this as much because the two
>> queries will execute in separate transactions and it'd be nice if I
>> could just perform a single SQL query and get all this information at
>> once. However, I will use this if there's no other way.
>>
>
> Just run them in one transaction.
> You can also just show the Next/Prev buttons and then do something just for
> the case where there is no data.
> Or use LIMIT 31 so you always know that there is the next page with at least
> one record.
> regards
> Szymon Guz
--
Regards,
Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior
From | Date | Subject | |
---|---|---|---|
Next Message | Marvin S. Addison | 2010-06-17 18:14:12 | Excessive Deadlocks On Concurrent Inserts to Shared Parent Row |
Previous Message | pythonista | 2010-06-17 17:23:06 | Re: An Enigma of a weird query problem |