.NET Data Providers - How do I determine what they can do?

Question

I have code which could be executed using a Provider that doesn't support transactions, or doesn't support nested transactions.

How would I programmatically determine such support?

E.g. The code below throws a System.InvalidOperationException on the final commit when using the MySQL .NET Connector, but works fine for MSSQL.

I'd like to be able to alter the code to accommodate various providers, without having to hardcode tests based on the type of provider (E.g. I don't want to have to do if(typeof(connection) == "some provider name"))

using (IDbConnection connection = Use.Connection(ConnectionStringName))
using (IDbTransaction transaction = connection.BeginTransaction())
{
  using (currentCommand = connection.CreateCommand())
  {
    using (IDbCommand cmd = connection.CreateCommand())
    {
      currentCommand = cmd;
      currentCommand.Transaction = transaction;
      currentCommand.ExecuteNonQuery();
    }

    if (PipelineExecuter.HasErrors)
    {
      transaction.Rollback();
    }
    else
    {
      transaction.Commit();
    }
  }

  transaction.Commit();
}

Answer 1

There is no built-in way for an ADO.NET provider to give details about what capabilities they provide. SQLite used to not support nested transactions either, but some ADO.NET providers for SQLite would fake it in code (without proper support for savepoints). In that case, you'd never get errors, but you might not get the behavior you expected.

I also have seen many different implementations of the IDataReader indexer. It used to be that some providers would throw an exception when passed a field name that didn't exist, and some providers would return null. In ADO.NET 2.0 the help docs were updated to indicate that providers should throw an exception, but without checked exceptions there's no way to ensure that all providers were properly updated.

So, in short, The System.Data.Common namespace and various ADO.NET providers make it a lot easier to work with different databases, but you do have to be aware of what DB(s) you're using and the differences in each provider (most of which will probably be undocumented annoying quirks as opposed to big differences).