VB.NET- Modify datagridview's datasource dynamically


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
from SQL.

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
WHERE clause.

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David L. HansenCEOCommented:
You should be able to just build the sql statement dynamically during runtime after the user has made their filter  choices.  So, if they choose a certain window of time or a specific database for example; the code builds will build the sql statement and send it off to the database.  When the table is returned from the database then you just rebind the new table to the datagridview (or just refesh the already bound table).
dawesleyAuthor Commented:
Sorry, but I don't understand what you're saying.  Where does the rebuilt SQL statement wind up
in the SQL Server database?  And this is a multi-user environment, so I can't drop a stored proc
or view and re-create it dynamically. It could get re-created from some other user before I re-fill my
datasource for the datagridview.

Am I missing something?
David L. HansenCEOCommented:
Look at this: http://www.java2s.com/Code/VB/Database-ADO.net/GetdatafromSelectcommand.htm
Note: the sql variable is a string and can be altered to read anyway you like.

Such as...
sql = "Select * From "
sql += TextBoxDatabase.Text
sql += "Where myDate Between " & TextBoxBegin.Text & " AND " & TextBoxEnd.Text

Open in new window

OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

dawesleyAuthor Commented:
I do this sort of thing all the time, but it doesn't seem to solve my problem.  My datagridview is bound
in designer with specific column widths and column formatting and has a couple of added "link" columns.  Are you suggesting that I set up the columns of the datagridview in code and late bind it?
David L. HansenCEOCommented:
I've always done late binding with my DataGridView's datasources.  I find it much more flexable and maintainable as the user makes changes (Insert, Update, etc.).  I also set the column widths late (at runtime).  For debugging it's great too, I can use a different Database environment for debugging and another for production.
David L. HansenCEOCommented:
The example at the site I referenced shows datasets being used.  I usually just declare and use a single datatable.
dawesleyAuthor Commented:
I'm trying this but I have 68 columns in my datagridview, so it will take a little time to finish.  I'll get back to you in a while. Thanks.
David L. HansenCEOCommented:
Wow.  Good luck.  I think I'd test the concept with just a few columns before you go to all the setup trouble for the whole thing.
dawesleyAuthor Commented:
I did.  I'm now setting the properties for all 68 columns, but manually setting the width, formatting, header text, etc.for 68 columns is exactly what I was trying to avoid.

Well, at least it works, and the response time when refreshing the grid is greatly improved.

Thanks very much.
David L. HansenCEOCommented:
You are very welcome. :-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David L. HansenCEOCommented:
I suspect you could display only a subset of the columns based on the user's need at a given time.  When their needs change you could change the columns they see.  

If this suggestion is not applicable then forget I mentioned it.
David L. HansenCEOCommented:
Can I help with anything else before you award the points?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.