Click here to Skip to main content
15,867,308 members
Articles / Containers / Virtual Machine
Article

Search As You Type in C#

Rate me:
Please Sign up or sign in to vote.
4.21/5 (13 votes)
21 Dec 2010CPOL4 min read 121.6K   6.1K   55   14
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).

C#
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:

C#
//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)


Written By
Software Developer (Senior) DeGarmo
United States United States
I am currently a Senior Software Developer at a company in Illinois called DeGarmo. 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, 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.

Comments and Discussions

 
Questionyou just save my life thanks alot Pin
Member 1500193810-Jan-21 10:45
Member 1500193810-Jan-21 10:45 
QuestionSearch in Web Page Pin
Nitin_Rajurkar26-Aug-18 2:31
Nitin_Rajurkar26-Aug-18 2:31 
QuestionFormat numbers to string Pin
Member 77632615-Mar-17 20:01
Member 77632615-Mar-17 20:01 
GeneralMy vote of 4 Pin
Meer Wajeed Ali5-May-15 20:41
professionalMeer Wajeed Ali5-May-15 20:41 
Outstanding work keep it up...
QuestionIt is Ok with string type variable. If I want to use multiple varaibles I am getting exception. Pin
Meer Wajeed Ali3-May-15 20:17
professionalMeer Wajeed Ali3-May-15 20:17 
AnswerRe: It is Ok with string type variable. If I want to use multiple varaibles I am getting exception. Pin
Tim Corey4-May-15 4:26
professionalTim Corey4-May-15 4:26 
GeneralRe: It is Ok with string type variable. If I want to use multiple varaibles I am getting exception. Pin
Meer Wajeed Ali4-May-15 23:39
professionalMeer Wajeed Ali4-May-15 23:39 
GeneralNice article this saves my lot of time Pin
Meer Wajeed Ali17-Mar-15 0:49
professionalMeer Wajeed Ali17-Mar-15 0:49 
QuestionSearch box for listview Pin
Member 1040791715-Jul-14 21:06
Member 1040791715-Jul-14 21:06 
QuestionThis was helpful and great but if you want a simple,easy working 'search as you type' database visit this link Pin
Syed Wajahat Ali Shah11-Apr-13 6:06
Syed Wajahat Ali Shah11-Apr-13 6:06 
GeneralMy vote of 5 Pin
sariqkhan21-Nov-12 0:37
sariqkhan21-Nov-12 0:37 
GeneralMy vote of 5 Pin
taha bahraminezhad Jooneghani30-May-12 17:44
taha bahraminezhad Jooneghani30-May-12 17:44 
QuestionCan you 'walk me' thru the steps that you create this app, please? Pin
LongT5-Jan-11 15:05
LongT5-Jan-11 15:05 
AnswerRe: Can you 'walk me' thru the steps that you create this app, please? Pin
Tim Corey15-Jan-11 18:16
professionalTim Corey15-Jan-11 18:16 

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

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