Strongly-Typed BindingSource Filter

Question

Constructing a BindingSource.Filter string feels like an ugly, manual, forced way of filtering already retrieved data.

1. No explicit type checking
2. No explicit column name checking
3. No explicit SQL syntax checking
4. Requires manual ToString() formatting
5. DataSet design changes not propagated to Filter
6. Managing a Filter with multiple criteria from multiple controls quickly becomes tedious, error-prone, and unwieldy.

Using a typedTableAdapter.FillBy(typedDataSet.typedTable, @params ...) is a powerful, easy, and straightforward method for "filtering" between the database and the DataSet.

Does .NET provide any strongly-typed filtering between a strongly-typed DataSet and Form controls (perhaps through a BindingSource)?

Initial Bounty:
The initial bounty was awarded for a proof of concept (using a LINQ query as the DataSource). However, it does not demonstrate how to actually access the strongly-typed typedTableRow to perform the filtering.

Additional Bounty:
All of the casting through IList, DataView, DataRowView, DataRow, and typedTableRow has proven to be quite confusing.

Object                         Generic       Watch List Type
------                         --------      ------------
myBindingSource.List           IList         {System.Data.DataView}
myBindingSource.List[0]        object        {System.Data.DataRowView}

((DataRowView)myBindingSource.List[0]).Row
                               DataRow       typedTableRow

Demonstrate a BindingSource with a DataSource using a strongly-typed LINQ query (ie: with typedTableRow fields accessible in .Where( ... )).

Notes (for shriop):

• The form controls are bound to myBindingSource.
• myBindingSource.DataSource: typedDataSet
• myBindingSource.DataMember: typedTable

Filter Code (applied in FilterBtn_Click()):

myBindingSource.DataSource 
    = typedDataSet.typedTable.Where( x => x.table_id > 3).ToList();

After filtering, the BindingNavigator shows the appropriate number of records. However, if I navigate to any record which contains a null value, I get a StrongTypingException thrown in typedDataSet.typedTableRow.get_FIELDNAME(). Since this behavior only happens after filtering, I assume the LINQ filtering breaks something in the data binding.

Answer 1

Ok, I think this is what you want. I created a typed DataSet called AdventureWorks and added the Product table to it. I then added a DataGridView and a TextBox to a Form. I added an instance of the typed DataSet to the form. I added a BindingSource to the form. I set the DataSource of the BindingSource to the DataSet instance on the form. I set the DataMember to the Product table, which generated a ProductTableAdapter on the form. I set the DataSource of the DataGridView to the BindingSource. I bound the Text property of the TextBox to the Name property of the BindingSource, which resolves to the Name column of the Product table. In OnLoad, it had already generated a Fill for me using the TableAdapter and the Product DataTable. I then just had to add a single line to set my typed filter:

this.bindingSource.DataSource = this.adventureWorks.Product.Where(p => !p.IsProductSubcategoryIDNull()).ToList();

I then ran the form and was able to see only the filtered set of rows, and as I clicked through them, the text of the TextBox would change to match the name of the product of the selected row.

The ToList is key because the BindingSource does something goofy when either binding or supplying values out to the bound controls where without it you will get an exception that says

The method or operation is not implemented.
   at System.Linq.Enumerable.Iterator`1.System.Collections.IEnumerator.Reset()
   ...

You also have to remember to watch out for the nullable fields when applying your filter criteria and make sure you're using the typed Is*Null() methods.

While this is a fairly straightforward way, it throws exceptions when it displays column values that have nulls, unless you go into the DataSet designer and change the option for handling nulls to return a null instead of throwing an exception. This works for string columns, but not so well for other column types like DateTime.

After ALOT of research into how DataView implements this, which DataTable uses internally, I can't find a simple fully functional implementation and did find this answer which best describes the pain, Data binding dynamic data .

I did find a pretty simple solution if you're ok with binding to a copy of the data, using some logic from Simple way to convert datarow array to datatable . This gets you back to a DataTable, and using it's implementation, but with only your rows after filtering.

DataRow[] rows = this.adventureWorks.Product.Where(p => !p.IsProductSubcategoryIDNull()).ToArray();
if (rows.Length > 0)
{
    this.bindingSource.DataSource = rows.CopyToDataTable();
}
else
{
    this.bindingSource.DataSource = rows;
}

Now you should still be able to use this copy of the data to send updates back to the database if you get the DataTable back out of the DataSource, making sure that it's a DataTable and not a DataRow[], and send that DataTable into the TableAdapter's Update method. Then, depending on how you're doing things, you could refill your original table, and reapply your filter.

Answer 2

You can use linq on binding source:

this.BindingSource.DataSource = ((IList<T>)this.BindingSource.List).Where( ... );