Using ExecuteScalar() results in a call to ExecuteReader() with a "default" CommandBehaviour?

Question

Stack trace shows that ExecuteReader is the underlying method for ExecuteScalar.While digging for associated commandbehaviour, i found the post below - stating that the commandbehaviour.default is used by the reader.

Does ExecuteScalar() have any advantages over ExecuteReader()?

I'm aware that a connection always can be closed and disposed explicitly using datareader.close(), datareader.dispose() -- provided, ExecuteReader(commandbehaviour.Closeconnection) is used. ExecuteReader(CommandBehaviour.Default) on the other hand keeps the underlying connection open even when the datareader object is closed and/or disposed. So my question is, if executescalar uses Default behaviour, how do i ensure that the underlying connection is closed once the scalar value is retrieved?

Answer 1

The C# using statement is one of the best things included in the language. It doesn't directly solve your problem, but if you make a standard habit of using it to wrap SQLConnections (and any other type that implements IDisposable) you won't have to worry about a lot of this sort of thing...

using(SQLConnection connection = new SQLConnection(...))
{
}