none
Using the "Using" statement with the Entity Framework - EntityConnection use required to prevent open DB connections RRS feed

  • Question

  • I have read first-hand accounts from other developers that the method below "GetUserEmailById" can leave a database connection open if an exception is thrown inside of the using statement.  Instead, a variation on the second method was suggested as the best practice (BestPractice_GetUserEmailById).

    Are there any experts who have experience with this?  Is it true that the first method could result in an open database connection?  Here is also an example of issues with the Using statement that seems related.

    I am using SQL Azure, but I've read this is a property of the Using statement, and not the underlying type.  Thank you.

     

     

            private static string GetUserEmailById(int userId)
            {
                using (MySqlAzureDB db = new MySqlAzureDB())
                {
                    return db.Users.Where(u=> u.Id == userId).Select(u => u.Email).FirstOrDefault();
                } // this could leave a connection to the DB open!
            }
            private static string BestPractice_GetUserEmailById(int userId)
            {
                // This is required if we want to be absolutely sure there is never a connection left open
                using (EntityConnection ec = new EntityConnection("Name=MySqlAzureDB"))
                {
                    ec.Open();
                    try
                    {
                        using (MySqlAzureDB db = new MySqlAzureDB())
                        {
                            // This returns the email address of the user
                            return db.Users.Where(u => u.Id == userId).Select(u => u.Email).FirstOrDefault();
                        } 
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        ec.Dispose();
                    }
                }
            }
    


     


    • Edited by OceanMorning Wednesday, January 18, 2012 5:27 PM
    Wednesday, January 18, 2012 3:16 AM

Answers

  • Hi Matt,

    Welcome!

    To tell you truth, I'm not familiar with SQL Azure. But When you compile this code,the compiler automatically emits the try and finally blocks. Inside the finally block,the compiler emits code to cast the object to an IDisposable and calls the Dispose method. The second method(BestPractice_GetUserEmailById) is just call the dispose method explicitly. In fact there is Connection Pool in SQL Azure: http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx at the end of the link post.

    -----------------------------------

    We strongly recommend that you use the connection pooling and always close the connection when you are finished using it so that the unused connection will be returned to the pool. For more information about connection pooling, see Connection Pooling.

    -----------------------------------

    The connection pooling is here:

    -----------------------------------

    Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application callsClose on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call. http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

    -----------------------------------

    Close method is same with Dispose method.

    Based on my knowledge, I think they are same.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 19, 2012 7:30 AM
    Moderator

All replies

  • Hi Matt,

    Welcome!

    To tell you truth, I'm not familiar with SQL Azure. But When you compile this code,the compiler automatically emits the try and finally blocks. Inside the finally block,the compiler emits code to cast the object to an IDisposable and calls the Dispose method. The second method(BestPractice_GetUserEmailById) is just call the dispose method explicitly. In fact there is Connection Pool in SQL Azure: http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx at the end of the link post.

    -----------------------------------

    We strongly recommend that you use the connection pooling and always close the connection when you are finished using it so that the unused connection will be returned to the pool. For more information about connection pooling, see Connection Pooling.

    -----------------------------------

    The connection pooling is here:

    -----------------------------------

    Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application callsClose on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call. http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

    -----------------------------------

    Close method is same with Dispose method.

    Based on my knowledge, I think they are same.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 19, 2012 7:30 AM
    Moderator
  • Thanks for the reply, and I'll mark as answer, although I still have reservations about this.
    Thursday, January 19, 2012 4:35 PM