Leave MySQL connection idle to be reused or connect on demand, what about classes?

Question

I am currently working on a Desktop Application using winforms and .NET 4.0, I have downloaded the latest MySQL connector for .NET 4.0 but I have a few doubts of what would be the best way to go with it.

NOTE: Just want to point out this application is for personal use and will be running on my own server.

• My application is connected to a server but does not receive information at all the times, should I make on demand connections to MySQL to process the data received or should I leave an connection to MySQL open for as long as the application is open idling so it can be reused when data is received ?

• I have been looking around for some more recent documents in regards of using MySQL with c# but all I could found was tutorials dated from 2006 is there some good examples that are more recent that I could use as a reference (this is my first time using MySQL with c#) specially documents showing how to serialize/deserialize data using the DataTable stuff would be very welcome ?

  I would be happy with some code examples of classes you guys often use to establish a connection or pull data from it.

• Another question I was considering which is not so related to this is: When should I consider using a web API over direct MySQL connections with my application ?

Answer 1

In my view, you should open the connection at the beginning of a user action and close it at the end. This won't affect performance significantly because connecting is just as quick as doing a single query.

However, if you open/close a connection for each sub-operation, you can end up with poor performance. Be sure that you don't do that.

---

The main motivation for using a new connection each time, is that you can avoid the following classes of bugs:

• "Morning" bugs where the database has disappeared or disconnected after a while (or timeouts on stateful firewalls, etc)
• "Stale state" bugs where some previous action has left the connection in a "broken" state and causes the next action to fail or behave unexpectedly (e.g. "SET names koi8")

It is possible to reset a mysql connection by doing a mysql_change_user (If your API supports it), but this is almost the same as reconnecting (it just uses the same tcp connection).