C# Delay SQL Query while user typing in a textbox

Question

I have just written my first program that queries a SQL database with the information a user writes into a textbox. This is C# using windows forms. My goal is to have this be similar to the search function in our ERP software where results are displayed as a user types (similar to Google's prediction search feature).

What I am struggling with is reducing the number of queries to the database. Right now I have it so that a query is not executed until the user has typed as least 3 characters otherwise too many results would be returned.

private void SearchField_TextChanged(object sender, EventArgs e)
{
    string search = SearchField.Text;
    if (search.Length >= 3)
    {
        dataGridView1.DataSource = sql.ExecuteQuery(Query(search));
    }
}

What I want to add is a query delay until the user has stopped typing or rather not entered a character for so many milliseconds. I have been looking at the timer class but struggling with the examples I found to implement it properly. Basically I want to change my code to be something like the following:

private void SearchField_TextChanged(object sender, EventArgs e)
{
    string search = SearchField.Text;
    if (search.Length >= 3 && aTimer.Time > 500)  //500 is milliseconds
    {
        dataGridView1.DataSource = sql.ExecuteQuery(Query(search));
    }
    aTimer.Reset();
}

If using the timer class I don't know how to implement it properly. If there is a better solution I would be open to that as well.

Why not have a `Submit` button? Sure, you won't have predictive results, but you'd limit your queries. — PiousVenom, Mar 14 '13 at 14:02
@BradM The OP stated that his project is `C# using windows forms` — Seany84, Mar 14 '13 at 14:03
Yes I was planning on implementing the timer in the clients windows application form used to search the database. — Matt, Mar 14 '13 at 14:05
Is the issue the number of queries? Or the size of the result set? — Ryan Gates, Mar 14 '13 at 14:06
I suggest you check out RX. One of the first examples of it did exactly what you want. — Daniel Kelley, Mar 14 '13 at 14:06
Is user going to search from predefined values? Or its open search? — Yahya, Mar 14 '13 at 14:06
@CL$PTR4P I wanted to try without a submit button. If I get this figured out it will help with other projects I have in mind. — Matt, Mar 14 '13 at 14:06
http://stackoverflow.com/questions/8001450/c-sharp-wait-for-user-to-finish-typing-in-a-text-box http://stackoverflow.com/questions/671728/net-textbox-control-wait-till-user-is-done-typing — User 12345678, Mar 14 '13 at 14:07
@Ryan Gates. The issue is both the number of queries and size of the result. A 10 character search would query 7 times. A 4-6 character query returns a lot of results. — Matt, Mar 14 '13 at 14:08
For returning a lot of results, that can easily be solved with a `TOP N` or something similar depending on your RDBMS. — Ryan Gates, Mar 14 '13 at 14:22
@Ryan Gates, TOP N would be a good thing to consider. I just can't easily define how much data the user will be looking at. It may be 3 lines or it could be 50+. — Matt, Mar 14 '13 at 16:55

score 3 · Accepted Answer · answered Mar 14 '13 at 17:52

What you want to do is schedule the query to happen at some point in the future, while being able to reset or revoke the pending query as the user types. Here is an example:

using System;
using System.Diagnostics;
using System.Drawing;
using System.Windows.Forms;

class Form1 : Form
{
    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        Application.Run(new Form1());
    }

    Timer queryTimer;
    TextBox SearchField;

    public Form1()
    {
        Controls.Add((SearchField = new TextBox { Location = new Point(10, 10) }));
        SearchField.TextChanged += new EventHandler(SearchField_TextChanged);
    }

    void SearchField_TextChanged(object sender, EventArgs e)
    {
        if (SearchField.Text.Length < 3)
            RevokeQueryTimer();
        else
            RestartQueryTimer();
    }

    void RevokeQueryTimer()
    {
        if (queryTimer != null)
        {
            queryTimer.Stop();
            queryTimer.Tick -= queryTimer_Tick;
            queryTimer = null;
        }
    }

    void RestartQueryTimer()
    {
        // Start or reset a pending query
        if (queryTimer == null)
        {
            queryTimer = new Timer { Enabled = true, Interval = 500 };
            queryTimer.Tick += queryTimer_Tick;
        }
        else
        {
            queryTimer.Stop();
            queryTimer.Start();
        }
    }

    void queryTimer_Tick(object sender, EventArgs e)
    {
        // Stop the timer so it doesn't fire again unless rescheduled
        RevokeQueryTimer();

        // Perform the query
        Trace.WriteLine(String.Format("Performing query on text \"{0}\"", SearchField.Text));
    }
}

I just implemented what you put and it seems to work perfectly initially. I just have to figure out how to get it to start again if the user stops typing and then continues again or starts fresh. Also where you initialized the timer with 'Timer queryTimer;' I had to use 'System.Windows.Forms.Timer queryTimer;' — Matt, Mar 14 '13 at 18:47
@Matt It does start again if the user stops and then continues. The code is an entire program. Run it with the Output tab in the debugger visible and you can see it in action. — Tergiver, Mar 14 '13 at 18:51
yes your program worked perfectly after testing. I missed one line when implementing what you wrote into my own program. It is fixed and appears to be working. Thank you! — Matt, Mar 14 '13 at 19:33

C# Delay SQL Query while user typing in a textbox

1 Answers1

Linked