0

I'm facing a challenge when filtering values in a database table that involves considering case sensitivity for primary key recognition, while simultaneously allowing for case-insensitive filtering. Here's an example that illustrates the problem:

In my DataTable, I have the following data:

Primary Key column Another column
ABC data
abc DATA

I'm performing the filtering operation using the DataView.RowFilter property, where I assign the filter as "Another column LIKE '%DATA%'" to retrieve the desired results.

When the CaseSensitive property is set to false, I encounter an error indicating that the keys are not unique. To address this issue,

I attempted to change the CaseSensitive flag to true in the constructor. However, using the same filter "Another column LIKE '%DATA%'" and setting case sensitivity to true resulted in retrieving only one record [(ABC, data)], whereas before, with case insensitivity, I obtained two records [(ABC, data), (abc, DATA)].

I've tried to switch the CaseSensitive flag between true and false before applying the filter, but this approach has not been successful, as it leads to the aforementioned error stating that the keys are not unique.

I'm seeking advice or alternative approaches to achieve case-sensitive primary key recognition while still allowing for case-insensitive filtering. Any suggestions would be greatly appreciated. Thank you.

Konrad G
  • 1
  • 1
  • Case sensitivity is part of the database "collation", and it should be possible to specify collation per column. This is kind of needed to make indices work. Assuming you are actually asking about database queries and not filtering in memory data in a DataTable. – JonasH Jun 20 '23 at 14:17
  • Maybe you then need two columns. One for the PK, the other one for filtering. Store the value in lower case in the latter and also convert the user filter input into lower case. – Olivier Jacot-Descombes Jun 20 '23 at 16:19
  • @JonasH Since he mentions `DataView.RowFilter` and `DataTable`, I don't think collation applies. – NetMage Jun 20 '23 at 20:40
  • @OlivierJacot-Descombes I have a few columns but I don't I have influence on the content of the data, I edited the question to clarify that – Konrad G Jun 21 '23 at 07:37
  • Can't you simply make a filter like `LOWER(Another_column) LIKE '%lowerdata%'` – Olivier Jacot-Descombes Jun 21 '23 at 08:08
  • You either need to use a proper SQL directly which has many ways to handle that, or prepare data first using raw sql before loading it into the DataTable. I am not familiar with DataTable component, so can't help more than that. You also need to be more clear about what is DataTable. I assume it is a WinForms component. – Tanuki Jun 21 '23 at 23:23
  • @OlivierJacot-Descombes If I use this filter I get an error System.Data.EvaluateException "The expression contains undefined function call LOWER()." – Konrad G Jun 22 '23 at 09:58
  • @OlivierJacot-Descombes Expression syntax is database independent https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=net-7.0#system-data-datacolumn-expression – Konrad G Jun 23 '23 at 07:24
  • For the filtering, maybe you could use linq ? Starting point is DataTable.AsEnumerable(). e.g. : https://stackoverflow.com/questions/19449449/how-i-can-filter-a-datatable-with-linq-to-datatable – Moe Sisko Jun 29 '23 at 23:31

0 Answers0