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.

Solution 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.

Solution 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.

Comments

[Yuriy Faktorovich]

Wouldn't the nested foreach statements be nice and clean, why do you have to have LINQ?

[TaW]

Wouldn't trimming in the DB be the better ie more efficient solution? LINQ will certainly not be more efficient than a loop, just more stylish..

[jradich1234]

When you attempt to match you could trim at that point

[disasterkid]

@TaW not when you don't know what your query is going to be. Or is there a way to trim all values regardless of query?

[disasterkid]

@jradich1234 that is true but getting rid of all unnecessary spaces in the beginning is more convenient, I suppose.

[TaW]

Hm, that sounds very mysterious; unless you want to the obnoxious select * from sometable;

[TaW]

Let me add the obvious: If your DB data need sanitizing make sure to do it or have it done; also make sure the sources of the paddings get stomped out as well..

[disasterkid]

Thanks! I will use your solution instead of LINQ.

[Drew Chapin]

To prevent null reference errors add if( row[column] != DBNull.Value )