Search As You Type in C#

• Download source - 10.24 KB

Introduction

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 DataTable, 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.

Background

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.

The Pieces

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.
• 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 Code

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)
{
    //Populates the DataSet using a helper method
    ReadData("SELECT * FROM adventureworks.production.vproductanddescription", 
        ref dstResults, "Products");

    //Creates a DataView from our table's default view
    myView = ((DataTable)dstResults.Tables["Products"]).DefaultView;

    //Assigns the DataView to the grid
    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:

//This method is fired by the KeyUp event handler on the textbox.
//The purpose of this method is to take the text from the search
//box, split it up into words, and then create and assign a filter
//statement that will do a LIKE comparison on each of the selected
//search fields. Each word's filter statement is AND'ed together
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 + "%')";
        }
        else
        {
            outputInfo += " AND (Name LIKE '%" + word + "%' OR ProductModel LIKE '%" + 
                word + "%' OR Description LIKE '%" + word + "%')";
        }
    }

    //Applies the filter to the DataView
    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.

Wrapping Up

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.

History

• December 20, 2010 – Initial version