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
I don't know about DAL but the singleton pattern is a great way to make data global while maintaining good encapsulation.
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.
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.
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, ...)
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.
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.