How safe or bad is using BasePage for opening and closing SQL connection?

Question

Hi,

I have BasePage.cs class which is being used by other .cs files instead of System.Web.UI.Page (public partial class page : BasePage).

I am using it for opening and closing SQL connections to make sure every SQL connection gets closed.

Code looks like this

{
    public class BasePage: System.Web.UI.Page
    {
        public SqlConnection globalConnection;

        protected override void OnInit(EventArgs e)
        {
             globalConnection = new SqlConnection();
             globalConnection.ConnectionString = ConfigurationManager.ConnectionStrings["kontemiConnectionString"].ToString();
             globalConnection.Open();
        }

        protected override void OnUnload(EventArgs e)
        {
            if (globalConnection != null)
            {
                 globalConnection.Close();
            }
        }
    }
}

So far it has worked really well for me. It means that every time a connection opens it also gets closed. Or at least I thought so.

My question is whether this solutions is bulletproof and every single connection gets closed in case there is some processing error during code execution. When tracing this code, if I on purpose create error 500 it always goes to OnUnload event and gets closed.

So, do you think is this execution safe? (To stop discussion whether I shouldn't open SQL when I actually need it, answer is that every page which uses BasePage also opens a SQL connection.)

Answer 1

I might be wrong, but my guess would be that the connection does not get closed if the page lifecycle is ended through an exception before OnUnload fires. The very least you could do to prevent this is make sure you catch all exceptions on a global 'last-chance' level, and close the connection there. I still think using connections locally, ideally with using blocks, is the better solution, because it doesn't keep connections open longer than it needs to, and you don't have to worry about closing them (the semantics of using will do the work for you).

Answer 2

Opening and closing SQL connections where you are using them is better practice. Under the hood, the CLR manages connection pooling anyway - reusing connections and closing them when it sees fit. Doing lots of open and closes on connections with the same connection string doesn't add up to as much overhead as you might expect.