DataTable.Load（FbDataReader）都没有加载到数据表 [英] DataTable.Load(FbDataReader) does not load everything into DataTable

问题描述

我有retunrs 169结果的SQL查询。结果是这样的：

I have a SQL Query that retunrs 169 results. The result looks like this:

CustomerID Customer Name TerminalID Creation Date
     1     First Customer   12345   2010-07-07
     1     First Customer   12346   2010-07-07
     1     First Customer   12347   2010-07-07
     2     Second Customer  23456   2011-04-18

这结果是正确的。

我enterend查询在C＃程序并执行它像这样

I enterend the query in a C# program and execute it like this:

public DataTable getDataTableFromSql(FbCommand command)
{
    // Create a new datatable
    DataTable result = new DataTable();

    // Set up the connection
    using (FbConnection con = new FbConnection(this.connectionString))
    {
        // Open the connection
        con.Open();

        // Set up the select command
        FbCommand sqlCmd = command;
        // Add the connection to it
        sqlCmd.Connection = con;

        try
        {
            // Get the results
            using (FbDataReader sqlReader = sqlCmd.ExecuteReader())
            {
                // Load the results into the table
                result.Load(sqlReader);
            }
        }
        catch (Exception ex)
        {
            System.Windows.Forms.MessageBox.Show(ex.Message);
        }
    }

    // Return the table
    return result;
}

这代码进行测试，它工作正常了许多不同的SQL查询。
，但因上述查询数据表只包含39结果是这样的：

This code is tested and it works fine for many different sql queries. But for the above query the DataTable only contains 39 results and looks like this:

CustomerID Customer Name TerminalID Creation Date
     1     First Customer   12347   2010-07-07
     2     Second Customer  23456   2011-04-18

我的代码有点摆弄周围，这里是我发现至今：在 FbDataReader 正确地读取从数据库的结果。如果我只是查询的 TerminalID 我结束了169导致数据表。如果我查询了客户ID 我收到39结果

I fiddled around with the code a bit and here's what I found out so far: The FbDataReader correctly fetches the results from the database. If I just query for the TerminalID I end up with 169 results in the DataTable. If I query for the CustomerID I recieve 39 results.

结论：行 result.Load（SQLReader的）组为客户ID 的结果，并丢弃所有其他的结果，不管他们可能是分组或没有。

Conclusion: The line result.Load(sqlReader) groups the result for CustomerID and throws away all other results, no matter if they can be grouped or not.

这是怎么回事？我如何可以加载我的查询到数据表而不丢失由于unlogical分组任何行的结果？而为什么在数据表组的结果摆在首位。

Why is this happening? How can I load the result of my query into the DataTable without "losing" any rows due to unlogical grouping? And why does the DataTable "group" the result in the first place?

请注意：我也尝试过所有三个 LoadOptions 可用于数据表，都具有相同的结果：只有39结果被加载到 。数据表

Note: I also tried all three LoadOptions available for DataTables, all with the same outcome: Only 39 results are loaded into the DataTable.

编辑：

感谢蒂姆Schmelter指着我这样一个问题： .NET数据表将跳过负荷（DataReader的）行

Thanks to Tim Schmelter for pointing me to this question: .NET DataTable skips rows on Load(DataReader)

我现在能够找出问题：

数据表.Load（）寻找一个主键在结果中。如果主键尝试已经加载出现在数据表，它会以新的结果行。在我的情况 - 奇怪的是 - 使用不同的别名与主键列确实帮助。更奇怪：栏中的原始的名字甚至没有包含 ID 在它的名字，而是信息，这是一个主键仍然传递它似乎。为什么要使用的别名列帮助...我不知道。

DataTable.Load() looks for a Primary Key in the results. If the Primary Key it tries to load already is present in the DataTable, it overwrites the row with the new result. In my case - strangely enough - using a different alias for the column with the Primary Key did help. Even more strange: The original name of the column doesn't even contain ID in it's name, but the info that it is a Primary Key still is passed on it seems. Why using an alias for the column helps... I don't know.

推荐答案

即使我不知道问题我建议使用的DataAdapter 来代替。也许这作品：

Even if i don't know the problem i would suggest to use a DataAdapter instead. Maybe that works:

// Get the results
using(var da = new FbDataAdapter(sqlCmd))
{
    // Load the results into the table
    da.Fill(result);
}

这篇关于DataTable.Load（FbDataReader）都没有加载到数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！