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 |
|---|---|---|
| SqlConnection | OracleConnection | IDbConnection |
| SqlCommand | OracleCommand | IDbCommand |
| SqlDataReader | OracleDataReader | IDataReader/IDataRecord |
| SqlTransaction | OracleTransaction | IDbTransaction |
| SqlParameter | OracleParameter | IDbDataParameter |
| SqlParameterCollection | OracleParameterCollection | IDataParameterCollection |
| SqlDataAdapter | OracleDataAdapter | IDbDataAdapter |
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
SqlClient class Base class Generic interface SqlConnection DbConnection IDbConnection SqlCommand DbCommand IDbCommand SqlDataReader DbDataReader IDataReader/IDataRecord SqlTransaction DbTransaction IDbTransaction SqlParameter DbParameter IDbDataParameter SqlParameterCollection DbParameterCollection IDataParameterCollection SqlDataAdapter DbDataAdapter* IDbDataAdapter SqlCommandBuilder DbCommandBuilder SqlConnectionStringBuilder DbConnectionStringBuilder 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 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 FactoriesRather 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
CreateConnection CreateCommand CreateCommandBuilder CreateConnection CreateConnectionStringBuilder CreateDataAdapter CreateDataSourceEnumerator CreateParameter CreatePermission 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 Method Purpose GetFactoryClasses() Returns a DataTable of provider information from the information in machine.config GetFactory(DataRow) Returns the correct DbProviderFactory instance given a DataRow from the DataTable produced by GetFactoryClasses GetFactory(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 Odbc OleDb SqlClient OracleClient Source to connect to Server Data Source Server or Data Source Server or Data Source User UID User ID UID or User ID User ID Password PWD Password PWD or Password Password Is a Windows login used? Trusted_Connection Integrated Security Trusted_Connection or Integrated Security Integrated Security Database to connect to Database Initial Catalog Database or Initial Catalog N/A Connection Pooling OLE DB Services Pooling Pooling
-----------------------------------------------------------------------------------------------------------------------------------
Table 6. Parameter usage styles in different ADO.NET data providers
| Provider | Named/Positional | Parameter Marker |
|---|---|---|
| SqlClient | Named | @parmname |
| OracleClient | Named | :parmname (or parmname) |
| OleDb | Positional | ? |
| Odbc | Positional | ? |
-----------------------------------------------------------------------------------------------------------------------------------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);
}
}
1462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
