C# Delay SQL Query while user typing in a textbox

2019-03-01 18:25发布

问题:

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.

回答1:

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));
    }
}