Click here to Skip to main content
13,088,439 members (56,826 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Is there a way to automatically remove all whitespaces when I fill an ADO.Net table through an SqldataAdapter ?

I would like to leave ansi padding on for my SQL server and I would like to prevent to Trim each datarow field or datarowview field individually in my code.

Is there a way that the dataadapter or the bindingsource can do this for me automatically, through a central setting.
Posted 21-Jan-13 6:35am
Updated 21-Jan-13 6:49am
rizwan muhammed khan gouri 22-Jan-13 8:47am
Please give your code???????
Wietze Bron 22-Jan-13 12:22pm
There is no code yet, only a question.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Well in that case you can add your own extension method to your datarow class.
Pass datarow, loop through all datarow fields, write code to remove white spaces.
You can create a separate folder called as Fliters and add class DataRowFilter in which you write a method RemoveWhiteSpaces()

Code After such implementation will look like:

    DataRow row;
    row = tableName.NewRow();
    // Then add the new row to the collection.
    row["greeting"] = "Hello World   ";
    row["text"] = "How are you?   ";
    row.RemoveWhiteSpaces(); //After implementation you will get this method available for datarow class.
    //Persist at DB side.

Extension method article link: Extension Methods C#

1) Data filteration method.
2) Whenever DataRow will be created in your dll, you can use this method.
Wietze Bron 22-Jan-13 12:21pm
Hi Kishor, thanks for your reaction.
Your solution still needs to be used on individual rows/columns.
In that case there is already the Row.Item(Col.ColumnName).Trim method.
I need one setting / command for the entire table.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

I already made this sub myself, which can be called after the DataAdapter has filled the table. However the performance tradeoff is too much (especially with larger tables).

Friend Sub Trim_DataTable(dt As DataTable)
    For Each Row As DataRow In dt.Rows
        For Each Col As DataColumn In dt.Columns
            If Col.DataType = System.Type.GetType("System.String") Then
                If IsDBNull(Row.Item(Col.ColumnName)) Then
                    Row.Item(Col.ColumnName) = ""
                    Row.Item(Col.ColumnName) = Row.Item(Col.ColumnName).trim
                End If
            End If
End Sub
Kishor Deshpande 22-Jan-13 21:39pm
It's obvious that there will be performance issue cause your are dealing with [row*column] number of items. :(
Wietze Bron 23-Jan-13 12:47pm
Yes, hence my question in the first place.
Using field numbers also doesn't gain enough in this case.
I am afraid there is no real solution.
Kishor Deshpande 23-Jan-13 22:44pm
Can we try one more approach?
While adding Values to DataColumn at the time of insert itself, we can have a Format() method for Strings which will before adding a value to column remove white spaces.
If you have a central method that adds DataColumn to DataRow and DataRow to DataTable, we call Format() only once.
But the existing data in database will remain as it is :(
Wietze Bron 24-Jan-13 13:20pm
Hi Kishor, thanks for givin it another try.
The code I need to populate my table is as follows:

Dim DBconnection As String
Dim selectCommand As String
Dim da As SqlDataAdapter
Dim bs As New BindingSource()
Dim dgv As New DataGridView
Dim table As New DataTable()

DBconnection = "user id=...."
selectCommand = "select * from ...."

da = New SqlDataAdapter(selectCommand, DBconnection)
bs.DataSource = table
dgv.DataSource = bs

This means I will not be able to add DataColumns individually.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.170813.1 | Last Updated 22 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100