How do I connect to ANY database from C#?

Question

Is it possible to build database-agnostic code in C# that will work against either SQL Server or MySQL, depending only on connection string?

I guess I'm looking for something that will throw SQL at a database server, and return a very simplistic set of rows/columns. This database could be...SQL, MySQL, Access, Filemaker, whatever. It seems that all the databases have their own drivers and objects libraries. SqlConnection vs. MySqlConnection, etc.

I know about OBDC and the OdbcConnection, which would theoretically do what I want, but I don't want to have to be creating ODBC connections on the server all the time either.

Am I asking for too much here?

Answer 1

Have a look at NHibernate, LLBLGEN, MyGeneration, NetTiers and other ORMs.

They may allow you to do some sort of abstraction you need. Some are free, some charge.

I personally like NHibernate.

You could do above with interfaces like gleng says:

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 ();
        }
    }

Answer 2

Yes. It's called using interfaces and Dependency Injection. There are many articles and tutorials online that you can find for setting this stuff up.

The idea is you write your own implementation based off an interface. You can then swap these out without having to change any of your code by registering the one you want to use in your IoC container.