Trim All Cells of a DataTable

Question

I have a DataTable that is filled using an SQL query. If the values of this DataTable are not trimmed (from both left and right), the program is unable to find correct matches. So I am looking for an efficient LINQ query (not two nested foreach statements) to trim all the values in the DataTable and return a clean version of it.

Answer 1

The most efficient way is to do that in the database, f.e.:

SELECT RTRIM(LTRIM(ColumnName)) AS TrimmedColumn FROM TableName

If you must do it with C# the most performant way is a simple loop (LINQ also uses loops):

DataColumn[] stringColumns = table.Columns.Cast<DataColumn>()
    .Where(c => c.DataType == typeof(string))
    .ToArray();

foreach(DataRow row in table.Rows)
   foreach(DataColumn col in stringColumns)
      row.SetField<string>(col, row.Field<string>(col).Trim());

In general, don't use LINQ to modify the source. A query should not cause side-effects.

Answer 2

I'm not sure if you can apply a LINQ Select statement on a DataTable and use the Trim() on the String class to achieve your goal. But as a Database Developer I would suggest acting on the SQL Query and use the Rtrim(Ltrim(field1)) AS field1 on your query to get rid of the spaces before the datatable.