4

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?

Aloraman
  • 1,389
  • 1
  • 21
  • 32
  • I think this is fine - but remember to call `Dispose()` when you aren't using it any more. – Bridge Dec 21 '12 at 15:13
  • 1
    I think this begs the question... Why close it in the first place? – Dave Zych Dec 21 '12 at 15:13
  • Simply use `using (SqlConnection conn = new SqlConnection(ConnectionString)) { //your code }` - this will do dispose and close for you. The answer is "no", no problem open/close connection as long as it is live object. The question is why? – T.S. Dec 21 '12 at 15:45

3 Answers3

4

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.

C.Evenhuis
  • 25,996
  • 2
  • 58
  • 72
1

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.

jmrnet
  • 548
  • 3
  • 11
  • Actually I have a class, which serves as proxy to some old VB libraries. These VB modules require methods for beginning and committing transactions - so there should be connection object in instance of my class. Since lifetime of the class is actually long, I cannot afford keeping connection opened forever - so I was interested if I could simply keep one connection object per instance in close/open cycle instead of recreating it every time. – Aloraman Dec 21 '12 at 16:06
1

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

Duc Thanh
  • 11
  • 2