DB abstraction

[moko]

If we had to build a data layer to abstract the RDBMS used in an ASP.Net
application,
so that we call a common set of ADO.Net classes, instead of SQLClient
classes
or OleDB classes, there are three ways i know of :-

1) Use a switch to use SQLClient classes if DB is SQL Server, or use
OleDb classes for non-SQL servers.

2) Use the IDbCommand , IDbConnection, IDataAdapter interfaces which
abstract the RDBMS,
and help us to use a higher level of ADO.Net classes.

3) Use OleDB classes for all types of RDBMS, so that there is no need to
use SQLClient or OleDB, depending on DB server.

I feel option 1 is best, where we use a switch mechanism, whereby we get the
best of both worlds.
We get the optimization of ADO.Net when we use SQL server, and use the
normal
channel when we use non-SQL RDBMS.

Which would be best practise , or recommended approach ?

 

[Miha Markic]

Hi,

Actually 2 in combination with 1 is the best IMO.
You would employ a factory pattern and access the instances via interfaces.