CODEWITHSUNDEEP
c#.netvb.netwinformsdatagridview
Stijn
Stijn

Reputation: 2060

Limit rows displayed in DataGridView when using DataSource

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?

Upvotes: 1

Views: 4216

Answers (1)

Charlie
Charlie

Reputation: 2261

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.

Upvotes: 0

Related Questions