为什么不建议使用一个公共OleDbConnection?解决该错误的替代方案:打开了太多的连接 [英] Why one Public OleDbConnection is deprecated? Alternative to solve the bug: too many connections opened

问题描述

我必须与另一个开发人员制作的项目一起工作.一个带有Visual Basic代码的项目Win-Form，带有MS-Access作为db和一些OleDbConnections.有一个错误:有时应用程序无法打开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

但是在项目中，有许多类可以与db一起使用，并且在这些类中，有许多具有"Friend"可见性的OleDbConnections，它们在不同的时间打开和关闭.因此，不可能将所有OleDbConnections放入Using构造中，并且很难找到要忘记哪些操作来关闭其中一个OleDbConnection的操作.

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.

可能的解决方案是仅使用一个唯一的公共OleDbConnection，并在打开之前检查是否尚未打开. 但是有人告诉我这是一个非常糟糕的做法.我想他告诉过我有关演出的信息，但我不知道. 您能告诉我为什么如此弃用一个唯一的公共OleDbConnection吗? 对我来说，您有解决我问题的简单"解决方案吗? 谢谢， 皮莱吉(pileggi)

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

推荐答案

从您的描述中，我看到了一些可能导致您出现问题的问题:

From your description, I see a couple of possible issues that could result in your problem:

• 嵌套连接:
  您可以在彼此之间打开多个连接
• 打开/释放连接太快:
  正如David-W-Fenton提到的那样，有了访问权限，每次您打开/关闭单个连接时，都会创建/删除锁定文件.此操作非常慢，如果您快速打开/关闭应用程序中的数据库(执行大量原子查询)，则可能会遇到此问题.

• nested connections:
  You open multiple connections within each-other
• open/release connections too fast:
  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:

OLE DB Services=-1

使用连接管理器类为您创建/释放连接.
用您自己的代码替换所有新的OleDbConnection的显式创建并关闭操作. 这样一来，您就可以始终在整个应用程序中重复使用单个现有连接，并可以通过将行为集中在一个位置来快速调整整个应用程序.

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.

那为什么通常不建议保留一个连接?

• 通常，您不应该在整个应用程序中保持连接打开，因为它们会迫使数据库服务器为您保留可用资源，并且这会减少可以连接的客户端数量(始终有有限数量的可用连接) ).
  对于Access，虽然-一个没有服务器部分的基于文件的数据库-保持单个连接打开实际上是更可取的，因为与打开新连接(创建锁定文件)相关的延迟.由于Access并不打算与大量并发用户一起使用，因此保持连接打开的资源成本不足以成为问题.
  通过简单测试，它可以是表明保持连接始终打开可以使以后的连接打开速度提高约10倍！

• 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!

OleDb驱动程序为您执行连接池，因此释放连接后便可以重新使用连接.

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.

这篇关于为什么不建议使用一个公共OleDbConnection?解决该错误的替代方案:打开了太多的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！