Leaving MySQLConnection open or not in Windows Forms project

I am wondering if I should open the MySQLConnection once at the beginning and close it when the form is disposed, or open and close every time I have to run a command or query.

I’m guessing opening it every time prevents open connections from being left open in case of a bug or a crash.
On the other hand, opening once seems like a more pratical and faster way of doing things. Any thoughts?

Right now this is what I use:

    Using connection As New MySqlConnection("Data Source=localhost; user id=root; password=; database=mydata;")
        Dim myDataReader As MySqlDataReader
        Dim myDT As New DataTable()
        Dim myCommand As MySqlCommand = New MySqlCommand("SELECT * FROM...;", connection)
        
        ' Code here
    End Using

This in essence opens and closes the connection every time. I could also simply use a global MySQLConnection variable and open it when the app loads.

Please give me your thoughts on what is considered best.

The safest bet is to open and close it for each call. If you’ve got sequential commands to execute it’s ok to use one connection. You might want to check with the MySqlConnection specs but it may have connection pooling built in, in that case it will manage the multiple connections for you.

1 Like

It’s typically a bad idea to keep the connection open for a long time. Suppose each client does the same you would soon run out of connections. The best bet is run all your sequential queries in one connection then close down the connection. Also read about connection pooling.

1 Like

Thanks to the both of you! It all makes sense…

by using syntex your connection is automatically open and close of use database . you no need to open and close database .