使用ADO.NET 建立适应多种数据库的数据访问层接口

The final rule specifies why and how you should abstract the .NET Framework data provider used internally in your DAL. As I've mentioned, the ADO.NET programming model exposes distinct .NET Framework data providers including SqlClient, OleDb, and others available on the MSDN Online Web site. While this design results in improved performance and the ability for providers to expose data-source-specific functionality (such as the ExecuteXmlReader method of the SqlCommand object), it forces you to decide which provider to code against. In other words, a developer typically chooses to use SqlClient or OleDb and then writes code directly against the classes in the respective namespace.

If you want to change the .NET Framework data provider, you'll need to recode your data access methods. To avoid this, you can use a design pattern known as the Abstract Factory. Using this pattern, you can build a simple class that exposes methods to create the primary .NET Framework data provider objects (command, connection, data adapter, and parameter) based on information identifying the .NET Framework data provider passed into the constructor. The code in Figure7 shows a simple C# version of this class.

In order to use this class, the code in your data access classes would need to program against the various interfaces that the .NET Framework data providers implement, including IDbCommand, IDbConnection, IDataAdapter, and IDataParameter. For example, in order to fill a DataSet with results from a parameterized stored procedure, you could use the following code inside a method of your data access class:

Dim _pf  As New ProviderFactory(ProviderType.SqlClient)
Dim cn As IDbConnection = _pf.CreateConnection(_connect)
Dim da As IDataAdapter = _pf.CreateDataAdapter("usp_GetBook", cn)

Dim db As IDbDataAdapter = CType(da, IDbDataAdapter)
db.SelectCommand.CommandType = CommandType.StoredProcedure
db.SelectCommand.Parameters.Add(_pf.CreateParameter("@productId", _
    DbType.Int32, id))

Dim ds As New DataSet("Books")
da.Fill(ds)

Typically, you would declare the ProviderFactory variable at the class level and instantiate it in the constructor of the data access class. Additionally, its constructor would be populated with the provider read from a configuration file, rather than hardcoded, as shown here. As you can imagine, the ProviderFactory would be a great addition to your DAL base class and can then be included in the assembly and distributed to other developers.

You can choose to take it a step further and encapsulate common ADO.NET code that developers write over and over. In fact, Microsoft has released a Data Access Application Block that performs this function for SQL Server.

Figure 7 ProviderFactory

public enum ProviderType :int {SqlClient = 0, OLEDB = 1}

public class ProviderFactory {

    public ProviderFactory(ProviderType provider) {
        _pType = provider;
        _initClass();
    }

    public ProviderFactory() {
        _initClass();
    }
        
    private ProviderType _pType = ProviderType.SqlClient;
    private bool _pTypeSet = false;
    private Type[] _conType, _comType, _parmType, _daType;


    private void _initClass() {
    _conType = new Type[2];
    _comType = new Type[2];
    _parmType = new Type[2];
    _daType = new Type[2];

       // Initialize the types for the providers
       _conType[(int)ProviderType.SqlClient] = typeof(SqlConnection);
       _conType[(int)ProviderType.OLEDB] = typeof(OleDbConnection);
       _comType[(int)ProviderType.SqlClient] = typeof(SqlCommand);
       _comType[(int)ProviderType.OLEDB] = typeof(OleDbCommand);
       _parmType[(int)ProviderType.SqlClient] = typeof(SqlParameter);
       _parmType[(int)ProviderType.OLEDB] = typeof(OleDbParameter);
       _daType[(int)ProviderType.SqlClient] = typeof(SqlDataAdapter);
       _daType[(int)ProviderType.OLEDB] = typeof(OleDbDataAdapter);
    }

    public ProviderType Provider {
        get {
            return _pType;
        }
        set {
           if (_pTypeSet) {
                throw new ReadOnlyException("Provider already set to " 
                    + _pType.ToString());                             
            }
            else {
                _pType = value;
                _pTypeSet = true;
            }
        }
    }
    public IDataAdapter CreateDataAdapter(string commandText,IDbConnection 
                                          connection) {
        IDataAdapter d;
        IDbDataAdapter da;
            
        d = (IDataAdapter)Activator.CreateInstance(_daType[(int)_pType], 
                                                   false);
        da = (IDbDataAdapter)d;
        da.SelectCommand = this.CreateCommand(commandText, connection);
        return d; }
        
    public IDataParameter CreateParameter(string paramName, DbType 
                                          paramType) {
        IDataParameter p;
        p = (IDataParameter)Activator.CreateInstance(_parmType[(int)_pType], 
                                                    false);
        p.ParameterName = paramName;
        p.DbType = paramType;
        return p; 
    }
        
    public IDataParameter CreateParameter(string paramName, DbType 
                                          paramType, Object value) {
        IDataParameter p;
        p = (IDataParameter)Activator.CreateInstance(_parmType[(int)_pType], 
                                                    false);
        p.ParameterName = paramName;
        p.DbType = paramType;
        p.Value = value;
        return p;
    }
        
    public IDbConnection CreateConnection(string  connect) {
        IDbConnection c;
        c = (IDbConnection)Activator.CreateInstance(_conType[(int)_pType], 
                                                    false);
        c.ConnectionString = connect;
        return c;
    }
        
    public IDbCommand CreateCommand(string cmdText, IDbConnection 
                                    connection) {
        IDbCommand c;
        c = (IDbCommand)Activator.CreateInstance(_comType[(int)_pType], 
                                                 false);
        c.CommandText = cmdText;
        c.Connection = connection;
        return c;
    }
}