Is Try/Catch/finally required with the Using statement in order to handle exceptions?

Question

I wonder how to use statement handles exceptions? Do I need to wrap the using statements with a Try/Cath/Finally clause in order to be sure that the SqlConnection object is closed and disposed even if the containing code throws an exception?

Public Function GetUserAccountKeyByUsername(ByVal pUsername As String) As Int32
    If String.IsNullOrEmpty(pUsername) Then
        Throw New ArgumentNullException("pUsername", "Username is missing")
    End If

    Dim o As Object
    Dim userAccountKey As Int32
    Dim SQL As StringBuilder = New StringBuilder()

    With SQL
        .Append("SELECT USER_KEY ")
        .Append("FROM USER ")
        .Append("WHERE USERNAME = @Username ")
    End With

    Try
        Using conn As SqlConnection = New SqlConnection(ConnectionString)
            conn.Open()
            Using cmd As SqlCommand = New SqlCommand(SQL.ToString, conn)
                Try
                    cmd.CommandTimeout = Convert.ToInt32(ConfigurationManager.AppSettings("SQLQueryLimitTime"))
                    cmd.Parameters.Add(New SqlParameter("@Username", SqlDbType.VarChar)).Value = pUsername
                    o = cmd.ExecuteScalar()
                    If (o IsNot Nothing) AndAlso Not (IsDBNull(o)) Then
                        userAccountKey = Convert.ToInt32(o)
                    End If
                Catch ex As Exception
                    _log.logError(ex, cmd)
                End Try
            End Using
        End Using
    Catch ex As Exception
        _log.logError(ex, conn.ConnectionString)
    Finally
        conn.Close()
        conn.Dispose()
    End Try
    Return userAccountKey
End Function

Answer 1

using puts try and finally in your code and automatically it calls .Dispose() and eventually .Close() coz DbConnection.Dispose() calls Close(), but there is no catch, so you will need to add catch over using block, some thing like this

try
{
   using(some resource)
   {
   }
}
catch(Exception)
{
}

vs

try
{
}
catch(Exception)
{
}
finally{ }

So looking at this you might think Try/Catch/Finally is better than Using, coz in using in any case you need to handle error, but it's not.

If there is any error during .Close() or .Dispose() occurs, the first sample will handle that too, but in second case you will have to put try-catch in finally block.

Read more about Avoiding Problems with the Using Statement (MSDN)

Hope this answers your question.