Which one of these samples it the correct way to close the connection using Command Behavior?
A.
SqlDataReader rdr = new SqlDataReader();
string sql = @”sql statement”;
SqlConnection conn = connection.GetConnection();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine(“{0}”, rdr);
B.
SqlDataReader rdr = new SqlDataReader();
string sql = @”sql statement”;
SqlConnection conn = connection.GetConnection();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
rdr.Close();
Console.WriteLine(“{0}”, rdr);
C.
SqlDataReader rdr = new SqlDataReader();
string sql = @”sql statement”;
SqlConnection conn = connection.GetConnection();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
conn.Close();
Console.WriteLine(“{0}”, rdr);
D.
using (SqlDataReader rdr = new SqlDataReader())
{
string sql = @”sql statement”;
SqlConnection conn = connection.GetConnection();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine(“{0}”, rdr);
}
Explanation:
An alternative to explicitly closing the Connection is to pass CommandBehavior.CloseConnection to the ExecuteReader method to ensure
that the associated connection is closed when the DataReader is closed. This is especially useful if you are returning a DataReader
from a method and do not have control over the closing of the DataReader or associated connection.
When you close the data reader and you use CommandBehavior.CloseConnection – the SQL connection also closes.Best Practices of using ADO.NET
(http://msdn.microsoft.com/en-us/library/ms971481.aspx )
Wouldn’t “Console.WriteLine(“{0}”, rdr);” on the last line after the reader has been closed be an error?
All of the answers declare rdr twice. And B is definitely not the right answer.
B is definitely not the answer.
rdr should not be declared twice
D is the right answer because the reader rdr will be closed after exiting the using block, and cause the connection to be closed via the command behavior
As both @Anderson and @E suggest, rdr is incorrectly defined in all the answers, and calling Console.WriteLine() with rdr as a parameter after it is closed is also a bug. However, the question asks about using the CommandBehavior with regard to the reader, and answer B is the only one that uses this pattern somewhat correctly. The concept being tested is that calling Close()/Dispose() on a reader that was opened with CommandBehavior.CloseConnection will cause the underlying connection to be closed. Technically, though, the ExecuteReader call should be either inside a using block, or any code that uses rdr should be in a try/finally block with a call to Close()/Dispose() inside the finally.
Another clusterf*ck of a question from Microsoft. I agree with @Gaius, all answers are wrong, but answer B is the closest to what they’re trying to test here. Check out the following MSDN article for the pattern being used. Bottom line is you want to close the reader (rdr.Close()), which will end up closing the underlying connection, because of the CommandBehavior.CloseConnection argument. Only answer B does that:
http://msdn.microsoft.com/en-us/library/aa326246%28v=vs.71%29.aspx
[C#]
public void CreateMySqlDataReader(string mySelectQuery,string myConnectionString)
{
SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
//Implicitly closes the connection because CommandBehavior.CloseConnection was specified.
}