Database abstraction layer in C# for MySQL?

Question

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 be ADO.NET, but I can't seem to find an practical example of ADO.NET with MySQL.

Answer 1

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.
    }
}