CODEWITHSUNDEEP
c#sql-serverlinq
Kyle
Kyle

Reputation: 5557

In a DataTable object, how can I get a list of columns and their min/max string.Length?

I'm getting a string truncation error when I attempt SqlBulkCopy and I need to debug which field is causing it.

I have this so far. I can get the column names but don't know how to proceed to get their min/max string.length.

var columnNames = from columns in table.Columns.Cast<DataColumn>()
                  select columns.ColumnName;

I don't know if it's possible with LINQ. If it isn't, I'm open to dumping this into SQL server and perform the analysis there too.

Upvotes: 0

Views: 1743

Answers (2)

Richard Deeming
Richard Deeming

Reputation: 31248

To get the length of the longest string stored in the column, something like this should work:

var columns = 
    from DataColumn column in table.Columns
    where column.DataType == typeof(string)
    select new
    {
        column.ColumnName,
        MaxLength = 
        (
            from DataRow row in table.Rows 
            where !row.IsNull(column) 
            select ((string)row[column]).Length
        ).Max()
    };

Or, in lambda syntax:

var columns = table.Columns.Cast<DataColumn>()
    .Where(column => column.DataType == typeof(string))
    .Select(column => new 
    { 
        column.ColumnName, 
        MaxLength = table.Rows.Cast<DataRow>()
            .Where(row => !row.IsNull(column))
            .Select(row => ((string)row[column]).Length)
            .Max() 
    });

EDIT: You could also start with the rows and enumerate the columns for each row, which might give you better performance over a large table:

var columns =
    from DataRow row in table.Rows
    from DataColumn column in table.Columns
    where column.DataType == typeof(string) && !row.IsNull(column)
    let length = ((string)row[column]).Length
    group length by column.ColumnName into g
    select new
    {
        ColumnName = g.Key,
        MaxLength = g.Max()
    };

In lambda syntax:

var columns = table.Rows.Cast<DataRow>()
    .SelectMany(row => table.Columns.Cast<DataColumn>(), (row, column) => new { row, column })
    .Where(pair => pair.column.DataType == typeof(string) && !pair.row.IsNull(pair.column))
    .GroupBy(pair => pair.column.ColumnName, (key, items) => new 
    { 
        ColumnName = key, 
        MaxLength = items.Max(x => ((string)x.row[x.column]).Length) 
    });

Upvotes: 4

Luaan
Luaan

Reputation: 63772

There's really not much point in using LINQ for this, but going with it:

var columnNames = from column in table.Columns.Cast<DataColumn>()
                  select new { column.ColumnName, column.MaxLength };

If you need more information, you can also get the schema itself. This just requires you to ExecuteReader some command (select * from table) and call GetSchemaTable on that data reader. This will result in a table that contains a lot more information about each of theses columns (one row per column).

Upvotes: 1

Related Questions