"General Network Error" and Connection Pooling

[Jeppe Jespersen]

I have a very simple WinForms app, that uses a sqlDataAdapter retrieve data
into a dataset.
The dataset is set to be the datasource of a DataGrid. If - while editing
rows in my grid - i lose my network connection (or stop + start my
sqlserver), i get a "General Network Error" when I call my dataAdapters
..update method. (...naturally, after reestablishing network connection, that
is...)

However, if i disable connection pooling, I get no error. Also, with
connection pooling enabled, if I repeatedly call update, eventually it
succeeds on the third attempt.

Anyone who can smarten me up?

J. Jespersen
Denmark

 

[Norman Yuan]

Since you use DataSet, which is disconnected object, you should not keep the
Connection alive after fill the DataSet (how long the use could play with
the data in the DataSet? A few seconds, a few hours, or he leave his
computer and the program on for days, you never know), and only create a
connection again when you need to update to database.

 

[William \(Bill\) Vaughn]

Of course. This is a known issue that was fixed with 2.0. When you use
connection pooling, the pool is filled with identical connections that
remain live after your code executes Close. When the server fails these
"open" connections are invalidated, but ADO 1.1 does nothing about it--until
you try to use one. At that point you'll get an exception and the offending
connection is cleared but any remaining (identical) connections are not.
This means when you try again, you'll get another exception and you'll keep
getting exceptions until the pool is cleared. This might mean 2-20 or up to
100 retries. It might be faster to restart your application (in 1.1) as
there is no other way to empty the pool except to wait for them to time out
(4-8 minutes).

In ADO.NET 2.0 the pooling mechanism has been repaired in that as soon as it
determines that a connection is bad, it flushes the pool--knowing that they
are all bad.

I'll be talking about these issues in Cleveland and Eau Claire (Wisconsin)
in early November and in Sydney at VSLive.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik [MVP]]

Please see
http://codebetter.com/blogs/sahil.malik/archive/2005/10/07/132857.aspx for
the problem description .. and solution in both .NET 2.0 and 1.1

 

[Jeppe Jespersen]

Thanks to all for replies. The "add-a-space" solution is pretty sweet.
Coincidentally, I am right now reading your (Maliks) "Pro ADO.Net 2.0" book,
and it is EXCELLENT.

J.Jespersen
Denmark

 

[Sahil Malik [MVP]]

Oh Thank you Jeppe .. It's very encouraging to know see all that effort
being appreciated. THANKS , you made my day.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx