As new features become standard in technology, users come to expect these features in other areas. One feature that Google recently added was the auto-search as you type. I wanted to provide this same functionality to my WinForms users. After some trial and error, I figured out what I feel is a fairly simple yet powerful method for simulating the Google-style auto-search. Basically, you use a
DataView, and a View Filter to store and filter the data. Then, we use the
KeyUp event on the
TextBox to fire the filter.
The data objects provided by ADO.NET are very powerful and can allow us to manipulate data in a disconnected environment. If we were to attempt this type of operation using a database, it would entail re-querying the data every time the user added or subtracted a letter. This would be a costly application to operate. Instead, we can download all of our data into a
DataTable and then disconnect from the database. From here on out, the database will not be touched at all. Instead, we will treat the
DataTable like an in-memory database of our own. We will bind it to our grid so that when we apply a filter to the data, the grid will be automatically updated to reflect the filter.
There are four main pieces to this solution. Since this is an introductory article, I will explain each piece and its function.
DataSet – A
DataSet stores a set of
DataTables. It can store data relators but that is beyond the scope of this article. For our purposes, we will just be using the
DataSet to store one
DataTable – The
DataTable holds a set of data. It is in the table format but don’t limit yourself to thinking of it as a table. It can be the output of a stored procedure or view as well.
DataView – Think of this as a query platform against your
DataTable. We will use this to apply our filter. We could also use it to sort our data as well.
RowFilter – This is the actual filter we will apply on our
DataView. We will build this filter based upon our search box data.
The first section of code deals with the loading of the data and the basic view creation. I created a helper method called
ReadData that takes a SQL statement and populates a
DataSet with the returned data. The table name will be the name we pass in. I then create a
DataView based upon the
DefaultView of my
DataTable inside my
DataSet. I assign this
DataView to be the
DataSource for my grid (the standard
DataGridView that comes with Visual Studio).
private void Main_Load(object sender, EventArgs e)
ReadData("SELECT * FROM adventureworks.production.vproductanddescription",
ref dstResults, "Products");
myView = ((DataTable)dstResults.Tables["Products"]).DefaultView;
dgvResults.DataSource = myView;
Once I have my
DataView assigned to my grid, the only thing I have left to do is filter the
DataView whenever the user enters information into the search box. This can be done on the
KeyUp event. Here is that code:
private void txtSearch_KeyUp(object sender, KeyEventArgs e)
string outputInfo = "";
string keyWords = txtSearch.Text.Split(' ');
foreach (string word in keyWords)
if (outputInfo.Length == 0)
outputInfo = "(Name LIKE '%" + word + "%' OR ProductModel LIKE '%" +
word + "%' OR Description LIKE '%" + word + "%')";
outputInfo += " AND (Name LIKE '%" + word + "%' OR ProductModel LIKE '%" +
word + "%' OR Description LIKE '%" + word + "%')";
myView.RowFilter = outputInfo;
That is all there is to the code. I did set the
DataSet and the
DataView objects to be scoped at the class level on my form so that I could access them from different methods. I also used a couple
using statements to make the code cleaner but that is about all. I’ve included the full source code in the download. The sample application uses the
AdventureWorks database. To change this, just change the connection string and the SQL statement.
In this article, I showed you how to use a
DataView to create an auto-searchable grid using basically one simple method. In testing on a virtual machine with 1 GB of RAM and a local version of Microsoft SQL 2008 R2, I was able to smoothly load and filter 200,000 records based upon a search of three string fields. I was able to operate on 500,000 records and beyond but the performance became noticeably slower.
I hope you have enjoyed this article. Let me know what you think below. If you have an idea on how to make this better, I would love to hear it. Please note, however, that I am aware that this code does not follow best practices for layers, extensibility, etc. This code was written for simplicity and ease of explanation.
- December 20, 2010 – Initial version
In my previous positions, I have worked as a lead developer, professor and IT Director. As such, I have been able to develop software on a number of different types of systems and I have learned how to correctly oversee the overall direction of technology for an organization. I've developed applications for everything from machine automation to complete ERP systems.
I enjoy taking hard subjects and making them easy to understand for people unfamiliar with the topic.