I have a VB.NET 2008 Windows form with a datagridview bound to a datasource who's source is an SQL table, but the datasource could also be a view or a stored procedure that references the table.
On the Windows form, I allow the user to select from various filter criteria to dynamically filter the bindingsource of the datagridview.
My problem is that the datasource is a table containing over 100,000 rows, and the bindingsource filter acts like a "HAVING" clause; that is, it filters the recordset after the
datasource has collected all the records in the table. This causes a major delay when
refreshing the grid.
What I really need to do is change the SELECT command of the datasource or the WHERE clause of the SELECT command dynamically, so only the filtered set of records is returned
As an alternative to this, I've toyed with creating a dynamic SQL stored procedure who's WHERE clause is stored in a table record that I update from the app, but this is a multi-user
app and I have no way to control one user's WHERE clause from overwriting another user's
I hope I haven't been too confusing. Does anyone know how to solve this problem? Thanks
in advance to anyone who can come up with a solution.