views:

28

answers:

1

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 As 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
A: 
SLaks
I tried explicitly closing the connection, but as I mentioned it is already closed after the fill. If I close it again, it still has not effect on the DB in SSMS, still shows connected.
using the "using" code above also still gives me an active connection showing in SSMS after the sub returns
Are you sure you aren't opening any other connections?
SLaks
absolutely none, I check and there are no connections before I run the sub, after I debug thru the sub is the connection. I close the program down right then and the connection is gone. This is a dev machine and only I have access.
Press Ctrl+D, N, and add a function breakpoint at `System.Data.SqlClient.SqlConnection.Open()`, then see how many connections are opened. (Right-click `this` in the Locals window and click Make Object ID)
SLaks
ssms activity monitor shows one connection
But it might be opened later. Check.
SLaks
Press Ctrl+D, N, and add a function breakpoint at System.Data.SqlClient.SqlConnection.Open()...I'm not following how to do this, I press Ctrl+D, N and nothing happens...
ok, I went into Breakpoints/ New (Break at function) and msg said: intellisense could not find specified location.
Ignore that message, but make sure it has a hit count.
SLaks
now it dispplays a window " there is no source code for the ..."the hitcount is one after it returns.
You hit the breakpoint. Look at the call stack, make sure you know what's going on, then hit F5. Count the number of times it gets hit. (You can see the count in Debug, Windows, Breakpoints)
SLaks
call stack show it is currently sitting at the function, from the da.Fill(ds) program call - hit F5 and goes to the next program statment, hitcount of the function is 1.So that is the only connection to the DB, why isn't the stupid connection able to be closed? Something seems to not be playing nicely here.
Does the breakpoint ever get hit again?
SLaks
no, one time only.
Make a similar breakpoint on .Close() and make sure the same instance (Make Object ID on Open) ends up getting closed.
SLaks
yes, I had just done that. it only gets called when I explicitly call the close method (da.SelectCommand.Connection.Close() after the fill method returns), but even after it gets called SSMS still shows the connection.
You might be fighting connection pooling.
SLaks
yes, this kills it, finally..SqlConnection.ClearPool(da.SelectCommand.Connection)
@bmutch:Then you should accept this answer.
SLaks
yes, I will, thanks.