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

Question

I need an example of source code in C #, which is an abbreviation of databases (it is easy to change from one database to another with minimal code modification). Do you know some examples of websites or textbooks with good quality?

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

My specific requirements and emerging issues are as follows:
1. The abstraction of the classes used when accessing data.
2. Using parameters when calling stored procedures. On Microsoft SQL Server @ excellent. Other databases do not support @.
3. Convert query syntax from one database to another. Do we have some kind of "general" query, and then make some classes that generate queries against the target database?
4. Strictly typed data sets at the data access level. From my experience, I remember that the MySQL TableAdapter and Query wizard for Visual Studio failed for MySQL.

Thank you in advance for your experience and time.

Answer 1

Take a look

• LLBLGEN (commercial)
• NHibernate (free)
• MyGeneration (free)
• NetTiers (view free)

and other ORM

Answer 2

Although I highly recommend NHibernate , you can also look at the Data Access Microsoft Enterprise Library application block.

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

Answer 3

The best way to get close to this is to use the interfaces provided by database providers; they are almost parallel to functionality. Create a static factory class to create interfaces for command adapters, data adapters, and connections based on a customized database. 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 will not know the difference, although it will need to pass a configuration string. Use VS docs to learn the interfaces given by each of the objects that you usually use, and stick to them and it will be quite simple - although you may have to hack your way through a couple of things.