Datagridview filter with BindingSource

Question

I want to perform a filter on a DataGridView using 3 different TextBox. The solution below works, but does not consider very effective and good. Is there a way to improve it?

private void textBoxCod_TextChanged(object sender, EventArgs e)
{
    Filter();
}

private void textBoxArt_TextChanged(object sender, EventArgs e)
{
    Filter();
}

private void textBoxDesc_TextChanged(object sender, EventArgs e)
{
    Filter();
}

private void Filter()
{
    string queryFilter = "1=1"; //With this we avoid to check if there is the AND in the query
    var filterCod = textBoxCod.Text;
    var filterArt = textBoxArt.Text;
    var filterDescr = textBoxDesc.Text();
    if (filterCod != "")
        queryFilter += " AND product_code LIKE '%" + filterCod + "%'";
    if(filterArt != "")
        queryFilter += " AND article LIKE '%" + filterArt + "%'";
    if (filterDescr != "")
        queryFilter += " AND description LIKE '%" + filterDescr + "%'";

    bSource.Filter = queryFilter;
    dataGridView.DataSource = bSource;
}

Before I used the string.Format but I realized that not having optional parameters could not combine several filters.

Answer 1

You can remove the 1=1 hack by utilizing a collection of some sort, such as a List<string>, and String.Join.

Including braces with your if blocks aids readability.

You can avoid setting bindingSource filter if there are not actually any queries to run, and there is no need to reset the grids datasource:

    private void Filter()
    {
        var queries = new List<string>();
        //string queryFilter = "1=1"; //With this we avoid to check if there is the AND in the query

        var filterCod   = textBoxCod.Text;
        var filterArt   = textBoxArt.Text;
        var filterDescr = textBoxDesc.Text();

        if (filterCod != "")
        {
            queries.Add(String.Format("codice_prodotto LIKE '%{0}%'", filterCod));
        }

        if (filterArt != "")
        {
            queries.Add(String.Format("articolo LIKE '%{0}%'", filterArt));
        }

        if (filterDescr != "")
        {
            queries.Add(String.Format("descrizione_pt LIKE '%{0}%'", filterDescr));
        }

        if (queries.Count >= 1)
        {
            var queryFilter = String.Join(" AND ", queries);
            bSource.Filter = queryFilter;
            //dataGridView.DataSource = bSource;
        }

    }

Answer 2

Adding few improvements to @Steve's answer:

• It is always good to check for spaces(blanks): if(!string.IsNullOrWhiteSpace(filterCod))

• Wouldn't be nicer to have queries.Any() instead of queries.Count >= 1 ?

Answer 3

In addition to the other suggestions, I'd also advise you to consider moving away from pure SQL and instead use an ORM like Entity Framework or NHibernate, or even Dapper.NET.

---

And also consider splitting up your project into layers. Right now you're constructing SQL in your UI layer, which is all kinds of wrong and a maintainability nightmare going forward. Even your simplified example takes 30+ lines, I hesitate to guess how big the full .cs file is...

Take a look at these suggestions. I realize this seems like a lot of work, but the pay-off in the long run is well worth it. Dealing with well-structured code in small classes instead of managing a 1000+ lines .aspx.cs file is far more pleasant.