CODEWITHSUNDEEP
c#repository-patterndappersqlconnectionn-tier-architecture
Blake Rivell
Blake Rivell

Reputation: 13875

Difference between a 'using statement' for every SqlConnection and single SqlConnection as private property in C# data layer

When implementing the repository pattern using Dapper ORM I am currently doing the following:

private readonly ConnectionStrings _connectionStrings;
private IDbConnection _db;

public CustomerRepository(IOptions<ConnectionStrings> connectionStrings)
{
    _connectionStrings = connectionStrings.Value;
    _db = new SqlConnection(_connectionStrings.DefaultConnection);
}

public Customer Find(int id)
{
    return this._db.Query<Customer>("SELECT * FROM Contacts WHERE Id = @Id", new { id }).SingleOrDefault();
}

Can someone please tell me if I should be doing it this way or if I should be using a using statement with a new SqlConnection in every single repository function.

I am assuming my above code will need something like UnitOfWork to be effective right? And also some way of disposing the connection when done running all of the repository functions needed.

Upvotes: 0

Views: 793

Answers (2)

Jon Hanna
Jon Hanna

Reputation: 113322

In general when a type implements IDisposable (and hence works with using) it can sometimes be useful to wrap it in another type, having that other type also implement IDisposable and have its Dispose() call the wrapped object, and then use using (or another mechanism to call Dispose()) on it.

The question is whether this is one of those sometimes.

It's not. In particular note that SqlConnection implements pooling behind the scenes, so (unless you explicitly opt-out in your connection string) instead of Dispose() shutting down the entire connection to the server what actually happens is that an object internal to the assembly of SqlConnection that handles the details of the connection is put into a pool to use again the next time an SqlConnection with the same connection string is opened.

This means that your application will get as efficient use of as few connections as possible over many uses of the SqlConnection class. But you are stymying that by keeping the connections out of the pool by not returning to the pool as promptly as possible.

Upvotes: 1

steliosbl
steliosbl

Reputation: 8921

The recommended approach is to use using statements. User paulwhit explained their usage great in this answer:

The reason for the "using" statement is to ensure that the object is disposed as soon as it goes out of scope, and it doesn't require explicit code to ensure that this happens.

The essential difference between having using statements in your methods and having the connection be a class member is that the using statement makes sure that once you're done with your operations, and have exited the block, your connection is closed and disposed of properly. This removes any possibility of error on the part of the developer, and generally makes everything neater.

An important additional benefit of the using statement in this situation is that it ensures the connection is disposed of, even if there is an exception (though it is worth noting that this isn't strictly the only way to achieve this). According to the documentation:

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object.

If you were to have the connection be a class member, then an unhandled exception in the middle of a method that caused your program to exit early would possibly leave the connection open. This is of course not a good thing.

So to sum up, unless you have a very good reason not to, go with the using statement.

Upvotes: 1

Related Questions