1.先建一个DbConnectionDescriptorProvider.cs 提供连接
/// <summary>
/// Db连接提供
/// </summary>
public class DbConnectionDescriptorProvider
{
//读写库连接字符串(主库）
static string readWriteConnection = "server=172.16.2.84;userid=root;password=123456;database=test_db;";
/// <summary>
/// 从库连接字符串
/// </summary>
static string readOnlyConnection = "server=172.16.2.84;userid=root;password=123456;database=test_db;";
/// <summary>
/// 获取对DB进行操作的类
/// </summary>
public static DbConnectionDescriptor DbConnectionDescriptor { get; } = getDbConnectionDescriptor();
/// <summary>
/// 从配置中获取数据库链接
/// </summary>
private static DbConnectionDescriptor getDbConnectionDescriptor()
{
return new DbConnectionDescriptor(readWriteConnection);
}
/// <summary>
/// 数据库连接字符串
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public static DbConnectionDescriptor DbConnection(DbReadWriteEnum dbType= DbReadWriteEnum.Master)
{
switch(dbType)
{
case DbReadWriteEnum.Master:
return new DbConnectionDescriptor(readWriteConnection);
case DbReadWriteEnum.Slave:
return new DbConnectionDescriptor(readOnlyConnection);
default:
return new DbConnectionDescriptor(readWriteConnection);
}
}
}
public enum DbReadWriteEnum
{
/// <summary>
/// 主
/// </summary>
Master = 1,
/// <summary>
/// 从
/// </summary>
Slave = 2
}
2.再建一个DbConnectionDescriptor.cs保存当前连接的具体库
/// <summary>
/// 连接字符串连接对像
/// </summary>
public class DbConnectionDescriptor
{
/// <summary>
/// 初始化类的新实例
/// </summary>
/// <param name="databaseType"></param>
/// <param name="connectionString"></param>
public DbConnectionDescriptor(string connectionString)
{
this.ConnectionString = connectionString;
}
/// <summary>
/// 获取连接字符串
/// </summary>
public string ConnectionString { get; }
/// <summary>
/// 获取连接字符串
/// </summary>
/// <returns></returns>
public IDbConnection GetConnection()
{
//Mysql
var conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);
//pgsql
// var conn = new Npgsql.NpgsqlConnection(this.ConnectionString);
//sqlserver
// var conn = new Microsoft.Data.SqlClient.SqlConnection(this.ConnectionString);
return conn;
}
}
3.再建一个DbConnectionDescriptorDapperExtensions.cs 扩展具体数据库操作封装
public static class DbConnectionDescriptorDapperExtensions
{
public static IDbConnection GetConn(this DbConnectionDescriptor connectionDescriptor)
{
return connectionDescriptor.GetConnection();
}
/// <summary>
/// 获取事务
/// </summary>
/// <returns></returns>
public static IDbTransaction CreateTranscation(IDbConnection conn)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn.BeginTransaction();
}
/// <summary>
/// 增删改
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public static bool ExecuteNonQuerySql(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
Func<DbConnectionDescriptor, bool> execFunc = (connDescriptor) =>
{
using (var conn = connDescriptor.GetConnection())
{
return conn.Execute(sql, param, transaction, commandTimeout, commandType) >= 0;
}
};
return ExecSql(connectionDescriptor, execFunc, sql, param);
}
/// <summary>
/// 执行返回首行首列
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public static T ExecuteScalarSql<T>(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
Func<DbConnectionDescriptor, T> execFunc = (connDescriptor) =>
{
using (var conn = connDescriptor.GetConnection())
{
return conn.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
}
};
return ExecSql(connectionDescriptor, execFunc, sql, param, transaction);
}
/// <summary>
/// 查询单条
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public static T QueryModelSql<T>(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
Func<DbConnectionDescriptor, T> execFunc = (connDescriptor) =>
{
using (var conn = connDescriptor.GetConnection())
{
return conn.Query<T>(sql, param, transaction, true, commandTimeout, commandType).FirstOrDefault<T>();
}
};
return ExecSql(connectionDescriptor, execFunc, sql, param);
}
/// <summary>
/// 查询列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public static IEnumerable<T> QueryListSql<T>(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
Func<DbConnectionDescriptor, IEnumerable<T>> execFunc = (connDescriptor) =>
{
using (var conn = connDescriptor.GetConnection())
{
return conn.Query<T>(sql, param, transaction, true, commandTimeout, commandType);
}
};
return ExecSql(connectionDescriptor, execFunc, sql, param, transaction);
}
/// <summary>
/// 执行统一操作
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="func"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
private static TResult ExecSql<TResult>(DbConnectionDescriptor connectionDescriptor, Func<DbConnectionDescriptor, TResult> func, string sql, object param = null, IDbTransaction transaction = null)
{
TResult result = default(TResult);
try
{
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
result = func.Invoke(connectionDescriptor);
stopwatch.Stop();
if (stopwatch.ElapsedMilliseconds > 1000)
{
//执行耗时超过1秒->记录日志
//todo
}
}
catch (Exception ex)
{
//错误日记记录 todo
//事务回滚
if (transaction != null)
{
transaction.Rollback();
}
throw ex;
}
finally
{
}
return result;
}
}
4.调用示例
public class DapperTest
{
/// <summary>
/// 增删改示例
/// </summary>
public void Add()
{
User user = new User()
{
user_name = "张三",
mobile = "13600000000",
email = "zhangsan@qq.com",
create_time = DateTime.Now
};
string sqlstr = @"INSERT INTO user
(user_name, mobile, email, create_time)
VALUES(@user_name, @mobile, @email, @create_time); ";
var response = DbConnectionDescriptorProvider.DbConnection().ExecuteNonQuerySql(sqlstr, user);
Console.WriteLine($"插入结果:{response}");
}
/// <summary>
/// 查询单条
/// </summary>
public void Query()
{
string sqlstr = "select * from user where id=@id";
var param = new
{
id = 1
};
var result = DbConnectionDescriptorProvider.DbConnection(DbReadWriteEnum.Slave).QueryModelSql<User>(sqlstr, param);
}
/// <summary>
/// 查询多条
/// </summary>
public void QueryList()
{
string sqlstr = "select * from user where user_name=@user_name";
var param = new
{
user_name = "张三"
};
var result = DbConnectionDescriptorProvider.DbConnection().QueryListSql<User>(sqlstr, param);
}
/// <summary>
/// 事务执行
/// </summary>
public void Transcation()
{
//创建事务
var conn = DbConnectionDescriptorProvider.DbConnection().GetConn();
var transtion = DbConnectionDescriptorDapperExtensions.CreateTranscation(conn);
//订单
Order order = new Order
{
order_no = DateTime.Now.ToString("yyyyMMddHHmmssfff"),
order_status = "待支付",
create_time = DateTime.Now,
user_id = 1
};
//插入返回Id
string sqlSaveOrder = @"INSERT INTO `order` (order_no, order_status, create_time, user_id) VALUES(@order_no, @order_status, @create_time, @user_id);SELECT LAST_INSERT_ID();";
//保存订单
int orderId = DbConnectionDescriptorProvider.DbConnection().ExecuteScalarSql<int>(sqlSaveOrder, order, transtion,conn);
//订单明细
List<OrderDetail> orderDetails = new List<OrderDetail>
{
new OrderDetail
{
order_id = orderId,
goods_name="冰箱22款",
goods_no=DateTime.Now.ToString("yyyyMMddHHmmssfff"),
goods_price=1688,
create_time=DateTime.Now
},
new OrderDetail
{
order_id=orderId,
goods_name="热水器c-102",
goods_no=DateTime.Now.ToString("yyyyMMddHHmmssfff"),
goods_price=1200,
create_time=DateTime.Now
}
};
string sqlSaveOrderDetail = @"INSERT INTO order_detail
(goods_no, goods_name, goods_price, create_time, order_id)
VALUES(@goods_no, @goods_name, @goods_price, @create_time, @order_id);";
//保存订单明细
DbConnectionDescriptorProvider.DbConnection().ExecuteNonQuerySql(sqlSaveOrderDetail, orderDetails, transtion,conn);
//事务提交
transtion.Commit();
}
}
