DataTable.Load (FbDataReader) does not load everything in a DataTable

Question

There is a close question here: .NET DataTable skips rows in Load (DataReader)

I have an SQL query that returns 169 results. The result is as follows:

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 request into a C # program and executed it as follows:

 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) { Console.WriteLine(ex); } } // Return the table return result; } 

This code has been tested, and it is great for different SQL queries. But for the above query, the DataTable contains only 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 distorted the code a bit and this is what I have learned so far: FbDataReader correctly retrieves the results from the database. If I just request a TerminalID , I get 169 results in a DataTable . If I ask for CustomerID , I get 39 results.

Conclusion: The line result.Load(sqlReader) groups the result for CustomerID and discards all other results, regardless of whether they can be grouped or not.

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

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

Answer 1

Even if I don't know the problem, I would suggest using the DataAdapter instead. Maybe this works:

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

---

Why did this happen? I think this answer in another question explains this.

Answer 2

The DataTable.Load method expects a primary key column in the underlying data (i.e., from the DataReader ). It looks like there is no primary key column in your procedure, or if you have one order by user in the sql statement so that the DataTable can accept it as primary.

This is a very old problem with DataTable.Load and is not very well documented. Normally SQLDataAdapter is good with DataTable .

In your case, I think, as soon as Load detects a duplicate, it will stop loading data. I have not documented this document anywhere, but it seems to be a problem.

Answer 3

The essence of the problem is as follows:

DataTable.Load() looks for the primary key in the results. If the primary key that he is trying to load into the DataTable is already in this table, he overwrites the row with the new result.

However, using a different alias for the Primary Key column solves the problem, all the results will be loaded into the DataTable , since it seems that the information that this column is the Primary Key is overwritten with an alias.