Design a generic interface to connect to variety of database types

Question

I need to create a service that would be able to

• Get data from a table in defined format from all specified connection strings.
• Connect to variety of databases types.

• I imagined it would look like this

  interface IDataConnectorModule { Model PullData() } All I need to do when I'll support new database type to implement the interface for new class lets say, OracleConnector, MySQLConnector,... and then use them in runtime to retrieve data.

• What is the best API's to achieve this? Taking the performance into consideration.

• ODBC, OLE DB or whatever?

Answer 1

Depending on which database you are connecting you should use different API. For Sql Server, juste use the SQLConnection with SQL Adapter. It is the fastest for SQL Server. I have never used ODP.Net but as it is developped by Oracle I would use this one for querying Oracle Database.

ODBC and OLEdb both adds a layer that is going to slow your queries. So use as much "specialized" connectors to your database. This will be more efficient.