473,599 Members | 3,227 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

database connection loss

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.Com mon.DBConnectio n 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
Apr 12 '06 #1
3 10285
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.Com mon.DBConnectio n 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

Apr 12 '06 #2
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.C learPool 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*******@yaho o.nospammin.com > wrote in message
news:4A******** *************** ***********@mic rosoft.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.Com mon.DBConnectio n 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

Apr 13 '06 #3
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.C learPool 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.ino de.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.C learPool 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*******@yaho o.nospammin.com > wrote in message
news:4A******** *************** ***********@mic rosoft.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.Com mon.DBConnectio n 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


Apr 13 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
12091
by: Peter Larsson | last post by:
Hi there, I've recently developed a VBA-program in Excel that fetches and presents data from a distant Access database over a wireless peer-to-peer network (both computers running Win XP Pro). It has worked relatively well for some time, but recently I'm often encountering errors when trying to fetch the data. I am pretty sure that this is because of a poor wireless connection since I'm also having some problems e.g. copying files...
3
3128
by: markaelkins | last post by:
I want to create a simple user interface to collect the following data and store the data in a SQL database…. Could someone please help me get started? Data to collect from user interface and store in database: tDateS (trade date sell) tDateB (trade date buy) contracts (number of contracts) strike (stike price) sValue (sell value) bValue (buy value)
1
2345
by: Sonya | last post by:
Hello, I have trouble connecting to Access database from my web service. Database is located in a subdirectory of where service pages are. Code below produces the following exception: "No error information available - E_NOINTERFACE". public bool TestConnection() { // Get connection string from web.config
3
2110
by: Martin B | last post by:
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
5
1912
by: Dennis | last post by:
I am totally confused between Access, SQL Express, and SQL Server and MSDE and OLEDB vs SQL in .net. Please someone tell me if I"m correct in the following: With MSDE installed, I can program using OLEDB in ADO.Net for Access then convert later to an SQL Express or SQL Server database and my program still work if I change the connection string. With MSDE installed, I can program ADO.Net for SQL and it will work for SQL express or SQL...
3
3409
by: Big Charles | last post by:
Hi, We have developed an ASP.NET web application and are planning to host it in an external Server, which provides us a good bandwidht. We need to put he web application outside because the bandwidtht that we have in our local office won't be enough for the number of users that access to our web app. But for backup issues, the database server will be in our local office.
6
5632
by: mpmason14 | last post by:
I found a vbscript on vbskrypt.com that allows me to inventory the software installed on my computer and puts it in an Access database. problem: the script only finds those programs installed by Windows Installer - nothing else. objective: i want to expand the quality of the script to be able to find ALL programs as they are listed in Add/remove programs. part of the script includes the search location as "Win32 Product"...I think...
8
13236
by: mark_aok | last post by:
Hi all, I have a split database. Both the forms, and the tables are stored on a shared network drive (this is Access 2003). The users use the forms, and the tables on the network drive, there are no local copies. When connection to this drive is lost, Access CRASHES. It does it every single time. Does anyone know if there is a way to check if
8
1542
by: Rahul | last post by:
Hi Everyone, I'm currently developing a class for a database, each object of the class will establish a connection to the database on a remote server and and all of this happens on the constructor. So there are cases when the connection can fail during the initial setup in the constructor and i was wondering how to send this error to the caller. Is it a good design to throw exceptions from the constructor? or is there any other...
0
8401
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8053
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8268
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5440
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3900
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3944
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2418
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1508
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1252
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.