title: multi thread database insertion
body: <p>I have an application(C#) which is inserting data to database(MySQL). The program runs on multithreads with a timer. </p>
  
  <p>The question of mine is the following, what would be the best way to insert  data to the database. </p>
  
  <p>Example of the application:</p>
  
  <p>The timer tick each second, I have data which I would like to insert to the database in each second, but I have some which I want to insert rearer, like every half minute. The data always goes to different tables, but same database. </p>
  
  <p>I did always open and close the database connection after each insertion. In that case I had problems when I want to open an already open connection or use a connection which is already closed by the other thread. To fix that problem I open the connection when I started the timer and closed it when I stopped it. It is working the way like that but not so safe.</p>
  
  <p>With this solution my database still get confused sometimes. I just tried it with local database, I do not know if it would be different with remote one or not(be honest it should work with local and remote DB as well).</p>
  
  <p>There was an idea to have an extra method which is collect the data end it uploads after each tick(or after X tick) to the database. I am not sure if that would be a better solution or not, but I do not really have better idea just now. The problem with that solution is that it could get messy, (I guess I would need a global list what could be accessed by all of the threads and insert data in it, It could cause of a problem when I upload/clear the list while one of the threads might want to access to it).</p>
  
  <p>Please let me know if it is not clear enough. Any suggestions will be appreciated. </p>
  
  <p>Thanks</p>
  
  <p>My code for databse insertion is the following:</p>
  
  <p><code>
  con.Open();<br/> MySqlCommand cmd = new MySqlCommand(command, con);<br/> cmd.ExecuteNonQuery();<br/> con.Close();<br/>
  </code></p>
  
  <p>I think I should insert here some pseudo code as well, to make my problem more understandable:</p>
  
  <p><code>
  Timer<br/>
      foreach(sensor)<br/>
       {<br/>
         if sensor reading finished<br/>
         {<br/>
         check the type of the sensor<br/>
         run the right thread<br/>
         }<br/></p>
  
  <p></code>
  Example thread:</p>
  
  <p><code>
  Thread<br/>
   Read out data from sensor<br/>
   insert data to DB<br/>
  </code></p>
  
  <p>Response of the sensors are different, but still can be the same. This is why I have problem inserting to the database.</p>