Search code examples
c#.netodbcoledb

Design a generic interface to connect to variety of database types

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?
Solution

  • 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.