what is the best approach to make an application to work with multiple ado.net providers?

We develop a set of industrial applications that use SQL Server.

As demand expands, customers want to use our applications with their own RDBMS开发者_StackOverflow such as Oracle, MySQL and others.

Currently, some applications uses OLEDB provider, and other uses native SQLServer for various reasons, including programming experience and religion.

We seek a unified approach. The project manager prefers OLEDB because "it works with everything." Personally, I hate it because how the queries parameters are handled...

I have two solutions in mind :

The first would be to use SQLOLEDB keeping the existing code and adapt each SQL statement in case of incompatibility by using branch instructions. It would go very quickly, the SQL would not differ that much and it would please the project manager. However, it might turn code to spaghetti.

The second would be to use the native ADO.NET provider for each RDBMS, and to develop a library of data access for each. Each library might contain a common part to avoid code duplication. Of course it would take some time, but it will lead to a clean architecture and optimal performances.

It's mainly client server applications. Some parts of our database are dynamically generated, and there is a lot of dynamic queries. That's why using an ORM is not possible.

How would you try to achieve the same objective?

---

I would do one of two things:

• Use the common features between them all, in code this would mean using IDbConnection etc.
• In your app, create a set of interfaces onto the DAL and then have a DAL implementation for each supported database.

If you can get away with it, go the lowest-common-denominator route. However, it may sound like you need to take advantage of database-specific features. If so, then your best route to cleanly do this in code is to talk to a set of interfaces instead of a specific DAL.

Just make sure your interfaces are well enough defined to reduce changes later on and also offer enough openings to support all the necessary databases.

It is then just a case of having a factory to provide the concrete implementation for a given interface.

---

In your library, ensure you are only using the base classes in the System.Data.Common namespace.

So, instead of SqlCommand, use DbCommand etc.

You can inject the actual implementation to the DAL.

public class MyDal
{
  private cmd DbCommand;

  public MyDal(DbCommand command)
  {
      cmd = command;
  }
}

Alternatively, write your own abstraction (interface/abstract class), write implementation classes for it that wrap the different provider implementation and use that.