如何在给定 DbCommand 或 DbConnection 的情况下创建 DbDataAdapter？

Question

我想创建一个可与任何数据提供者一起使用的数据访问层。

我知道可以使用连接上可用的工厂方法创建 DbCommand 。

objDbCon.CreateCommand();  

但是，我找不到任何东西来创建DbDataAdapter。这是 ADO.NET 中的错误还是什么？

Answer 1

从.NET 4.5开始，在编写独立于提供程序的代码时，您现在可以使用 DbProviderFactories.GetFactory 重载接受 DbConnection 获取正确的提供程序工厂，然后您可以从中创建数据适配器。

示例：

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

_{似乎 ADO.NET 团队中的某个人阅读了 Ian Boyd 对他的答案的评论...:)}

Answer 2

DbProviderFactory.CreateDataAdapter *

您也可以通过 DbProviderFactories 类获取所有已注册的 DbProvider 。

*我认为这个方法有一个错误的地方。

Answer 3

虽然 Sergey 很好地回答了这个问题，但我花了一些时间才将其转化为我自己的需求。所以我的理解是，如果您有 DbConnection 但知道您在幕后使用 SqlClient，您的代码将类似于：

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

Answer 4

 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;
 }

Answer 5

您可以使用另一种方式将数据获取到 DataTable 中，而无需使用 DbDataAdapter。

这是我的代码

                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;

Answer 6

鉴于您不知道给定的连接类型，.NET 没有提供解决该问题的好方法。这是我们使用的：

/// <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;
}

Answer 7

死灵术。
如果您低于 .NET 4.5，则可以通过与已编译的 Linq-Expression 的连接从受保护的属性 DbProviderFactory 获取它：

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 


    }


}