After running the following sub (VS debugger), I try to detach the database in SSMS, but it shows the connection open still and won't let me detach. If I close program in debugger, the database shows no connections. I check the dataadapter's connection in the finally block and is shows closed. What gives

Private Function ClientMasterDBFiles(ByVal MasterClientDBConnection 开发者_如何学PythonAs String, ByVal DBName As String) As DataTable
    Dim da As SqlDataAdapter
    Dim ds As DataSet

    Try
        ds = New DataSet
        da = New SqlDataAdapter
        da.SelectCommand = New SqlCommand
        With da.SelectCommand
            .CommandType = CommandType.StoredProcedure
            .Connection = New SqlConnection(MasterClientDBConnection)
            .CommandText = "QT_DataSync_GetDBFileLocations"
            .Parameters.Add(New SqlParameter("@DBName", SqlDbType.VarChar, 100))
            .Parameters.Item("@DBName").Direction = ParameterDirection.Input
            .Parameters.Item("@DBName").Value = DBName
            .CommandType = CommandType.StoredProcedure
            .CommandTimeout = 10
        End With

        da.Fill(ds)

        If ds.Tables.Count > 0 Then
            Return ds.Tables(0)
        End If

    Catch ex As Exception
        m_ErrorLog.HandleException(ex)
        Throw
    Finally
        If Not da Is Nothing Then da.Dispose()
        If Not ds Is Nothing Then ds.Dispose()
        da = Nothing
        ds = Nothing
    End Try
End Function

EDIT

I was wrong all along.
Your problem is that the .Net SqlClient classes pool connections.

You need to explicitly close the SqlCommand's Connection, like this:

If Not da Is Nothing Then da.SelectCommand.Connection.Close()

Dim ds As DataSet

Try
    Using da As SqlDataAdapter, _
          da.SelectCommand = New SqlCommand, _
          da.Connection = New SqlConnection(MasterClientDBConnection)

        With da.SelectCommand
            .CommandType = CommandType.StoredProcedure            
            .CommandText = "QT_DataSync_GetDBFileLocations"
            .Parameters.Add(New SqlParameter("@DBName", SqlDbType.VarChar, 100))
            .Parameters.Item("@DBName").Direction = ParameterDirection.Input
            .Parameters.Item("@DBName").Value = DBName
            .CommandType = CommandType.StoredProcedure
            .CommandTimeout = 10
        End With

        da.Fill(ds)

        If ds.Tables.Count > 0 Then
            Return ds.Tables(0)
        End If
    End Using
Catch ex As Exception
    m_ErrorLog.HandleException(ex)
    Throw
End Try

Also, you shouldn't dispose the DataSet, since you're returning one of its tables.