Sql connection in a windows service

Question

I have written a Windows Service which listens for data from a third party service, holds it in memory for a short time and periodically all the new data is flushed to the database.

I was initially opening a new connection each time I needed to flush the data and closing it again afterwards. (Every 5 seconds or so)

As the server seems to be getting hammered I have changed that so there is a single connection opened and reused for the life of the application.

Just wondering if this is a bad idea?

I usually do web stuff where the connection is open and closed over the life of a single request. What is the best practice for a windows service that needs to do the sort of operation I have described?

I was going to make a fault tolerant connection like this:

private SqlConnection _sqlConnection;
public SqlConnection SqlConnection
{
    get
    {
        if (_sqlConnection == null || !_sqlConnection.State.Equals(ConnectionState.Open))
        {
            var conn = new SqlConnection(_connectionString);
            conn.Open();
            return conn;
        }

        return _sqlConnection;
    }
}

so if some reason the existing connection is closed or faulted in some way we would get a new open one

is that bad design for any reason?

Answer 1

If you are the single user of the database, hold onto the connection. If not you can really rely on connection pooling to do that for you.

I personally would go for opening the connection everytime. In .NET 2.0 a new feature was implemented so that if you have an open connection to a sql server and sql server gets restarted, etc... your connection becomes invalid and that is not something I can risk my service with. See my post from some years ago.