.NET DataTable 在 Load(DataReader) 上跳过行
我正在尝试使用以下内容填充数据表以构建本地报告:
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = new MySqlConnection(Properties.Settings.Default.dbConnectionString);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ... LEFT JOIN ... WHERE ..."; /* query snipped */
// prepare data
dataTable.Clear();
cn.Open();
// fill datatable
dt.Load(cmd.ExecuteReader());
// fill report
rds = new ReportDataSource("InvoicesDataSet_InvoiceTable",dt);
reportViewerLocal.LocalReport.DataSources.Clear();
reportViewerLocal.LocalReport.DataSources.Add(rds);
有一次我注意到报告不完整并且缺少一条记录。 我更改了一些条件,以便查询将返回两行并且... 惊喜:报告仅显示一行而不是两行。 我尝试对其进行调试以找出问题所在,但当
dt.Load(cmd.ExecuteReader());
我注意到 DataReader
包含两条记录但 DataTable
仅包含一条记录时,我陷入了困境。 偶然间,我在查询中添加了一个 ORDER BY
子句,并注意到这次报告正确显示。
显然,DataReader 包含两行,但如果 SQL 查询字符串包含 ORDER BY ,则 DataTable 仅读取这两行(否则仅读取最后一行)。 谁能解释为什么会发生这种情况以及如何解决它?
编辑: 当我第一次发布这个问题时,我说它正在跳过第一行; 后来我意识到它实际上只读取了最后一行,并且我相应地编辑了文本(当时所有记录都分组为两行,当它实际上只显示最后一行时,它似乎跳过了第一行)。 这可能是由于它没有唯一标识符来区分 MySQL 返回的行,因此添加 ORDER BY 语句导致它为每行创建唯一标识符。
这只是一个理论,我没有任何证据支持它,但我所有的测试似乎都得出相同的结果。
I'm trying to populate a DataTable, to build a LocalReport, using the following:
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = new MySqlConnection(Properties.Settings.Default.dbConnectionString);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ... LEFT JOIN ... WHERE ..."; /* query snipped */
// prepare data
dataTable.Clear();
cn.Open();
// fill datatable
dt.Load(cmd.ExecuteReader());
// fill report
rds = new ReportDataSource("InvoicesDataSet_InvoiceTable",dt);
reportViewerLocal.LocalReport.DataSources.Clear();
reportViewerLocal.LocalReport.DataSources.Add(rds);
At one point I noticed that the report was incomplete and it was missing one record. I've changed a few conditions so that the query would return exactly two rows and... surprise: The report shows only one row instead of two. I've tried to debug it to find where the problem is and I got stuck at
dt.Load(cmd.ExecuteReader());
When I've noticed that the DataReader
contains two records but the DataTable
contains only one. By accident, I've added an ORDER BY
clause to the query and noticed that this time the report showed correctly.
Apparently, the DataReader contains two rows but the DataTable only reads both of them if the SQL query string contains an ORDER BY
(otherwise it only reads the last one). Can anyone explain why this is happening and how it can be fixed?
Edit:
When I first posted the question, I said it was skipping the first row; later I realized that it actually only read the last row and I've edited the text accordingly (at that time all the records were grouped in two rows and it appeared to skip the first when it actually only showed the last). This may be caused by the fact that it didn't have a unique identifier by which to distinguish between the rows returned by MySQL so adding the ORDER BY
statement caused it to create a unique identifier for each row.
This is just a theory and I have nothing to support it, but all my tests seem to lead to the same result.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(15)
经过一番摆弄后,我发现 DataTable.Load 方法需要基础数据中的主键列。 如果您仔细阅读文档,这一点就会变得显而易见,尽管没有明确说明。
如果您有一个名为“id”的列,它似乎会使用该列(这为我修复了它)。 否则,它似乎只是使用第一列,无论它是否唯一,并在读取时覆盖该列中具有相同值的行。 如果您没有名为“id”的列并且您的第一列不是唯一的,我建议在加载数据读取器之前尝试显式设置数据表的主键列。
After fiddling around quite a bit I found that the
DataTable.Load
method expects a primary key column in the underlying data. If you read the documentation carefully, this becomes obvious, although it is not stated very explicitly.If you have a column named "id" it seems to use that (which fixed it for me). Otherwise, it just seems to use the first column, whether it is unique or not, and overwrites rows with the same value in that column as they are being read. If you don't have a column named "id" and your first column isn't unique, I'd suggest trying to explicitly set the primary key column(s) of the datatable before loading the datareader.
为了防止有人遇到与 canceriens 类似的问题,我使用 If
DataReader.Read
...而不是 IfDataReader.HasRows
在调用之前检查是否存在dt.load(DataReader)
哦!Just in case anyone is having a similar problem as canceriens, I was using If
DataReader.Read
... instead of IfDataReader.HasRows
to check existence before callingdt.load(DataReader)
Doh!我有同样的问题。 我从你的博客中得到提示,并在查询中添加了 ORDER BY 子句,以便它们可以一起形成查询返回的所有记录的唯一键。 它解决了问题。 有点奇怪。
I had same issue. I took hint from your blog and put up the ORDER BY clause in the query so that they could form together the unique key for all the records returned by query. It solved the problem. Kinda weird.
不要使用,
因为它将指针移动到下一行。
删除这一行希望它能起作用。
Don't use
Because It moves the pointer to the next row.
Remove this line hope it will work.
有同样的问题。 这是因为所有行的主键都是相同的。 它可能是用来键入结果的,因此它只是一遍又一遍地覆盖同一行。
Datatables.Load 指向 fill 方法以了解其工作原理。 此页面声明它是主键感知的。 由于主键只能出现一次并用作行的键...
“Fill 操作然后将行添加到 DataSet 中的目标 DataTable 对象,如果 DataTable 对象尚不存在则创建它们。创建 DataTable 对象时,填充操作通常仅创建列名称元数据,但是,如果 MissingSchemaAction 属性设置为 AddWithKey,还会创建适当的主键和约束。” (http://msdn.microsoft.com/en-us/library/zxkb3c3d.aspx)
Had the same issue. It is because the primary key on all the rows is the same. It's probably what's being used to key the results, and therefore it's just overwriting the same row over and over again.
Datatables.Load points to the fill method to understand how it works. This page states that it is primary key aware. Since primary keys can only occur once and are used as the keys for the row ...
"The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created." (http://msdn.microsoft.com/en-us/library/zxkb3c3d.aspx)
今天遇到这个问题。
不幸的是,这个线程中没有任何内容修复它,但后来我将 SQL 查询包装在另一个 SELECT 语句中,它工作了!
例如:
奇怪……
Came across this problem today.
Nothing in this thread fixed it unfortunately, but then I wrapped my SQL query in another SELECT statement and it work!
Eg:
Strange....
您能否获取从 SQL 探查器运行的实际查询并尝试运行它? 这可能不是你所期望的。
使用 SqlDataAdapter.Fill(dataTable) 时是否得到相同的结果?
您是否在阅读器上尝试过不同的命令行为? MSDN 文档
Can you grab the actual query that is running from SQL profiler and try running it? It may not be what you expected.
Do you get the same result when using a SqlDataAdapter.Fill(dataTable)?
Have you tried different command behaviors on the reader? MSDN Docs
我知道这是一个老问题,但对我来说,在查询访问数据库并注意到查询中缺少 1 行时有效的想法是更改以下内容:-
I know this is an old question, but for me the think that worked whilst querying an access database and noticing it was missing 1 row from query, was to change the following:-
对于像我一样遇到此线程的任何其他人,关于由 MySql 中的唯一 ID 填充的 DataTable 的答案是正确的。
但是,如果一个表包含多个唯一 ID,但从 MySql 命令仅返回一个 ID(而不是使用“*”接收所有列),则该 DataTable 将仅按给定的单个 ID 进行组织,并充当一个您的查询中使用了“GROUP BY”。
简而言之,DataReader 将提取所有记录,而 DataTable.Load() 将仅看到检索到的唯一 ID 并使用它来填充 DataTable,从而跳过信息行
For anyone else that comes across this thread as I have, the answer regarding the DataTable being populated by a unique ID from MySql is correct.
However, if a table contains multiple unique IDs but only a single ID is returned from a MySql command (instead of receiving all Columns by using '*') then that DataTable will only organize by the single ID that was given and act as if a 'GROUP BY' was used in your query.
So in short, the DataReader will pull all records while the DataTable.Load() will only see the unique ID retrieved and use that to populate the DataTable thus skipping rows of information
不确定为什么您缺少数据表中的行,您是否可能需要关闭读取器? 无论如何,这就是我通常加载报告的方式,并且每次都有效......
很好奇它是否仍然会发生。
Not sure why you're missing the row in the datatable, is it possible you need to close the reader? In any case, here is how I normally load reports and it works every time...
Curious to see if it still happens.
就我而言, ORDER BY 和 dt.AcceptChanges() 都不起作用。 我不知道为什么会出现这个问题。 我的数据库中有 50 条记录,但数据表中只显示 49 条。 跳过第一行,如果 datareader 中只有一条记录,则它什么也不显示。
真是太奇怪了……
In my case neither ORDER BY, nor dt.AcceptChanges() is working. I dont know why is that problem for. I am having 50 records in database but it only shows 49 in the datatable. skipping first row, and if there is only one record in datareader it shows nothing at all.
what a bizzareeee.....
您是否尝试过在
dt.Load(cmd.ExecuteReader())
调用之后调用dt.AcceptChanges()
来查看是否有帮助?Have you tried calling
dt.AcceptChanges()
after thedt.Load(cmd.ExecuteReader())
call to see if that helps?我知道这是一个老问题,但我遇到了同样的问题,这里提到的解决方法都没有帮助。
就我而言,在用作
PrimaryKey
的列上使用别名解决了该问题。所以,
我没有使用它,
而且它有效。
I know this is an old question, but I was experiencing the same problem and none of the workarounds mentioned here did help.
In my case, using an alias on the colum that is used as the
PrimaryKey
solved the issue.So, instead of
I used
and it worked.
我遇到了同样的问题..根本不使用 dataReader.Read() ..它会将指针指向下一行。 而是直接使用 datatable.load(dataReader)。
I had the same problem.. do not used dataReader.Read() at all.. it will takes the pointer to the next row. Instead use directly datatable.load(dataReader).
遇到同样的问题,我也尝试选择唯一的第一列,但数据表仍然缺少一行。
但是选择 group by 中的第一列(也是唯一的)解决了问题。
即
这解决了问题。
Encountered the same problem, I have also tried selecting unique first column but the datatable still missing a row.
But selecting the first column(which is also unique) in group by solved the problem.
i.e
This solves the problem.