Where should I define a SQLConnection? RRS feed

  • Question

  • I've been told that Global, Shared, and Static SQLConnections are a bad idea. In other words, declare SQL connections at the method-level rather than class-level, and do not pass as a SQLConnection as a parameter, because of possible "Connection Leaks".

    Are there whitepapers or code-samples to demonstrate SQLConnection declaration and usage?

    Thanks in advance.
    Friday, February 22, 2008 9:36 PM


  • Nothing so grandiose as a white paper, but a pattern that you see a lot is this:


    Code Snippet

    using (SqlConnection conn = new SqlConnection(connectionString))



       // create and execute commands here




    The using block forces the CLR to close and dispose the SqlConnection even if the code within it throws an exception.


    It's not actually problematic to keep the reference to a connection around:  the problem is keeping a connection open.  If you look at the TableAdapter code that the typed dataset generator produces, you'll see that the TableAdapter has a persistent connection property, but that every one of the commands opens the connection in a try block and closes it in a finally block. 


    The using pattern has a little more overhead, as it creates the SqlConnection and calls its constructor every time.  But I don't have any reason to think that the overhead is significant enough to worry about.

    Friday, February 22, 2008 9:57 PM