C# database connection use of USING or not?

Question

I tried to find my answer on google as always but I found only a partial answer to my question.

My question is: while creating a connection to a database, what is the more preferable way to make it: without or with the USING.

For now, as I start in C# (but I have some skill in PHP), I learned online with the USING method like below:

string conString = Properties.Settings.Default.mattDatabaseConnectionString;

using (SqlCeConnection con = new SqlCeConnection(conString))
{
    using (SqlCeCommand query = new SqlCeCommand("SELECT * FROM customers", con))
    {
        SqlCeDataReader reader = query.ExecuteReader();
    }
}

On google, I found that the use of the USING prevent from forget to close the ExecuteReader and dispose the SqlCeCommand. But you can also make it without like below (example took from msdn):

SqlCeConnection conn = null;
        SqlCeCommand cmd = null;
        SqlCeDataReader rdr = null;
        try
        {
            // Open the connection and create a SQL command
            //
            conn = new SqlCeConnection("Data Source = AdventureWorks.sdf");
            conn.Open();

            cmd = new SqlCeCommand("SELECT * FROM DimEmployee", conn);

            rdr = cmd.ExecuteReader();

            // Iterate through the results
            //
            while (rdr.Read())
            {
                int employeeID = rdr.GetInt32(0);   // or: rdr["EmployeeKey"];
                string lastName = rdr.GetString(5); // or: rdr["FirstName"];
            }

            // Always dispose data readers and commands as soon as practicable
            //
            rdr.Close();
            cmd.Dispose();
        }
        finally
        {
            // Close the connection when no longer needed
            //
            conn.Close();
        }

I know the 2 methods above are equivalent but whats is the best method for skilled programmers and why. Probably the second for less characters??

And is the SqlCeEonnection conn = null; and the 2 others really necessary??

Thanks guys

Answer 1

using is the cleanest way to make sure your connection is disposed, and I like to write nested using statements in this way:

using (SqlCeConnection con = new SqlCeConnection(conString))
using (SqlCeCommand query = new SqlCeCommand("SELECT * FROM customers", con))
{
    con.Open();
    using(SqlCeDataReader reader = query.ExecuteReader())
    {
        //...
    }
}

The try-catch is equivalent as you mentioned.

The only exception, of course, is when you don't want to dispose the connection right away. For instance, you might need to use the data reader outside the using block, in which case you shouldn't use a using because it will dispose the connection when you exit it, and the data reader will throw an exception when you try to use it while the underlying connection is closed.