Devart Forums

Posted: **Mon 17 Aug 2009 19:44**

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.

When I load the results of this query into a PgSqlDataTable using Load(PgSqlDataReader) the rows are not being loaded in the correct order.

I have tried looping through the results in the DataReader myself using Read() and the row order is still wrong, so I don't think it has anything to do with the DataTable.

Why isn't the row order being maintained by the DataReader?

Edit: I should note that I'm using the latest version of dotConnect for PostgreSQL (4.55.39).

The C# code for this is very straight forward:

Code: Select all

using (PgSqlConnection conn = new PgSqlConnection(connString)) {
    PgSqlCommand cmd = new PgSqlCommand(query, conn);
    conn.Open();
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
}

dgGrid.DataSource = dt;
dgGrid.DataBind();

"dgGrid" is a DataGrid.

Posted: **Tue 18 Aug 2009 13:34**

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:

SELECT id, name
FROM tbl_foo
ORDER BY @orderBy
LIMIT @limit OFFSET @offset

cmd.Parameters.Add("@sortBy", "name");
cmd.Parameters.Add("@pageSize", "10");
cmd.Parameters.Add("@offset", "0");

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.

Devart Forums

PgSqlDataTable.Load() and ORDER BY

PgSqlDataTable.Load() and ORDER BY