Correct pattern to use connections

Administrator

Hi Im trying to figure out what's the best way to connect and disconnect from my database. Im building a web based application. What i'm doing right now is providing a connection in the constructor for every class that will need one. I store the connection in a variable and then use it in whatever methods need the connection. Since I need to specifically close the connection, I do so in the destructor. I figured if I do this, then the db connection closing can be done in the background whenever the object is garbageCollected and save myself a bunch of open-closes, plus I dont have to worry about every method having to open or receive its own connection and then closing it. Is there something flawed in my thoughts? any Ideas? Best Regards JR

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Administrator23y ago
Open and closing connection it not costly as long as you apply connection pooling. You dont want your connection open due to the nature of Web and number of users (unless you have only few users). The best way is to open the connection within a method, for example executing a query or returing a DataSet and close the connection right away. Same pattern in every method of the class, instead of opening a connection when then class is instantiated and close the connection on the destructor. Again, it depends on how your database class is being used. If you are creating Db class instance when you need the data and destroying it, it should be fine also.
+1
Administrator23y ago
Yeah, I have a Data Access class which does a similar thing, slightly different but same principals. One thing that I did do was separate my close method, so that I can specifically close my connection in my code, but if I forget, in the destructor I check for an open connection, if there is one close it.
+1