PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Connection to database not closing...-VBForums
Results 1 to 13 of 13

Thread: Connection to database not closing...

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2006
    Posts
    170

    Connection to database not closing...

    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:

    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

    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

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: Connection to database not closing...

    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.
    Last edited by FunkyDexter; Jan 21st, 2014 at 08:48 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2006
    Posts
    170

    Re: Connection to database not closing...

    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

  4. #4

    Re: Connection to database not closing...

    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:

    Code:
    sp_who
    or

    Code:
    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.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: Connection to database not closing...

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    May 2006
    Posts
    170

    Re: Connection to database not closing...

    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

  7. #7

    Re: Connection to database not closing...

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

  8. #8
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,242

    Re: Connection to database not closing...

    Sorry but ...

    vb.net Code:
    1. ' Check connection to database
    2.         Dim myDB As New DBAccess()
    3.         If myDB.Connect("myDatabaseName") = False Then
    4.             Return False ' return where?
    5.         Else
    6.             myDB.DisConnect()
    7.             Return True
    8.         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.
    Last edited by dunfiddlin; Jan 21st, 2014 at 12:33 PM.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,122

    Re: Connection to database not closing...

    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.

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,122

    Re: Connection to database not closing...

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

  11. #11

    Re: Connection to database not closing...

    Quote Originally Posted by jmcilhinney View Post
    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!

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: Connection to database not closing...

    I completely forgot about those!
    I never even knew about them! That's a great tip TG!
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13

    Re: Connection to database not closing...

    Quote Originally Posted by FunkyDexter View Post
    I never even knew about them! That's a great tip TG!
    TG? I think that was JMC!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width