在 DAL 中使用单例模式的优点和缺点
我已要求使用单例模式实现的 DAL，但我认为很难池化连接、使用事务等。
我想知道优点和缺点，也想知道池化连接的最佳方法，因为可以我正在开发的网站有超过 500 个并发用户。
数据库服务器是Oracle 10g。
DAL 使用 Enterprise 库 3.1
I have asked to use singleton pattern implemented DAL, but I think its difficult to pool the connections,use transactions..etc
I would like to know the pros and cons and also would like to know the best way to pool the connections as there can be more than 500 concurrent users for the site I am developing.
DB Server is Oracle 10g.
DAL uses Enterprise library 3.1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
单例模式非常适合 DAL —— 我在自己的企业 Web 应用程序中使用它（数百个用户和 20 多个单例类中的 2,000 多个方法）。 连接池确实由 ado.net 和 sql server 本身处理得最好。 如果您想要拥有多种类型的后端服务器，那不是问题。 即使使用单例模式，您也可能需要一个集中式数据访问类来处理实际直接调用数据库的细节（参数、文本/过程名称、凭据/连接字符串全部传入）。
在我的情况下，单个方法上的每个方法与我的数据库中的存储过程一一对应。 这实质上为每个存储过程创建了一个 C#“前端”挂钩，因此从语法上讲，它们几乎可以像本机 C# 代码一样被调用。 它使得对 DAL 的调用非常简单。 由于存在大量 SP，我有多个单身人士。 每个 SP 都有一个前缀，例如 Development_、Financial_、Organization_ 等。 然后我有一个与每个类相对应的单例类，例如开发、财务或组织。 因此，sp Organization_ViewData 在 C# 中将是名为 Organization 的单例类上名为 ViewData 的方法。
当然，这只是实现这一目标的一种方法，但我发现在过去六年中，这种方法可以与多个开发人员和大量代码很好地配合。 最重要的是，一致性是关键。 如果前端程序员正在查看其中一个单例代理上的方法名称，那么这应该准确地告诉他们该方法将进入数据库端的位置。 这样，如果出现问题，或者有人必须搜索代码来尝试理解它，则需要进行的跟踪就会减少。
The singleton pattern is great for a DAL -- I use this in my own enterprise web application (hundreds of users and over 2,000 methods in 20-some singleton classes). The connection pooling is indeed handled best by ado.net and the sql server itself. If you are wanting to have multiple types of back-end server, that's not a problem. Even with a singleton pattern, you probably want a centralized data-access class that handles the specifics of actually making direct calls to the database (with parameters, text/procedure names, credentials/connection string all passed in).
In my situation, each method on a single corresponds 1:1 with a stored procedure in my database. This essentially makes a C# "front end" hook for each stored procedure, so that they can be called almost like native C# code, syntactically speaking. It makes calls to the DAL very straightforward. I have multiple singletons because of the massive number of SPs in question. Each SP has a prefix, like Development_, or Financial_, or Organization_ or whatever. Then I have a singleton class that corresponds to each, like Development, Financial, or Organization. So the sp Organization_ViewData would in C# be a method named ViewData on a singleton class named Organization.
That's just one way of doing it, of course, but I've found that to work very well with multiple developers and a large amount of code over the last six years. The main thing is that consistency is key. If a front-end programmer is looking at the name of a method on one of your singleton brokers, that should tell them exactly where it is going into the database end. That way if there's a problem, or if someone's having to search through code to try to understand it, there's less tracing that has to be done.
连接池的最佳实践是不要自己实现它，而是让 ADO.NET 框架来处理它。
您可以将连接池选项设置为连接字符串中的参数。 然后，使用该字符串打开的每个连接都将从框架实现和管理的连接池中提供。 当您关闭或处置 OracleConnection 时，底层连接不会被销毁，而是会返回到池中。
此处对此进行了描述：
http://msdn.microsoft.com/en-us/库/ms254502.aspx
关于单例的一般使用：我使用它们来包装数据访问层，并且它一直运行良好。
请注意，事务仅适用于特定连接，而不适用于整个数据库。 这意味着您可以运行多个线程，并且每个线程都可以通过独立事务读取和写入数据库，前提是每个线程使用单独的 OracleConnection 实例。
The best practice for connection pooling is to not implement it yourself, but instead let the ADO.NET framework take care of it.
You can set connection pooling options as parameters within the connection string. Then, every connection that is opened with that string will be supplied from the connection pool that is implemented and managed by the framework. When you close or dispose of the OracleConnection, the underlying connection is not destroyed but will instead go back onto the pool.
This is described here:
http://msdn.microsoft.com/en-us/library/ms254502.aspx
About the use of Singletons in general: I've used them to wrap the data access layer, and it has always worked well.
Note that transactions only apply to specific connections, not the database as a whole. This means you can have several threads running, and each thread can read and write to the database through independent transactions, providing each thread uses a separate OracleConnection instance.
我不了解 DAL，但单例模式是使数据全局化同时保持良好封装的好方法。
I don't know about DAL but the singleton pattern is a great way to make data global while maintaining good encapsulation.
在 DAL 中使用单例作为数据库连接工厂非常常见。 它使您可以更轻松地插入工厂的不同实现，而无需更改大量代码。 很多人似乎不喜欢单例模式，但我认为它适用于这种类型的事情。
Using a singleton for the database connection factory in the DAL is pretty common. It lets you more easily plug in different implementations of the factory without changing a lot of code. A lot of people don't seem to like the singleton pattern, but I think it works ok for this type of thing.
我对在 DAL 中使用单例感到有点不安。 如果我想使用多个数据库后端怎么办？ 也许我想使用 MsSQL 处理发票，但使用 Active Directory 进行身份验证。 或者也许我想使用 MySQL 来发布论坛帖子，但使用 PostgreSQL 来进行地理集群（对我来说更现实，呵呵）。 当我无法将模拟数据库连接传递给测试时，单例接口可能会使测试数据库层变得更具挑战性。
I'm a bit uneasy about using singletons in the case of a DAL. What if I want to use more than one database backend. Perhaps I want to use MsSQL for invoices but Active Directory for Authentication. Or maybe I want to use MySQL for forum posts, but PostgreSQL for geo-clustering (more realistic for me, heh). Singleton Interfaces might make testing the database layers much more challenging when I can't pass a mock database connection to the test.
我认为无论是否使用单例，性能都不会有差异，因为您仍然可以让多个线程同时在同一方法上运行。 如果您注意不要让内部字段在所有线程中共享，那么一切都会正常进行。
最后，管理连接池的类需要是线程安全的，并且您最终将创建一些可能影响性能的锁，但它们都是必需的。 （它是在框架内部创建的，无论如何您都无法更改它的行为）
如果您决定不使用单例，请确保您的 DAL 实例是轻量级的，因为这可能会产生影响，但通常情况并非如此。
注意：谈论连接池时，您必须注意的唯一重要的事情是遵循“晚打开，早关闭”模式。 这意味着，尽可能延迟连接的打开，并在完成所需的所有操作后尽快关闭它。
使用此神奇规则构建所有系统后，您可以使用连接字符串参数来更改一些池选项（初始大小、最大大小……）
I don't think you'll have performance differences if you use a singleton or not, because you still can have multiple threads running on the same method at the same time. If you take care of not having internal fields that will be shared in all threads, everything will work well.
At the end, the class that manages the connection pool needs to be thread safe and you will end up making a few locks that could affect the performance, but they are all needed. (it's made internally in the framework, and you cannot change it's behavior anyways)
If you decide to do not use a singleton, be sure that your DAL instances are lightweigth because this might make a difference, but usually it is not.
Note: talking about the connection pools, the only important thing you must take care is of following the "open late, close early" pattern. That means, delay the open of the connection as much as possible, and close it ASAP after you done all you need with it.
After having all the system built using this magic rule, you can play with the connection string parameters to change some pool options (initial size, max size, ...)