Database abstraction layer in C# for MySQL?

I am trying to use MySQL 5 with C#. I downloaded MySQL driver on mysql.com and installed it. I can now connect to MySQL in C# with the following code.

    string ConString = "SERVER=192.168.10.104;";
    ConString += "DATABASE=test;";
    ConString += "UID=user;";
    ConString += "PASSWORD=password;";

    MySqlConnection connection = new MySqlConnection(ConString);

    MySqlCommand command = connection.CreateCommand();
    MySqlDataReader Reader;
    command.CommandText = "select * from j_people";
    connection.Open();

    Reader = command.ExecuteReader();

The problem is that what if I change the database server to MS SQL Server or Oracle later?

Isn't there a database abstraction layer in C#?

I guess it would b开发者_运维问答e ADO.NET, but I can't seem to find an practical example of ADO.NET with MySQL.

Add an abstraction yourself... Create an interface with method names for data that you need to retrieve/add/delete/update and implement the interface with a class that uses the MySQL driver. If you need to later use a different connector (MS SQL Server or Oracle) you will just have to implement the interface for the different connector. This makes the data access layer self contained and the implementation hidden from the rest of your code.

You can implement this interface with any technology you want: LINQ or NHibernate or ADO, etc. This is the only layer that needs to be touched if the database management system changes.

public interface IDataLayer
{
    IList<Data> GetData();
    void SaveData(Data dataToSave);
    void DeleteData (Data dataToDelete);
}

public class MySqlDataLayer : IDataLayer
{
    public IList<Data> GetData()
    {
        //Use MySQL connector to get data.
        return data;
    }

    public void SaveData(Data dataToSave)
    {
        //Use MySQL connector to save to data.
    }

    public void DeleteData(Data dataToDelete)
    {
        //Use MySQL connector to delete passed in data.
    }
}

You can use a Linq to SQL provider. The BCL's one dosen't support MySQL, so you can use a Linq to SQL provider for MySQL. You can use the Entity Framework or NHibernate too.

You may also checkout iBatis.NET. I think learning curve for iBatis is lower than Hibernate / Linq as well as for small projects and people who are well versed with SQL, iBatis is a nice helper.

There's really two things that are important to keep in mind here:

1. You can make your specific .NET code database-agnostic by writing your code against the interfaces that the MySql libraries implement, namely IDbConnection, IDbCommand, IDataParameters, etc. Your overall strategy is to isolate the points where you use the MySql-specific classes to as few places as possible (ideally, it would only be in factory methods which can later be expanded to utilize Oracle, SQL Server or another RDBMS.

2. Once your .NET code is database-agnostic, the SQL code you're passing in must be as well. In an ideal world, all RDBMSes would speak in a common language, but various "flavours" of SQL exist with the different providers. Simple SELECTS, INSERTS and UPDATES might be possible, but you'll run into a wall and need to use a vendor-specific feature fairly early on. This is where you might consider a technology like an ORM - otherwise, you will need to introduce some sort of branching to provide specific SQL for each database vendor that you support.

Old article but the concepts may be useful: http://www.informit.com/articles/printerfriendly.aspx?p=25318

Also, you may want to consider Entity Framework: Using MySQL with Entity Framework

The classes in the MySQL .NET Connector API implement the platform-agnostic interfaces defined in the System.Data namespace (IDbConnection, IDbCommand etc.)

You could use the Gateway pattern to hide vendor-specific data access API details behind an abstraction layer. You could then delegate creation of instances of your Gateway to an IoC container like Castle, StructureMap etc. This would allow you to swap between different data access strategies without having to refactor your code.