c# - 停止使用数据库的线程

Question

我正在开发自动完成脚本。

当用户停止输入时，他的输入被发送到服务器，服务器将在数据库中寻找一些匹配项。

例如:如果要查找人“Obam”，则应返回“Barack Obama”。

我希望将此搜索限制在 ~500 毫秒内。如果需要更长的时间，我想中止搜索，只返回及时找到的结果。

我从寻找完美匹配开始(这个不会被打断)，然后我在寻找部分匹配(这个可以被打断):

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

• 我在这里找到了 RunWithTimeout 方法: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;
  }
  

• 评分是一种对结果进行简单排序的结构

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

---

目标是快速获得结果(不一定全部)。

问题

• 我在 ~30 秒后在 conn.Close(); 行随机出现 Timeoutexception。
• 我在 FindOtherBrands 中的第一个 Cmd.ExecuteReader(); 调用中随机出现 NullReferenceException。

谁能解释一下为什么？我做错了什么或有解决方法吗？

我猜 TimeoutException 是因为我试图在命令执行期间关闭连接，我可以删除/取消该查询吗？

Answer 1

我会采取不同的方法。由于您正在查询自然异步的数据库，因此您可以使用 async-await 来查询数据。有了它，您可以传递一个设置为超时的 CancellationToken，您将在每次读取时对其进行监控:

例如:

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

当您调用它时，您只需将它包装在 try-catch 中并传递一个 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;
}

旁注 - 您的查询容易发生 SQL 注入(inject)。您不应该使用字符串连接。请改用查询参数。