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.
dawesleyAsked:
Who is Participating?
 
David L. HansenProgrammer AnalystCommented:
You are very welcome. :-)
0
 
David L. HansenProgrammer AnalystCommented:
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).
0
 
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?
.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
David L. HansenProgrammer AnalystCommented:
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

0
 
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?
0
 
David L. HansenProgrammer AnalystCommented:
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.
0
 
David L. HansenProgrammer AnalystCommented:
The example at the site I referenced shows datasets being used.  I usually just declare and use a single datatable.
0
 
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.
0
 
David L. HansenProgrammer AnalystCommented:
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.
0
 
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.
0
 
David L. HansenProgrammer AnalystCommented:
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.
0
 
David L. HansenProgrammer AnalystCommented:
Can I help with anything else before you award the points?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.