Instantiate IDataAdapter from instance of IDbConnection

Question

I have an instance of IDbConnection, which can be any connection, Sql, OleDb, etc. I want to make a generic wrapper so I can just send the wrapper a connection and get a nice set of methods for easy manipulation. I have a Query method, I want it to return a DataTable, so I can do

IDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = myCommand;
DataSet ds = new DataSet();
adapter.Fill(ds);

The problem is I have to use OleDbAdapter and it wouldn't work for SQL, I don't really want to write "driver specific" code. Is there a way I can get a IDataAdapter instance from my instantiated IDbConnection object? I know I can create a command doing

IDbCommand command = _connection.CreateCommand();

It's only logical to think there must be some easy way to do the same with an IDataAdapter.

EDIT:

using (var reader = command.ExecuteReader())
{
    var dataTable = new DataTable();
    dataTable.Load(reader);
}

Well not exactly what I asked for but a nice solution.

When you write generic ADO.Net code, at some point you will have Factory methods that need to look at the Provider property of the connection string. If you have that piece, you can use [DbProviderFactory.CreateDataAdapter](http://msdn.microsoft.com/en-us/library/system.data.common.dbproviderfactory.createdataadapter.aspx) — mdisibio, May 23 '12 at 17:22
+1 for sharing the solution with the IDataReader. We have a custom framework which is used for handling database connections, transactions etc, but it does not (yet) support Data Adapters. By using IDataReader, we can however use that framework without having to extend/modify it. — Tim Meyer, Feb 26 '15 at 07:48
I took the liberty of editing your question to add the `using` statement which [automatically closes the reader](http://stackoverflow.com/a/2157331/808151). — Tim Meyer, Feb 26 '15 at 08:02

score 7 · Answer 1 · answered May 23 '12 at 17:06

Here is a rough example of how you could get the adapter using reflection.

IDataAdapter GetAdapter(IDbConnection connection) {
    var assembly = connection.GetType().Assembly;
    var @namespace = connection.GetType().Namespace;    

    // Assumes the factory is in the same namespace
    var factoryType = assembly.GetTypes()
                        .Where (x => x.Namespace == @namespace)
                        .Where (x => x.IsSubclassOf(typeof(DbProviderFactory)))
                        .Single();

    // SqlClientFactory and OleDbFactory both have an Instance field.
    var instanceFieldInfo = factoryType.GetField("Instance", BindingFlags.Static | BindingFlags.Public);
    var factory = (DbProviderFactory) instanceFieldInfo.GetValue(null);

    return factory.CreateDataAdapter();
}

score 4 · Answer 2 · answered Dec 13 '14 at 17:39

I had the same problem. This is how I solved it

private DataSet executeDataQuery(string query, string connection, string provider, out Exception ex) {
        DataSet ds = new DataSet();
        ex = null;
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(provider);
        IDbConnection dbConnection = dbFactory.CreateConnection();
        dbConnection.ConnectionString = connection;
        using (dbConnection) {
            try {
                IDbDataAdapter dbAdapter = dbFactory.CreateDataAdapter();
                IDbCommand dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = query;
                dbCommand.CommandType = CommandType.Text;
                dbAdapter.SelectCommand = dbCommand;
                dbAdapter.Fill(ds);
            }
            catch (Exception exc) {
                ex = exc;
            }
            finally {
                if (dbConnection.State == ConnectionState.Open) {
                    dbConnection.Close();
                }
            }
        }
        return ds;
    }

you should put the using around the IDbConnection dbConnection = dbFactory.CreateConnection(); — BlackICE, Aug 31 '17 at 13:30
@TanmayNehete on your connectionstring configuration you have the connection and the provider name. The provider variable must have the provider name of the connectionstring. — jcvegan, Nov 02 '18 at 13:33

Instantiate IDataAdapter from instance of IDbConnection

2 Answers2