DbProviderFactoriesr的使用

SqlConnectionOracleConnectionIDbConnectionSqlCommandOracleCommandIDbCommandSqlDataReaderOracleDataReaderIDataReader/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 configuration
provider 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
  
   
   

SqlConnectionDbConnectionIDbConnectionSqlCommandDbCommandIDbCommandSqlDataReaderDbDataReaderIDataReader/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,odbc};
public DbConnection GetConnectionBaseClass()
{
// determine provider from configuration
provider 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
       
       
        
        

GetFactoryClasses()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
       
       
        
        

Source 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

SqlClientNamed@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[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[1]);??????????? }??????????? Console.WriteLine("----After Delete-----");??????????? Console.WriteLine("Customer XYZZZ was deleted.");??????? }??? }??? catch (Exception ex)??? {??????? Console.WriteLine(ex.Message);??? }}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

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 class	Oracle class	Generic interface
SqlClient class	Base class	Generic interface
DbProviderFactories Method	Purpose
Meaning	Odbc	OleDb	SqlClient	OracleClient
Provider	Named/Positional	Parameter Marker