Database connection closure location in try catch/using

Question

Which is better to make sure that the db connection is closed if the execution fails?

try
{
    using (var mySqlConnection = new MySqlConnection(DatabaseManagement.DatabaseConnectionString))
    {
        DatabaseManagement.DatabaseCommand = mySqlConnection.CreateCommand();
        DatabaseManagement.DatabaseCommand.CommandText = "INSERT INTO  lastlaptimes Values ('" + UserObject.UserName + "','" + _lastLapTime + "')";
        mySqlConnection.Open();
        DatabaseManagement.DatabaseCommand.ExecuteNonQuery();
    }
}
catch (MySqlException exception)
{
    Logger.Error(exception);
}

Or this:

using (var mySqlConnection = new MySqlConnection(DatabaseManagement.DatabaseConnectionString))
{
    try
    {
        DatabaseManagement.DatabaseCommand = mySqlConnection.CreateCommand();
        DatabaseManagement.DatabaseCommand.CommandText = "INSERT INTO  lastlaptimes Values ('" + UserObject.UserName + "','" + _lastLapTime + "')";

        mySqlConnection.Open();
        DatabaseManagement.DatabaseCommand.ExecuteNonQuery();
    }
    catch (MySqlException exception)
    {
        mySqlConnection.Close();
        Logger.Error(exception);
    }
}

I'm having issue with too many connections against the db, and I'm wondering if my first approach is leading to the problem with the connections, as the code is called numerous times and a new connection is opened and fails again and increasing the connections by 1.

Thanks for any help.

I'd be worried that exceptions were being swallowed, when someone failed to get their injection attack correct. — Tony Hopkinson, May 04 '14 at 20:25
@TonyHopkinson you right, I misunderstood his question. At least you manage the exception. But it depends if you want to write more or less code. Use SQL parametrized to avoid injection. — , May 04 '14 at 20:37
Thanks for the comments about SQL injection. I'd forgot about it in this statement :) — Blackvault, May 04 '14 at 21:25

score 0 · Accepted Answer · edited May 23 '17 at 12:18

The only difference between these is whether or not you are explicitly calling the Close statement (both have a using statement, which runs Dispose automatically).

So the real question here is - does Dispose close the connection or do you need to call it explicitly? I believe the answer is that Dispose will call this for you (see this question). What that means is either works well - pick whichever you favor (I suppose the first is technically one line less code...)

Database connection closure location in try catch/using

1 Answers1