Mysql singleton threading issue

Question

Hi I have a program that works well. But I need to expand upon a portion and need to use threading. Here is my problem. Every class uses the same connection which has never been a problem because i didnt multithread. Now I have a form that needs to run itself independently but uses much of the same classes and operations that my main forms do. Since I used the same connection for everything I am throwing constant exceptions because the connection will often be in use. Is there a more simple way to get around this that doesnt involve rewriting/overloading all my old work?

theLizard · Answer

Patrick It is always advisable to open and close your connections after each use you do not need to multi thread to open multiple connections whoever told you it was the right way to do it was wrong. Can you explain how you use your one connection so that a possible solution can be found.

theLizard · Answer

Patrick I posted the example class here

theLizard · Answer

Hi Patrick Unfortunately yes you would need to re-write code but if planned correctly would (possibly) not be that big of an issue. Look at the thread below following the thread(s) you will find an example of a class written for your issue it is for MS SQL but you would only need to change things like SqlConnection to MySqlConnection to get it to work I wrote the class to enable me to deal with multiple connections so that in any function that requires connections to multiple tables can be achieved. For example SqlCon (the class) con = new SqlCon(connection string command text) establishes a connection ready to perform all the tasks needed SqlCon con = new SqlCon( ....) con.Execute(sql statement) con.terminate(); this is a simplified example but in your code blocks these 3 lines of code (maybe 4 5 depending on what you need to do) is all that would be needed. if you need to get data from another table as well you can instantiate a new connection SqlCon con1 = new SqlCon(.. ..) and using the functions in the SqlCon object return values to build the sql statement in your first con object. SqlCon() encapsulates functions that fill list boxes combo boxes or whatever else you want to do Say you want to fill a combo box you would create the connection SqlCon con = new SqlCon( ....) con.FillCombo(an sql statement the combo box to fill by reference) con.Terminate(); http://www.c-sharpcorner.com/Forums/ShowMessages.aspx?ThreadID=82003 Can you post an example block of your function code for me to look at?

patrick · Answer

That's upsetting. I'm assuming I now have a lot of rewrite. Heres how I have it. In the main form there is a public static string connectionString and public static mysqlConnection connection. mainform.open has connection = new mysqlconnection(connectionString); connection.open(); then every function has using(mysqlCommand cmd = new mysqlCommmand(mainform.connection)){ ... run cmd ... } thats pretty much the layout i can go into more detail if needed. I appreciate all the feedback.

patrick · Answer

The code was written in a way that a single connection was used throughout. Multithreading never had a purpose in the program before now; I didn't plan for it. I have 100+ functions that use the same connection but never needed to use it at the same time until now- I did it this way because I was told it was the right way to do things as opposed to creating a new connection every time.

PJ Martins · Answer

Is there a reason for each thread not to have its own connection?