How to close SqlConnection via DataReader. 如何通过DataReader关闭SqlConnection

It is a common issue of how to handle SqlConnection gracefully, when implementing some general helper methods like

SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText);

This method is supposed to create a SqlConnection, then execute the command and return the DataReader.

But when will the SqlConnection be closed? The SqlConnection definitely can not be closed in the same method, otherwise, the returned DataReader will be useless. Ok, then what will happen if we don't close the SqlConnection inside the method? Yes, We got the handy, "live" DataReader instance, from which we get the data we need. Then we could safely close the DataReader itself. How about the SqlConnection? it is not occupied any more, but it is still there, we cannot reach it, as it is already out of scope.

Have you ever heard noticed SqlCommond has an overload method: SqlDataReader ExecuteReader(CommandBehavior behavior) ? Just passing in the magic CommandBehavior.CloseConnection in this method, it guarantees the SqlConnection will get closed when the DataReader is closed.

--------------------------------------

SqlConnection connection = new SqlConnection("myconnectionstringhere");
SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{  ...    }

reader.Close(); //or reader.Dispose();