Pros and cons of using the Singleton template in DAL

Question

I asked to use a single template implemented by DAL, but I think it is difficult to combine connections, use transactions..etc

I would like to know the pros and cons, and also would like to know how best to combine the connections, as there can be more than 500 simultaneous users for the site that I am developing.

The database server is Oracle 10g.

DAL uses the corporate library 3.1

Answer 1

The syntax template works great for DAL - I use it in my own web application (hundreds of users and more than 2000 methods in 20 of some singleton classes). The connection pool is really best handled by ado.net and the sql server. If you want to have several types of server servers, this is not a problem. Even with a singleton pattern, you probably need a centralized data access class that handles the specifics of the actual direct calls to the database (with parameters, text / procedural names, credentials / connection chain, all passed to).

In my situation, each method on one corresponds to 1: 1 with a stored procedure in my database. This essentially makes a C # “front end” hook for each stored procedure, so you can call them almost like your own C # code, syntactically speaking. It makes accessing the DAL very simple. Due to the large number of SPs under consideration, we have several singletones. Each SP has a prefix, for example Development_ or Financial_, or Organization_, or something else. Then I have a singleton class corresponding to each, for example Development, Financial or Organization. So sp Organization_ViewData in C # will be a method named ViewData in a singleton class named Organization.

This is one way to do this, of course, but I found that I worked very well with several developers and a lot of code over the past six years. The main thing is that coherence is key. If an external programmer looks at the name of the method at one of your peer brokers, he must say exactly where he goes to the end of the database. Thus, if there is a problem or someone who needs to look for code to try to understand it, there is less trace that needs to be done.

Answer 2

The best practice for a connection pool is not to implement it yourself, but instead let the ADO.NET platform take care of it.

You can set the pooling parameters as parameters in the connection string. Then, each connection that opens with this line will be provided from the connection pool, which is implemented and managed by the infrastructure. When you close or uninstall OracleConnection, the underlying connection will not be destroyed, but instead will return to the pool.

This is described here: http://msdn.microsoft.com/en-us/library/ms254502.aspx

About using singletones in general: I used them to wrap my data access layer, and it always worked well.

Please note that transactions apply only to specific connections, and not to the database as a whole. This means that you can run multiple threads, and each thread can read and write to the database through independent transactions, providing each thread with a separate instance of OracleConnection.

Answer 3

I don't know about DAL, but the singleton pattern is a great way to make data global, while still maintaining good encapsulation.

Answer 4

Using singleton to connect to a factory database in DAL is pretty common. This allows you to more easily connect various factory implementations without changing a lot of code. Many people don't seem to like the singleton pattern, but I think it works fine for this type of thing.

Answer 5

I don’t think you will have performance differences if you use singleton or not, because you can still have multiple threads working on the same method at the same time. If you take care not to have internal fields that will be used in all threads, everything will work well.

At the end, the class that manages the connection pool must be thread safe, and you end up with a few locks that can affect performance, but they are all needed. (he did internally within the framework, and you cannot change his behavior in any way)

If you decide not to use singleton, make sure that your DALs are lightweigth, because it may matter, but usually it is not.

Note. Speaking of connection pools, the only thing you need to take care of is to follow the “open late, close early” pattern. This means that delay the opening of the connection as much as possible and close it as soon as possible, having completed all that you need.

After the whole system is built using this magic rule, you can play with the parameters of the connection string to change some parameters of the pool (initial size, maximum size, ...)

Answer 6

I am a little worried about using singletons in the case of DAL. What if I want to use multiple databases. Maybe 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 can make testing database layers more difficult if I cannot transfer the database connection to the test.