none
What's wrong with reusing a connection object?

    Question

  • I program in VB.net and C# with SQL Server. I would like to know why advice is often given not to reuse connection object. I always close my connections but wonder why I must also dispose. And then recreate a few seconds later. I dispose when the application closes. Why is this bad?


    Mary

    Monday, February 5, 2018 4:43 AM

Answers

  • There is nothing wrong with have a single connection object so long as you are mindful of its state in a typical application yet as applications grow in size and perform many operations it’s better to create a connection and command as needed. Yet if this works for you, by all means use it.

    If you look at the current method to work with data via Entity Framework there is no visible connection or command objects but how they are used is create a context, use it then it’s closed and disposed.

    Entity Framework example which does not use DataSet or DataTable objects yet does the same as opening a connection and populate a strong type list with database table data. The End Using disposes of the objects used to retrieve the data. No different then 

    Public Function LoadCustomersDTO() As List(Of CustomerDTO)
        Using entity As New DataEntities
    
            Dim ListOfCustomersDTO = entity.Customers.Select(Function(cust) _
                New CustomerDTO With
                {
                    .id = cust.id,
                    .FirstName = cust.FirstName,
                    .LastName = cust.LastName,
                    .Address = cust.Address,
                    .City = cust.City,
                    .State = cust.State,
                    .ZipCode = cust.ZipCode
                }
            ).ToList
    
            Return ListOfCustomersDTO
    
        End Using
    End Function

    Similar too

    Imports System.Data.SqlClient
    Public Class Operations
        Public Function LoadCustomers() As DataTable
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandText = "SELECT ....."}
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            Return dt
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Mary OB Monday, February 5, 2018 9:04 AM
    Monday, February 5, 2018 5:25 AM
    Moderator

All replies

  • There is nothing wrong with have a single connection object so long as you are mindful of its state in a typical application yet as applications grow in size and perform many operations it’s better to create a connection and command as needed. Yet if this works for you, by all means use it.

    If you look at the current method to work with data via Entity Framework there is no visible connection or command objects but how they are used is create a context, use it then it’s closed and disposed.

    Entity Framework example which does not use DataSet or DataTable objects yet does the same as opening a connection and populate a strong type list with database table data. The End Using disposes of the objects used to retrieve the data. No different then 

    Public Function LoadCustomersDTO() As List(Of CustomerDTO)
        Using entity As New DataEntities
    
            Dim ListOfCustomersDTO = entity.Customers.Select(Function(cust) _
                New CustomerDTO With
                {
                    .id = cust.id,
                    .FirstName = cust.FirstName,
                    .LastName = cust.LastName,
                    .Address = cust.Address,
                    .City = cust.City,
                    .State = cust.State,
                    .ZipCode = cust.ZipCode
                }
            ).ToList
    
            Return ListOfCustomersDTO
    
        End Using
    End Function

    Similar too

    Imports System.Data.SqlClient
    Public Class Operations
        Public Function LoadCustomers() As DataTable
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandText = "SELECT ....."}
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            Return dt
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Mary OB Monday, February 5, 2018 9:04 AM
    Monday, February 5, 2018 5:25 AM
    Moderator
  • thank you Karen. I do check state before I close. I have not found a use for EF yet. Also, it is slower than just using ADO.net directly, or so I have read.

    Mary

    Monday, February 5, 2018 9:03 AM
  • Mary,

    An open connection (in SQL Server) becomes one in the connectionpool. That can give errors if there are to much open connections. Therefore to avoid that is it better always to  close as soon as it is possible. I've seen gone things down when the connectionpool was full.

    Be aware this is only about closing and opening, but as Close means the same as Dispose since version .Net 1.1 you need constantly to create a new connection. (Not that it takes recognizable time).

    The dispose was needed in .Net version 1.0 when the dispose was overridden or shadowed with the removal of the connectionstring which was needed to get the mechanism running. Since newer versions that should not be needed anymore. 


    Success Cor



    • Edited by Cor Ligthert Monday, February 5, 2018 10:44 AM wrong word used
    Monday, February 5, 2018 9:18 AM
  • If you are referring to persistent connections they typically are not necessary and maintaining them simply uses up database resources when they are not currently in use. Also, in a file based DBMS such as Microsoft Access, it can increase the possibility of database corruption.

    In Windows, .NET, OLEDB, ODBC most database connection providers support what is called a connection or resource pool. When a connection to a database is requested the provider looks for a connection pool that matches the app and connection string information, including credentials. If a connection is available, then it is returned to the app for use. If one is not available a new connection is established with the database and added to the pool. When an app closes a connection it is returned to the pool, but not disconnected from the database. In .NET there is no need to dispose it. The connection will remain in the pool for a defined period of time, usually a minute or so, in case it is required to service the next call. This "pooling" of connections reduces the overhead involved in creating a new connection for a server type DBMS system. Connection pooling is all managed behind the scenes and doesn't require any coding to implement.

    Connection pooling can be disabled but in most instances it's not necessary since it works rather effectively in managing database connection resources.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, February 5, 2018 1:16 PM
  • Thank you Cor for the response. When you say "but as Close means the same as Dispose " I respectfully disagree. I always .Close my connections and the reuse them. If it was .Dispose then I would not be able to reuse. As far as I know, cn.Close() returns the connection to the pool.


    Mary

    Thursday, March 8, 2018 8:50 AM
  • thank you Karen. I do check state before I close. I have not found a use for EF yet. Also, it is slower than just using ADO.net directly, or so I have read.

    Mary

    EF is using ADO.NET directly. One has to know how use performance considerations in using EF and object materialization, how to use cached queries and other considerations.

    https://msdn.microsoft.com/en-us/library/hh949853(v=vs.113).aspx

    And somehow if one has to get the data into some kind of container and if using datasets and datatables,  they are slow compared to a collection of custom objects that EF uses.

    https://dzone.com/articles/reasons-move-datatables

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    Thursday, March 8, 2018 12:09 PM