需要引入包：
Dapper Dapper.Contrib MySql.Data
接口：
public interface IMySqlDapperContext : IDisposable
{
/// <summary>
/// MySql连接对象
/// </summary>
IDbConnection MySqlConnection { get; }
}
/// <summary>
/// 数据库CRUD操作
/// </summary>
/// <typeparam name="T"></typeparam>
public interface IMySqlRepository<T> where T : class
{
/// <summary>
/// 连接
/// </summary>
IDbConnection Connection { get; }
/// <summary>
/// 上下文
/// </summary>
IMySqlDapperContext Context { get; }
/// <summary>
/// 根据主键获取数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="key"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
T GetByKey<T>(object key, IDbTransaction transaction = null, int? commandTimeout = null) where T : class;
/// <summary>
/// 添加数据
/// </summary>
/// <param name="entity"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
long Add(T entity, IDbTransaction transaction = null, int? commandTimeout = null);
/// <summary>
///
/// </summary>
/// <param name="entity"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
void Update(T entity, IDbTransaction transaction = null, int? commandTimeout = null);
/// <summary>
/// 删除单条数据
/// </summary>
/// <param name="entity"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
void Remove(T entity, IDbTransaction transaction = null, int? commandTimeout = null);
/// <summary>
/// 根据主键获取数据
/// </summary>
/// <param name="key"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
T GetByKey(object key, IDbTransaction transaction = null, int? commandTimeout = null);
/// <summary>
/// 获取所有数据
/// </summary>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
IEnumerable<T> GetAll(IDbTransaction transaction = null, int? commandTimeout = null);
/// <summary>
/// 根据条件获取数据列表
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
IEnumerable<T> GetBy(object where = null, object order = null, IDbTransaction transaction = null, int? commandTimeout = null);
IEnumerable<T> GetBy<T>(object sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class;
T QuerySingleOrDefault<T>(string sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class;
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="sql"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
IEnumerable<dynamic> Query(string sql, IDbTransaction transaction = null, int? commandTimeout = null);
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="sql"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class;
/// <summary>
/// 查询数据列表
/// </summary>
/// <typeparam name="TFirst"></typeparam>
/// <typeparam name="TSecond"></typeparam>
/// <typeparam name="TReturn"></typeparam>
/// <param name="sql"></param>
/// <param name="map"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 查询数据列表
/// </summary>
/// <typeparam name="TFirst"></typeparam>
/// <typeparam name="TSecond"></typeparam>
/// <typeparam name="TThird"></typeparam>
/// <typeparam name="TFourth"></typeparam>
/// <typeparam name="TReturn"></typeparam>
/// <param name="sql"></param>
/// <param name="map"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="splitOn"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null, IDbTransaction transaction = null, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
/// <summary>
/// 执行sql
/// </summary>
/// <param name="sql"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
int Excute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null);
/// <summary>
/// 返回单个参数
/// </summary>
/// <param name="sql"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
object ExcuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null);
}
实现：
/// <summary>
/// 连接字符串
/// </summary>
private string ConnectionString;
/// <summary>
/// 连接
/// </summary>
private IDbConnection Connection;
/// <summary>
/// 构造函数注入IConfiguration
/// </summary>
/// <param name="configuration"></param>
public MySqlDapperContext(IConfiguration configuration)
{
ConnectionString = configuration["AppSetting:MySqlConnectionString"];
}
public IDbConnection MySqlConnection {
get {
if (Connection == null || Connection.State == ConnectionState.Closed)
{
Connection = new MySqlConnection(ConnectionString);
}
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
return Connection;
}
}
/// <summary>
/// 释放链接
/// </summary>
public void Dispose()
{
if (Connection != null && Connection.State == ConnectionState.Open)
{
Connection.Close();
}
}
}
public class MySqlRepository<T> : IMySqlRepository<T> where T : class
{
/// <summary>
/// 存储appsetting.json里的数据库连接字符串
/// </summary>
private string ConnectionString;
public IMySqlDapperContext Context { get; private set; }
public IDbConnection Connection
{
get
{
return Context.MySqlConnection;
}
}
public MySqlRepository(IMySqlDapperContext context)
{
Context = context;
ConnectionString = context.MySqlConnection.ConnectionString;
}
public virtual T GetByKey<T>(object id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
{
if (id == null)
{
throw new ArgumentNullException("id");
}
return Connection.Get<T>(id, transaction: transaction, commandTimeout: commandTimeout);
}
public long Add(T entity, IDbTransaction transaction = null, int? commandTimeout = null)
{
if (entity == null)
{
throw new ArgumentNullException("entity", "Add to DB null entity");
}
var res = Connection.Insert(entity, transaction: transaction, commandTimeout: commandTimeout);
return res;
}
public virtual void Update(T entity, IDbTransaction transaction = null, int? commandTimeout = null)
{
if (entity == null)
{
throw new ArgumentNullException("entity", "Update in DB null entity");
}
Connection.Update(entity, transaction: transaction, commandTimeout: commandTimeout);
}
public virtual void Remove(T entity, IDbTransaction transaction = null, int? commandTimeout = null)
{
if (entity == null)
{
throw new ArgumentNullException("entity", "Remove in DB null entity");
}
Connection.Delete(entity, transaction: transaction, commandTimeout: commandTimeout);
}
public virtual T GetByKey(object id, IDbTransaction transaction = null, int? commandTimeout = null)
{
if (id == null)
{
throw new ArgumentNullException("id");
}
return Connection.Get<T>(id, transaction: transaction, commandTimeout: commandTimeout);
}
public virtual IEnumerable<T> GetAll(IDbTransaction transaction = null, int? commandTimeout = null)
{
return Connection.GetAll<T>(transaction: transaction, commandTimeout: commandTimeout);
}
public virtual T QuerySingleOrDefault<T>(string sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
{
return Connection.QuerySingleOrDefault<T>(sql, param, transaction: transaction, commandTimeout: commandTimeout);
}
public virtual IEnumerable<T> GetBy(object where = null, object order = null, IDbTransaction transaction = null, int? commandTimeout = null)
{
return Connection.Query<T>(where.ToString(), commandTimeout: commandTimeout);
}
public virtual IEnumerable<T> GetBy<T>(object sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
{
return Connection.Query<T>(sql.ToString(), param: param, transaction: transaction, commandTimeout: commandTimeout);
}
public IEnumerable<dynamic> Query(string sql, IDbTransaction transaction = null, int? commandTimeout = null)
{
return Connection.Query<dynamic>(sql);
}
public IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
{
return Connection.Query<T>(sql, param: param, transaction: transaction);
}
public IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
return Connection.Query<TFirst, TSecond, TReturn>(sql, map, param: param, transaction: transaction, splitOn: splitOn, commandType: commandType);
}
public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
return Connection.Query<TFirst, TSecond, TThird, TReturn>(sql, map, param: param, transaction: transaction, splitOn: splitOn, commandType: commandType);
}
public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null, IDbTransaction transaction = null, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
return Connection.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sql, map, param: param, transaction: transaction, splitOn: splitOn, commandType: commandType);
}
public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
return Connection.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sql, map, param: param, transaction: transaction, splitOn: splitOn, commandType: commandType);
}
public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
return Connection.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(sql, map, param: param, transaction: transaction, splitOn: splitOn, commandType: commandType);
}
public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
return Connection.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(sql, map, param: param, transaction: transaction, splitOn: splitOn, commandType: commandType);
}
public int Excute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
{
return Connection.Execute(sql, param: param, transaction: transaction);
}
public object ExcuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
{
return Connection.ExecuteScalar(sql, param: param, transaction: transaction, commandTimeout: commandTimeout);
}
public GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
{
return Connection.QueryMultiple(sql, param: param, transaction: transaction, commandTimeout: commandTimeout);
}
}