database connection loss

[Martin B]

Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications which
are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection and
my Select-Statement works again. Then I have to trigger the other threads to
reopen their connections and continue working (every thread gots his own
DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in fact
of this exception. Such, or similar problems I can not get handled with my
solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin

[Peter Bromberg [C# MVP]]

Martin,

Best-practices coding in ADO.NET dictates that a connection should be opened
immediately prior to its use, and closed immediately thereafter. Your post
seems to indicate that you have connections that are kept open. When you
close an ADO.NET connection with most providers, it is returned to the
connection pool automatically, which is almost always the preferential way to
handle database work.

When you attempt to open a connection and it throws a timeout exception,
your code should be engineered to handle this.

Hope that helps.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Martin B" wrote:

Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications which
are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection and
my Select-Statement works again. Then I have to trigger the other threads to
reopen their connections and continue working (every thread gots his own
DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in fact
of this exception. Such, or similar problems I can not get handled with my
solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin

[Martin B]

Hallo Peter!

Thank you for immediate response.

I know that ADO.NET offers a pooling strategy to accelerate establishing
database connectivity, but can you tell me what is the great benefit for
this architecture? I understood it that way, that this solution makes only
sense when several forms not so frequently performs their updates against
the database.

In my case I've coded a Win-Service which performs the entire booking
operations for all my Terminal-Programs, that means that the Service is busy
all the time and for me it would not make any sense closing and reopening
the database connection after each transaction. The Service performs up to 5
transactions per second so I have to pay attention of optimizing the
performance.

By closing the connection you mean releasing it to the connection-pool?

When exactly will the connections of the pool be opened and what happens if
the network is not available, are all connections marked as broken, will
they be released or do I get at each open command an exception till all
connections of the pool are passed through and detected as broken. Is it up
to the programmer to flush the pool when he detects first appearance of a
network loss (using SQLConnection.ClearPool or ClearAllPools).

Do I alwayes get the same Timeout Exception at opening or can it change
depending on the network error (for example: Server down, Database down,
cutting network cable on server-side, cutting network cable on client-side
....).
I could not exactly figure out how this pooling mechanism works. Are all
connections prepared and opened at the first Open command and all further
Open commands get the next available Connection from the Pool. But there
must be any verifications whether the Connection state is valid or not to
get the Timeout Exception.
with friendly regards

Martin
"Peter Bromberg [C# MVP]" <pb*******@yahoo.nospammin.com> wrote in message
news:4A**********************************@microsof t.com...

Martin,

Best-practices coding in ADO.NET dictates that a connection should be
opened
immediately prior to its use, and closed immediately thereafter. Your post
seems to indicate that you have connections that are kept open. When you
close an ADO.NET connection with most providers, it is returned to the
connection pool automatically, which is almost always the preferential way
to
handle database work.

When you attempt to open a connection and it throws a timeout exception,
your code should be engineered to handle this.

Hope that helps.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Martin B" wrote:

Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications
which
are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection
and
my Select-Statement works again. Then I have to trigger the other threads
to
reopen their connections and continue working (every thread gots his own
DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in
fact
of this exception. Such, or similar problems I can not get handled with
my
solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin

[Kevin Spencer]

Hi Martin,

I know that ADO.NET offers a pooling strategy to accelerate establishing
database connectivity, but can you tell me what is the great benefit for
this architecture? I understood it that way, that this solution makes only
sense when several forms not so frequently performs their updates against
the database.
This is the way ADO.Net works. The solution always makes sense because that
is the way ADO.Net is architected. If you go against the grain, you're in
trouble (as you are).

Here's how it works: The most expensive and time-consuming database
operation is opening a Connection. Therefore, ADO.Net employs Connection
Pooling, which means that when you close a Connection, it is not actually
closed, but returned to the Connection Pool. When you open a new Connection
that uses the same Connection parameters (i.e. Connection String), it
fetches that Connection, or the next available one from the Pool. ADO.Net
manages the Connection Pool itself, and you don't even have to think about
it.

When you bypass Connection Pooling, by keeping a Connection opened, you
break it. Since you keep the Connection open, it cannot be returned to the
pool and re-used by another thread.
In my case I've coded a Win-Service which performs the entire booking
operations for all my Terminal-Programs, that means that the Service is
busy
all the time and for me it would not make any sense closing and reopening
the database connection after each transaction.
As you should be able to see now, it doesn't make any sense *not* to
"reopen" a Connection after each transaction. In a very real sense, it is
already opened after the first use. In addition, if a Connection from the
Pool is in use, the Pool will create another one.

As for optimization, you should probably look at client-side caching of data
when you can. Of course, to perform some sort of transaction that *changes*
data, this requires another round-trip to the database.
When exactly will the connections of the pool be opened and what happens
if
the network is not available, are all connections marked as broken, will
they be released or do I get at each open command an exception till all
connections of the pool are passed through and detected as broken. Is it
up
to the programmer to flush the pool when he detects first appearance of a
network loss (using SQLConnection.ClearPool or ClearAllPools).
Again, this is handled by the framework. If the network is not available,
what difference does it make to you? You cannot perform any database
operations regardless of whether you or the Framework manages your
Connections, until the Network becomes available again.
Do I alwayes get the same Timeout Exception at opening or can it change
depending on the network error (for example: Server down, Database down,
cutting network cable on server-side, cutting network cable on client-side
...).
Depending on the type of "Timeout Exception," you may be able to tweak this
via your Connection String. However, your first priority should be to fix
the Connection model you're using to make use of the built-in Connection
Pooling. If you still have a Timeout problem, you can fix that later.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

"Martin B" <ma************@gamed.com> wrote in message
news:44***********************@newsreader.inode.at ... Hallo Peter!

Thank you for immediate response.

I know that ADO.NET offers a pooling strategy to accelerate establishing
database connectivity, but can you tell me what is the great benefit for
this architecture? I understood it that way, that this solution makes only
sense when several forms not so frequently performs their updates against
the database.

In my case I've coded a Win-Service which performs the entire booking
operations for all my Terminal-Programs, that means that the Service is
busy
all the time and for me it would not make any sense closing and reopening
the database connection after each transaction. The Service performs up to
5
transactions per second so I have to pay attention of optimizing the
performance.

By closing the connection you mean releasing it to the connection-pool?

When exactly will the connections of the pool be opened and what happens
if
the network is not available, are all connections marked as broken, will
they be released or do I get at each open command an exception till all
connections of the pool are passed through and detected as broken. Is it
up
to the programmer to flush the pool when he detects first appearance of a
network loss (using SQLConnection.ClearPool or ClearAllPools).

Do I alwayes get the same Timeout Exception at opening or can it change
depending on the network error (for example: Server down, Database down,
cutting network cable on server-side, cutting network cable on client-side
...).
I could not exactly figure out how this pooling mechanism works. Are all
connections prepared and opened at the first Open command and all further
Open commands get the next available Connection from the Pool. But there
must be any verifications whether the Connection state is valid or not to
get the Timeout Exception.
with friendly regards

Martin
"Peter Bromberg [C# MVP]" <pb*******@yahoo.nospammin.com> wrote in message
news:4A**********************************@microsof t.com...

Martin,

Best-practices coding in ADO.NET dictates that a connection should be
opened
immediately prior to its use, and closed immediately thereafter. Your
post
seems to indicate that you have connections that are kept open. When you
close an ADO.NET connection with most providers, it is returned to the
connection pool automatically, which is almost always the preferential
way to
handle database work.

When you attempt to open a connection and it throws a timeout exception,
your code should be engineered to handle this.

Hope that helps.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Martin B" wrote:

Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications
which
are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can
I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection
and
my Select-Statement works again. Then I have to trigger the other
threads to
reopen their connections and continue working (every thread gots his own
DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in
fact
of this exception. Such, or similar problems I can not get handled with
my
solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin