How to create a DbDataAdapter given a DbCommand or DbConnection?

I want to create a data access layer that works with any data provider.

I know it's possible to create a DbCommand using the factory method available on the connection.

objDbCon.CreateCommand();  

However, I could not find anything to create a DbDataA开发者_StackOverflowdapter. Is this is a bug in ADO.NET or what?

As of .NET 4.5, when writing provider independent code, you can now use the DbProviderFactories.GetFactory overload that accepts a DbConnection to obtain the correct provider factory from which you can then create a data adapter.

Example:

DbDataAdapter CreateDataAdapter(DbConnection connection)
{
    return DbProviderFactories.GetFactory(connection).CreateDataAdapter();
}

_{It seems someone on the ADO.NET team read Ian Boyd comment on his answer... :)}

DbProviderFactory.CreateDataAdapter *

Also you can get all registered DbProviders via DbProviderFactories class.

*I think this is a wrong place for this method.

Although this was answered well by Sergey, it took me a little while to translate it to my own needs. So my understanding was that if you had a DbConnection but knew you were using a SqlClient under the hood your code would look something like:

DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();

 private static DbDataAdapter CreateDataAdapter(DbCommand cmd)
 {
    DbDataAdapter adapter;

    /*
     * DbProviderFactories.GetFactory(DbConnection connection) seams buggy 
     * (.NET Framework too old?)
     * this is a workaround
     */
     string name_space = cmd.Connection.GetType().Namespace;
     DbProviderFactory factory = DbProviderFactories.GetFactory(name_space);
     adapter = factory.CreateDataAdapter();
     adapter.SelectCommand = cmd;
     return adapter;
 }

Given that you don't know the type of connection you are given, .NET provides no good way to solve the problem. Here's what we use:

/// <summary>
/// Construct a DataAdapater based on the type of DbConnection passed.
/// You can call connection.CreateCommand() to create a DbCommand object,
/// but there's no corresponding connection.CreateDataAdapter() method.
/// </summary>
/// <param name="connection"></param>
/// <exception>Throws Exception if the connection is not of a known type.</exception>
/// <returns></returns>
public static DbDataAdapter CreateDataAdapter(DbConnection connection)
{
   //Note: Any code is released into the public domain. No attribution required.

   DbDataAdapter adapter; //we can't construct an adapter directly
         //So let's run around the block 3 times, before potentially crashing

   if (connection is System.Data.SqlClient.SqlConnection)
      adapter = new System.Data.SqlClient.SqlDataAdapter();
   else if (connection is System.Data.OleDb.OleDbConnection)
      adapter = new System.Data.OleDb.OleDbDataAdapter();
   else if (connection is System.Data.Odbc.OdbcConnection)
      adapter = new System.Data.Odbc.OdbcDataAdapter();
   else if (connection is System.Data.SqlServerCe.SqlCeConnection)
      adapter = new System.Data.SqlServerCe.SqlCeDataAdapter ();
   else if (connection is Oracle.ManagedDataAccess.Client.OracleConnection)
      adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter();
   else if (connection is Oracle.DataAccess.Client.OracleConnection)
      adapter = new Oracle.DataAccess.Client.OracleDataAdapter();
   else if (connection is IBM.Data.DB2.DB2Connection)
      adapter = new IBM.Data.DB2.DB2DataAdapter();
   //TODO: Add more DbConnection kinds as they become invented
   else
   {
      throw new Exception("[CreateDataAdapter] Unknown DbConnection type: " + connection.GetType().FullName);
   }

   return adapter;
}

Necromancing.
If you're below .NET 4.5, then you can get it from the protected property DbProviderFactory from the connection with a compiled Linq-Expression:

namespace System.Data.Common
{


    public static class ProviderExtensions  
    {

        private static System.Func<System.Data.Common.DbConnection, System.Data.Common.DbProviderFactory> s_func;


        static ProviderExtensions()
        {
            System.Linq.Expressions.ParameterExpression p = System.Linq.Expressions.Expression.Parameter(typeof(System.Data.Common.DbConnection));
            System.Linq.Expressions.MemberExpression prop = System.Linq.Expressions.Expression.Property(p, "DbProviderFactory");
            System.Linq.Expressions.UnaryExpression con = System.Linq.Expressions.Expression.Convert(prop, typeof(System.Data.Common.DbProviderFactory));
            System.Linq.Expressions.LambdaExpression exp = System.Linq.Expressions.Expression.Lambda(con, p);
            s_func = (Func<System.Data.Common.DbConnection, System.Data.Common.DbProviderFactory>)exp.Compile();
        } // End Static Constructor  


        public static System.Data.Common.DbProviderFactory GetProviderByReflection(System.Data.Common.DbConnection conn)
        {
            System.Type t = conn.GetType();
            System.Reflection.PropertyInfo pi = t.GetProperty("DbProviderFactory", 
                  System.Reflection.BindingFlags.NonPublic 
                | System.Reflection.BindingFlags.Instance
            );

            return (System.Data.Common.DbProviderFactory)pi.GetValue(conn);
        } // End Function GetProviderByReflection 


        public static System.Data.Common.DbProviderFactory GetProvider(this System.Data.Common.DbConnection connection)
        {
            return s_func(connection);
        } // End Function GetProvider 


        public static System.Data.Common.DbDataAdapter CreateDataAdapter(this System.Data.Common.DbConnection connection)
        {
            System.Data.Common.DbProviderFactory fact = GetProvider(connection);

            return fact.CreateDataAdapter();
        } // End Function CreateDataAdapter 


        public static System.Data.Common.DbDataAdapter CreateDataAdapter(this System.Data.Common.DbConnection connection, System.Data.Common.DbCommand cmd)
        {
            System.Data.Common.DbProviderFactory fact = GetProvider(connection);

            System.Data.Common.DbDataAdapter da = fact.CreateDataAdapter();
            da.SelectCommand = cmd;

            return da;
        } // End Function CreateDataAdapter 


    }


}

You can use another way to get data into DataTable without DbDataAdapter.

Here is my code

                DataTable dt = new DataTable();
                using (IDataReader dr = com.ExecuteReader())
                {
                    if (dr.FieldCount > 0)
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i));
                            dt.Columns.Add(dc);
                        }
                        object[] rowobject = new object[dr.FieldCount];
                        while (dr.Read())
                        {
                            dr.GetValues(rowobject);
                            dt.LoadDataRow(rowobject, true);
                        }
                    }
                }
                return dt;