Making ADODB More Bulletproof and Connection Timeout

[Chuck Cobb]

I'm trying to make my code more "bulletproof"...I have a user who is using a
wireless LAN network connection that is very unreliable and it has caused
some problems. For example, sometimes when they have a very bad network
connection the ADODB connection can hang the application. I want to make it
so that it is more graceful about handling error situations like this. (My
application uses an OLEDB connection to an Access database - or it can use
SQL Server)

I'm looking at various potential failure points to see what can be done to
handle errors due to network failures. One example is attempting to open a
database connection over an unreliable network. I would think that it would
timeout if the network wasn't there and I tested that by unplugging the
network cable to the client system before it attempted to open the
connection and it just hangs the application. The code to open the
connection is shown below. With the network connection unplugged, the
application will hang on the dbConn.Open instruction and doesn't timeout:
Dim dbConn As New OleDbConnection(strConnString)

dbConn.Open()

Any idea why this won't timeout and declare an error? Does anyone have any
other suggestions of what to do to make ADODB more "bulletproof" over
unreliable network connections? For example, I initialize the connection
once when the application is initialized and leave it open. Perhaps a
better approach would be to close it and re-open it and run some kind of
test that it can actually connect and transfer data prior to doing a live
data transfer. Any ideas would be appreciated.

Thanks,

Chuck

 

[Mary Chipman]

You would be better off if you explicitly open and then close the
connection with each call rather than leaving it open all the time,
especially in a semi-disconnected environment where the network
connection is unreliable. Another way to make your app more robust
would be to check if the connection is already open both before
opening and before closing it.

--Mary

 

[Scott Allen]

Something you might want to check out that will work with Mary's
suggestion is the Smart Client Offline Application Block from the
patterns and practices group at MS:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/offline.asp

The block can test for the presence of network connectivty, cache data
for offline use, and synchronize data when the network is up.

 

[Chuck Cobb]

Thanks, Those are good suggestions...I have one more question. Does the
version of MDAC that you are using make any difference in this area? In
other words, do later versions of MDAC have any improvements for handling
unreliable network connections?

Thanks,

Chuck

 

[Chuck Cobb]

I took a look at the Smart Client Offline Application Block and it is
interesting, but it's 99% pattern and 1% substance. The part that really
matters is the connection detection strategy and it has only a very simple
detection strategy implemented that tests whether there is an internet
connection to some other system anywhere. That really isn't sufficient in
my opinion...I need to go beyond that and detect that there is a connection
to a specific database and that connection is reliable, which means I would
have to implement my own connection detection strategy to use this pattern.

The connection detection strategy is the essence of the issue...Here's an
interesting article on that subject:

http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html

I would be interested in any thoughts people have on an appropriate
detection strategy for ADODB.Net to handle unreliable database connections.
I can see how to provide additional error handling as the author of the
above article has done to retry the connection; but a difficulty I'm having
is that my application seems to hang or crash when there is an unreliable
network/database connection...that seems to be happening in the
ADODB.Net/MDAC layer somewhere before it even gets to my code and if that's
the case, I probably won't see an exception at all.

Here's a very simple test to demonstrate this problem...Develop an
application to open a database connection with the following two lines of
code:

Dim dbConn as New OleDbConnection(strConnectionString)
dbConn.Open

Run the application in debug mode and just prior to the point where you
execute the dbConn.Open statement or create the connection, unplug the
network cable to the database so that it can't connect. The timeout is set
to the default timeout of 15 seconds but the application will hang
indefinitely on the dbConn.Open command with no error generated.

Why doesn't it time out? Is this a bug in the ADODB layer somewhere???

Thanks,

Chuck

 

[Mary Chipman]

Yes -- you should always get the most recent MDAC version available.
Check out
http://msdn.microsoft.com/data/downloads/updates/default.aspx.

--Mary