Better way to filter records on a grid as user types few letters.

Question

My standalone Windows app. is written on C# 2008 and back-end is MySQL 5.1.

In a Windows Form, I have a TextBox control on top and a DataGridView below. Initially, as the Form loads, the DataGridView is filled with Item Names of products in sorted order. As and when the user starts typing few letters in the TextBox, the records in the DataGridView starts filtering.

At present a SQL query with LIKE is fired. Though there are no performance issues even after two years, but I am wondering that it is not a better way to fire this query after each key-stroke.

The alternative in my mind is to take all the Item Names in a Collection as the form loads. As the user types, I may use LINQ to filter records from the Collection.

But there is one problem. First, I want to store rows with 5 columns in the Collection, and secondly, there are so many Items, it will consume more memory to hold.

And there is a third concern also. Even if all the five columns are somehow stored in a Collection, how to populate the DataGridView each time with a new set of records when the user starts typing keywords?

Answer 1

Why do you use additional collections (Item Names)? Use datasource of DataGridView. I think, datasource is a collection of objects or it can be datatable (it is also in-memory collection). So just use client-side filter that build-in into datasource, datatable for example or linq for datasource (collection of objects).

If your grid datasource is datatable use dataview to filter rows, otherwise (collection of objects) use LINQ.

ADDED:

DataView is a "wrapper" of DataTable. Use it to filter records. DataTable has as many as you have memory on client and less then int.MaxInt (I guess, saurabh is right).

If your product's table is too large using datatable it is not a option in this case. I recommend to use combination of client and server side filters. For example, some user can work with some category of products, so application can filter products by category on server-side and by name on client-side.

Answer 2

You can make use of ICollectionView to filter and sort data. Try it with the datasource of your grid.

i am sure that it will result in the more performance benefit.

do you use paging also on your grid?

See it here