[RESOLVED] Opening and Closing MySQL Connection

[dccjr3927]

Not sure if this is the correct place for this question. I created a module for handling connect and disconnect actions to my database. The reason was to prevent from having to put the same 20+ lines of code in each button action or form that queried the db. This way I can call the DbConnect, handle my db business, the call the DbDisconnect. Here is my module so far:

Friend Module Connections
    Public Sub DbConnect()
        Using conn As New MySql.Data.MySqlClient.MySqlConnection
            Try

                Dim myConnectionString As String = "server=127.0.0.1;" _
                    & "uid=root;" _
                    & "pwd=12345;" _
                    & "database=test"
                conn.ConnectionString = myConnectionString
                conn.Open()

            Catch ex As MySql.Data.MySqlClient.MySqlException
                MessageBox.Show(ex.Message)
            End Try
        End Using
    End Sub

    Public Sub DbDisconnect()

    End Sub
End Module

The question is how do I reference conn in the DbDisconnect in order to .close and .dispose the connection? As a side question, is it OK to do it this way? My reasoning was that if I ever had to change my connection string, it was only in one place.

Thanks for any assistance or recommendations.
(VS 2019, MySQL 8.0)

[Shaggy Hiker]

You can't reference conn if you do it that way. It's not just local to that method, it's local to that Using block, so the only code that can even SEE conn would be something inside that block.

Overall, the approach isn't a bad one, but in this case I think you are trying to hard to avoid a bit of redundant code. I have had times when I wanted the opening and closing not to be located in the same place, but that was because a variety of modules needed to use that particular connection, which was because they needed to be able to take action in the transaction that the connection has started. For all other uses, I get in, do what I need to do, and get out. The End Using takes care of close and dispose, and will do so even if an exception is thrown (you could have put the Using inside the Try block and the connection would still have been cleaned up in the event of an exception). Yes, this does make for a bunch of redundant Using blocks scattered around, but...they all look the same, so they are super easy to write, or I could just copy them from elsewhere. Heck, I could put the rough outline in a method and just copy it from there whenever I needed to, while just filling out what came between the Using and End Using, which is all that is different, in most cases.

[dccjr3927]

Thanks. I am developing on the local host, but will be deploying on a network with the database either on a server or on a cloud server (boss has not decided yet). Is there an easy way of having to change the connection string every time I switch between the two? All of my forms have at least 2 actions that will require a connection, and I have about 20 forms (not counting reports). It just seems like a pain to have to change the connection in 40+ locations.

[si_the_geek]

You can store the connectionstring in one place, eg:

Friend Module Connections
   Public Const myConnectionString As String = "server=127.0.0.1;" _
                    & "uid=root;" _
                    & "pwd=12345;" _
                    & "database=test"
End Module

You can then use myConnectionString elsewhere, without having to re-define it.

[dccjr3927]

Thank you. I think this will work well for my use.