C# SQL Connection, Command, and Using statements - explicitly close and dispose, or not?

Question

I have read conflicting info regarding explicitly closing and / or disposing of IDisposable objects when they are used with Using clauses.

From what I understand:

using (x) { .... }

when compiled is rewritten as:

try { .... } finally { if (x != null) x.Dispose(); }

Which means Dispose is called right away at the end of the using block, right?

Some people recommend explicitly calling close and / or dispose even when using a Using clause, as there may be some delay to wait for the Finally block to execute?

Others say that calling Dispose is always redundant in a Using block. I tend to agree, but am looking for a definitive answer.

And does Garbage Collection (GC) only come into play if Using clauses are not used AND you are NOT explicitly closing and disposing?

Take the following method for example (see comments).

public Musician GetMusician(int recordId)
{
    Musician objMusician = null;

    using(SqlConnection con = new SqlConnection(_connectionString))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandText = "selectMusician";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", recordId);

            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    objMusician = new Musician((int) reader["id"]);
                    objMusician.Name = (string) reader["name"];
                }

                if objMusician != null)
                {
                    objMusician.Albums = Albums.GetAlbums((int)objMusician.ID);
                    objMusician.Tours = Tours.GetTours((int)objMusician.ID);
                    objMusician.Interviews = Interviews.GetInterviews((int)objMusician.ID);
                }

                // do these two lines close and dispose of the reader any faster?
                reader.Close();
                reader.Dispose();
            }

            // does this line dispose of the command any faster?
            cmd.Dispose();
        }
        // do these two lines close and dispose of the connection any faster?
        con.Close();
        con.Dispose();

        return objMusician;
    }
}

After the using-block the SqlCommand will be disposed but yet GC will collect it. — diiN__________
– diiN__________, Commented Mar 3, 2016 at 7:08
Unless you explicitly add the delay, finally-blocks are executed directly when the program comes to them. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Mar 3, 2016 at 7:29

progpow · Accepted Answer · 2016-03-03 07:17:17Z

You right described Dispose method called at the end of block using. https://msdn.microsoft.com/en-us/library/yh598w02.aspx

GC call Finalize() method (not Dispose() directly) of custom object at random time. https://msdn.microsoft.com/en-us/library/system.object.finalize.aspx

Usually Close method includes in Dispose method and its not needed call both, you must read docs for concrete class.

At your case i would change code to

 public Musician GetMusician(int recordId)
 {
     Musician objMusician = null;


     using(SqlConnection con = new SqlConnection(_connectionString))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "selectMusician";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@id", recordId);

                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        objMusician = new Musician((int) reader["id"]);
                        objMusician.Name = (string) reader["name"];
                    }

                    if objMusician != null)
                    {
                        objMusician.Albums = Albums.GetAlbums((int)objMusician.ID);
                        objMusician.Tours = Tours.GetTours((int)objMusician.ID);
                        objMusician.Interviews = Interviews.GetInterviews((int)objMusician.ID);
                    }
                }
            }
        }    
    return objMusician;
}

Collectives™ on Stack Overflow

C# SQL Connection, Command, and Using statements - explicitly close and dispose, or not?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related