The recommended approach is to open a new connection each time with the
using (SqlConnection connection = new SqlConnection(connectionString))
// 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).
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.
It looks like the ODBCConnection is slightly different, but still has a connection pooling option.
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))
foreach(var parameter in parameters)
private void OnConnectionEstablished(object sender, ConnectionArgs<IConnection> e)
this.connstr = e.Connection.ConnectionString ;