0

I have an application(C#) which is inserting data to database(MySQL). The program runs on multithreads with a timer.

The question of mine is the following, what would be the best way to insert data to the database.

Example of the application:

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.

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.

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).

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).

Please let me know if it is not clear enough. Any suggestions will be appreciated.

Thanks

My code for databse insertion is the following:

con.Open();
MySqlCommand cmd = new MySqlCommand(command, con);
cmd.ExecuteNonQuery();
con.Close();

I think I should insert here some pseudo code as well, to make my problem more understandable:

Timer
foreach(sensor)
{
if sensor reading finished
{
check the type of the sensor
run the right thread
}

Example thread:

Thread
Read out data from sensor
insert data to DB

Response of the sensors are different, but still can be the same. This is why I have problem inserting to the database.

Daniel
  • 383
  • 1
  • 5
  • 20
  • 1
    If you are using .NET 4.0 you can implement asynchronous operations using Task Parallel Library or better if you are already using .NET 4.5 where you can use the async and await features – Jobert Enamno Jan 24 '13 at 09:32
  • check this out http://stackoverflow.com/questions/9952137/multi-threading-c-sharp-application-with-sql-server-database-calls – Master Stroke Jan 24 '13 at 09:33
  • Please correct me if I am wrong but async threading is kind of about threads who wait for each other, is not it? If they are so then it is not really an option. The reason because I call those threads in the same time because I am reading out data form sensors. The time what it takes to read out can be really different, but it is possible that they finish at the same time. How a thread works is they read out data from the sensor(the way depends on the type of the sensor) then insert in DB. I can not really wait for the threads as one could take a min while the other one 2 sec. – Daniel Jan 24 '13 at 10:30
  • _are different, but still can be the same. This is why I have problem_ - try to explain that better. Is there a PK violation? – H H Jan 24 '13 at 13:35
  • @Henk Holterman I do not have Pk violation as I create my own primary key. I got the error when I did the following: I opened the connection to the database at the beginning of the reading. Then I started to insert data in the database,(I am not 100% sure but I think) I got the error when the threads from the application tried to insert data in to the database at the same time(same time/different table). – Daniel Jan 24 '13 at 14:15
  • Golden rule: 1 Connection per thread. Don't share a Connection. – H H Jan 24 '13 at 14:56
  • @Henk Holterman So have the middle way, I mean do not have one connection for all of the threads, an do not open/close the connection more times then one in each thread? So create a new connection in each thread leave it open till all of the insertion and then close it when the thread terminates, yes? – Daniel Jan 24 '13 at 16:26
  • Leave it open as short as possible. – H H Jan 24 '13 at 16:46

2 Answers2

2

I did always open and close the database connection after each insertion [...] It is working the way like that but not so safe.

It is certainly the best way. Use the Connection in a using () {} block and it will be safe.

With this solution my database still get confused sometimes.

You will have to describe that a lot better.

There was an idea to have an extra method which is collect the data end it uploads after each tick

Yes, you can (should) probably use a extra Thread to push things to the Db. Look up "Producer/Consumer Pattern"

H H
  • 263,252
  • 30
  • 330
  • 514
  • 1, I meant, it did not work when I opened/closed in each thread. I have a method for inserting which is something like that:
    con.Open();
    MySqlCommand cmd = new MySqlCommand(command, con);
    cmd.ExecuteNonQuery();
    con.Close();
    It is possible that I call this method at almost the same time. In that case I might try to open an already open connection or the conenction closed before I executed the query.     – Daniel Jan 24 '13 at 10:16
  • 2, It is possible that the threads are trying to insert data to the database in the same time. If I leave open the database connection and the threads to that, then it will crash. – Daniel Jan 24 '13 at 10:22
  • 3, So I should do something at the main thread which upload the data what is collected by the other threads? – Daniel Jan 24 '13 at 10:22
  • Edit and improve the question, code in comments is not readable. – H H Jan 24 '13 at 11:15
0

Use single connection only,
use one function whom you pass your data for insertion, that function will do insertion work.

like

func(strQry){ Command cmd = new ...(strQry, conn); cmd.execute...(); }

Reuse conn obj , but create new cmd obj every time..

Comment if stuck...

Gaurav Gandhi
  • 3,041
  • 2
  • 27
  • 40