I am executing this SQL statement using the sqlclient method:
select top 10 *
from wlo_104466_CustomerPriceFile
order by [UPC Code]
Which seems to be working correctly.
However, when I to get the maximum width for each column using the following code:
List<int> maximumLengthForColumns =
Enumerable.Range(0, dataTable.Columns.Count)
.Select(col => dataTable.AsEnumerable()
.Select(row => row[col]).OfType<string>()
.Max(val => val.Length)).ToList();
I get an exception: "Sequence contains no elements.
However, I can run the following code on the same datable results from the query:
foreach(DataRow row in dataTable.Rows)
{
foreach(var item in row.ItemArray)
{
Console.Write(item + ",");
}
Console.WriteLine();
}
I can write out every row, field by field.
The next query, which excludes the "top 10" limit but otherwise is the same table:
select *
from wlo_104466_CustomerPriceFile
order by [UPC Code]
Generates the maximumLengthForColumns list perfectly.
This is the data generated when I run the first query in Microsoft SQL Server Management Studio:
UPC Code Supplier Code Part No Pack Qty Unit of Measure
---------------- ------------- ------------- --------------- ---------------
NULL HOB S228519T-P01 14 CTN
NULL LIN ED031252 4SRP CTN
NULL LIN ED031253 3.3SRP CTN
NULL LIN ED031267 10.3CT CTN
NULL LIN ED031269 11.9CT CTN
NULL LIN ED031271 10.3CT CTN
NULL LIN ED031288 3.7TB CTN
NULL LIN ED031289 4.6TB CTN
NULL LIN ED031290 3.5TB CTN
NULL LIN ED031291 3.7TB CTN
(10 row(s) affected)
I like using the clean look of the Linq code and would like to understand what I'm doing wrong.
Thanks for your help.
What I have tried:
1) iterating through the datatable using a foreach loop which seemed to work.
2) when tracing through the maximumLengthForColumns routine I noticed that the execution line never seems to get past the ".Select(row..." line on the query that throws the exception but does get down to the ".Max(val..." line in the query that does.
NEW
I tried using another table with all nulls in the first column:
C1 C2 C3 C4 C5
---------- ---------- ---------- ---------- ----------
NULL 1C2 1C3 1C4 1C5
NULL 2C2 2C3 2C4 2C5
NULL 3C2 3C3 3C4 3C5
NULL 4C2 4C3 4C4 4C5
NULL 5C2 5C3 5C4 5C5
NULL 6C2 6C3 6C4 6C5
NULL 7C2 7C3 7C4 7C5
NULL 8C2 8C3 8C4 8C5
NULL 9C2 9C3 9C4 9C5
and the max width query failed.
I then added 9 rows with all nulls in the second column:
C2 C3 C4 C5
---------- ---------- ---------- ---------- ----------
NULL 1C2 1C3 1C4 1C5
NULL 2C2 2C3 2C4 2C5
NULL 3C2 3C3 3C4 3C5
NULL 4C2 4C3 4C4 4C5
NULL 5C2 5C3 5C4 5C5
NULL 6C2 6C3 6C4 6C5
NULL 7C2 7C3 7C4 7C5
NULL 8C2 8C3 8C4 8C5
NULL 9C2 9C3 9C4 9C5
and the max function succeeded but it only seemed to check the rows without the null in the first column.
I then tried all nulls in the second column:
C2 C3 C4 C5
---------- ---------- ---------- ---------- ----------
1C1 NULL 1C3 1C4 1C5
2C1 NULL 2C3 2C4 2C5
3C1 NULL 3C3 3C4 3C5
4C1 NULL 4C3 4C4 4C5
5C1 NULL 5C3 5C4 5C5
6C1 NULL 6C3 6C4 6C5
7C1 NULL 7C3 7C4 7C5
8C1 NULL 8C3 8C4 8C5
9C1 NULL 9C3 9C4 9C5
and the function failed.