none
Best Practices For SqlConnection Object In Recursive Method RRS feed

  • Question

  • I have a method that can recursively call itself that uses a SqlConnection object.  I'm wondering what would be considered best practice for closing this connection:

    Using a local variable for the connection as I would in a non-recursive method (and closing the connection in the "Finally" block at the end of the method, which leaves the possibility of "nested" open connections while the recursion is occurring, although they will all eventually get closed).

    Or

    Using a class level variable for the connection, opening the connection at the beginning of the original call to the method, leaving it open during the recursion, and closing it after all the recursive calls (as well as the original call) have run their course

    Or

    Something else that I haven't thought of...

    Thanks,

    CF
    Thursday, August 6, 2009 1:02 AM

Answers

  • If you are performing a lot of database operations in the same thread during a short interval of time, you should use the same SqlConnection object.  Of course, this is a must if an SqlTransaction is also involved.

    > Using a class level variable for the connection

    Fine if done in a thread-safe way (or if objects of your class cannot be used simultaneously by multiple threads).

    > Something else that I haven't thought of...

    Here is the way that jumped to my mind:

        public class Test
        {
            public int ComputeFactorial(int x)
            {
                using (SqlConnection conn = new SqlConnection("connstring"))
                {
                    return ComputeFactorialInternal(conn, x);
                }
            }
    
            private int ComputeFactorialInternal(SqlConnection conn, int x)
            {
                if (x < 0)
                    throw new ArgumentOutOfRangeException("x");
                if (x == 0)
                    return 1;
    
                // Code to access database here, perhaps.
    
                int ret =  x * ComputeFactorialInternal(conn, x - 1);
    
                // Code to access database here, perhaps.
    
                return ret;
            }
        }
    • Marked as answer by Chicken Foot Thursday, August 6, 2009 7:32 PM
    Thursday, August 6, 2009 1:14 AM
  • The question is a little difficult to answer since we don't know the nature of the recursion, but I would simply open and close connections as needed. For example, when you make a recursive call does the connection need to remain open or can you close it before making the call?

    Remember you're working with a connection pool by default so the overhead of repeatedly opening and closing connections should be minimal.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Chicken Foot Thursday, August 6, 2009 7:32 PM
    Thursday, August 6, 2009 1:26 PM

All replies

  • If you are performing a lot of database operations in the same thread during a short interval of time, you should use the same SqlConnection object.  Of course, this is a must if an SqlTransaction is also involved.

    > Using a class level variable for the connection

    Fine if done in a thread-safe way (or if objects of your class cannot be used simultaneously by multiple threads).

    > Something else that I haven't thought of...

    Here is the way that jumped to my mind:

        public class Test
        {
            public int ComputeFactorial(int x)
            {
                using (SqlConnection conn = new SqlConnection("connstring"))
                {
                    return ComputeFactorialInternal(conn, x);
                }
            }
    
            private int ComputeFactorialInternal(SqlConnection conn, int x)
            {
                if (x < 0)
                    throw new ArgumentOutOfRangeException("x");
                if (x == 0)
                    return 1;
    
                // Code to access database here, perhaps.
    
                int ret =  x * ComputeFactorialInternal(conn, x - 1);
    
                // Code to access database here, perhaps.
    
                return ret;
            }
        }
    • Marked as answer by Chicken Foot Thursday, August 6, 2009 7:32 PM
    Thursday, August 6, 2009 1:14 AM
  • The question is a little difficult to answer since we don't know the nature of the recursion, but I would simply open and close connections as needed. For example, when you make a recursive call does the connection need to remain open or can you close it before making the call?

    Remember you're working with a connection pool by default so the overhead of repeatedly opening and closing connections should be minimal.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Chicken Foot Thursday, August 6, 2009 7:32 PM
    Thursday, August 6, 2009 1:26 PM