2

Is it normally to create an instance "connection" (type of MySqlConnection) every time in every methods in form (I have about 20 methods) and open connection with Open method (in the start of the method) and close connection with Close method (in the end of the method)?

Code (one of my methods):

private void buttonOk_Click(object sender, EventArgs e)
        {
            
            MySqlConnection conn = new MySqlConnection(connStr);
            conn.Open();
            // other code
            conn.Close(); 
        }

Or in my case it is better to create a field inside form class and open connection in constructor and close connection in the form_closed method or in destructor?

  • Does this answer your question? [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice) – Charlieface Jul 10 '22 at 02:35

1 Answers1

1

depends on the implementation of the SqlConnection and the configuration.

In your case (the MySqlConnection) and in most variants of other databases, there is a pooling implementation under the hood.

https://dev.mysql.com/doc/connector-net/en/connector-net-connections-pooling.html

If there is pooling, there is no benefit of keeping a connection open without using it. If there is a poolsize of 1000 Connections, you can only instanz 1000 of your classes, the next one needs to wait, untill one of the other connection will be closed.

So, its better to open a connection directly, where you need it and as long you need it to not dry out the pool.

And don't forget to dispose the connection

Another reason could be mutlithreading -> connections are not threadsafe. Avoiding a shared connection will avoid threading issues

Nobody
  • 149
  • 5
  • *open a connection directly*: must read as *ask for a Connection*, you don't actually open anything. The ConnectionManager of the ConnectionPool does (and hands back to you what's available) -- There are specific cases when you need to store a Connection object. You know it because you cannot do without it. Doesn't happen in the context of this question (no specific System version is mentioned). – Jimi Jul 09 '22 at 23:33