多线程和数据库连接 [英] Multithreading and database connection(s)

问题描述

所以我试图找出我的数据库连接的最佳做法。我有一个大的.NET GUI，作为MySQL数据库的前端。目前，我在应用程序加载时打开一个连接，并将其用于我需要的任何交互。但是，整个GUI是单线程的。

当我开始为大型查询添加BackgroundWorkers并执行时，我担心我的打开的连接。我知道，例如，我只能在该连接上一次打开一个dataReader。使用多个线程，用户可以尝试实例化更多。

为应用程序保持一个打开的连接，为每个开放一个新的连接有哪些优点/缺点互动？

这是一些常见的设计模式？

谢谢 -

Jonathan

解决方案

使用线程安全连接池并保持连接线程特定（don' t / b>

我相信MySQL .NET连接框架内置一个。如果对所有连接使用相同的连接字符串，只需添加 pooling = true到你的连接字符串。 （来源 - 没有超链接片段，所以在表中查找pooling）

这种方法的缺点是某些线程将阻塞，直到连接可用。您需要在程序结构中解释这一点。

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

解决方案

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.

这篇关于多线程和数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！