Which of these methods is better for closing SqlDataReader:
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
reader.Dispose();
or
SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
}
or there are another further closing methods?
The correct way of handling this is the using statement:
using(SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read())
{
}
}
This way the object gets disposed correctly (and you don't need to call Close()).
A using statement is the best practice in such situations from my experience. It makes sure the connection is properly closed even if an exception happens somewhere inside.
using (SqlDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
//Do stuff...
}
}
Of course you could do the same with a try { } finally { }, which is what the using statement does internally. I found it's generally a good idea to get in the habit of always handling readers via the using statement to avoid the possibility of leaked connections.
Use first scenario if you work with reader within one method and second one if you pass reader as return value (not within one scope).
the doc you need is this one: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.close.aspx
To quote: "You must explicitly call the Close method when you are through using the SqlDataReader to use the associated SqlConnection for any other purpose."
