VB.NET- Modify datagridview's datasource dynamically
Hi,
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.
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.
David L. Hansen
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).
ASKER
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?
.
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?
.
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...
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.TextASKER
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?
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?
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.
The example at the site I referenced shows datasets being used. I usually just declare and use a single datatable.
ASKER
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.
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.
ASKER
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.
Well, at least it works, and the response time when refreshing the grid is greatly improved.
Thanks very much.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
If this suggestion is not applicable then forget I mentioned it.
Can I help with anything else before you award the points?