Opening SqlConnection that has been closed already

Question

Are there any problems with closing and opening of the same SqlConnection object instead of creating new one each time? For example:

SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
//Some work here
conn.Close()
//Some work here... conn stays in scope
conn.Open()

Is there any chance to get illegal state exception by opening connection second time?

Answer 1

You can reopen the connection after closing it (you cannot reopen a disposed connection, though).

As for Dave Zych's question - some of our customers have per-connection licences on their databases, closing a connection allows other applications to use it.

Usually you'll have connection pooling enabled, in which case the actual connection to the database will not (always) be closed (immediately) but can be used by other, equal connection objects within your application.

Answer 2

Open and close the same connection does exactly same thing with create new connection then open and close it as long as the connection string doesn't change.

Reason: ADO.NET use technique called connection pooling. When you open a connection with a connection string, ADO.NET will look at the pool and see if this connection with that connection string already exist in the pool or not, if the answer is yes, then it will get this and open that connection for you. Otherwise, ADO.NET will add a connection with that connection string to the pool and open that. That's why the first time accessing to database is always slower than others.

Details here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

Answer 3

There is no problem doing this. You can open and close the connection as much as you like. However, I wonder why you would want to open and close the same one rather than just create a new one for each call. Is that because you are making many calls in rapid succession? If so, you may consider using a single SqlConnection and opening it once at the start and closing it once at the end.