在 C# 中使用 DataAdapter.Fill 时保留 MS Access 行顺序
这是一个棘手的问题...我在代码查询中注意到，当我使用如下所示的 .NET DataAdapter.Fill 方法来查询 Access 数据库时，记录的顺序不是记录的“自然”顺序（因为它们最初被插入到表中）。
OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter("SELECT * FROM SomeTable ", oleDbConnection);
oleDbAdapter.Fill(sourceData, "SomeTable" );
foreach(DataRow theRow in sourceData.Tables["SomeTable"].Rows)
{ ... }
在一张表上，我有一个主键，因此我只能按主键进行排序。我有一个没有任何主键的新表，我想查询该表并按自然表顺序对记录进行排序。我应该使用 OleDbDataReader 来保留顺序，还是有某种方法可以使 OleDbDataAdapter.Fill 方法保留顺序？
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 "natural" 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
DataAdapter的Fill()方法相当于ExecuteReader(CommandBehavior.Default)，因此在保留顺序时您不会获得任何结果通过使用一种或另一种方法。显然，CommandBehavior 枚举没有提供任何选项来明确指定应按自然顺序读取表。
尽管
DataAdapter.Fill应该重新排序数据库中存储的数据的自然顺序，但我发现这很令人困惑。编辑：有关自然顺序的更多信息
您的表中是否定义了任何索引？ MS Access 将显示表中按任何定义的索引排序的数据，因此在可视化数据（插入顺序）时不会保留自然顺序。
另一方面，.Fill() 将保留自然顺序，无论源表中定义了什么索引，因此您可能认为不按自然顺序读取可能是由于源表中的索引而不是
Fill()方法中的问题。我做了一些快速测试，在所有测试中，
DataAdapter返回的行都保留自然顺序。The
Fill()method of aDataAdapteris equivalent toExecuteReader(CommandBehavior.Default)so you will not gain anything when it comes to preserving order by using one method or the other.The
CommandBehaviorenumeration 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.Fillshould 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
DataAdapteris returning rows preserving natural order.关系数据库中不存在“自然顺序”这样的东西。
事实上，Jet/ACE 存储的表的主键聚集在 PK 上，但这与您定义“自然顺序”的方式不同，因为 PK 可能会导致未按 PK 顺序插入的记录显示出来别处。
没有 PK 的表根本不属于任何数据库，因为无法通过 SQL 可靠地访问和操作它。当然，这是可以做到的，但这是一个错误。
我认为你需要重新考虑一下你想要做什么。您似乎依赖于一些奇怪的东西，这些东西在您的演示顺序中有时恰好起作用。如果该顺序很重要，那么您必须构建数据，以便可以按照这种方式对其进行排序。
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.