Handling DB connection breaks

Question

My WCF service is keeping DB connections for futher sending SQL through they. Sometimes connection become broken for various reasons. Early there was special timer that checks connections every 1 minute. But it's not so good solution of problem. Could you please advice me some way to keep connections working properly or even though reconnect as soon as possible to deliver user stable service. Thanks!

EDIT: Database server is Oracle. I'm connecting to databse server using devart dotConnect for Oracle.

Pooling database connections is generally not a great idea, scenario depending (which isn't exactly stated) I would tend to go with a "unit of work" connection each time. Any pooling benefits can and will come from the innards of the connection pool itself. — Grant Thomas
– Grant Thomas, Commented Oct 4, 2011 at 11:09
Post some code. That might help us to answer the question with code from your context... — Yves M.
– Yves M., Commented Oct 4, 2011 at 11:11
@Mr. Disappointment: this would already be the answer and not a comment ;-) — Yves M.
– Yves M., Commented Oct 4, 2011 at 11:11

Yves M. · Accepted Answer · 2011-10-04 11:40:15Z

4

You don't have to "keep" database connections. Leave the reuse and caching of database connections to the .net framework.

Just use this kind of code and dispose the connection as soon as you are finished using it:

using(var connection = new SqlConnection(...))
{
  //Your Code here
}

There is no problem in executing the code above for each call to the database. The connection information is cached and the second "new" connection to the database is very fast.

To read more about "ConnectionPooling" you might read this MSDN Articel.

Edit:

If you use pooling the connection is not really close but put back to the pool. The initial "handshake" between the client and the database is only done once per connection on the pool.

The component you are using supports the connection pooling as well:

Read1 Read 2

edited Oct 4, 2011 at 11:40

answered Oct 4, 2011 at 11:09

Yves M.

3,31816 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

kseen Over a year ago

Thanks for your response. There is a lot of intensively sending requests for DBs, so it's getting much faster with keeping all db connections open.

Yves M. Over a year ago

I don't think so. I've done a transaction system that did millions of database calls during a day and there was no big performance hit. Sharing a connection over more that one Unit of Work can be very dangerous and error prone.

TomTom Over a year ago

But wrong. every time a conenction is ataken out of the pool, a reset sequence is executed to test the connection is still valid and to reset any / all fragments still left over from the old connection. Clearly visible in the trace of all sql running.

TomTom Over a year ago

That said, the overhead normally is neglegible comapred to the rurnning sql statements.

TomTom Over a year ago

I know - but reusing a known connection is stll faster. We optimized it that way with a system doing like a couple of tens of thousands connections per second.

|

Collectives™ on Stack Overflow

Handling DB connection breaks

1 Answer 1

22 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

22 Comments

Your Answer

Sign up or log in

Post as a guest

Related