Background filling a DataGridView from an SqlDataAdapter

Question

I have a large dataset (over 100,000 records) that I wish to load into a DataGridView. The stored procedure that does this can take 10 seconds or more to complete.

So far I have a BackgroundWorker preventing the UI from locking up, and have implemented a rudimentary 'Please Wait' dialog.

What I'd like to do is somehow populate the DataGridView with results as they get returned from the database somehow. The best way to describe it is how the SQL Server Management Studio does it - when a query runs, rows come back immediately even though the query is still executing. There's also a button to stop the query and keep the rows that have been returned.

How can I do this in my own code?

The DataGridView is only used to display the rows of data, and then the user clicks one to do something else. Nothing gets written back to the database.

Answer 1

I doubt you can do it in a DataGridView the same way as Management Studio does. I'd say that you get all the rows in your app at once when the stored procedure call completes.

Answer 2

It seems like the best option would be to use some sort of paging mechanism, so you only show the user a set amount of data at a time. That would speed up pulling the data and loading the page. You could use the built-in paging of the GridView (I would recommend using the .NET cache with this approach because it pulls the whole dataset each time even though it only displays a page of records). You could also implement paging with LINQ to SQL where you only grab a page at a time. Below is a link to a good article I found recently that explains how to do that.

http://www.dbtutorials.com/display/linq-to-sql-paging-cs.aspx

Answer 3

You may also look into some backend tuning, as well. Adding an index in such a case has helped us out many times. Try running the stored procedure from SQL Server Management Studio with the "Execution Plan" option enabled. Look for places where the stored procedure might be bogging down (i.e. high execution percentages). When you hover over the items, you'll see a list of execution details. At the bottom of the list, look to see if any fields are being compared. Those are dead giveaways for indexing candidates.

Answer 4

100,000 rows in a datagridview? just say "no"!

1. the user cannot see 100,000 rows at a time
2. the network traffic to transmit 100,000 rows is not insignificant
3. the memory overhead for 100,000 datagridview rows is not insignificant
4. the user only needs to select one row and go on
5. if this app is ever used by more than one user at once, the DBA will hunt you down

follow austin's advice, and display only a page at a time

Answer 5

As suggested by others, displaying 100K records in a grid sounds like a bad idea, but if you really have to...

You're on the right track with your backgroundworker thread, but you'll have to use a data reader and read the rows sequentially as they are returned. Your background worker thread would then need to marshal a grid row addition through to the UI thread for every row read from the data reader.

Be aware that any benefit from using a separate thread to keep the UI responsive will be negated as it will be busy constantly adding rows anyway. I propose you implement some sort of batching approach, and have the UI add new rows only once every second or so. You'll want to be very careful here, and keep a possible race condition in mind. A situation could arise where your backgroundworker is adding rows from the datareader to some sort of collection, and your UI might want to read the collection at the same time - this will almost certainly result in issues.

Answer 6

any real-life COMPLETE small app examples (not the buggy microsoft or code project crap that never works) of datagridview - where 50,000 + rows are loaded and each column has to be word-wraped into (hieght-wise). With the data coming in as one scroll down. but allowing saving, editing and deleting as well when in operation. any example...i've checked all the one one net - all have bugs and are a pain as they are somone elses code and im new to vb and paging