DbProviderFactoriesr的使用

System.Data.Common 命名空间提供用于创建与特定数据源一起使用的 DbProviderFactory 实例的类。当创建 DbProviderFactory 实例并向其传递有关数据提供程序的信息时，DbProviderFactory 可以根据为其提供的信息确定要返回的正确的强类型连接对象。

每个公开 DbProviderFactory 的 .NET Framework 数据提供程序都会在 machine.config 文件中注册配置信息和一个提供程序字符串。.NET Framework 中具有已注册的工厂类的数据提供程序包括 System.Data.Odbc、System.Data.OleDb、System.Data.SqlClient、System.Data.SqlServerCe 和 System.Data.OracleClient。

- ---------------------------------------------------------------------------------------------------------------------------------

Table 1. Provider-specific classes and generic interfaces in ADO.NET 1.0/1.1

SqlClient classOracle classGeneric interfaceSqlConnectionOracleConnectionIDbConnectionSqlCommandOracleCommandIDbCommandSqlDataReaderOracleDataReaderIDataReader/IDataRecordSqlTransactionOracleTransactionIDbTransactionSqlParameterOracleParameterIDbDataParameterSqlParameterCollectionOracleParameterCollectionIDataParameterCollectionSqlDataAdapterOracleDataAdapterIDbDataAdapter

In ADO.NET 1.0 and 1.1, programmers had two choices. They could code to the provider-specific classes or the generic interfaces. If there was the possibility that the company database could change during the projected lifetime of the software, or if the product was a commercial package intended to support customers with different databases, they had to program with the generic interfaces. You can't call a constructor on an interface, so most generic programs included code that accomplished the task of obtaining the original IDbConnection by calling "new" on the appropriate provider-specific class, like this.

enum provider {sqlserver, oracle, oledb, odbc};public IDbConnection GetConnectionInterface(){// determine provider from configurationprovider prov = GetProviderFromConfigFile();IDbConnection conn = null;switch (prov) {  case provider.sqlserver:     conn = new SqlConnection(); break;  case provider.oracle:         conn = new OracleConnection(); break; // add new providers as the application supports them }return conn;}----------------------------------------------------------------------------------------------

Table 2. Generic base classes and Generic interfaces in ADO.NET 2.0
SqlClient classBase classGeneric interfaceSqlConnectionDbConnectionIDbConnectionSqlCommandDbCommandIDbCommandSqlDataReaderDbDataReaderIDataReader/IDataRecordSqlTransactionDbTransactionIDbTransactionSqlParameterDbParameterIDbDataParameterSqlParameterCollectionDbParameterCollectionIDataParameterCollectionSqlDataAdapterDbDataAdapter*IDbDataAdapterSqlCommandBuilderDbCommandBuilder SqlConnectionStringBuilderDbConnectionStringBuilder SqlPermission
DBDataPermission*
 
In addition to these "main" base classes, many new base classes were added in ADO.NET 2.0, including some that we'll be talking about later in this article. The provider base classes in ADO.NET are abstract, however, meaning that they can't be instantiated directly. Our interface-based code above would change to: 
enum provider {sqlserver, oracle, oledb, odbc};public DbConnection GetConnectionBaseClass(){// determine provider from configurationprovider prov = GetProviderFromConfigFile();DbConnection conn = null;switch (prov) {  case provider.sqlserver:     conn = new SqlConnection(); break;  case provider.oracle:         conn = new OracleConnection(); break; // add new providers as the application supports them }return conn;}
-----------------------------------------------------------------------------------------------
Provider Factories
Rather than rely on the case statements above, it would be nice to have a class that gave out a DbConnection based on instantiating "the correct" provider-specific connection. But how to know whether to instantiate SqlConnection or OracleConnection? The solution to this is to use a Provider Factory class to give out the right type of concrete class. Each provider implements a provider factory class, e.g., SqlClientFactory, OracleClientFactory, and OleDbFactory. These classes all derive from DbProviderFactory and contain static methods (shared in Visual Basic .NET) to distribute classes that can be created. Here's the list of DbProviderFactory methods:
Table 3. DbProviderFactory Methods
CreateConnectionCreateCommandCreateCommandBuilderCreateConnectionCreateConnectionStringBuilderCreateDataAdapterCreateDataSourceEnumeratorCreateParameterCreatePermission
public DbConnection GetInitializedConnectionBaseClass(){DbConnection conn = null;ConnectionStringSettings s =      ConfigurationSettings.ConnectionStrings["Publications"];DbProviderFactory f = DbProviderFactories.GetFactory(  s.ProviderName);if ((f.SupportedClasses & DbProviderSupportedClasses.DbConnection) > 0) {  conn =  f.CreateConnection();  conn.ConnectionString = s.ConnectionString; }return conn;}
------------------------------------------------------------------------------------------------------------------------------------
Table 4. DbProviderFactories methods
DbProviderFactories MethodPurposeGetFactoryClasses()Returns a DataTable of provider information from the information in machine.configGetFactory(DataRow)Returns the correct DbProviderFactory instance given a DataRow from the DataTable produced by GetFactoryClassesGetFactory(string)Returns the correct DbProviderFactory instance given a provider-invariant name string that identifies the provider------------------------------------------------------------------------------------------------------------------------------------
Table 5. Connection string names and values in different data providers
Meaning OdbcOleDbSqlClientOracleClientSource to connect toServerData SourceServer or Data SourceServer or Data SourceUserUIDUser IDUID or User IDUser IDPasswordPWDPasswordPWD or PasswordPasswordIs a Windows login used?Trusted_ConnectionIntegrated SecurityTrusted_Connection or Integrated SecurityIntegrated SecurityDatabase to connect toDatabaseInitial CatalogDatabase or Initial CatalogN/AConnection Pooling OLE DB ServicesPoolingPooling

-----------------------------------------------------------------------------------------------------------------------------------

Table 6. Parameter usage styles in different ADO.NET data providers

ProviderNamed/PositionalParameter MarkerSqlClientNamed@parmnameOracleClientNamed:parmname (or parmname)OleDbPositional?OdbcPositional?

 

 

-----------------------------------------------------------------------------------------------------------------------------------static void CreateDataAdapter(string providerName, string connectionString)
{
    try
    {
        // Create the DbProviderFactory and DbConnection.
        DbProviderFactory factory =
            DbProviderFactories.GetFactory(providerName);

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        using (connection)
        {
            // Define the query.
            string queryString =
                "SELECT CustomerID, CompanyName FROM Customers";

            // Create the select command.
            DbCommand command = factory.CreateCommand();
            command.CommandText = queryString;
            command.Connection = connection;

            // Create the DbDataAdapter.
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = command;

            // Create the DbCommandBuilder.
            DbCommandBuilder builder = factory.CreateCommandBuilder();
            builder.DataAdapter = adapter;

            // Get the insert, update and delete commands.
            adapter.InsertCommand = builder.GetInsertCommand();
            adapter.UpdateCommand = builder.GetUpdateCommand();
            adapter.DeleteCommand = builder.GetDeleteCommand();

            // Display the CommandText for each command.
            Console.WriteLine("InsertCommand: {0}",
                adapter.InsertCommand.CommandText);
            Console.WriteLine("UpdateCommand: {0}",
                adapter.UpdateCommand.CommandText);
            Console.WriteLine("DeleteCommand: {0}",
                adapter.DeleteCommand.CommandText);

            // Fill the DataTable.
            DataTable table = new DataTable();
            adapter.Fill(table);

            // Insert a new row.
            DataRow newRow = table.NewRow();
            newRow["CustomerID"] = "XYZZZ";
            newRow["CompanyName"] = "XYZ Company";
            table.Rows.Add(newRow);

            adapter.Update(table);

            // Display rows after insert.
            Console.WriteLine();
            Console.WriteLine("----List All Rows-----");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine("{0} {1}", row[0], row[1]);
            }
            Console.WriteLine("----After Insert-----");

            // Edit an existing row.
            DataRow[] editRow = table.Select("CustomerID = 'XYZZZ'");
            editRow[0]["CompanyName"] = "XYZ Corporation";

            adapter.Update(table);

            // Display rows after update.
            Console.WriteLine();
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine("{0} {1}", row[0], row[1]);
            }
            Console.WriteLine("----After Update-----");

            // Delete a row.
            DataRow[] deleteRow = table.Select("CustomerID = 'XYZZZ'");
            foreach (DataRow row in deleteRow)
            {
                row.Delete();
            }

            adapter.Update(table);

            // Display rows after delete.
            Console.WriteLine();
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine("{0} {1}", row[0], row[1]);
            }
            Console.WriteLine("----After Delete-----");
            Console.WriteLine("Customer XYZZZ was deleted.");
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}