RowFilter including [ character in search string

Question

I fill a DataSet and allow the user to enter a search string. Instead of hitting the database again, I set the RowFilter to display the selected data. When the user enters a square bracket ( "[" ) I get an error "Error in Like Operator". I know there is a list of characters that need prefixed with "\" when they are used in a field name, but how do I prevent RowFilter from interpreting "[" as the beginning of a column name?

Note: I am using a dataset from SQL Server.

Answer 1

So, you are trying to filter using the LIKE clause, where you want the "[" or "]" characters to be interpreted as text to be searched ?

From Visual Studio help on the DataColumn.Expression Property :

"If a bracket is in the clause, the bracket characters should be escaped in brackets (for example [[] or []])."

So, you could use code like this :

        DataTable dt = new DataTable("t1");
        dt.Columns.Add("ID", typeof(int));
        dt.Columns.Add("Description", typeof(string));

        dt.Rows.Add(new object[] { 1, "pie"});
        dt.Rows.Add(new object[] { 2, "cake [mud]" });

        string part = "[mud]";
        part = part.Replace("[", "\x01");
        part = part.Replace("]", "[]]");
        part = part.Replace("\x01", "[[]");

        string filter = "Description LIKE '*" + part + "*'";

        DataView dv = new DataView(dt, filter, null, DataViewRowState.CurrentRows);

        MessageBox.Show("Num Rows selected : " + dv.Count.ToString());

Note that a HACK is used. The character \x01 (which I'm assuming won't be in the "part" variable initially), is used to temporarily replace left brackets. After the right brackets are escaped, the temporary "\x01" characters are replaced with the required escape sequence for the left bracket.