Filtering a DataGridView per column with filters visible above columns

Question

I have a rather specific query -

I want to be able to retrieve data from a database, display it in a DataGridView and allow the user to filter columns by inputting simple filter queries above each column.

For example:-

        | Foo   | Bar                 | Baz |
        ------------------------------------|
Filters | > 10  | 1/1/1980 - 1/1/2009 | Boo |
        | 12    | 1/3/1995            | Boo |
        | 99    | 5/12/2005           | Boo |
                     etc.

The method I thought would best address this problem was to have two DataTables, one which acts as the DataSource for the DataGridView (so I can have the view autogenerate the columns and not have to write a whole bunch of boilerplate to insert rows myself), which itself consists of a single row to hold the filters and the database data below it, and the second holding the database data which can be programmatically appended to the first.

The problem with that solution is that the columns are restricted to the type of the column, for example here Foo is an integer, Bar is a DateTime and Baz is a string. When trying to enter filter text into Foo or Bar the DataGridView throws an error stating that the cell cannot contain text.

Is it possible to have a row containing types utterly divorced from the types of the columns of the rest of the table?

Another solution might be to have textboxes sat above the grid synced with column addition, removal, resizing and user scrolling, however this seems like an enormous amount of work and knowing WinForms I'd probably find out 3 days in that this approach fails spectacularly due to some subtle WinForms limitation.

Solutions which are not possible - using WPF, using a 3rd-party DataGridView (unless, perhaps, an OSS DGV with a commercial usage license).

Currently it seems the best solution might be to import all data into the database datatable as strings (the data below the filters is read-only). That seems quite astoundingly hackish, however.

Am I missing something simple and obvious here? I'm happy to be proven wrong if it gives me some way forward! :-)

Solution

I did this one in the past and it was actually a real fun project. The idea is basically to inherit from the DataGridViewColumnHeaderCell base class, and do your own painting. It would take me alot of time to explain everything that's involved, but there's a really great article from Microsoft that got me started. I didn't do it exactly how they did it, but it got me on the right track.

http://msdn.microsoft.com/en-us/library/aa480727.aspx

Also, what you should take into account is that a DataTable has a DataView property on it which is of type DataView. That has a RowFilter property on it that you can use to filter out the rows. There's no need for two DataTables at all. Here's a good article on how to use the RowFilter property:

http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx