C# multi-database project?

Question

Database

I'm getting acquainted with C# and faced the following task, which I don't know how to solve.
There are many different Objects, each with a finite set of fields that are known in advance. It is necessary to organize support for storing each such Object in different databases (oracle, mysql, firebird, interbase and postgres). In them, it can be stored in different ways, up to the fact that its different parts will be in different tables, the names of the fields and tables will be different.
(storage support - in the sense that the software should be able to read records from the selected database, change, delete them)
Maybe I ask a little chaotically, along the way, if necessary, I will clarify. But, can anyone advise the Right Way, Best Practice, suggest how it can be implemented correctly and beautifully?

Answer 1

For the Entity Framework, this situation is standard, except that the visual editor does not support it. We create one conceptual model, create a storage model for each database, and for each database create our own mapping of the storage model to the conceptual one.

Answer 2

If to use ORM the answer is obvious. You will use the tools built into them, as well as struggle with their limitations.
In principle, the answer is this: highlight the classic DAL - Data Access Layer. There are many standard patterns for this. For example:

• Data Mapper (+ Repository )
• Active Record
• And then Query Object

The simplest is Data Mapper. Create an interface for your entity:

interface ICarData
{
  void Insert(Car car);
  Car GetById(int id);
  // и т.д.
}

Then you inherit from this interface the classes that implement it - eg MySqlCarData, MongDbCarData.
A separate task here is determining which class to use. The easiest way is to set the class name through the configuration file and create an object of the desired class with a simple factory (switch(className) {… } ). More beautiful is to use an IoC / DI container.

Answer 3

Usually use Object to Relation Mapping (ORM) Frameworks. We use NHibernate (to work with MSSQL, SQLite and Mysql). It is configured using adapters, and if you do not use direct queries to the database, then LINQ over NHibernate works quite well with these databases transparently for the programmer. There are adapters for Interbase and other databases too.
In the case of “different parts in different tables”, a data access layer is needed, for example, for each storage case. This layer will return a set of required data according to the specified criteria, isolating access specifics from the programmer and making access to the database more transparent.