CODEWITHSUNDEEP
mysqlvb.net
Mark Ismail
Mark Ismail

Reputation: 720

In VB.NET, a MySQL connection won't close after calling conn.close or conn.dispose or even with USING

I am using MySQL Workbench. If I open a connection (in Visual Basic) to MySQL it will show that a connection is opened. If I close the connection, (in MySQL Workbench), it shows the connection is still open. If I try to reopen the same connection, it will open and it will close without any problem. However, MySQL Workbench shows that the first connection is still open. It stays open until I exit the application.

   Try
        Using myConnection As New MySqlConnection(_ServerString_vip)
            myConnection.Open()

            myConnection.Close()

            End Using
        End Using
    Catch ex As Exception
        MsgBox("Error getting Username Information.")
    End Try

Upvotes: 0

Views: 208

Answers (2)

Mark Ismail
Mark Ismail

Reputation: 720

jmcilhinney is absolutely right. I was looking all over the Internet and I couldn't find an answer for that. Hopefully, this question will help a lot of people. And eventually the connection will die automatically.

Upvotes: -1

jmcilhinney
jmcilhinney

Reputation: 54417

You should do some reading on connection pooling in ADO.NET.

A DbConnection object is actually quite light-weight and exists at a higher level than the actual database connection. When you open a DbConnection object, a low-level database connection is opened. When you close the DbConnection object, the low-level database connection remains open for some time. That's because opening and closing it is an expensive operation. If you open another DbConnection with the same connection string, the same low-level database connection will be reused. The low-level connection is only closed if it remains unused for some time.

This is not something you have to worry about. This is the way it is and has always been in ADO.NET and the system handles it automatically. You keep doing what you're doing, i.e. creating DbConnection objects with Using statements, and the system will take care of the rest.

By the way, there's no point opening the connection explicitly when calling Fill or Update on a data adapter. Both those methods will automatically open the connection and close it again afterwards. You should only open the connection explicitly if you want it to stay open after the Fill or Update call, i.e. if you have more work to do over that connection.

Upvotes: 2

Related Questions