Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ADO.NET VB.NET
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 7:35am
Edited 21-Jan-13 7:49am
v2
Comments
rizwan muhammed khan gouri at 22-Jan-13 8:47am
   
Please give your code???????
Wietze Bron at 22-Jan-13 12:22pm
   
There is no code yet, only a question.
Rate this: bad
good
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.
    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.
  Permalink  
Comments
Wietze Bron at 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
good
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) = ""
                    Else
                        Row.Item(Col.ColumnName) = Row.Item(Col.ColumnName).trim
                    End If
                End If
            Next
        Next
    End Sub
  Permalink  
Comments
Kishor Deshpande at 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 at 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 at 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 at 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)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 305
1 OriginalGriff 225
2 DamithSL 130
3 Kornfeld Eliyahu Peter 130
4 Peter Leow 95
0 OriginalGriff 7,355
1 DamithSL 5,199
2 Sergey Alexandrovich Kryukov 4,942
3 Maciej Los 4,906
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 22 Jan 2013
Copyright © CodeProject, 1999-2014
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