In C# I have this code in a method:
async Task<SqlDataReader> GetSourceData()
{
await using var sourceConnection = new SqlConnection(GetSourceConnectionString());
await sourceConnection.OpenAsync(cancellationToken);
var commandSourceData = new SqlCommand(GetSelectStatement(), sourceConnection);
return await commandSourceData.ExecuteReaderAsync(cancellationToken);
}
Right now the returned reader does not work because the underlying connection is closed. I presume this is because the sourceConnection variable is disposed once I return from my GetSourceData method block.
My question is: If I remove the using statement from the line where sourceConnection is instantiated, and I don't dispose of the returned DataReader manually, will the sourceConnection ever be disposed, and if so, when?
My question is: if I remove the using statement from the line where sourceConnection is instantiated, and I don't dispose of the returned DataReader manually, will the sourceConnection ever be disposed, and if so, when?
You want to read up about finalizers. This is a method called during garbage collection of objects to free any native resources the object owns. But even if the object is eligible for collection, there is no guarantee when the object will be collected, or if it will happen at all.
Simply removing using may not have immediate detrimental effect, but it will likely cause other issues sooner or later, like running out of connections. And issues due to undisposed objects can be difficult to find and fix. So the recommendation is to treat the finalizer as a safety net. A good thing to have, but should not be relied upon.
A better solutions would be to return the data and not the reader. Another possibility is to refactor the code to ensure the connection lifetime exceeds that of the reader, for example by changing your method to a class that disposes both the reader and connection when done. You should also ensure the SQLCommand, and any other object implementing IDisposable is disposed. With the possible exception of Task.