Try-catch-finally with 'using' in ADO.NET

Question

I want to check if I'm correctly disposing of resources using Ado.NET. I'm not sure when the 'using' statement makes .Dispose() irrelevant.

Please bear in mind I'm aware I shouldn't hardcode my SQL commands, this is just for training purposes.

 internal void CreateTable(string connectionString)
 {
 using (var connection = new SqliteConnection(connectionString))
 {
 using (var tableCmd = connection.CreateCommand())
 {
 connection.Open();
 tableCmd.CommandText =
 @"CREATE TABLE IF NOT EXISTS coding (
 Id INTEGER PRIMARY KEY AUTOINCREMENT, 
 Date TEXT, 
 Duration TEXT
 )";
 try
 {
 int rowCount = tableCmd.ExecuteNonQuery();
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 throw;
 }
 finally
 {
 connection.Dispose();
 tableCmd.Dispose();
 }
 }
 // Console.WriteLine($"\n\nDatabase created! \n\n");
 }
 }
 }

• CharlesNRice: you don't need the finally for the dispose the using will take care of that for you. From docs.microsoft.com/en-us/dotnet/csharp/language-reference/… The using statement ensures that Dispose (or DisposeAsync) is called even if an exception occurs within the using block. You can achieve the same result by putting the object inside a try block and then calling Dispose (or DisposeAsync) in a finally block; in fact, this is how the using statement is translated by the compiler.
• BCdotWEB: Don't use ADO.NET, use Dapper: dapper-tutorial.net

Answer 1

The using block or the using expression (collectively know as using statements) are just syntactical sugar. They will be translated to a proper try-finally block. If you visit sharplab.io and copy-paste there the following code:

using System;
public class C 
{
 public void M() 
 {
 using (var disposable = new Disposable()) 
 {
 Console.WriteLine("Inside the using block");
 }
 }
}
public class Disposable : IDisposable 
{
 public void Dispose() 
 {
 }
}

then the decompile code will look like this:

public class C
{
 public void M()
 {
 Disposable disposable = new Disposable();
 try
 {
 Console.WriteLine("Inside the using block");
 }
 finally
 {
 if (disposable != null)
 {
 ((IDisposable)disposable).Dispose();
 }
 }
 }
}
public class Disposable : IDisposable
{
 public void Dispose()
 {
 }
}

For the sake of simplicity I have omitted the attributes.

---

The good news is that the compiler is smart enough to incorporate catch blocks into the generated code (instead of naively have two nested try blocks).

Source

using System;
public class C 
{
 public void M() 
 {
 using var disposable = new Disposable(); 
 try 
 {
 _ = disposable.Get();
 }
 catch (DivideByZeroException)
 {
 Console.WriteLine("Something went wrong");
 }
 
 }
}
public class Disposable : IDisposable 
{
 public int Get() 
 {
 throw new DivideByZeroException();
 }
 public void Dispose() 
 {
 }
}

Decompiled

public class C
{
 public void M()
 {
 Disposable disposable = new Disposable();
 try
 {
 disposable.Get();
 }
 catch (DivideByZeroException)
 {
 Console.WriteLine("Something went wrong");
 }
 finally
 {
 if (disposable != null)
 {
 ((IDisposable)disposable).Dispose();
 }
 }
 }
}
public class Disposable : IDisposable
{
 public int Get()
 {
 throw new DivideByZeroException();
 }
 public void Dispose()
 {
 }
}