none
Is this code correctly closing/disposing connections? RRS feed

  • Question

  • I'm getting the following error during the day sometimes: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    Everything I have read doesn't seem to answer why I'm getting the above error.  2 reasons I found it could happen 1 the query takes too long to happen. I'm 99.9% sure this isn't the case. 2 uncommitted transaction.  maybe this is the reason, but I don't quite understand it.  I'm not using a stored procedure, it is SQLCommand, so maybe there is a commit that I'm not doing in the below string?

    Or, maybe there is a 3rd reason.  I have researched it a lot, and I think the problem is because I'm not closing out my connections, and I'm running out of Application Pool connections.  I have no idea how or where I can check this.

    I have written my best version of an insert query, and just want to know if it is doing everything correctly so I can pretty much copy it through the rest of my code. Should I close the conn or not? Different people have different answers on the web.  Because it is in a using? Should/can I close out the cmd?

    Here is my code:

    protected void insertnotes(string comment)
            {
                string query = "Insert Into [TblCSA_Journal] (incidentid,logtext, sysmoduser,sysmodtime) Select @incidentid,@logtext, @sysmoduser,@sysmodtime";
                string connect = ConfigurationManager.ConnectionStrings["QCModuleConnectionString1"].ToString();          
                using (SqlConnection conn = new SqlConnection(connect))
                {
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("incidentid", HFCSAID.Value);
                        cmd.Parameters.AddWithValue("logtext", comment);
                        cmd.Parameters.AddWithValue("sysmoduser", oUser.NTID);
                        cmd.Parameters.AddWithValue("sysmodtime", getglenviewtime().ToString());
    
                        cmd.CommandTimeout = 90;
                        try
                        {
                            conn.Open();
                            cmd.ExecuteScalar();
                        }
                        catch (Exception ex)
                        {
                            String catchex = ex.ToString();
                            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
                            oErrorEmail.Application_Error(catchex, oUser.UserEmail, Request.Path, method);
                            Response.Write(ex.Message);
                        }
                        finally
                        {
                            conn.Close();
                            conn.Dispose();                       
                        }
                    }                
                }


    • Edited by GregGohl Thursday, July 11, 2019 8:42 PM
    Thursday, July 11, 2019 8:39 PM

Answers

  • Hi GregGohl,

    Thank you for posting here.

    Based on my test, I could not reproduce your problem.

    >>I'm getting the following error during the day sometimes:

    I think that sometimes the using statment will conflict with the dispose method. Therefore, I suggest that you could try the following code.

                string con = @"";
                SqlConnection connection = new SqlConnection(con);
    
                string query = "insert into Student(Id,Name,Age) select @Id,@Name,@Age";
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@Id",1003);
                    cmd.Parameters.AddWithValue("@Name", "test5");
                    cmd.Parameters.AddWithValue("@Age", 28);
                    cmd.CommandTimeout = 90;
                    try
                    {
                        connection.Open();
                        cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                    finally
                    {
                        connection.Close();
                        connection.Dispose();
                    }
                }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 12, 2019 3:07 AM
    Moderator

All replies

  • No, you're not doing it correctly. The 'using statment'  will close and dispose of the connection,  if there is an exception thrown too.

    And also, you should be using global exception handling, which allows no try/catch to be used anywhere in any code, and the GEH logs the error and redirects to a user friendly error page.

    You want to send an email, then you do it at the GEH.

    https://stackify.com/csharp-catch-all-exceptions/

    https://www.c-sharpcorner.com/UploadFile/db2972/error-logging-using-log4net-in-web-application/

    The 'using' statment disposes of objects within its scope.


    • Edited by DA924x Friday, July 12, 2019 12:37 AM
    Thursday, July 11, 2019 10:58 PM
  • Hi GregGohl,

    Thank you for posting here.

    Based on my test, I could not reproduce your problem.

    >>I'm getting the following error during the day sometimes:

    I think that sometimes the using statment will conflict with the dispose method. Therefore, I suggest that you could try the following code.

                string con = @"";
                SqlConnection connection = new SqlConnection(con);
    
                string query = "insert into Student(Id,Name,Age) select @Id,@Name,@Age";
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@Id",1003);
                    cmd.Parameters.AddWithValue("@Name", "test5");
                    cmd.Parameters.AddWithValue("@Age", 28);
                    cmd.CommandTimeout = 90;
                    try
                    {
                        connection.Open();
                        cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                    finally
                    {
                        connection.Close();
                        connection.Dispose();
                    }
                }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 12, 2019 3:07 AM
    Moderator
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer. So that it will help other members to find the solution quickly if they face the similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 30, 2019 9:01 AM
    Moderator