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:
- Use native DB motorists (MySQLDbConnection etc.) and encapsulate the logic of performing queries and processing results or
- 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
Notice the existence of the
attribute and it is value. (You might include a value for an additional DB provider, e.g.
Now, you are able to use the required database inside a completely provider-agnostic fashion the following:
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;
using (IDbCommand command = connection.CreateCommand())
... // do something with the database
attribute value within the
providerName file. (I have marked all of the places in which a setting from
app.config is taken with
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.