DB Class connection management RRS feed

  • Question

  • Hi,

    I just have a a question whether my Shared Function is "really" closing the connection made since I instantiate the DBConnection twice everytime I call another Shared function which are the ones that gets called throughout the pages. Just trying to make sure that the Using from the 2nd function disposes and closes the connection OPENED.  Hence, I might be just be better off opening it from the lower level Shared functions.


    ''' SHARED Function that opens the connection within my class

    Public Shared Function GetDbConnection(ByVal providerName As String) As Data.Common.DbConnection 
                Dim sqlConn As DbConnection = GetDbProviderFactory(providerName).CreateConnection() 
                sqlConn.ConnectionString = GetDbConnectionString(providerName) 
                Return sqlConn 
            End Function

     Public Shared Function GetDataTable(ByVal sqlQuery As String, ByVal providerName As String) As DataTable 
                Using conn As DbConnection = GetDbConnection(providerName)                 Using dbDataTable As DataTable = New DataTable() 
                        Using da As DbDataAdapter = GetDbProviderFactory(providerName).CreateDataAdapter() 
                            Using cmd As DbCommand = GetDbProviderFactory(providerName).CreateCommand() 
                                cmd.CommandText = sqlQuery 
                                cmd.Connection = conn 
                                da.SelectCommand = cmd 
                                Return dbDataTable 
                            End Using 
                        End Using 
                    End Using 
                End Using         End Function

    Tuesday, February 7, 2012 3:43 AM


  • Hi,

    in my applications, I open the connection in the lower shared function(s); GetDbConnection should only do what is says: get you a connection. Otherwise you'd have to call it GetAndOpenDbConnection. Having functions doing one, well defined thing is always a good practice.

    Regards, Nico

    Wednesday, February 8, 2012 3:30 PM