Return the datatable max column width over all rows.

See more:

C#

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.