.NET DataReader and ORDER BY

Question

Is it normal behaviour for a DataReader to return the rows from a query out-of-order?

I'm fetching some rows from a PostgreSQL 8.3.7 database and am using ORDER BY, LIMIT and OFFSET as follows:

SELECT id, name
FROM tbl_foo
ORDER BY name
LIMIT 10 OFFSET 0;

If I run this query manually the results are sorted and then the first ten rows are returned, which is what I would expect.

If I run the same query using a third-party ADO.NET provider for PostgreSQL (http://www.devart.com/dotconnect/postgresql/) the rows are not returned in the same order. It could be an issue with the third-party provider, and I have a post on their forums asking about it.

I've tried loading the rows with DataTable.Fill() and have also looped through them with DataReader.Read(). In either case they aren't coming across in the correct order.

I thought I'd check here as well though and see if anyone has experienced the same behaviour with DataReaders in general.

Answer 1

I don't know Postgres or the Devart drivers, but I can confirm that in SQL Server (2000 and up), if you do specify an ORDER BY (as you do), then these rows will be returned in that specified order by the SqlDataReader. I'm relying on that every day! :-)

Marc

Answer 2

Sounds like an issue with the provider, just make sure the columns you order by gives a determined result for the rows you are expecting e.g. a table with

id | Name
1  | John
2  | Peter
3  | John

And an order by on the Name column might not yield the same row order across queries. It could yield

1  | John        3  | John
3  | John    or  1  | John
2  | Peter       2  | Peter

Answer 3

Came back to the problem with a fresh head this morning and as often happens the answer presented itself promptly.

Out of habit I was using Parameters to set the values of my ORDER BY, LIMIT and OFFSET in the query. The later two work fine when set via parameters, but ORDER BY doesn't, probably because it's being turned into a string literal.

For interests sake I tested LIMIT and OFFSET and they will accept string literals instead of INTEGERs.

I guess I need to use string concatenation for the ORDER BY.