Convert Empty Strings To DBNull

Converts all empty or whitespace-only string values in a DataTable to DBNull.

Only columns of type String are evaluated
Values already set to DBNull are not modified
Whitespace-only strings (e.g., spaces or tabs) are also converted to DBNull

    Public Function ConvertEmptyStringsToDBNull(dt As DataTable) As DataTable
    
        For Each row As DataRow In dt.Rows
            For Each col As DataColumn In dt.Columns
                If col.DataType Is GetType(String) Then
                    Dim value As Object = row(col)
                    If value IsNot DBNull.Value AndAlso String.IsNullOrWhiteSpace(value.ToString()) Then
                        row(col) = DBNull.Value
                    End If
                End If
            Next
        Next
        
        Return dt
    End Function

    Public Function ConvertEmptyStringsToDBNull(dt As DataTable) As DataTable
    
        For Each row As DataRow In dt.Rows
            For Each col As DataColumn In dt.Columns
                If col.DataType Is GetType(String) Then
                    Dim value As Object = row(col)
                    If value IsNot DBNull.Value AndAlso String.IsNullOrWhiteSpace(value.ToString()) Then
                        row(col) = DBNull.Value
                    End If
                End If
            Next
        Next
        
        Return dt
    End Function

Posted by ipercube