Click here to Skip to main content
15,867,895 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 21-Jan-13 6:49am
v2
Comments
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.

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:

C#
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.
tableName.Rows.Add(row);
//Persist at DB side.


Extension method article link: Extension Methods C#

Advantage:
1) Data filteration method.
2) Whenever DataRow will be created in your dll, you can use this method.
 
Share this answer
 
Comments
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.
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) = ""
                Else
                    Row.Item(Col.ColumnName) = Row.Item(Col.ColumnName).trim
                End If
            End If
        Next
    Next
End Sub
 
Share this answer
 
Comments
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    
Hi,
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)
da.Fill(table)
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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900