0

I'm registering a new instance of System.Data.SqlClient.SqlConnection as InstancePerRequest in Autofac, and I .Open() the connection in the registration.
I use it in multiple repository services which implement IDisposable.
I'm not sure whether I need to .Close() the connection or .Dispose() it.
Every source or information says different things and it made me pretty confused.
I would appreciate any clarification on this matter and reliable sources of information.
Thanks1

Askish
  • 79
  • 1
  • 18
  • 2
    Some people might disagree with this, but I don't think `SqlConnection` is a good candidate for dependency injection. `new` it up with a `using` block when you need it; you own it so you dispose it. When you're using EF and you need to go to bare metal, you can use the context's underlying connection object; you don't own that so you shouldn't dispose it. That will get disposed *via EF's internal dependency injection*. But registering `SqlConnection` for your own purposes? No. Register a connection string provider and inject that. – madreflection Mar 15 '23 at 23:11
  • 1
    Bad idea, as others have said. As soon as you have this model, you have to *ensure* that only one possible database operation at a time happens for each request, which rules out many nice modern features around async and/or parallelism. Or even worse, you start introducing locks to try to make sure that those DB operations are synchronized and it rapidly turns nasty. – Damien_The_Unbeliever Mar 16 '23 at 09:35

2 Answers2

1

When using SqlConnection, it's important to manage the connection properly to avoid resource leaks and potential security issues. Here are some recommendations: Use the using statement to ensure that the SqlConnection object is properly disposed of when it's no longer needed.

When using SqlConnection with Autofac, you should register the connection as a per-request instance and open the connection in the registration. You don't need to close the connection explicitly; Autofac will dispose of the object when the request is completed.

Calling Dispose on the SqlConnection object will close the connection if it's still open, so you don't need to call Close explicitly.

Here's an example registration in Autofac:

builder.Register(c => {
    var connectionString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
    var connection = new SqlConnection(connectionString);
    connection.Open();
    return connection;
}).InstancePerRequest();

And here's an example usage in a repository service:

public class MyRepository : IDisposable {
    private readonly SqlConnection _connection;

    public MyRepository(SqlConnection connection) {
        _connection = connection;
    }

    public void Dispose() {
        // No need to explicitly close the connection; it will be disposed of by Autofac
    }
}

For further reading, you can refer to the following resources:

Shivang Patel
  • 26
  • 1
0

SqlConnection leverages the connection pool automatically, and relies on disposal to indicate the connection is no longer used.

The magic happens on the Open() call. It will figure out based on the ConnectionString if it can use a pooled connection from before or not. By always "opening" a "new" connection and disposing of it, you ensure you do not leave anything hanging from other operations on your connection. But if you try to implement connection pooling yourself by keeping the `SqlConnection object and injecting it, you'll run into trouble.

See this question for more information.

To directly answer your question, the code that receives the injected connection should not dispose of it, because it seems like you're intending for it to continue to be used. But you shouldn't use SqlConnection specifically that way. Your approach may be correct for other things.

The using patterns are the easiest ways to use this properly.
With a using statement:

{
    using var connection = new SqlConnection(yourConnectionStringHere);
    connection.Open();
    //Use the connection somehow
    //connection will be disposed when the scope exits in using statement
}

Or with a classic using block:

{
    using (var connection = new SqlConnection(yourConnectionStringHere))
    {
        connection.Open();
        //Use the connection somehow
    }    
}
AvenDonn
  • 11
  • 4