ASP.NET - How To Open/Close SQL Connections Correctly

Connections are very limited resources. They should always be handled very carefully and should not be kept open for long time. In fact the Connection should only be opened when it is needed and then should be closed instantly after the use. This means that we open the connection as late as possible and release it as quickly as possible.


Private Sub SurroundingSub()
    Dim cn As SqlConnection = New SqlConnection("CONNECTION_STRING")

    Try
        cn.Open()
		
	    ' Perform DB operation here i.e. any CRUD operation 
	
	    cn.Close()	
    Catch ex As Exception
    Finally
	    ' Connection should always be closed here so that it will close always
        cn.Dispose()
    End Try
End Sub

For the acquisition and disposal of resource, the following Try construction is equivalent to the Using block.


Using resource As New resourceType   
    ' Insert code to work with resource.  
End Using


Dim resource As New resourceType  
Try   
    ' Insert code to work with resource.  
Finally   
    If resource IsNot Nothing Then  
        resource.Dispose()   
    End If  
End Try