IDbConnection exception

Question

I have a IDbConnection for both Sql or Oracle connections. I have no problem to open it and then read data or save data through the connection. However, when the job is done, I tried to close the connection. Then I got a exception: "Internal .Net Framework Data Provider error 1".

Here are some codes to close the connection:

if (conn != null) // conn is IDbConnectin
{
  if (conn.State == System.Data.ConnectionState.Open)
  {
    conn.Close(); // bung! exception
  }
  conn.Dispose();
}
conn = null;

Anything else I should check before safely closing the connection?

Answer 1

I think you call close method in other thread or in Finilize method. Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, you should only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. see here

Answer 2

I know it might not sound like it solves your problem directly, but IDbConnection is IDisposable, so wrap your code that uses it in a using {} block.

Why?

You probably know that at the end of a using {} block, Dispose() is called. And, in every instance of IDbConnection, calling Dispose() will indirectly call Close(). But you get an additional bonus to using using that'll prevent you from running into this issue entirely -- by using using, you're forced to keep the creation, opening, closing, and disposal of the connection within the same context.

Most problems I found where people were running into your issue is when they use a Finalizer, or a separate thread to dispose of their connection objects. To me, there's an even bigger smell going on, where they're keeping their disposable objects alive for just a little bit too long, possibly sharing the connection between multiple members of the same class.

In other words, when you pass around the connection object, you might be tempted to write something like this:

class AccountService {
   private IDbConnection conn;
   internal AccountService(IDbConnection connection) {
     this.conn = connection;
   }

   public Account GetAccount(String id) {
     IDbCommand command = conn.CreateCommand();
     conn.Open;
     Account a = Account.FromReader(command.Execute(Strings.AccountSelect(id)));
     conn.Close;  // I remembered to call Close here
     return a;
   }

   // ... other methods where I Open() and Close() conn

   // hopefully not necessary since I've been careful to call .Close(), but just in case I forgot or an exception occured
   ~AccountService() { 
     if (conn != null) 
     {
       if (conn.State == System.Data.ConnectionState.Open)
       {
         conn.Close(); 
       }
       conn.Dispose();
     }
     conn = null;
   }
}

If you had used using, you wouldn't have even needed to think about using a Finalizer:

// IDbFactory is custom, and used to retrieve a Connection for a given Database
interface IDbFactory {
    IDbConnection Connection { get; }
}

class AccountService {
  private IDbFactory dbFactory; 
  internal AccountService(IDbFactory factory) { 
    this.dbFactory = factory;
  }

  public Account GetAccount(String id) {
    using (IDbConnection connection = dbFactory.Connection) { 
      using (command = connection.GetCommand()) {
        connection.Open();
        return Account.FromReader(command.Execute(Strings.AccountSelect(id)));
      } 
    } // via using, Close and Dispose are automatically called
  }

  // I don't need a finalizer, because there's nothing to close / clean up

}

There are exceptions to the using rule, especially if the construction of the disposable object is expensive, but 99 times out of 100, if you're not using using, there's a smell.