There are tons of different frameworks out there from very low level impementations on top of System.Data.Common to very heavy feature rich frameworks like EF.
Most of these frameworks are not MySQL specific, since ADO.NET offers a good abstraction level that is independent from the underlying database (except some SQL dialect specific details).
As a staring point I would recommand EF since it is well documentated
http://entityframework.codeplex.com/
and Dapper (Stackoverflow itself runs on Dapper afaik)
https://code.google.com/p/dapper-dot-net/
which does not support CRUD operations out of the box, but there are frameworks on top of it.
https://github.com/tmsmith/Dapper-Extensions
Update
Just a quick introducion to ADO.NET
the basic concept of every ADO.NET driver is
- create a connection object
- open connection
- create a command object
- execute command
That means every DatabaseName ADO.NET provider has a DatabaseNameConnection, DatabaseNameCommand (and some other).
this is some very basic sample code to retrive data from a mysql database
private DataTable GetUsers()
{
using (var conn = new MySqlConnection("Server=localhost;Database=mysql;Uid=root"))
using (var cmd = new MySqlCommand("SELECT * FROM user", conn))
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
var table = new DataTable();
table.Load(reader);
return table;
}
}
}
this is totaly hard wired to mysql because we are using MySqlConnection and MySqlCommand but what is really mysql specific here? Only the connection string and the raw sql query. But let me rewrite it, so I can get data from every database with an ADO.net provider.
A little more code but provider independant
public void SomeMethod()
{
var helper = new DbHelper("MySql.Data.MySqlClient", "Server=localhost;Database=mysql;Uid=root");
var users = helper.ExecuteDataTable("SELECT * FROM users");
}
public class DbHelper
{
private string providerName;
private string connectionString;
public DbHelper(string providerName, string connectionString)
{
this.providerName = providerName;
this.connectionString = connectionString;
}
private IDbConnection CreateConnection()
{
var factory = DbProviderFactories.GetFactory(providerName);
var conn = factory.CreateConnection();
conn.ConnectionString = connectionString;
conn.Open();
return conn;
}
private IDbCommand CreateCommand(IDbConnection conn, string commandText)
{
var cmd = conn.CreateCommand();
cmd.CommandText = commandText;
return cmd;
}
private DataTable ExecuteDataTable(IDataReader reader)
{
var table = new DataTable();
table.Load(reader);
return table;
}
public DataTable ExecuteDataTable(string commandText)
{
using (var conn = CreateConnection())
using (var cmd = CreateCommand(conn, commandText))
{
return ExecuteDataTable(cmd.ExecuteReader());
}
}
}
This works if you have MySQL Connector/NET installed because it registers itself as a DbProvider machine wide, you don't even need a reference to MySql.Data.dll in your project.
If you don't want to install Connector/NET you have to
- add a reference to MySql.Data.dll
- add some lines to your app.config/web.config file
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description="Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data" />
</DbProviderFactories>
Now the call to DbProviderFactories.GetFactory(providerName); will give you a MySql specific DbProviderFactory implementation.
Most DAL / ORM Frameworks use this concept. Eventually they don't have to support MySQL in the first place (except if they generate sql commands which is database specific). Entity Framework makes a difference since it requires the provider itself to support it, that's why you need MySql.Data.Entities, too.
Just a quick hint. Everything I talked about is available via nuget.
