How to use textbox textChanged property with background worker to fill datagridview

RayMond 0
Viewed 64

in my form i have a textbox, background worker and a data grid view. using the textbox(TextChanged property) i want to fatch data using the background worker and fill matching data in the data grid view as i type.

here is what i have tried out.

 private void txtSearchBox_TextChanged(object sender, EventArgs e)
 {
   if (backgroundWorker1.IsBusy)
   {
   backgroundWorker1.CancelAsync();
   }
   backgroundWorker1.RunWorkerAsync();///Runs the background worker     
 }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {                
                try
                {
                    con.Open();
                    MySqlDataAdapter da = new MySqlDataAdapter("select * from Games where Name like '%" + txtSearchBox.Text + "%'", con);
                    
                    da.Fill(ds, "GameID");                    
                    con.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error" + ex);
                }  
        }



        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            SimilarData.DataSource = ds.Tables["GameID"].DefaultView;
        }

With that am getting this error. ErrorSystem.InvalidOperationExceptoin:This Background worker is currently busyand and not run multiple tasks concurrently.

What do i do?

winforms c#
1 Answers

If you would be open to a simpler approach than using BackgroundWorker what I would suggest is making an async handler for the textbox.TextChanged event that takes note of the keystroke count going in before awaiting a "cooling off" period for rapid typing. If the count is the same before and after awaiting this delay it indicates that typing is sufficiently stable to perform a query.

screenshot

I have mocked this out using sqlite-net-pcl for the sake of expediency. The DGV is bound to a DataSource that is a BindingList<Game>. The OnLoad override of MainForm initializes the DataGridView, then whatever database server you're using. The last thing is to subscribe to the TextChanged event and this is where all the where all the action takes place.

protected override void OnLoad(EventArgs e)
{
    base.OnLoad(e);
    initDGV();
    initSQL();
    textBox.TextChanged += onTextBoxTextChanged;
}
BindingList<Game> DataSource = new BindingList<Game>();
// Comparing the awaited count to the total count so that
// the DGV isn't visually updated until all queries have run.
int _queryCount = 0;

TextBox.TextChanged event handler (async)

private async void onTextBoxTextChanged(object sender, EventArgs e)
{
    if(string.IsNullOrWhiteSpace(textBox.Text))
    {
        return;
    }
    _queryCount++;
    var queryCountB4 = _queryCount;
    List<Game> recordset = null;
    var captureText = textBox.Text;

    // Settling time for rapid typing to cease.
    await Task.Delay(TimeSpan.FromMilliseconds(250));

    // If keypresses occur in rapid succession, only
    // respond to the latest one after a settling timeout.
    if (_queryCount.Equals(queryCountB4))
    {
        await Task.Run(() =>
        {
            using (var cnx = new SQLiteConnection(ConnectionString))
            {
                var sql = $"SELECT * FROM games WHERE GameID LIKE '{captureText}%'";
                recordset = cnx.Query<Game>(sql);
            }
        });
        DataSource.Clear();
        foreach (var game in recordset)
        {
            DataSource.Add(game);
        }
    }
    else Debug.WriteLine("Waiting for all pending queries to complete");
}

DataGridView

private void initDGV()
{
    dataGridView.DataSource = DataSource;
    dataGridView.AllowUserToAddRows = false;
    DataSource.Add(new Game { GameID = "Generate Columns" });
    dataGridView
        .Columns[nameof(Game.GameID)]
        .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
    dataGridView
        .Columns[nameof(Game.Created)]
        .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
    DataSource.Clear();
}

Database

private void initSQL()
{
    // For testing, start from scratch every time
    if (File.Exists(ConnectionString)) File.Delete(ConnectionString);
    using (var cnx = new SQLiteConnection(ConnectionString))
    {
        cnx.CreateTable<Game>();
        cnx.Insert(new Game { GameID = "Awe" });
        cnx.Insert(new Game { GameID = "Abilities" });
        cnx.Insert(new Game { GameID = "Abscond" });
        cnx.Insert(new Game { GameID = "Absolve" });
        cnx.Insert(new Game { GameID = "Absolute" });
    }
}

Game

[Table("games")]
class Game
{
    [PrimaryKey, Browsable(false)]
    public string Guid { get; set; } = System.Guid.NewGuid().ToString().ToUpper();
    public string GameID { get; set; }
    private DateTime _created = DateTime.Now.Date;
    public string Created 
    {
        get => _created.ToShortDateString();
        set
        {
            if(DateTime.TryParse(value, out DateTime dt))
            {
                _created = dt.Date;
            }
        }
    }
}
IVSoftware0
Related