Use ADO. Net to create data access layer interfaces for multiple databases

Abstract. net
Framework data provides the reason for final rules and how you should abstract them. . NET Framework data provider is used within your dal. As I mentioned, the ADO. NET programming model is completely different. Net
Framework data is provided on the SQL client, oledb, and other msdn online websites. Although the design results expose specific features of the data source (such as the executexmlreader method of the sqlcommand object), it forces you to decide which vendor code pairs. In other words, developers usually choose to use sqlclient or oledb, and then write code directly to the classes in their namespaces.

If you want to change. NET framework data provider, you need to re-compile the data access method. To avoid this, you can use an abstract factory design pattern. In this mode, you can build a simple class that exposes methods to create a master. Net
Framework data provider object (commands, connections, data adapters, and parameters) based on the identified. NET Framework data provider passed to the constructor information. The code in Figure 7 shows a simple C # class version.

To use this class, you need to program the code of your data pipeline class. Net
Framework data provider implements various interfaces, including idbcommand, idbconnection, idataadapter, and idataparameter. For example, to fill in a result dataset of a stored procedure with parameters, you can use the data handler class method in the following code:

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.StoredProceduredb.SelectCommand.Parameters.Add(_pf.CreateParameter("@productId", _    DbType.Int32, id))Dim ds As New DataSet("Books")da.Fill(ds)

In general, you will declare the constructor instantiation of the data catalog class of the providerfactory variable at the class level. In addition, its constructor will populate the vendor that reads from a configuration file rather than hard encoding, as shown below. As you can imagine, providerfactory will be a great addition to your dal base class, which can then be distributed to other developers at the conference.

You can choose to take a step to encapsulate the common ado.net code, and the developers write it over and over again. In fact, Microsoft has released SQL
Server Data Access Application Block.

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