I understand that you need to call .Close() on a SqlConnection object to release the underlying SQL connection back to the pool when you are done with it; but if you refrain from doing so, does the .NET object remain in memory even after going out of scope? I ask because I am working with some code that is experiencing memory leaks and I noticed that the SqlConnection objects are not being closed or disposed (they are created, opened, then simply allowed to go out of scope).
The issue isn't a memory leak. The issue is that the connection to the SQL server remains open, meaning that connection isn't available for something else that needs to communicate with that server.
The connection will be closed eventually if it goes out of scope and gets garbage collected and disposed, but there's no telling when that will happen. Your application can only have so many SQL connections open at a given time, and the SQL server itself can only support so many connections.
Think of it like a responsible person borrowing a book from the library. If you don't return the book it will eventually get back to the library, because one day you'll die and when someone cleans up your house they'll find the book and send it back to the library. But if everyone did that then it would be really hard to find books at the library. So we don't check out the book until we're actually ready to read it, and we return it as soon as we're done.
Same thing with SQL connections. Don't open them until you need them, and close them as soon as possible when you're done with them. And, as shown in other answers, using simplifies it by ensuring that the connection will be disposed (which also closes it) without having to use try/finally.