为什么一个公共 OleDbConnection 已被弃用？解决 bug 的替代方案：打开的连接过多
我必须处理另一个开发人员制作的项目。带有 Visual-Basic 代码的 Win-Form 项目，使用 MS-Access 作为数据库和一些 OleDbConnections。有一个错误：有时应用程序无法打开 OleDbConnection，因为数据库已达到最大连接数。我知道使用连接的最佳方法是这样的：
Using cn As New OleDbConnction(s)
...
cn.Close()
End Using
但是在项目中，有许多类可以与数据库一起使用，并且在许多这些类中，有具有“Friend”可见性的 OleDbConnections，它们在不同的时间打开和关闭。由于这个原因，不可能将所有 OleDbConnection 放入一个 using 构造中，并且很难找到哪个操作“忘记”关闭这些 OleDbConnection 之一。
一种可能的解决方案是仅使用一个唯一的公共 OleDbConnection，并在打开它之前检查它是否尚未打开。 但有人告诉我这是一种非常糟糕的做法。我想他告诉了我有关表演的事情，但我不太清楚。 您能告诉我为什么一个独特的公共 OleDbConnection 如此被弃用吗？ 对于我来说，您有一个“简单”的解决方案来解决我的问题吗？ 谢谢你， 皮莱吉
I have to work with a Project made by another developer. A project Win-Form with Visual-Basic code, with MS-Access as db and some OleDbConnections. There is a bug: sometimes the application can't open the OleDbConnection because the max number of connections has been reached on the db. I know the best way to use the connections is this:
Using cn As New OleDbConnction(s)
...
cn.Close()
End Using
But in the project there are many classes to work with the db, and in many of these classes there are OleDbConnections with "Friend" visibility, that are opened and closed in different times. For this reason it's impossible to put all the OleDbConnections in a Using construct, and it's very very hard to find what operation "forgets" to close one of these OleDbConnection.
A possible solution could be to use only one unique public OleDbConnection, and to check, before opening it, if it isn't already opened.
But someone have told me it's a very bad practice. I suppose he told me this about the performance, but I don't know it exactly.
Can you tell me why one unique public OleDbConnection is so deprecated?
Have you got, for me, an "easy" solution for my problem?
Thank you,
Pileggi
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
从您的描述中，我看到一些可能导致您的问题的问题：
您打开多个连接，彼此
正如 David-W-Fenton 提到的，通过访问，每次打开/关闭单个连接时，都会创建/删除锁定文件。此操作非常慢，如果您在应用程序中快速打开/关闭数据库（执行大量原子查询），您可能会遇到此问题。
调查和解决问题的几种可能方法：
跟踪所有打开/关闭调用
添加一些调试跟踪，在每次打开和关闭连接时显示。
它将允许您检测嵌套连接以及连接池被浪费的地方。
强制连接轮询
一个简单的“修复”可能是在连接字符串中显式设置连接池。它应该是默认行为，所以也许它不会做任何事情来解决您的问题，但它是如此简单，没有理由不尝试它：
使用连接管理器类来创建/释放为您提供联系。
用您自己的代码替换所有显式创建的新 OleDbConnection 和关闭操作。
这将允许您始终在整个应用程序中重复使用单个现有连接，并允许您通过将行为集中在一个位置来快速调整整个应用程序。
那么为什么通常不推荐保持单个连接？
通常，您不应该在整个应用程序中保持连接打开，因为它们会强制数据库服务器为您保留可用的资源，并且会减少连接的数量。可以连接的客户端（可用的连接数量始终有限）。
对于 Access（基于文件的数据库，没有服务器部分）来说，保持单个连接打开实际上更可取，因为与打开新连接（创建锁定文件）相关的延迟。由于 Access 不适合与大量并发用户一起使用，因此保持连接打开的资源成本不足以构成问题。
通过简单的测试，可以结果表明，保持连接始终打开可以使后续连接的打开速度提高大约 10 倍！
OleDb 驱动程序为您执行连接池操作，因此能够在释放连接时重用连接。
通过保持连接和数据库操作较小且包含在内，您在使用线程时不太可能遇到并发问题。如果您使用同一个数据库管道执行多个操作，保持全局连接可能会成为一个问题。
From your description, I see a couple of possible issues that could result in your problem:
You open multiple connections within each-other
As David-W-Fenton mentionned, with access, every time you open/close a single connection, the lock file will be created/removed. This operation is quite slow and if you quickly open/close the database within you application (execute lots of atomic queries), you may get this issue.
A few possible ways to investigate and solve the issue:
Trace all open/close calls
Add some debug traces that show every time you open and close a connection.
It will allow you to detect nested connections and where your connection pool is being wasted.
Force connection polling
An easy 'fix' may be to explicitely set connection pooling in your connection string. It should be the default behaviour, so maybe it won't do anything to solve your problem, but it's so simple that there is no reason not to try it:
Use a connection manager class to create/release connections for you.
Replace all the explicit creations of new OleDbConnection and close operations by your own code.
This would allow you to always re-use a single existing connection throughout your application and allow you to quickly make tweaks for the whole of your app by centralising the behaviour in a single place.
So why holding a single connection is generally deprecated?
Generally, you should not keep connections open throughout your application as they force the database server to keep resources available for you, and it decreases the number of client that can connect (there is always a limited number of connections available).
For Access though -a file-based database without server part- keeping a single connection open is actually preferable because of the delay associated with opening new connections (creation of the lock file). Since Access is not meant to be used with a large number of concurrent users, the resource cost of keeping the connection open is not significant enough to be an issue.
From simple tests, it can be shown that keeping a connection always open allows subsequent connections to open about 10x faster!
The OleDb driver does connection pooling for you, so it is able to re-use connections when they are freed.
By keeping your connections and database operations small and contained, you would be less likely to run into concurrency issues when using threads. Keeping a global connection may become an issue if you are executing multiple operations using the same pipeline to the database.
只需添加一些对我来说成功多年的信息（它有点类似于David-W-Fenton 建议）
首先，到 Microsoft Access（MDB、JET）的
OleDbConnection不使用连接池。正如 Microsoft 在 KB191572 中所述：关于连接池，还有这篇博文伊万·米特夫 (Ivan Mitev) 指出：
以及 Microsoft MSDN 中的注释：
基于所有这些和我自己的测试，即使使用 Microsoft Access 数据库“模拟”连接池的我的解决方案大致遵循以下步骤：
OleDbConnection在应用程序生命周期中尽可能。OleDbConnection，就像建议的那样。OleDbConnection。这极大地加快了我的应用程序（主要是 WinForms）的速度。
请注意，这也适用于 Sqlite，它似乎也不支持连接池。
Just adding some information that works for years successfully for me (it is somewhat similar to what David-W-Fenton suggests)
First, an
OleDbConnectionto Microsoft Access (MDB, JET) is not using connection pooling. As Microsoft states in KB191572:Regarding connection pooling, there is also this blog post from Ivan Mitev that states:
And Microsoft notes in MSDN:
Based on all this and my own tests, my solution to "simulate" connection pooling even with Microsoft Access databases roughly follows these steps:
OleDbConnectionto the Access database as early as possible in application lifecycle.OleDbConnections as early as possible, just like recommended.OleDbConnectionas late as possible in application lifecycle.This sped up my applications (mostly WinForms) tremendously.
Please note that this also works for Sqlite which seems to not support connection pooling, too.