Connection to database not closing...

[lidds]

I am stuggling to find out what I am doing wrong here and think a fresh pair of eyes might help.

I have a class called DBAccess which I am using to connect to a SQL Server database, the DBAccess class contains the following code:

Public Class DBAccess

    Private connStr As String = Nothing
    Public myConn As New SqlClient.SqlConnection

    Public Sub New()

    End Sub

    Public Function Connect(ByVal DBName As String) As Boolean
        ' connect to database
        connStr = "Data Source=myServer;initial catalog='" & DBName & "';user id=myUsername;password=myPassword"        
        myConn.ConnectionString = connStr

        Try
            myConn.Open()
            Return True
        Catch e As Exception
            MsgBox(e.ToString)
            Return False
        End Try
    End Function

    Public Function DisConnect() As Boolean
        ' connect to database
        myConn.Close()
        Return True 
    End Function
End Class

I then am calling this class using the below code

        ' Check connection to database
        Dim myDB As New DBAccess()
        If myDB.Connect("myDatabaseName") = False Then
            Return False
        Else
            myDB.DisConnect()
            Return True
        End If

The problem that I have is this seems to not close the connect for some reason and I don't know why. Does anyone know why this might be?

Thanks in advance

Simon

[FunkyDexter]

What makes you think it isn't closing the connection?

edit> BTW, I notice you never set myConn to nothing. Depending on how you use this class you could create a memory leak.

[lidds]

Hi,

Thank you for the info about the memory leak I will have a look into that.

The reason why I know it is not disconnecting is if I go into Microsoft SQL Management Studio and try to detach the database it warns me that there is still 1 connection. I have attached a small sample project to illistrate.

Really don't understand why this is not releasing the connect.

Any help would be great

Thanks

SimonDisconnect.zip

[formlesstree4]

The reason why I know it is not disconnecting is if I go into Microsoft SQL Management Studio and try to detach the database it warns me that there is still 1 connection.

Does it mean 1 connection to the database?

In SSMS, you should run the following query:

sp_who

or

sp_who2

This will tell you the active connections on the SQL Server. You should only really have SSMS as the active connection if your program is truly disconnecting. However, since you are using the native functionality in .NET (.Close()), I would say that you're fine.

[FunkyDexter]

As Formless said, you should use sp_Who2. The chances are the connection that's preventing you from detaching isn't your app at all. In fact, it might be SSMS itself, depending on what windows you've got open (I know I've had open query windows prevent me from detaching before and I'm pretty sure even having the db open in the object explorer tree can do it).

If this is a dev database and you're 100% sure that you're the only person playing with it you can simply disconnect all active users from the detach dialogue. Otherwise I'd close SSMS, re-open it and detach the database without expanding it in the tree or opening any windows.

[lidds]

Thanks guys for all your help.

The issue is not actually detaching the database it was more a concern that my application which uses the above code in it all through it is not closing the connections properly and that my application is leaving open connects. Which I know is the case as when launching my application SSMS says that there is 5 open connections, when there were none before launching my application.

With regards to SSMS having the connection, I don't think this is the case as I go to detach the database prior to running the sample project attached and there are no connections. I then run the attached sample project which is meant to open and then close the connection to the database. I then go to detach the database and at this point it says there is 1 connection. If I close the sample application and then go to detach the database it says there are no connections. Therefore I am quite positive that this code is not working for some reason?

Thanks

Simon

[formlesstree4]

Did you run [either of] the code I provided for you? That's the only way we can accurately determine what's going on.

[dunfiddlin]

Sorry but ...

' Check connection to database
        Dim myDB As New DBAccess()
        If myDB.Connect("myDatabaseName") = False Then
            Return False ' return where?
        Else
            myDB.DisConnect()
            Return True
        End If

Are you really creating a Function to call a Function that isn't really necessary in the first place? Why do you need to check the database at all. I shouldn't be at all surprised to find that your code execution path is simply diverted to a dead end.

[jmcilhinney]

An ADO.NET DbConnection object is not actually a connection to a database. The connection itself exists at a lower level. When you open your first ADO.NET connection, an actual database connection is opened. When you close the ADO.NET connection, the underlying database connection is not closed immediately, so that opening another ADO.NET connection is quicker. If no new ADO.NET connection is opened after a period of time, the database connection is closed.

So, the fact that SQL Server indicates that it has an open connection does not mean that you're not closing your ADO.NET connection. Just check the State property of your connection and if it says that it's closed then it's closed.

[jmcilhinney]

By the way, the SqlConnection class has Shared methods ClearPool and ClearAllPools to close those underlying database connections.

[formlesstree4]

By the way, the SqlConnection class has Shared methods ClearPool and ClearAllPools to close those underlying database connections.

Oh hell that's right it does. I completely forgot about those!

[FunkyDexter]

I completely forgot about those!

I never even knew about them! That's a great tip TG!

[formlesstree4]

I never even knew about them! That's a great tip TG!

TG? I think that was JMC!