c# - Stop a thread working with DB -

i'm working on autocomplete script.

when user stop typing, input send server matches in db.

eg: if looking people "obam" should return "barack obama".

i want search limited ~500ms. if takes longer, want abort search , return results found in time.

i start looking perfect match (this 1 won't interrupted), looking partial match (this 1 can 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 label marque m 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 should 500, put 1 force troubles 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 label marque m m.nom '" + 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 label marque m m.nom '%" + 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 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 struct easy sorting of results

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

---

the aim have results (not all) fast.

problems

• i randomly timeoutexception after ~30s on conn.close(); line.
• i randomly nullreferenceexception on first cmd.executereader(); call in findotherbrands.

can explain me why ? doing wrong or there workaround ?

i guess timeoutexception because trying close connection during command execution, can drop/cancel query ?

i take different approach. since you're querying database, naturally asynchronous, can use async-await querying data. that, can pass cancellationtoken set timeout, you'll monitor 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 label                 marque m                 m.nom '" + 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 label                 marque m                 m.nom '%" + 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 invoke it, need wrap in try-catch , pass 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 - query prone sql injection. shouldn't used string concatenation. use query parameters instead.