How to fix this CA2000 for SqlCommand?

Question

I'm trying to execute a SQL query like following, but Visual Studio complains a CA2000.

public static IDictionary<string, string> TemplateDirectories(string connectionString) {
    var directories = new Dictionary<string, string>();
    using (var connection = new SqlConnection(connectionString)) {
        connection.Open();
        using (var command = new SqlCommand { Connection = connection, CommandText = "select * from PATHS" }) {
            var reader = command.ExecuteReader();
            while (reader.Read())
                directories[reader["CODE"].ToString()] = reader["PATH"].ToString();
                reader.Close();
        }
    }
    return directories;
}

Error CA2000 ...object 'new SqlCommand()' is not disposed along all exception paths. Call System.IDisposable.Dispose on object 'new SqlCommand()' before all references to it are out of scope.

I tried several ways to fix it, but no one worked. So how to fix?

Answer 1

This is because of a pitfall when using object initializers.

The way SqlCommand has been initialized, if there occurs some exception while initializing the object, the SqlCommand will be left un disposed.

So what is the solution. Declare the object in old fashioned way, to get rid of the warning-

using (var command = new SqlCommand())
{
    command.Connection = connection;
    command.CommandText="select * from PATHS";
    ...
}

I found a very good article around this, which gives more details and approaches to resolve the issue - http://haacked.com/archive/2013/01/11/hidden-pitfalls-with-object-initializers.aspx/

Having said that, for this perticular issue, it would be better to use constructor of SqlCommand and pass the command text and connection object like this (courtesy: Damien_The_Unbeliever's comment)

 string commandText = "select * from PATHS";
 using (var command = new SqlCommand(commandText, connection))
 {
  ...
 }

Answer 2

Try assigning the command parameters explicitly:

using (var command = new SqlCommand())
{
    command.Connection = connection;
    command.CommandText="select * from PATHS";
    var reader = command.ExecuteReader();
    while (reader.Read())
        directories[reader["CODE"].ToString()] = reader["PATH"].ToString();
    reader.Close();
}