locked
Opening/closing database connections RRS feed

  • Question

  • User1411016004 posted

    Hi - not really sure of the best forum for this, but it's OK here...

    When accessing/updating databases on my ASP.NET forms, I always used to create adn open the connection within the required method, do whatever I needed to do, and then close it.

    However, it has been suggested that instead I open the connection as a global object (within the page) in Page_Load adn tehn close it in Page_Unload, accessing it as and when needed in all methods.

    Thios works very well, enabling me to define the connection in a seperate class altogether, and also makes it easier to remove all my database functions to their own module and call them from the relvevant pages - thus making logging and error tracking that much easier. In fact, by having a seperate "data adaptor" class (module) it helps in all sorts of ways.

    However, I wonder whether closing the database connections in each page_Unload is "safe" - by which I mean:  can it be guaranteed that this method is always called, no matter what?  If, for example, the page si slow loading and the user closes their browser? Are there any reasons for not doing things this way - I must say I do prefer it.

    regards
    F

     

    Thursday, March 3, 2011 6:37 AM

Answers

  • User-389939489 posted

    fredman:

    > However, it has been suggested that instead I open the connection as a global object (within the page) in Page_Load adn tehn close it in Page_Unload, accessing it as and when needed in all methods.

    > Thios works very well, enabling me to define the connection in a seperate class altogether, and also makes it easier to remove all my database functions to their own module and call them from the relvevant pages - thus making logging and error tracking that much easier. In fact, by having a seperate "data adaptor" class (module) it helps in all sorts of ways.

    As others have already pointed out, opening and closing the connection just where you need it is the recommended practice, and ADO.NET's built-in connection pooling does the rest. OTOH, the kind of abstraction of data access logic you mention can be implemented in any case, so as a justification for violating the best practice that rather sounds like a red herring...

    -LV

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 4, 2011 6:23 AM

All replies

  • User-1618234021 posted

    Hi

    First of all let me clarify that Page_Unload is not called when the user closes the browser. Once the page finishes loading and rendering the Page_Unload is also called. That means the moment you see the loaded page, unload event has been called already.

    So it is not a good approach to deal with opening and closing connection in load and unload. Instead, you should do this in your separate module where all the functions are. You functions should open the connection, do the stuff and close the connection. It should ideally be at function level, so that whenever you need it, it is created. Do not declare, open or close connections in page.

    Thursday, March 3, 2011 7:33 AM
  • User-610330605 posted

    Instead of reusing the connection for multiple needs on a page by page basis try connection pooling.

    http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

    http://aspalliance.com/1099

    http://www.15seconds.com/issue/040830.htm

    Thursday, March 3, 2011 8:00 AM
  • User1411016004 posted

    @adeelehsan
    Thanks - I do realise that the unload function is not called when the user closes their browser, but when the page has finished loading and rendering - hence I am not sure why you then say my approach should noyt be followed.  Is ther any scenario when the unload function would NOT be called?

    Thursday, March 3, 2011 9:50 AM
  • User1411016004 posted

    @jerryjoseph
    Thanks too - I will folow up on your links, but I would still like to know if there is any specific reason for not using the approach I've outlined...

    Thursday, March 3, 2011 9:50 AM
  • User-952121411 posted

    but I would still like to know if there is any specific reason for not using the approach I've outlined...

    Connection pooling is built into ADO.NET whether you asked for it or not, and that is a good thing! This does not suggest there is never a reason to have a single 'global' connection (i.e. via Singleton), but for most everything I do and have seen the best practice is to open that connection as late as possible (right as you actually need it) and close it as soon as possible after the connection call has completed. I would not be opening and closing connections on a more global aspect using Page events unless there was a specific requirement that required it (and note previous comments that those events like Unload will not work as intended anyway).

    I believe a lot of the concern about keeping connections alive to prevent having to waste resources opening and closing db connections came from other languages or technologies that do not have the embedded benefits such as connection pooling that exist in ADO.NET, or not knowing that these benefits already exist.

    Thursday, March 3, 2011 10:11 AM
  • User-389939489 posted

    fredman:

    > However, it has been suggested that instead I open the connection as a global object (within the page) in Page_Load adn tehn close it in Page_Unload, accessing it as and when needed in all methods.

    > Thios works very well, enabling me to define the connection in a seperate class altogether, and also makes it easier to remove all my database functions to their own module and call them from the relvevant pages - thus making logging and error tracking that much easier. In fact, by having a seperate "data adaptor" class (module) it helps in all sorts of ways.

    As others have already pointed out, opening and closing the connection just where you need it is the recommended practice, and ADO.NET's built-in connection pooling does the rest. OTOH, the kind of abstraction of data access logic you mention can be implemented in any case, so as a justification for violating the best practice that rather sounds like a red herring...

    -LV

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 4, 2011 6:23 AM
  • User1411016004 posted

    Yes, true, and I take everyone's point.  It just seems a bit - I don't know, wierd! - to be opening and closing DB conections all the time on the same  page - for example, an operation may call several DB functions which, because they may be accesed elsewhere too, are kept as seperate methods, and now each has to open and close its own connection. But anyway - yes, OK. Pooling, I get it.

    Thanks for posting

    Friday, March 4, 2011 10:23 AM