How to filter DataGridView using TextBoxes and list as DataSource using Dapper?

Question

I am aiming to filter the datagridview using the TextBoxes.

This is my current code.

Form

// Load the datagrid with Product Specifications
List<ProductSpecificationModel> productSpecificationsList = new List<ProductSpecificationModel>();
public void refreshData()
{
    DataAccess db = new DataAccess();
    productSpecificationsList = db.GetSpecifications();
    productSpecGrid.DataSource = productSpecificationsList;  
}

I fill the list from an external class:

DataAccess

public List<ProductSpecificationModel> GetSpecifications()
{
    using(MySqlConnection conn = new MySqlConnection(ConnectionString.ConnString))
    {
        var output = conn.Query<ProductSpecificationModel>(@"
                SELECT
                    ID,
                    ProductCode,
                    ProductDescription
                FROM ProductSpecifications;").ToList();
        return output;
    }
}

Model

public class ProductSpecificationModel
{
    public int ID { get; set; }
    [DisplayName("Product Code")]
    public string ProductCode { get; set; }
    [DisplayName("Product Description")]
    public string ProductDescription { get; set; }    
}

How I tried filtering

private void searchFunction()
{
    List<string> parts = new List<string>();
    if (filterCode.Text.Length > 0)
    {
        parts.Add("ProductCode like '%" + filterCode.Text + "%'");
    }
    if (filterDescription.Text.Length > 0)
    {
        parts.Add("ProductDescription like '%" + filterDescription.Text + "%'");
    }

    (productSpecGrid.DataSource as DataView).RowFilter = string.Join(" and ", parts);

}

private void searchBtn_Click(object sender, EventArgs e)
{
    searchFunction();
}

Error

When I click search this shows up:

An unhandled exception of type 'System.NullReferenceException' occurred in Application.exe
Additional information: Object reference not set to an instance of an object.

Try 2

Using these answers

https://stackoverflow.com/a/26608951/12485722

Also have tried this:

// From this
(productSpecGrid.DataSource as DataView).RowFilter = string.Join(" and ", parts);

>>>>>

// To this
(productSpecGrid.DataSource as DataTable).DefaultView.RowFilter = string.Join(" and ", parts);

And the error:

An unhandled exception of type 'System.NullReferenceException' occurred in Application.exe Additional information: Object reference not set to an instance of an object.

Answer 1

You are getting this exception because you are trying to cast the DataSource to the wrong types.

Here:

(productSpecGrid.DataSource as DataView)

and here:

(productSpecGrid.DataSource as DataTable)

So, if you declare a variable like:

var ds = (productSpecGrid.DataSource as DataView);

The ds yields null and trying to access a method or set a property of a null object throws the System.NullReferenceException exception.

The right type of the .DataSource is:

List<ProductSpecificationModel>

So the right cast is:

(productSpecGrid.DataSource as List<ProductSpecificationModel>)

---

For the filter part, and as you are using a List<T>, use LINQ to get a filtered list and reset the .DataSource property.

For example:

using System.Linq;

private List<ProductSpecificationModel> Filter(string productCode, string productDescription)
{
    if (!string.IsNullOrEmpty(productCode) && !string.IsNullOrEmpty(productDescription))
        return productSpecificationsList
            .Where(x => x.ProductCode
            .IndexOf(productCode, StringComparison.InvariantCultureIgnoreCase) >= 0 && x.ProductDescription
            .IndexOf(productDescription, StringComparison.InvariantCultureIgnoreCase) >= 0)
            .ToList();
    else if (!string.IsNullOrEmpty(productCode))
        return productSpecificationsList.Where(x => x.ProductCode
        .IndexOf(productCode, StringComparison.InvariantCultureIgnoreCase) >= 0).ToList();
    else if (!string.IsNullOrEmpty(productDescription))
        return productSpecificationsList.Where(x => x.ProductDescription
        .IndexOf(productDescription, StringComparison.InvariantCultureIgnoreCase) >= 0).ToList();
    else
        return productSpecificationsList;
}

... and call it like:

productSpecGrid.DataSource = Filter(filterCode.Text, filterDescription.Text);

... and to set back the main list:

productSpecGrid.DataSource = productSpecificationsList;