C# Database – New Connection vs New ConnectionString

cdatabase

This question might have been asked before, but I am unable to find it.

So here goes:

I am writing a program which connects to several databases (one at a time). It is using OleDbConnection at the moment, but will be changed to SqlConnection at some point.

Should I create, and open a new connection every time, or simply close the existing connection, assign a new connectionstring, and reopen it.

I know both solutions work, but why would I prefer one over other.

For completeness sake, here is the implementation:

Code executed when changing database.

var connection = new OleDbConnection(connectionString);
HasConnection = true;
ConnectionEstablished(this, new ConnectionArgs<OleDbConnection>(connection));

Code acting on that change

public async Task ExecuteAsync(string query, Func<IDataReader, Task> onExecute, params DataParameter[] parameters)
{
    using(var command = builder.BuildCommand(query))
    {
        foreach(var parameter in parameters)
            command.AddParameter(parameter);
        if(!connection.IsOpen)
            connection.Open();
        await command.ExecuteAsync(onExecute);
    }
}

private void OnConnectionEstablished(object sender, ConnectionArgs<IConnection> e)
{
    this.connection = e.Connection;
}

This there a way to use the using statement, and still preserve this structure?

Best Answer

The recommended approach is to open a new connection each time with the using statement.

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        // Do work here; connection closed on following line.
    }

The reason to prefer this approach is that it guarantees the connection will be closed after use.

However, this would seem to be non-performant, except that there is a feature of the SqlConnection object called 'Connection Pooling'.

To deploy high-performance applications, you must use connection pooling. When you use the .NET Framework Data Provider for SQL Server, you do not have to enable connection pooling because the provider manages this automatically, although you can modify some settings. For more information, see SQL Server Connection Pooling (ADO.NET).

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection(v=vs.110).aspx

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool.

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

It looks like the ODBCConnection is slightly different, but still has a connection pooling option.

Re:Updated question

So there is the obvious way of just using the connection string from the passed in connection to make 'new' connections with using. But it would probably be best to refactor.

Your current code has a race condition on the open statement and no close is shown.

However, is connection pooling used? not sure. You only call open once, so I think everything will run on the same db connection with no requirement for the pooling code to do anything.

This eliminates the race condition on Open, ensures commands can complete without the underlying connection closing or changing on them and ensures that all connections are closed;

public async Task ExecuteAsync(string query, Func<IDataReader, Task> onExecute, params DataParameter[] parameters)
{
    using(var conn = new SqlConnection(this.connstr))
    using(var command = builder.BuildCommand(query, conn))
    {
        conn.Open();
        foreach(var parameter in parameters) 
        {
            command.AddParameter(parameter);
        }
        await command.ExecuteAsync(onExecute);
    }
}

private void OnConnectionEstablished(object sender, ConnectionArgs<IConnection> e)
{
    this.connstr = e.Connection.ConnectionString ;
}
Related Topic