Introduction
The Winforms BindingControl
is very useful to filter DataGridView
and similar controls quickly, because it has a Filter
property which allows the display data to change without modification of the underlying data source. But ... it's a string
. And there is no "Parameters
" collection.
So you can't pass your user input as a parameterized query, you have to use string
concatenation:
dataSource.Filter = string.Format("description LIKE '%{0}%' OR FullText LIKE '%{0}%'", s);
And that means that if your user enters "doesn't work" or "don't do it!" or "100% or more" then your app crashes (you can catch the exception, yes - but that doesn't help your user much) because the filter sees the string
:
description LIKE '%doesn't%' OR FullText LIKE '%doesn't%'
and doesn't know what to do with it.
Background
The solution - or at least the only solution I've found - is to escape the "dodgy" characters in the input string
:
'*', '%', '[', and ']' need to be surrounded by square brackets
'\'' needs to be replaced with a pair of single quotes.
Simple enough, so I knocked up an extension method:
using System.Diagnostics;
using System.Text;
namespace GeneralTesting
{
public static class ExtensionMethods
{
private static string[] replace = { "*", "%", "[", "]", "'" };
private static string[] with = { "[*]", "[%]", "[[]", "[]]", "''" };
public static string EscapeLikeValue(this string raw)
{
Debug.Assert(replace.Length == with.Length, "replace and with do not match");
StringBuilder sb = new StringBuilder(raw.Length * 3);
sb.Append(raw);
for (int i = 0; i < replace.Length; i++)
{
sb.Replace(replace[i], with[i]);
}
return sb.ToString();
}
}
}
Using the Code
Simple:
private void tbFilter_TextChanged(object sender, EventArgs e)
{
string s = tbFilter.Text;
if (!string.IsNullOrWhiteSpace(s))
{
s = s.EscapeLikeValue();
dataSource.Filter = string.Format("description LIKE '%{0}%' OR FullText LIKE '%{0}%'", s);
}
else
{
dataSource.Filter = null;
}
}
Points of Interest
I learned that Microsoft doesn't think ahead and too many of its programmers don't understand parameterized queries, which is both sad and worrying...
History
- 2018-08-01: First version
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?