Click here to Skip to main content
Click here to Skip to main content
Go to top

Search As You Type in C#

, 21 Dec 2010
Rate this:
Please Sign up or sign in to vote.
Learn how to use ADO.NET objects to create a Google-like auto-search for your WinForms grid
AutoSearch_small.PNG

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tim Corey
Software Developer (Senior) Epicross
United States United States
I am currently a Lead Technical Consultant for a consulting company called Epicross. My primary skills are in .NET, SQL, JavaScript, and other web technologies although I have worked with PowerShell, C, and Java as well.
 
In my previous positions, I have worked as a lead developer 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.
 
My current position is mainly focused making our clients more efficient and effective. I use custom software (desktop, mobile, and web) to help facilitate this goal. When I'm not working for the company, I'm usually developing applications to fill the needs of the organizations I volunteer for.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
QuestionCan you 'walk me' thru the steps that you create this app, please? PinmemberLongT5-Jan-11 15:05 
AnswerRe: Can you 'walk me' thru the steps that you create this app, please? PinmemberTim Corey15-Jan-11 18:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 21 Dec 2010
Article Copyright 2010 by Tim Corey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid