Reputation: 5557
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
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
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