Should I create a brand new SqlConnection each time I want to use it, or just attempt to re-open an existing connection each time?

Question

I have a long-running .NET process (a Windows service), which talks to a SQL Server.

Originally, I opened a connection when the service started up, and just kept it open. However, occasionally a network hiccup or reboot of the SQL Server would break that connection, and my process wouldn't know this. It would attempt to keep throwing SQL against a closed connection.

Trying to maintain an open connection like that was clearly me trying to be too clever. So, do I:

1. Create (and open) a new SqlConnection object every time
2. Create the connection once on service start-up, and just attempt to re-open the connection every time

For the latter (#2), I have this code:

if(connection.State != ConnectionState.Open)
{
  connection.Open();
}

I only have to do this because the connection already exists. If I created the connection fresh each time (#1), clearly it would be closed and I would need to open it.

What I'm hoping to do is take advantage of SQL connection pooling to actually not open a new connection every time, but just let the connection pool manage that -- give me an open connection when it has one, or open a new one when it doesn't.

To achieve this, does it matter if I create the connection fresh each time (#1), or if I just-reuse a connection and attempt to re-open it each time (#2)?

Answer 1

Connection pooling means that even if you do (1), under the hood, the framework will do (2) for you for improved performance. So you can feel free to create a new connection each time.

It's worth pointing out that, for the most part, the pooling only applies to identical connection strings. So if you change certain options, those connections may not be pooled.