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

You dont require to write it out again its get created automatically in complied code..

The answer is in C# but its works same way in VB.NET

using block in C# comes very handly while dealing with disposable objects. Disposable objects are those objects that can explicitly release the resources they use when called to dispose. As we know .Net garbage collection is non-deterministic so you can’t predict when exactly the object will be garbage collected.

Read this post for more in details : understanding ‘using’ block in C#

CS file code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BlogSamples
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Car myCar = new Car(1))
            {
                myCar.Run();
            }
        }
    }
}

MSIL code

.method private hidebysig static void  Main(string[] args) cil managed
{
  .entrypoint
  // Code size       37 (0x25)
  .maxstack  2
  .locals init ([0] class BlogSamples.Car myCar,
           [1] bool CS$4$0000)
  IL_0000:  nop
  IL_0001:  ldc.i4.1
  IL_0002:  newobj     instance void BlogSamples.Car::.ctor(int32)
  IL_0007:  stloc.0
  .try
  {
    IL_0008:  nop
    IL_0009:  ldloc.0
    IL_000a:  callvirt   instance void BlogSamples.Car::Run()
    IL_000f:  nop
    IL_0010:  nop
    IL_0011:  leave.s    IL_0023
  }  // end .try
  finally
  {
    IL_0013:  ldloc.0
    IL_0014:  ldnull
    IL_0015:  ceq
    IL_0017:  stloc.1
    IL_0018:  ldloc.1
    IL_0019:  brtrue.s   IL_0022
    IL_001b:  ldloc.0
    IL_001c:  callvirt   instance void [mscorlib]System.IDisposable::Dispose()
    IL_0021:  nop
    IL_0022:  endfinally
  }  // end handler
  IL_0023:  nop
  IL_0024:  ret
} // end of method Program::Main

Answer 2

Yes, you need the try catch block e.g. for logging the exception as shown in your code. The using block makes sure that Dispose is called but does not handle exceptions.

Answer 3

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.