none
An Exception in the Delegate which is called in Using Clause with a SQL Command RRS feed

  • Question

  • I am defining a generatic method for the Database calls execution and that in turn calls the Delegate (which decide to execute as Scalar,NonQuery,XMLreader) etc.

    If the ActualFunction Delegate code thrown an exception, from the debugger i am not able to observe the Database Connection Close. So my questions

    1.  if we get the exception in the delegate, will be Database Connection Opened in the ExecCommand<T> method will be closed sucessfully?

    2.  How to debug the Connection object status in these kind of situations

    Please help me on this.

    public static T ExecCommand<T>(string ProcedureName, SqlParameter[] Parameters, Func<SqlCommand, T>
    ActionFunction)

    {

    T returnValue = default ;

    using (SqlConnection conn = new SqlConnection(Helper.GetDatabaseConnection().ConnectionString))

    {

    conn.Open();

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = conn;

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.CommandText = ProcedureName;

    cmd.Parameters.AddRange(Parameters);

    returnValue = ActionFunction(cmd);

    conn.Close();

    }

    return returnValue;

    }

    public static Func<SqlCommand, XmlDocument> XMLExecFunction = delegate(SqlCommand cmd)

    {

    XmlDocument xdoc = new XmlDocument();

    var reader = cmd.ExecuteXmlReader();

    using (reader)

    {

    xdoc.Load(reader);

    }

    return xdoc;

    };



    pvdp



    Tuesday, August 28, 2012 10:45 AM

Answers

  • Hi Durga,

    Welcome to the MSDN Forum.

    Cannot this code catch the exception:

    public static Func<SqlCommand, XmlDocument> XMLExecFunction = delegate(SqlCommand cmd)
    
    {
    
    XmlDocument xdoc = new XmlDocument();
    
    try
    {
    var reader = cmd.ExecuteXmlReader();
    
    using (reader)
    
    {
    
    xdoc.Load(reader);
    
    }
    }
    Catch (Exception exx)
    {
       Console.WriteLine(cmd.Connection.State);
    }
    
    
    return xdoc;
    
    };

    >>1.  if we get the exception in the delegate, will be Database Connection Opened in the ExecCommand<T> method will be closed sucessfully?

    I think it will be. If not, the connection should have been closed somewhere.

    >>2.  How to debug the Connection object status in these kind of situations

    Is the code above you expected?

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 29, 2012 10:30 AM
    Moderator
  • Hi Durga Prasad,

    Yes. The connection object definitely gets closed if the delegate throws an exception. That is because of 'using' block. Your code is internally converted to,

    SqlConnection conn = null;
    try
    { 
       conn = new SqlConnection(Helper.GetDatabaseConnection().ConnectionString);
       conn.Open(); 
       SqlCommand cmd = new SqlCommand(); 
       cmd.Connection = conn; 
       cmd.CommandType = CommandType.StoredProcedure; 
       cmd.CommandText = ProcedureName; 
       cmd.Parameters.AddRange(Parameters); 
       returnValue = ActionFunction(cmd); 
       conn.Close();   
    }
    finally
    {
       if(conn != null)
       {
          conn.Close();
       }
    }

    So, see that connection object gets closed automatically inside finally. Alo you might be knowing that finally block is guaranteed to be executed in any case (even when there is an exception in try block), your connection object is guaranteed to be closed. Also you don't have to call conn.Close manually inside using block.

    I hope this helps.


    Please mark this post as answer if it solved your problem. Happy Programming!

    Monday, September 3, 2012 2:56 AM

All replies

  • Hi Durga,

    Welcome to the MSDN Forum.

    Cannot this code catch the exception:

    public static Func<SqlCommand, XmlDocument> XMLExecFunction = delegate(SqlCommand cmd)
    
    {
    
    XmlDocument xdoc = new XmlDocument();
    
    try
    {
    var reader = cmd.ExecuteXmlReader();
    
    using (reader)
    
    {
    
    xdoc.Load(reader);
    
    }
    }
    Catch (Exception exx)
    {
       Console.WriteLine(cmd.Connection.State);
    }
    
    
    return xdoc;
    
    };

    >>1.  if we get the exception in the delegate, will be Database Connection Opened in the ExecCommand<T> method will be closed sucessfully?

    I think it will be. If not, the connection should have been closed somewhere.

    >>2.  How to debug the Connection object status in these kind of situations

    Is the code above you expected?

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 29, 2012 10:30 AM
    Moderator
  • Hi Durga Prasad,

    Yes. The connection object definitely gets closed if the delegate throws an exception. That is because of 'using' block. Your code is internally converted to,

    SqlConnection conn = null;
    try
    { 
       conn = new SqlConnection(Helper.GetDatabaseConnection().ConnectionString);
       conn.Open(); 
       SqlCommand cmd = new SqlCommand(); 
       cmd.Connection = conn; 
       cmd.CommandType = CommandType.StoredProcedure; 
       cmd.CommandText = ProcedureName; 
       cmd.Parameters.AddRange(Parameters); 
       returnValue = ActionFunction(cmd); 
       conn.Close();   
    }
    finally
    {
       if(conn != null)
       {
          conn.Close();
       }
    }

    So, see that connection object gets closed automatically inside finally. Alo you might be knowing that finally block is guaranteed to be executed in any case (even when there is an exception in try block), your connection object is guaranteed to be closed. Also you don't have to call conn.Close manually inside using block.

    I hope this helps.


    Please mark this post as answer if it solved your problem. Happy Programming!

    Monday, September 3, 2012 2:56 AM