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?

Hi @garethb I tried `using (var reader = command.ExecuteReader()) {...}`, but this CA2000 still complained.. — Yue Wang, Aug 15 '16 at 04:55
Answers are correct that it can be fixed by avoiding using the initializer - but why are you using one anyway? There's a specific constructor for `SqlCommand` that accepts the text of the command and an SqlConnection object. — Damien_The_Unbeliever, Aug 15 '16 at 06:08

score 3 · Answer 1 · answered Aug 15 '16 at 05:16

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();
}

Yogi · Accepted Answer · 2016-08-15T06:43:51.167

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))
 {
  ...
 }

How to fix this CA2000 for SqlCommand?

2 Answers2