Closing dbf connections

I'm receiving the max pool size reached errors intermittently in my web application. I suspect I'm not closing my connections properly in some places.

Please look at my code and tell me how to do this properly.

1) Read
   
    Dim cmd As SqlClient.SqlCommand
...
            Dim sqlConnection As New SqlClient.SqlConnection(Name.GetConnectionString)
    
            cmd = New SqlClient.SqlCommand("[sss.CMSContentRead]", sqlConnection)
            cmd.CommandType = CommandType.StoredProcedure
...

         cmd.Connection.Open() 
            cmd.ExecuteNonQuery()
            cmd.Connection.Dispose()
....
cmd.Dispose()

Open in new window


2) Read
            Dim sql As String = QueryString
            Dim con As New SqlConnection(Name.GetConnectionString)
            Dim cmd As New SqlCommand(sql, con)
            cmd.CommandType = CommandType.Text
            con.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                InitializeRow(dr)
            End If
            If Not dr.IsClosed() Then
                dr.Close()
            End If
            dr.Dispose()
            con.Dispose()
            cmd.Dispose()

Open in new window


3) Read
   Public Sub Read(ByVal cmd As SqlCommand, ByVal con As SqlConnection)
            con.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                InitializeRow(dr)
            End If
            If Not dr.IsClosed() Then
                dr.Close()
            End If
            dr.Dispose()
            cmd.Connection.Dispose()
            cmd.Dispose()

Open in new window



4) delete
 Public Function Delete() As Short
            
        Dim retval As Short = FAILURE
        Try
                                       
            Using con As New SqlConnection(Name.GetConnectionString)
                
                Dim cmd As SqlCommand = New SqlCommand("[sss.CMSDocumentDelete]",con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandTimeout = 30
                Dim params As SqlParameterCollection = cmd.Parameters	
            
               ' Register PK's as Input SQL Parameter(s) for matching
               params.Add("@CmsDocumentId", SqlDbType.Int).Value = _cmsDocumentId
                params.Add("@result", SqlDbType.SmallInt).Direction = ParameterDirection.Output

                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                
                ' 1 success
                '-1 failure
                '-2 entity is owned by other resource
                '-3 entity does not exist
                '-4 dirty read
                retval = ShortCheckNull(params("@result"))
    
            End Using ' using blocks automatically call Dispose() and close con
            Clear()

        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
            retval = FAILURE
        End Try
        
        return retval
    
    End Function

Open in new window



5) Update

      Dim cmd As SqlClient.SqlCommand
        Dim params As SqlClient.SqlParameterCollection
        Dim retval As Short
               
        Try

            Dim sqlConnection As New SqlClient.SqlConnection(Name.GetConnectionString)
    
            cmd = New SqlClient.SqlCommand("[sss.CMSDocumentUpdate]", sqlConnection)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = 30
...
                cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            cmd.Connection.Dispose()
...
   cmd.Dispose()		

Open in new window


thanks!
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I wouldn't worry so much about disposing of each object.  It is managed code and the garbage collector will handle it.  I would suggest putting a check for cmd.connection.Open() in a FINALLY section of your TRY instead of within the TRY itself.  This way if there is an error of some kind the connection will still be closed.

Excuse my syntax but basically

TRY
    ...
FINALLY
   IF cmd.connection.Open() = TRUE
      cmd.connection.Close
END TRY
0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
IF cmd.connection.Open() = TRUE

... does not produce a value.

Does anyone know, what is the proper command to check for an open connection?

thanks!
0
Brian CroweDatabase AdministratorCommented:
It's been a while but it's starting to come back to me...

IF cmd.connection.state == connectionstate.open
   cmd.connection.close()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.