C# Database abstraction for Microsoft SQL Server, Oracle, MySQL and DB2

Question

I need an example C# source code that abstracts databases (easy to change from one database to another with minimal code modification). Do you know some sample sites or tutorials with good quality?

Specific target databases are as follows:
1. Microsoft SQL Server
2. Oracle
3. MySQL
3. DB2

My specific requirements and encountered issues are the following:
1. Abstraction of classes used in data access.
2. Using parameters in calling stored procedures. In Microsoft SQL Server @ is fine. Other databases does not support @.
3. Converting query syntax from one database to another. Do we have some sort of "generic" query and then make some classes that generates queries it to a target database?
4. Strictly typed datasets in the data access layer. I remember from experience that the TableAdapter and Query wizard of Visual Studio failed for MySQL.

Thank you in advance for your expertise and time.

Answer 1

Have a look at

• LLBLGEN (commercial)
• NHibernate (free)
• MyGeneration (free)
• NetTiers (kind of free)

and other ORMs

Answer 2

Look at NHibernate site: https://www.hibernate.org/343.html

Answer 3

Although I highly recommend NHibernate, you may also want to look at the Data Access application block of Microsoft's Enterprise Library.

Of course, any ORM should provide the functionality you need.

Answer 4

The best way to approach this is to use the interfaces provided by the database providers; they are nearly parallel in functionality. Create a static factory class to create interfaces for Command adapters, data adapters, and connections, based on the database which is configured. For instance:

 public static IDbDataAdapter GetDataAdapter (Database db)
 {
  switch (db)
  {
   default:
   case "MsSql":
    return new SqlDataAdapter ();
   case "MySql"
    return new MySqlDataAdapter ();
  }
 }

 public static IDbCommand GetCommand (Database db)
 {
  switch (db)
  {
   default:
   case "MsSql":
    return new SqlCommand ();
   case "MySql"
    return new MySqlCommand ();
  }
 }

Your client code won't know the difference, though it will have to pass the configuration string around. Use VS docs to examine the interfaces given by each of the objects you normally use, and stick to using those, and it will be pretty straightforward - though you may have to hack your way through a couple of things.