Leaving a connection open for a while is fine, as long as:

1. you don t have so many concurrently idle connections that you hit the MySQL connection limit;

2. you don t leave it open for hours without doing anything. The default MySQL connection wait_timeout is 8 hours; leave a connection inactive for that long and when you next come to use it you ll get a “MySQL server has gone away” error.

Since you re using ADO.NET, you can use ADO.NET s inbuilt connection pooling capabilities. Actually, let me refine that: you must always use ADO.NET s inbuilt connection pooling capabilities. By doing so you will get the .NET runtime to transparently manage your connections for you in the background. It will keep the connections open for a while even if you closed them and reuse them if you open a new connection. This is really fast stuff.

Make sure to mention in your connection string that you want pooled connections as it might not be the default behaviour.

You only need to create connections locally when you need them, since they re pooled in the backrgound so there s no overhead in creating a new connection:

using (var connection = SomeMethodThatCreatesAConnectionObject())
{
    // do your stuff here
    connection.Close(); // this is not necessary as
                        // Dispose() closes it anyway
                        // but still nice to do.
}

That s how you re supposed to do it in .NET.

Yes you can, provided:

• You will reconnect if you lose the connection
• You can reset the connection state if something strange happens
• You will detect if the connection "goes quiet", for example if a firewall timeout occurs

Basically it requires a good deal of attention to failure cases and correct recovery; connecting and disconnecting often is a lot easier.

I think, if there is a connection pooling mechanism, you d better close the connection.

One reason for it is that you do not need to re-check if your connection is still alive or not.

If the application is using the connection there is no reason to close it. If you don t need the connection you should close it. If you were to have multiple applications connect to the database, you have a fixed number of connections to that database. That s why it s better to close when you are done and reopen when you need it.

From a security point of view, I d say its better to close it after a query, just to be sure that no other program can inject it s own things into the opened connection.

As performance is conered, it is clearly better to have the connection opened through the whole time.

Your choice^^

No, I don t see any reason why not to leave a connection open and re-use it: after all, this is the whole point behind the various connection-pool technologies that are about (although these are generally reserved for multi-threaded situations where works are all operating on the same data source).

But, to expand on the answer by bobince, - just beacause you are not closing the connection, don t assume that something else won t: the connection could timeout, there could be connection issues or a hundred and one other reasons why your connection dies. You need to assume that the connection may not be there, and add logic to code for this exception-case.

It is not good practise in my opinion to keep the connections open. Another aspect that speaks for closing connections every time is scaleability. It might be fine now to leave it open but what if you app is used by twice 3-times the amount of users. It s a pain in the neck to go back and change all the code. (i know i ve done it :-)

Your problem will be solved if you use connection pooling in your code. You don t need to open and close connection so you save precious resources which are used while opening a connection. You just return the connection to a pool which when requested for a connection returns back a idle connection.

Of course I am of the opinion, get an instance of the connection, use it, commit/rollback your work and return it to the pool. I would not suggest keeping the connection open for so long.

One thing I didn t see in the other answers, yet: In case you have prepared statements or temporary tables they might block server resources till the connection is closed. But on the other hand it can be useful to keep the connection around for some time instead of recreating them every few moments.

You ll pay a performance penalty if you re constantly opening and closing connections. It might be wise to use connection pooling and a short wait_timeout if you are concerned that too many running copies of your app will eat up too many database connections.