waka waka - 2 months ago 15
C# Question

DataTable.Load(FbDataReader) does not load everything into DataTable

I have a SQL Query that returns 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


This result is correct.

I entered 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;
}


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


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.

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.

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?

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

EDIT:

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

I was able to figure out the problem now:

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.

Answer Source

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);
}