Error with the Using Statement and Lazy Initialized Property

Question

The code below will throw an InvalidOperationException: The ConnectionString property has not been initialized. The exception is thrown at the line calling Connection.Open() in the Load method. If I use the try-finally statement instead of the using statement, everything works correctly. Can anyone explain why the exception occurs with the using statement?

Public Class SomeEntity
    Private _Connection As SqlClient.SqlConnection
    Private _ConnectionString As String

    Protected ReadOnly Property Connection() As SqlClient.SqlConnection
        Get
            If _Connection Is Nothing Then
                _Connection = New SqlClient.SqlConnection(_ConnectionString)
            End If
            Return _Connection
        End Get
    End Property

    Public Sub New(ByVal connectionString As String)
        _ConnectionString = connectionString
    End Sub

    Public Sub Load(ByVal key As Integer)
        Using Connection
            Connection.Open()
            ...
        End Using
    End Sub
End Class

Answer 1

Connection in your Using statement is not being initialized or declared. Your code should read more like:

Public Sub Load(ByVal key As Integer)
    Using myConnection as SqlClient.SqlConnection = Me.Connection
        Connection.Open()
            ...
    End Using
End Sub

Answer 2

You failed to mention a key piece of information: It succeeds the first time Load() is called, but then fails forever after.

When using Using, Dispose() is called on the used variable when the Using block exits. So in your scenario:

1. Load() gets called
2. The Using statement calls the Connection property's Get
3. _Connection gets set to a new SqlConnection and returned
4. The returned connection gets opened and used normally
5. The Using block exits, calling Dispose() on the connection

At this point, the SqlConnection object still exists, and is still pointed to by _Connection. It's no longer in a usable state though, since it's been Dispose()d. When the second call to Load() comes in:

1. Load() gets called
2. The Using statement calls the Connection property's Get
3. _Connection is still pointing to a (useless) SqlConnection object, therefore it's not Nothing, and doesn't get set to a new SqlConnection object
4. The (useless) connection gets returned
5. Open() gets called on the connection--which is in an unusable state--and triggers the InvalidOperationException

You're mixing conflicting approaches to connection management. Keeping the connection object around as a member of the class implies that you want to keep the connection alive for the life of the SomeEntity object, but using Using implies that you want to create and destroy the connection on the fly with each usage.