CRITICAL
Rule Definition
To avoid SQL connection leakage, it is highly recommended to close the SQL connection as soon as you are done using it, preferably within the Method that opened it.
Remediation
Close the SQL connection within the same Method
Violation Code Sample
//no close()/dispose() operation: //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ private static void Read_KO_1(){ SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand(queryString,conn); command.ExecuteNonQuery(); //VIOLATION - } //No Exception Handling - close/dispose is there but not surrounded with try catch : //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ private static void Read_KO_2(){ SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand(queryString,conn); command.ExecuteNonQuery(); conn.Close(); // VIOLATION }
Fixed Code Sample
//call close()/dispose() on connection in finally block: //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ private static void Read_OK_1(){ SqlConnection conn = new SqlConnection(connString); try{ conn.Open(); SqlCommand cmd = new SqlCommand(queryString,conn); command.ExecuteNonQuery(); } catch{ } finally{ if (conn != null) { conn.Close(); } // FIX : closed in finally block } } //using block : //~~~~~~~~~~~~~~~ private static void Read_OK_2(){ using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(queryString,conn); command.ExecuteNonQuery(); // FIX : connection closed on following line. } }
Reference
CISQ rules: ASCPEM-PRF-15, ASCRM-CWE-772.
Related Technologies
.Net
Technical Criterion
Efficiency - Memory, Network and Disk Space Management
About CAST Appmarq
CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.