Preserving MS Access Row Order when using DataAdapter.Fill in C#

Here's a tricky one... I noticed in a query for my code that when I used the .NET DataAdapter.Fill method as shown below to query an Access database, the order of the records was not the "natu开发者_开发知识库ral" order of the records (as they were originally inserted into the table).

OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter("SELECT * FROM SomeTable ", oleDbConnection);
oleDbAdapter.Fill(sourceData, "SomeTable" );
foreach(DataRow theRow in sourceData.Tables["SomeTable"].Rows)
{ ... }

On one table, I had a primary key so I was just able to order by the primary key. I have a new table which does not have any primary key and I would like to query the table and have the records ordered by the natural table order. Should I be using a OleDbDataReader to preserve the order, or is there some way to make the OleDbDataAdapter.Fill method to preserve order?

The Fill() method of a DataAdapter is equivalent to ExecuteReader(CommandBehavior.Default) so you will not gain anything when it comes to preserving order by using one method or the other.

The CommandBehavior enumeration does not, apparently, give any option to specify explicitly that the table should be read in natural order.

I find it confusing though that DataAdapter.Fill should reorder the natural order of the data stored in the DataBase.

EDIT: More on Natural order

Are there any indexes defined in your table? MS Access will show the data in the table ordered by any defined indexes and therefore it will not preserve the natural order when visualizing data (insert order).

On the other hand .Fill() will conserve natural order no matter what indexes are defined in the source table, therefore what you might percieve as not reading in natural order might be due to an index in the source table and not a problem in the Fill() method.

I have done a few quick tests and in all of them DataAdapter is returning rows preserving natural order.

There is no such thing as "natural order" in a relational database.

There is the fact that Jet/ACE stores tables with a primary key clustered on the PK, but that's not the same thing as how you seem to be defining "natural order" since the PK might cause records not inserted in PK order to show up elsewhere.

A table without a PK simply does not belong in any database because it cannot be reliably accessed and manipulated via SQL. Sure, it can be done, but it's a mistake.

I think you need to rethink what you're trying to do. You seem to be depending on quirky things that just happen to work some of the time for your presentation order. If that order is important, then you have to structure your data so that it can be ordered in that fashion.