none
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

Answers

  • 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))

    {

       conn.Open();

       // 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