Multithreading and database connection(s)

Question

So I'm trying to figure out best practices on my database connection. I have a large .NET GUI that serves as the front end for the MySQL db. Currently I open a connection on application load and use it for whatever interactions I need. However, the entire GUI is single-threaded.

As I start to add BackgroundWorkers for large queries and executes I'm concerned about my open connection. I know, for example, that I can only have one dataReader at a time open on that connection. With multiple threads, the user could try to instantiate more than that.

What are the advantages / disadvantages of keeping one open connection for the application vs opening a new connection for every interaction?

What are some common design patterns for this?

Thanks-

Jonathan

Answer 1

Use a thread-safe connection pool and keep connections thread-specific (don't share connections across threads).

I believe the MySQL .NET connection framework comes with one built in. If you use the same connection string for all connections, simply add "pooling=true" to your connection string. (Source -- there's no hyperlink fragment, so look for "pooling" in the table)

The drawback of this approach is that some threads will block until a connection is available. You'll need to account for this in your program structure.