C# using then exception / Open Database

Question

Hello,

    using (SqlConnection con = new SqlConnection(strConnectionString))
    {
     foreach (var position in ListTraceData.AllPositions)
     {
         try {
                z= position.SomethingToDo;
         }
         catch (Exception ex)
         {
            WriteLog("Error writing to database: " + ex.Message);
            throw new MyException("Error from OPC", Hm.Null, ex);
         }        
     }        
    }

If an exception occurs within the using statement, will it still close correctly?

Is it better to leave a database always open or better to open it when necessary?

Answer 1

"If an exception occurs within the using statement, will it still close correctly?"

Yes that is the entire point of the using statement. It is guaranteed to dispose (close in the case of the db connection) the object when the statement ends whether an exception occurs or not.

"Is it better to leave a database always open or better to open it when necessary?"

Open the connection, use it and then close it. That is the best for performant code. Leaving a connection open wastes a dedicated connection to the DB and can limit the ability of an app to scale. It also is critical for error handling as some errors (such as network drops) will break the connection. Any attempt to reuse the connection will fail. Opening the connection each time you need it will properly recover from these errors as the runtime will detect the bad connection and create a new one. The runtime pools connections so you don't have the overhead of a database connection each time you call open.

Answer 2

You can see at : SqlException Class

"The SqlConnection remains open when the severity level is 19 or less.
When the severity level is 20 or greater, the server ordinarily closes the SqlConnection. However, the user can reopen the connection and continue.
In both cases, a SqlException is generated by the method executing the command.
"

Answer 3

According to documentation, “using statement ensures that Dispose (or DisposeAsync) is called even if an exception occurs within the using block”. Therefore, con.Dispose() will be called automatically in both of normal and exceptional cases.