CODEWITHSUNDEEP
c#asp.netoptimizationoledb
Flob
Flob

Reputation: 128

best way to handle oledbconnections

i learned by searching stack overflow and other sites that singleton is a bad programming practice. i'am relying heavily on OleDbConnection to connect to an SqlServer , i have pages that can instantiate like 20+ times a connection . i wanted to replace this by a singleton to avoid opening connections when there's already a connection opened.

My questions are : Is Singleton in my case bad ? What is the best way to achieve multiple connections on an sql server 2008 ?

Upvotes: 0

Views: 1592

Answers (2)

Yahia
Yahia

Reputation: 70369

what you describe has nothing to do with Singleton... singleton itself is neither bad nor good - only when used for the wrong reason it is bad (as everything).

as for your connection issue:

This situation is usually solved/handled via a connection pool...

You could implement that yourself (not recommended)... depending on the ADO.NET provider you use it might already come with a well-implemented and well-tested connection pool (for example the one from Devart - not affiliated, just a happy customer)...

Connection pools provide sort of a cache for DB connections... when you need one you acquire it from the pool, when you are finished you return it to the pool... the pool keeps connections around (for some configurable time period since last use or similar)... since the connections in the pool are already created and open you gain a significant speed advantage...

OTOH you can't really profit from a connection pool if the connections you use use different logins and/or connection settings...

UPDATE:

OleDB comes with a built-in connection pooling mechanism - see MSDN for details. According to MSDN it is enabled by default... on closing/disposing the connection it will automatically be returned to the pool... which in turn means for your situation that you might already be using the pooling mechanism (assuming you are using the default settings for OleDB).

Upvotes: 1

Haris Hasan
Haris Hasan

Reputation: 30097

Singleton is not the right choice in your case because it is a recommended practise to close the connection as soon as you are done with your database transaction.Even if you need database connection frequently, you should open connection whenever you need and close it as soon as possible.

Other than that Singleton is not a bad programming practise at all. Singleton is a widely used pattern in programming. No pattern is bad programming practise. It is just that you should be using them in a scenario where they fit in and make perfect sense. If you will use a pattern in a inappropriate scenario that would be a bad programming practise.

Edit

Just to be clear, in my opinion, rule of thumb should be that you should not keep a connection open for operations in future. If there are some pending transactions, obviously it would be best to use the same connection to execute all the transactions. But again you should not keep the connection open for something that you might need to do in future

Upvotes: 1

Related Questions