Should I close a connection in a dispose method?

[SS]

I've built a class to broker the data in may application. In other words,
when my business logic needs a list of widgets in the db, it calls
mybrokerclass.getWidgetList, which might return a collection or arraylist of
widgets.

This way, the business logic doesn't care about where the data comes from.
(BTW, this is a web application)

So my problem is this.. during a postback, a series of operations will occur
wherein perhaps three requests to this broker class will take place, all for
different sets of data. Rather than opening and closing the connection to
the db in each method (three times opened, three times closed in this case),
I think it would be better to OPEN the connection in the constructor of the
class and then put the CLOSE method in the destructor (dispose). Does this
sound like correct thinking or am I screwed up in how I'm approaching this?

P.S., since I'm only worried about closing the db connection in this dispose
method, I'm assuming I should _not_ call the GC.suppressFinalize method so
the GC can clean up anything remaining...?

Thanks for any help on this!!

 

[Kevin Spencer]

It can get complicated. For example, if your class works with DataReaders,
only 1 DataReader at a time can be associated with an opened Connection.
Also, is this a static or instance class? If it is a static class, your
Connection will never be closed. In fact, as ADO.Net leverages Connection
Pooling very well, you would probably do just as well to simply open and
close the Connection as quickly as possible.

HTH,

Kevin Spencer
Microsoft FrontPage MVP
Internet Developer
http://www.takempis.com
Some things just happen.
Everything else occurs.

 

[Trond-Eirik Kolloen]

inline...

I've built a class to broker the data in may application. In other words,
when my business logic needs a list of widgets in the db, it calls
mybrokerclass.getWidgetList, which might return a collection or arraylist of
widgets.

This way, the business logic doesn't care about where the data comes from.
(BTW, this is a web application)

So my problem is this.. during a postback, a series of operations will occur
wherein perhaps three requests to this broker class will take place, all for
different sets of data. Rather than opening and closing the connection to
the db in each method (three times opened, three times closed in this case),
I think it would be better to OPEN the connection in the constructor of the
class and then put the CLOSE method in the destructor (dispose). Does this
sound like correct thinking or am I screwed up in how I'm approaching

this?

No, it does not sound like correct thinking.

You never know when your distructor is called (except if you implement the
IDisposable interface, and if you do so, your in the mercy of your callers)
This is because you do not know when the GC will do it's stuff, and you may
risk to get A LOT of open connections, witch really is something you do not
want.

As .NET connection automatically uses connection pooling, you do not
actually open and close your connection tree time, you just put's it back
into the connection pool (wich is a quite cheep operation regarding process
cycles)

The minimal advantages you might gain by keeping the connection open does
not defend the higher risk of using your suggested method.

You should also remember that if you want your class to scale as good as
possible, you should keep it statless. If you hold on to an open connection,
it's not statless....

Regards, TEK