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

问题描述

所以我试图找出最佳做法，在我的数据库连接。我有一个大的.NET图形用户界面，作为对MySQL数据库的前端。目前我打开应用程序负载的连接，并使用它，因为我需要什么互动。然而，整个图形用户界面是单线程的。

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.

当我开始添加BackgroundWorkers的大型查询，并执行我很担心我的开放连接。我知道，比如，我只能有一个DataReader的在一次连接上打开。随着多线程，则用户可以尝试实例不止这些。

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.

什么是保持应用程序VS开放的每一次互动的新连接一个打开的连接的优点/缺点是什么？

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?

感谢 -

乔纳森

推荐答案

使用一个线程安全的连接池，并保持连接线程特定（不共享在线程之间的连接）。

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

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

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.

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