1

I have a DataTable containing 10000+ rows, resulting from a SQL Server query. The DataTable is used as the DataSource for a DataGridView, like so (simplified code):

MyBindingSource = New BindingSource(MyDataTable, Nothing)
MyDataGridView.DataSource = MyBindingSource

As this takes a looong time to load, I would like to limit the number of rows displayed in the DataGridView, somehow.

I can't use TOP in my query, because I need all the data present in the DataTable for filtering later on, without re-setting the DataSource (MyBindingSource.Filter = MyFilter).

Also, I can't use the Filter property for limiting the number of rows, because there's no relevant data in the query result that I can use for this. To get around this, I've thought about adding TSQL's ROW_NUMBER to the query result (MyBindingSource.Filter = "[RowNumberField] < 100"), but this would only work when no other fields are used in the filter.

Any ideas?

Stijn
  • 1,970
  • 3
  • 27
  • 36
  • Check this [Implementing Virtual Mode in the Windows Forms DataGridView Control](https://msdn.microsoft.com/en-us/library/15a31akc.aspx) – Fabio Jun 25 '15 at 12:26
  • I came across that article while looking for a solution, but it seems like a lot of fuzz for such a seemingly simple task... – Stijn Jun 25 '15 at 12:39
  • Are you sure that "bottle neck" is `datagridview` and not you SQL query? – Fabio Jun 25 '15 at 15:23
  • @Fabio has a point about scaling, eventually you will be pulling too many records over the client. Although that action is much faster than the displaying of the grid. You can pull the table over read-only, create a DataView, add a filter, and then attach it to the DGV. You may want to do a search for 'DatagridView Paging' - do not confuse with DataGrid which is an ASP web control. – rheitzman Jun 25 '15 at 15:28
  • The query only takes a few seconds, displaying the data in the DataGridView takes a lot longer. Of course this depends on how many records the query returns. But I'll take a look at paging. – Stijn Jun 25 '15 at 15:34

1 Answers1

0

Here are a two options:

  1. I would simply implement pagination on all of your views (filtered or unfiltered) using this technique of using a BindingNavigator GUI control which uses a BindingSource object to identify page breaks.

  2. You can also filter by more than one criteria by using an OR operator but I don't see how that helps you with your current approach because your row numbers will have to be recalculated after each filter e.g. [RowNumberField] < 100 might return 100 rows with no filter by only 10 after a filter.

What you could do is move the filtering logic to your SQL query and then always show only the first X rows based on the row number (which I assume you are dynamically adding each time using TSQL's ROW_NUMBER()).

The advantage to this approach is that you can perform much more powerful filtering in TSQL and it keeps you Data Source smaller.

If you do take this approach, be careful about mixing your queries in with your view logic - I would recommend the Repository Pattern.

Community
  • 1
  • 1
Charlie
  • 2,004
  • 6
  • 20
  • 40
  • I'll try to implement pagination to see what results I get. Working on other things for the moment, but I'm hoping to get back to this in a few weeks. – Stijn Jun 29 '15 at 06:42