i'm wondering what the best practice is when using odbcconnection objects to connect to mysql in asp.net.
currently i am decaring a private OdbcConnection member that is opened and closed with each query / update / insert / etc on the page. one page can potentially make multiple calls to the db (ie filling a dropdown, displaying recent messages, etc all on one page). also, event handers tend to use the db connection as well to do whatever action is associated with clicking a button, selecting an item, etc.
here are some questions:
1) should the connection be kept open for the entire page (ie opened in page_load)?
2) should i create the connection object when i use it and not have it as a private member that is reused?
3) when executing db operations (ie a command.executenonreader) within a try-catch block, should the connection.close() call be made in the finally block (and even then only conditionally if the connectionstate is open)? if not, where should it be closed?
i'm trying to improve my code to behave in a 'best practices' type of way. it's all working now, so i just want to go through an optimization phase. any and all comments would be appreciated, and feel free to link me to any articles/posts that touch on the subject matter.
thanks in advance!