c# How to filter Datagridview bind to dataset - Accent Insentitive

Question

I have a DataGridView bind to a DataSet, some value (Name) have accent (ex: é, í, ž, ć, é, á) and I would to perform a filtering accent insensitive.

Usually I am filtering my DataGridView like this:

private void textBox1_TextChanged(object sender, EventArgs e)
{
    MyDataSet.People.DefaultView.RowFilter = "Name LIKE '%" + textBox1.Text + "%'";
    dataGridView1.DataSource = MyDataSet.People.DefaultView;
}

I tried to change this in my database:

CREATE TABLE [dbo].[People] (
[Num]        INT           NOT NULL,
[Name]     NVARCHAR (50) NOT NULL
);

by this

CREATE TABLE [dbo].[People] (
[Num]        INT           NOT NULL,
[Name]     NVARCHAR (50) COLLATE Latin1_General_CI_AI NOT NULL
);

and tried to change:

private void textBox1_TextChanged(object sender, EventArgs e)
{
    MyDataSet.People.DefaultView.RowFilter = "Name LIKE '%" + textBox1.Text + "%' COLLATE Latin1_General_CI_AI";
    dataGridView1.DataSource = MyDataSet.People.DefaultView;
}

but it doesn't work.

Answer 1

I can offer the following decision.

Add to datatable new column named NameWithoutAccent.

MyDataSet.People.Columns.Add("NameWithoutAccent", typeof(string));

Fill this column with values with removed accents. Filtering will occur by this column.

foreach (DataRow row in MyDataSet.People.Rows)
{
    string name = (string)row["Name"];
    string nameWithoutAccent = RemoveAccent(name);
    row["NameWithoutAccent"] = nameWithoutAccent;
}

First normalize the string to remove the accents with String.Normalize method. Then remove all diacritics with regular expressions. M is Unicode category "All diacritic marks".

public string RemoveAccent(string name)
{
    string normalizedName = name.Normalize(NormalizationForm.FormD);
    string pattern = @"\p{M}";
    string nameWithoutAccent = Regex.Replace(normalizedName, pattern, "");
    return nameWithoutAccent;
}

Hide this column after data binding.

dataGridView1.Columns["NameWithoutAccent"].Visible = false;

Input filter is cleaned from accents too.

private void textBox1_TextChanged(object sender, EventArgs e)
{
    string nameWithoutAccent = RemoveAccent(textBox1.Text);
    MyDataSet.People.DefaultView.RowFilter = "NameWithoutAccent LIKE '%" + nameWithoutAccent + "%'";
}