We're creating a fairly serious application that must remain agnostic towards the DB a customer really wants to use. Initially we intend on supporting MySQL, Oracle &lifier SQL Server. The tables &lifier sights are pretty straight forward much like the queries (no real fancy SQL), and so the question:

  1. Use native DB motorists (MySQLDbConnection etc.) and encapsulate the logic of performing queries and processing results or
  2. Make use of a generic OleDbConnection

Clearly option 2 involves no overhead, however i supposing the performance isn't as great just like native access?

IMHO utilizing an ORM is a great design decision to be able to possess a database agnostic application. Switching database may be as simple as altering a config setting and connection string.

You do not need OleDbConnection to gain access to nonspecific ADO.Internet companies. Only use DbConnection et. al. See [cde] on MSDN for more information.

Note: This response is relevant if you choose to use fundamental ADO.Internet 2 functionality rather than an ORM (Entity Framework, NHibernate) or LINQ to SQL.


Let us assume there is a connection string defined inside your DbProviderFactories:

app.config

Notice the existence of the <connectionStrings> <add name="SomeConnection" providerName="System.Data.SqlClient" connectionString="..." /> </connectionStrings> attribute and it is value. (You might include a value for an additional DB provider, e.g. providerName.)

Now, you are able to use the required database inside a completely provider-agnostic fashion the following:

System.Data.SQLite

Note how this code is only for interface types. The only real place in which you indicate a specific DB provider is thru the using System.Configuration; // for ConfigurationManager using System.Data; // for all interface types using System.Data.Common; // for DbProviderFactories var cs = ConfigurationManager.ConnectionStrings["SomeConnection"]; // ^^^^^^^^^^^^^^^^ var factory = DbProviderFactories.GetFactory(cs.ProviderName); // ^^^^^^^^^^^^^^^ using (IDbConnection connection = factory.CreateConnection()) { connection.ConnectionString = cs.ConnectionString; // ^^^^^^^^^^^^^^^^^^^ connection.Open(); try { using (IDbCommand command = connection.CreateCommand()) { ... // do something with the database } } finally { connection.Close(); } } attribute value within the providerName file. (I have marked all of the places in which a setting from app.config is taken with app.configs.)

LINQ is really a highly regarded as .Internet ORM, partially because technology-not only and saved methods. Issue is, it's SQL Server only but individuals are trying to provide similar functionality for Oracle &lifier MySQL.

For database &lifier query optimizations, I cringe thinking of utilizing an ORM. Data types, functions &lifier overall syntax are not so portable in SQL. Probably the most performant way of getting together with each database is to tailor the model &lifier queries to them, however it means expertise, money and time. If necessary, concentrate on one database vendor using the code setup to aid vendor swap out &lifier add support for other databases as necessary.