Options for DataTable string comparisons

  • Thread starter Thread starter Bill Menees
  • Start date Start date
B

Bill Menees

Are there any plans to make string comparisons within DataTables more
configurable? Specifically, I'd like to be able to specify different
compare options for each DataColumn in a DataTable.

My primary problem is that I'm loading varchar data from an Oracle table
into a DataTable. The primary key values can differ only by trailing
whitespace in the database. When I try to add the primary key on the
DataTable, it throws a UniqueConstraint violation because it says the values
aren't unique. For example, in the database "X" and "X " are distinct
values. When the DataTable compares the two, it treats them as the same.

It looks like it would be very simple to add support for a
"ConsiderTrailingWhitespace" comparison option at the DataTable or
DataColumn level. The DataTable.Compare method (where all the work is done)
is internal, so it shouldn't break anyone's code to add this.

Hoping this will be in .NET 2.0,

Bill
 
Hi Bill,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need the trailing white space
support for primary keys. If there is any misunderstanding, please feel
free to let me know.

As far as I know, this behavior is by design, both in DataTable and in SQL
Server. I have just tried this on SQL Server, it also complains about
primary key constraint violation when I was trying to add "X" and "X " to
the primary key column.

Anyway, thank you very much for your feedback. I will forward this to the
corresponding group.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Yes, that is the correct interpretation of the issue: ignoring trailing
whitespace in key comparisons should be optional.

I'm aware that DataTable behaves like SQL Server, but DataTable should be
more generic than that. SQL Server's whitespace handling rules are
different from other databases' rules (e.g., Oracle). Since .NET supports
Oracle access, DataTable ought to be able to handle key comparisons in an
Oracle-compatible way too. (FWIW, I don't like Oracle, but I have to
support it because some of my clients use it.)

I used the Reflector utility to decompile several System.Data classes and
ultimately tracked the trailing whitespace logic back into the internal
DataTable.Compare method. It always ignores trailing spaces, but it should
be very simple to allow each DataColumn to specify whether or not trailing
spaces should be ignored. It can easily be done as a non-breaking change if
DataColumn exposes a new read/write bool property that defaults to the
current "ignore" behavior. (I mention that it would be a non-breaking
change because I'm sure the .NET 2.0 API is pretty close to being locked
down...)

Thanks for your response and for forwarding this to the correct group,

Bill
 
You're welcome, Bill.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top