Stop a thread working with DB

Question

I'm working on an autocomplete script.

When user stop typing, his input is send to the server which will look for some matches in DB.

Eg: If looking for people "Obam" should return "Barack Obama".

I want this search to be limited to ~500ms. If it takes longer, I want to abort the search and only return results found in time.

I start by looking for a perfect match (this one won't be interrupted), then I am looking for a partial match (this one can be interrupted) :

private static MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["CxMySql"].ToString());

protected void Page_Load(object sender, EventArgs e)
{
    conn.Open();
    SearchTerm(table,term,domaine);
    conn.Close(); // TimeoutException HERE
    conn.Dispose();
}

private void SearchTerm(string table,string term,string domaine)
{
    Dictionary<int, Scoring> results = new Dictionary<int, Scoring>();

    var requete = "SELECT m.id, m.nom as label FROM marque m WHERE m.nom = '" + term + "'";
    var Cmd = new MySqlCommand(requete, conn);
    using (var Rd = Cmd.ExecuteReader())
    {
        while (Rd.Read())
        {
            results.Add(int.Parse(Rd["id"].ToString()), new Scoring()
            {
                score = 1000,
                value = Rd["label"].ToString()
            });
        }
    }
    // Here it should be 500, but I put 1 to force troubles to appear.
    RunWithTimeout(() => FindOtherBrands(term, ref results), TimeSpan.FromMilliseconds(1));

    var resultsList = results.ToList();
    resultsList.Sort(
        delegate(KeyValuePair<int, Scoring> firstPair,
            KeyValuePair<int, Scoring> nextPair)
        {
            return nextPair.Value.score - firstPair.Value.score;
        }
    );
}


private void FindOtherBrands(string term, ref Dictionary<int, Scoring> results)
{
    MySqlCommand Cmd;
    string requete;
    requete = "SELECT m.id, m.nom as label FROM marque m WHERE m.nom LIKE '" + term + "%'";
    Cmd = new MySqlCommand(requete, conn);
    var Rd = Cmd.ExecuteReader(); // NullReferenceException HERE
    while (Rd != null && Rd.Read())
    {
        int id = int.Parse(Rd["id"].ToString());
        if (!results.ContainsKey(id))
        {
            results.Add(id, new Scoring()
            {
                score = 100,
                value = Rd["label"].ToString()
            });
        }
    }
    Rd.Close();
    requete = "SELECT m.id, m.nom as label FROM marque m WHERE m.nom LIKE '%" + term + "%'";
    Cmd = new MySqlCommand(requete, conn);
    Rd = Cmd.ExecuteReader();
    while (Rd != null && Rd.Read())
    {
        int id = int.Parse(Rd["id"].ToString());
        if (!results.ContainsKey(id))
        {
            results.Add(id, new Scoring()
            {
                score = 10,
                value = Rd["label"].ToString()
            });
        }
    }
    Rd.Close();
}

• I found the RunWithTimeout method here : stop executing code in thread after 30s

  bool RunWithTimeout(ThreadStart threadStart, TimeSpan timeout)
  {
      Thread workerThread = new Thread(threadStart);
  
      workerThread.Start();
  
      bool finished = true;
      if (!workerThread.Join(timeout))
      {
          workerThread.Abort();
          finished = false;
      }
  
  
      return finished;
  }
  

• Scoring is a struct for an easy sorting of results

  private struct Scoring
  {
      public string value;
      public int score;
  }
  

---

The aim is to have results (not necessarily all) fast.

PROBLEMS

• I randomly get Timeoutexception after ~30s on the conn.Close(); line.
• I randomly get NullReferenceException on the first Cmd.ExecuteReader(); call in FindOtherBrands.

Can anyone explain me why ? Am I doing something wrong or is there a workaround ?

I guess the TimeoutException is because I am trying to close connection during command execution, can I drop/cancel that query ?

Answer 1

I would take a different approach. Since you're querying a database, which is naturally asynchronous, you can use async-await for querying the data. With that, you can pass a CancellationToken which is set to a timeout, which you'll monitor with every read:

For example:

private async Task FindOtherBrands(string term, 
                                   Dictionary<int, Scoring> results, 
                                   CancellationToken cancellationToken)
{
    MySqlCommand cmd;
    string requete;
    requete = "SELECT m.id, m.nom as label 
               FROM marque m 
               WHERE m.nom LIKE '" + term + "%'";

    cmd = new MySqlCommand(requete, conn);
    var Rd = await cmd.ExecuteReaderAsync();
    while (Rd != null && await Rd.ReadAsync())
    {
        cancellationToken.ThrowIfCancellationRequested();

        int id = int.Parse(Rd["id"].ToString());
        if (!results.ContainsKey(id))
        {
            results.Add(id, new Scoring()
            {
                score = 100,
                value = Rd["label"].ToString()
            });
        }
    }

    Rd.Close();
    requete = "SELECT m.id, m.nom as label 
               FROM marque m 
               WHERE m.nom LIKE '%" + term + "%'";

    cmd = new MySqlCommand(requete, conn);
    Rd = await Cmd.ExecuteReaderAsync();
    while (Rd != null && await Rd.ReadAsync())
    {
        cancellationToken.ThrowIfCancellationRequest();
        int id = int.Parse(Rd["id"].ToString());
        if (!results.ContainsKey(id))
        {
            results.Add(id, new Scoring()
            {
                score = 10,
                value = Rd["label"].ToString()
            });
        }
    }
    Rd.Close();
}

And when you invoke it, all you need is to wrap it in a try-catch and pass a CancellationToken:

private async Task<bool> RunWithTimeoutAsync(TimeSpan timeout)
{
    bool finished;
    try
    {
        var cancellationTokenSource = new CancellationTokenSource(timeout);
        await FindOtherBrandsAsnyc(term, 
              results, 
              cancellationTokenSource.CancellationToken);
        finished = true;
    }
    catch (OperationCanceledException e)
    {
        // Handle
    }

    return finished;
}

Side note - Your query is prone to SQL Injection. You shouldn't used string concatenation. Use query parameters instead.