如何处理数据访问层 (.net) 中的连接
我正在编写一个数据访问层。 我对管理系统中的连接感到困惑。 我知道.net 使用连接池。 但我不想在所有 dml 操作或所有 sql 查询中打开和关闭数据库连接。 我该如何处理这个问题？ 何时何地（可能在使用数据访问层的全局 asax 中或在数据访问层中）应管理连接？
I am writing a data access layer. I am confused about managing connections in the system.
I know that .net uses connection pooling. But I do not want to open and close database connections in all dml operations or in all sql query's. How can I handle this? Where and when ( maybe in global asax which uses data access layer or in the data access layer ) the connections should be managed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
您应该为每个查询打开和关闭 sql 连接，除非您正在运行一批语句。
“晚开早闭”是处理数据库连接的一贯方式。
如果您以传统方式（进行自己的查询）进行操作，MS 已经编写了一个很好的数据访问接口。 用于数据的企业库（应用程序块）具有所有优点形成了花哨的功能。
如果您不想编写查询，我建议您查看 linq2Sql 或 linq2EF（首选）。 它们将极大地简化您的编码。
You should open and close sql connections for each query, unless you are running a batch of statements.
"Open late, close early" is how you should always handle database connections.
If you are going about it in the traditional manner (making your own queries), MS has already written a nice data access interface. The enterprise library (application blocks) for data have all of the nice well formed bells and whistles.
If you dont want to bother with writing queries, I suggest you look at linq2Sql or linq2EF (preferred). They will greatly simplify your coding.
为什么不想为每个离散逻辑操作打开/关闭连接？ 大多数现有的 DAL 都是这样运行的。 通常，尝试超越运行时自动为您做的事情并不是一个好主意，例如智能地管理您的连接。 在投入时间和精力向应用程序添加复杂性之前，您应该有强烈的、可证明的技术需求。
Why don't you want to open/close the connection for every discrete logical operation? Most existing DALs behave that way. Usually it's not a good idea to try to outsmart what the runtime will do for you automatically, such as intelligently manage your connections. You should have a strong, demonstrable technical need before putting in the time and effort to add that complexity to your app.
那些必须作为事务发生的操作又如何呢？
是你们的BL负责操作和逻辑/验证吗？
假设您有一个 BL 层来
结果：客户无效。 所以你必须回滚你的交易。
人们将如何解决这个问题？
What about operations that has to occur as a transaction?
It is your BL that does the operation and logic/ validation right?
Let's say you have a BL layer that
the outcome: customer not valid. So you have to roll back your transaction.
How would one go about solving this issue?
连接管理不应由 DAL 管理。
唯一可以负责/可以决定是否应打开新连接或应关闭连接的层是使用 DAL 的服务层或应用程序层。
该层是唯一了解上下文的层，因此您可以在该层决定是否应该关闭连接，或将其保持打开状态，因为存在应使用相同连接的其他数据库通信。
Connection management should not be managed by the DAL.
The one and only layer that can be responsible / can decide whether a new connection should be opened, or a connection should be closed, is the service layer or application layer which uses the DAL.
That layer is the only layer that is aware of the context, and so this layer is where you can decide whether you should close a connection, or leave it open since there aer other DB communications that should use the same connection.
您确实应该为每个操作打开/关闭。 不要将连接使用视为高成本操作，因为它根本就不是。 当您在站点上打开连接时，它们将在连接池中创建。 当您“关闭”连接时，连接池不会释放该连接：它会将其保留在手边，以供重用。 因此，虽然导致新连接的第一次调用需要更长的时间，但后续连接速度非常快。
更新：这在网络应用程序中尤其如此！ 不要尝试在全局对象中仅打开一次连接并在所有线程中重复使用它，否则您的站点将无法工作。
You should indeed open/close for every operation. Do not look at Connection use as a high-cost operation because it simply isn't. As you open connections on your site, they will be created in a connection pool. When you "close" the connection, the connection pool does not release the connection: it keeps it on-hand, ready for reuse. Thus, while the very first call resulting in a new connection takes a bit longer, the subsequent connections are very fast.
Update: this is particularly true in a web application! Do not attempt to open a connection just once in your global object and re-use it across all threads or your site simply won't work.
在.Net 中编写了几个数据层（以及之前在 VB6 中编写的一些数据层）后，我的建议是：
3.5 确保所有表都有唯一的单字段主键！
有点OT？...
我自己的最新基本 DAL （没有 ORM）花了我半个小时写的，紧凑又漂亮
高效的。 MS 企业的东西是巨大的！
最后一件事 - 我个人认为从 xsd 生成的强类型数据集具有很高的烦恼（和膨胀）增益比 - 以及它们让您处理 NULL sux 的方式。 您编写的使用它们的所有代码也很臃肿......或者您最终将它们向下转换为 DataSet 以实际获得一个高效、非重复代码的库。
My advice after writing several datalayers in .Net (and a good few more in VB6 previously) is:
3.5 make sure all tables have a unique one-field primary key!
slightly OT?...
My own latest basic DAL (no ORM) took me half an hour to write and is compact and pretty
efficient. The MS enterprisey stuff is HUGE!!!
One last thing - I personally think that strongly-typed datasets generated from xsds have a high annoyance (and bloat) to gain ratio - and the way they make you handle NULL sux big time. All the code you write to use them is bloaty too... or you end up downcasting them to DataSet to actually get a library of efficient, non-repetitive code.
我不清楚回答旧问题的礼仪，而且我不知道如何评论另一个答案（我是全新的，而且我今天还没有喝完第一杯咖啡，所以让我放松一点=]）。
我总是编写 DAL 来打开/关闭每个查询的连接，并让驱动程序的连接池完成连接管理的工作。
但是，我有一个使用共享 MS Access DB 的多用户桌面应用程序（在制作此应用程序时，SQL Express 还没有以真正可用的形式存在），并且我偶尔会看到指示损坏的错误。 这篇 MS 文章 建议整个应用程序仅使用单个连接：
“重复打开和关闭不建议使用 Microsoft Access 数据库。在应用程序开始时打开数据库一次，然后在应用程序结束时关闭数据库。”
我的应用程序是多线程的，因此我假设我必须修改建议以“每个线程打开一个连接”以避免冲突。 有人有使用 OLEDB 共享 Access 数据库和类似损坏问题的经验吗？
I'm not clear on the etiquette for answering old questions, and I couldn't figure out how to comment on another answer (I'm brand new to SO, and I'm not quite through my first cup of coffee today, so cut me some slack =]).
I always write my DALs to open/close connections with every query and let the driver's connection pooling do the work of connection management.
However, I have a multi-user desktop app that uses a shared MS Access DB (SQL Express wasn't around in a really usable form when this app was made), and I've occasionally seen errors indicative of corruption. This MS article recommends using only a single connection for the entire app:
"Repeatedly opening and closing a Microsoft Access database is not recommended. Open the database once at the beginning of the application, and then close the database at the end of the application."
My app is multi-threaded, so I'm assuming I'd have to revise the recommendation to "open one connection PER THREAD" to avoid collisions. Does anyone have any experience using shared Access databases with OLEDB and similar corruption issues?