Unanswered: Mulitple database connections vs single connection?
I have a DLL that acts as an interface between the application and the database. So you create a new instance to the database via:
MyDatabaseClass db = new MyDatabaseClass()
Each instance of the class creates a new connection to the database.
So if I were to have the following, then 4 connections would be made.
MyDatabaseClass db1 = new MyDatabaseClass()
MyDatabaseClass db2 = new MyDatabaseClass()
MyDatabaseClass db3 = new MyDatabaseClass()
MyDatabaseClass db4 = new MyDatabaseClass()
My question is...What if instead of creating new connections upon each instantiation, I re-use the same connection through a "ConnectionManager" class. In this way, the above code will only create 1 connection instead of 4.
What do you guys think about each approach? Obviously the 2nd approach makes sense for non-web applications, but what about websites? Would that just cause that single connection to be overloaded??
need to be careful about that because you can only have one datareader open at a time on a single connection (unless you are using MARS).
So does that mean if concurrent connections are coming in, which require a datareader, then they will be processed in a queue-like fashion? (And the effect the user sees will just be a slight delay). Or will it actually error-out?
Hmm... I suppose whether or not to use this approach depends on the amount of traffic hitting the database.