Dapper 和 Dapper.Contrib 的使用示例
1. Dapper 的安装
工具 => NuGet包管理器，浏览 nuget 搜索 Dapper，完成dapper的安装
2. Dapper的优势
- 轻量级，速度快
- 支持多种数据库，如MSSQL,MYSQL,SQLLite等
- 支持多种映射关系
- 性能高
3. Dapper 的常用方法封装
/// <summary>
/// 数据库操作类
/// </summary>
public class DapperRepository
{
//static string connStrRead = ConfigurationManager.ConnectionStrings["Read"].ConnectionString;
//static string connStrWrite = ConfigurationManager.ConnectionStrings["Write"].ConnectionString;
static int commandTimeout = 30;
public static IDbConnection GetConnection(bool useWriteConn)
{
if (useWriteConn)
return new SqlConnection(PubConstant.ConnectionString);
return new SqlConnection(PubConstant.ConnectionString);
}
public static SqlConnection GetOpenConnection()
{
var conn = new SqlConnection(PubConstant.ConnectionString);
if (conn.State != ConnectionState.Open)
conn.Open();
return conn;
}
/// <summary>
/// 执行sql返回一个对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="useWriteConn"></param>
/// <returns></returns>
public static T QueryFirstOrDefault<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
{
if (transaction == null)
{
using (IDbConnection conn = GetConnection(useWriteConn))
{
return conn.QueryFirstOrDefault<T>(sql, param, commandTimeout: commandTimeout);
}
}
else
{
var conn = transaction.Connection;
return conn.QueryFirstOrDefault<T>(sql, param, commandTimeout: commandTimeout, transaction: transaction);
}
}
/// <summary>
/// 执行sql返回多个对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="useWriteConn"></param>
/// <returns></returns>
public static List<T> QueryGetList<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
{
using (IDbConnection conn = GetConnection(useWriteConn))
{
conn.Open();
return conn.Query<T>(sql, param, commandTimeout: commandTimeout, transaction: transaction).ToList();
}
}
/// <summary>
/// 执行sql返回一个对象--异步
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="useWriteConn"></param>
/// <returns></returns>
public static async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, bool useWriteConn = false)
{
using (IDbConnection conn = GetConnection(useWriteConn))
{
conn.Open();
return await conn.QueryFirstOrDefaultAsync<T>(sql, param, commandTimeout: commandTimeout).ConfigureAwait(false);
}
}
/// <summary>
/// 执行sql返回多个对象--异步
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="useWriteConn"></param>
/// <returns></returns>
public static async Task<List<T>> ExecuteReaderRetListAsync<T>(string sql, object param = null, bool useWriteConn = false)
{
using (IDbConnection conn = GetConnection(useWriteConn))
{
conn.Open();
var list = await conn.QueryAsync<T>(sql, param, commandTimeout: commandTimeout).ConfigureAwait(false);
return list.ToList();
}
}
/// <summary>
/// 执行sql，返回影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static int ExecuteSqlInt(string sql, object param = null, IDbTransaction transaction = null)
{
if (transaction == null)
{
using (IDbConnection conn = GetOpenConnection())
{
return conn.Execute(sql, param, commandTimeout: commandTimeout, commandType: CommandType.Text);
}
}
else
{
var conn = transaction.Connection;
return conn.Execute(sql, param, transaction: transaction, commandTimeout: commandTimeout, commandType: CommandType.Text);
}
}
/// <summary>
/// 执行sql，返回影响行数--异步
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static async Task<int> ExecuteSqlIntAsync(string sql, object param = null, IDbTransaction transaction = null)
{
if (transaction == null)
{
using (IDbConnection conn = GetOpenConnection())
{
return await conn.ExecuteAsync(sql, param, commandTimeout: commandTimeout, commandType: CommandType.Text).ConfigureAwait(false);
}
}
else
{
var conn = transaction.Connection;
return await conn.ExecuteAsync(sql, param, transaction: transaction, commandTimeout: commandTimeout, commandType: CommandType.Text).ConfigureAwait(false);
}
}
/// <summary>
/// 执行sql，返回数量
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static T ExecuteScalar<T>(string sql,object param = null,IDbTransaction transaction =null)
{
if (transaction == null)
{
using (IDbConnection conn = GetOpenConnection())
{
var res = conn.ExecuteScalar<dynamic>(sql, param);
Type type = typeof(T);
if (type == typeof(ValueType) || type == typeof(Int32) || type == typeof(Decimal))
return res ?? 0;
else if (type == typeof(String))
return res == null ? "" : Convert.ToString(res);
else
return res ?? default(T);
}
}
else
{
var conn = transaction.Connection;
var res = conn.ExecuteScalar<dynamic>(sql, param,transaction);
Type type = typeof(T);
if (type == typeof(ValueType) || type == typeof(Int32) || type == typeof(Decimal))
return res ?? 0;
else if (type == typeof(String))
return res == null ? "" : Convert.ToString(res);
else
return res ?? default(T);
}
}
public static dynamic ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null)
{
if (transaction == null)
{
using (IDbConnection conn = GetOpenConnection())
{
var res = conn.ExecuteScalar<dynamic>(sql, param);
return res ?? null;
}
}
else
{
var conn = transaction.Connection;
var res = conn.ExecuteScalar<dynamic>(sql, param, transaction);
return res ?? null;
}
}
/// <summary>
/// 根据id获取实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <param name="transaction"></param>
/// <param name="useWriteConn"></param>
/// <returns></returns>
public static T GetById<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
{
if (transaction == null)
{
using (IDbConnection conn = GetConnection(useWriteConn))
{
return conn.Get<T>(id, transaction: transaction, commandTimeout: commandTimeout);
}
}
else
{
var conn = transaction.Connection;
return conn.Get<T>(id, transaction: transaction, commandTimeout: commandTimeout);
}
}
/// <summary>
/// 根据id获取实体--异步
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <param name="transaction"></param>
/// <param name="useWriteConn"></param>
/// <returns></returns>
public static async Task<T> GetByIdAsync<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
{
if (transaction == null)
{
using (IDbConnection conn = GetConnection(useWriteConn))
{
return await conn.GetAsync<T>(id, commandTimeout: commandTimeout);
}
}
else
{
var conn = transaction.Connection;
return await conn.GetAsync<T>(id, transaction: transaction, commandTimeout: commandTimeout);
}
}
/// <summary>
/// 获取实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <param name="sort"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="buffered"></param>
/// <returns></returns>
public static T GetModel<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
{
using (var conn = GetOpenConnection())
{
return conn.QueryFirst<T>(sql, param, transaction, commandTimeout);
}
}
/// <summary>
/// 插入实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static bool Insert<T>(T item, IDbTransaction transaction = null) where T : class
{
string table = SqlBuilderHelper.GetTableName<T>();
var sql = SqlBuilderHelper.CreateInsertSql<T>(table, null);
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
var res = int.Parse(conn.Execute(sql, item, transaction, commandTimeout: commandTimeout).ToString());
//var res = conn.Insert<T>(item, commandTimeout: commandTimeout);
return res > 0;
}
}
else
{
var conn = transaction.Connection;
var res = int.Parse(conn.Execute(sql, item, transaction, commandTimeout: commandTimeout).ToString());
return res > 0;
//return conn.Insert(item, transaction: transaction, commandTimeout: commandTimeout) > 0;
}
}
/// <summary>
/// 插入实体返回Id
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="isReturnId"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static int Insert<T>(T item, bool isReturnId, IDbTransaction transaction = null) where T : class
{
if (!isReturnId)
{
Insert<T>(item, transaction);
return 0;
}
string table = SqlBuilderHelper.GetTableName<T>();
var sql = SqlBuilderHelper.CreateInsertSql<T>(table, null);
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
//var res = int.Parse(conn.ExecuteScalar(sql, item, transaction, commandTimeout: commandTimeout).ToString());
var res = (int)conn.Insert<T>(item, commandTimeout: commandTimeout);
return res;
}
}
else
{
var conn = transaction.Connection;
var res = (int)conn.Insert<T>(item, transaction: transaction, commandTimeout: commandTimeout);
//var res = int.Parse(conn.ExecuteScalar(sql, item, transaction, commandTimeout: commandTimeout).ToString());
return res;
}
}
/// <summary>
/// 批量插入实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="transaction"></param>
public static bool BulkInsert<T>(IEnumerable<T> list, SqlTransaction transaction = null) where T : class
{
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
var res = conn.Insert(list, commandTimeout: commandTimeout);
return true;
//conn.BulkInsert(list, transaction);
}
}
else
{
var conn = transaction.Connection;
//conn.BulkInsert(list, transaction: transaction);
var res = conn.Insert(list, transaction: transaction, commandTimeout: commandTimeout);
return true;
}
}
/// <summary>
/// 批量新增事务
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="T2"></typeparam>
/// <param name="enetiy"></param>
/// <param name="entityItems"></param>
/// <returns></returns>
public static bool BulkInsertTrans<T, T2>(T enetiy, List<T2> entityItems)
where T : class, new()
where T2 : class, new()
{
using (var conn = GetOpenConnection())
{
var trans = conn.BeginTransaction("SampleTransaction");
try
{
string tTable = SqlBuilderHelper.GetTableName<T>();
string sqlStr = SqlBuilderHelper.CreateInsertSql<T>(tTable);
conn.Execute(sqlStr, enetiy, trans, null, null);
string t2Table = SqlBuilderHelper.GetTableName<T2>();
//写入子表
for (int i = 0; i < entityItems.Count; i++)
{
string sqlItemStr = SqlBuilderHelper.CreateInsertSql<T2>(t2Table);
conn.Execute(sqlItemStr, entityItems[i], trans, null, CommandType.Text);
}
//conn.Insert(entityItems, trans);
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
return false;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// 更新单个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static bool Update<T>(T item, IDbTransaction transaction = null) where T : class
{
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
return conn.Update(item, commandTimeout: commandTimeout);
}
}
else
{
var conn = transaction.Connection;
return conn.Update(item, transaction: transaction, commandTimeout: commandTimeout);
}
}
/// <summary>
/// 批量更新实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static bool BulkUpdate<T>(List<T> item, IDbTransaction transaction = null) where T : class
{
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
return conn.Update(item, commandTimeout: commandTimeout);
}
}
else
{
var conn = transaction.Connection;
return conn.Update(item, transaction: transaction, commandTimeout: commandTimeout);
}
}
/// <summary>
/// 批量修改事务
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="T2"></typeparam>
/// <param name="enetiy"></param>
/// <param name="entityItems"></param>
/// <returns></returns>
public static bool BulkUpdateTrans<T, T2>(T enetiy, List<T2> entityItems)
where T : class, new()
where T2 : class, new()
{
using (var conn = GetOpenConnection())
{
IDbTransaction trans = conn.BeginTransaction("SampleTransaction");
try
{
string tTable = SqlBuilderHelper.GetTableName<T>();
string sqlStr = SqlBuilderHelper.CreateUpdateSql<T>(tTable, " ID=@ID");
conn.Execute(sqlStr, enetiy, trans, null, null);
string t2Table = SqlBuilderHelper.GetTableName<T2>();
//写入子表
for (int i = 0; i < entityItems.Count; i++)
{
string sqlItemStr = SqlBuilderHelper.CreateUpdateSql<T2>(t2Table, " ID=@ID");
conn.Execute(sqlItemStr, entityItems[i], trans, null, CommandType.Text);
}
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
return false;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// 删除单个实体(sql)
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static bool Delete(string sql, object param = null, IDbTransaction transaction = null)
{
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
return ExecuteSqlInt(sql, param) > 0;
}
}
else
{
var conn = transaction.Connection;
return ExecuteSqlInt(sql, param, transaction) > 0;
}
}
/// <summary>
/// 删除单个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static bool Delete<T>(T item, IDbTransaction transaction = null) where T : class
{
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
return conn.Delete(item, commandTimeout: commandTimeout);
}
}
else
{
var conn = transaction.Connection;
return conn.Delete(item, transaction: transaction, commandTimeout: commandTimeout);
}
}
/// <summary>
/// 批量删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="items"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static bool BulkDelete<T>(List<T> items, IDbTransaction transaction = null) where T : class
{
if (transaction == null)
{
using (var conn = GetOpenConnection())
{
return conn.Delete(items, commandTimeout: commandTimeout);
}
}
else
{
var conn = transaction.Connection;
return conn.Delete(items, transaction: transaction, commandTimeout: commandTimeout);
}
}
//示例：
//无参查询
//var qqModeList = conn.Query<UserModel>("select Id,Name,Count from User");
//带参查询 var qqModeList = conn.Query<UserModel>("select Id,Name,Count from User where Id in @id and Count>@count", new { id = new int[] { 1, 2, 3}, count = 1 });
/// <summary>
/// 根据sql获取实体列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="predicate"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="buffered"></param>
/// <returns></returns>
public static List<T> GetList<T>(string sql, object param = null, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
{
using (IDbConnection conn = GetOpenConnection())
{
return conn.Query<T>(sql, param, transaction, buffered, commandTimeout).ToList();
}
}
/// <summary>
/// 获取实体IEnumerable列表（查询全部,慎用）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="buffered"></param>
/// <returns></returns>
//public static List<T> GetList<T>(IDbTransaction transaction = null, int? commandTimeout = null) where T : class
//{
// using (IDbConnection conn = GetOpenConnection())
// {
// return conn.GetAll<T>(transaction, commandTimeout).ToList();
// }
//}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">主sql 不带 order by</param>
/// <param name="sort">排序内容 id desc，add_time asc</param>
/// <param name="pageIndex">第几页</param>
/// <param name="pageSize">每页多少条</param>
/// <param name="useWriteConn">是否主库</param>
/// <returns></returns>
public static List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, bool useWriteConn = false, object param = null)
{
string pageSql = @"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) _row_number_,* FROM
({2})temp )temp1 WHERE temp1._row_number_>{3} ORDER BY _row_number_";
string execSql = string.Format(pageSql, pageSize, sort, sql, pageSize * (pageIndex - 1));
using (var conn = GetOpenConnection())
{
return conn.Query<T>(execSql, param, commandTimeout: commandTimeout).ToList();
}
}
/// <summary>
/// 分页查询(返回总个数)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="sort"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <param name="param"></param>
/// <returns></returns>
public static List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, out int total, object param = null)
{
string pageSql = @"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) _row_number_,* FROM
({2})temp )temp1 WHERE temp1._row_number_>{3} ORDER BY _row_number_";
string execSql = string.Format(pageSql, pageSize, sort, sql, pageSize * (pageIndex - 1));
using (var conn = GetOpenConnection())
{
var query = conn.Query<T>(execSql, param, commandTimeout: commandTimeout);
total = query.Count();
return query.ToList();
}
}
}
测试示例：
/// <summary>
/// dapper 使用示例
/// </summary>
public void DapperTest()
{
try
{
//1.查询
//var res4 = SqlDapperHelper.QueryFirstOrDefault<Order>("select *
from Order", $" OrderID='15202116354864929452'");
//单个查询
var model = DapperRepository.GetById<OrderModel>(2);
var model2 = DapperRepository.GetModel<OrderModel>($"select * from
Order where Id={2}");
var query1 =
DapperRepository.QueryFirstOrDefault<OrderModel>("select * from Order", $"
OrderID='152021452'");
//条件查询单个
var query2 =
DapperRepository.QueryFirstOrDefault<OrderModel>("select * from Order where
OrderID=@orderId and remark = @remark", new { orderId = "152021452",
remark = "测试" });
//无参查询
//var qqModeList = conn.Query<UserModel>("select Id,Name,Count from
User");
//带参查询 var qqModeList = conn.Query<UserModel>("select
Id,Name,Count from User where Id in @id and Count>@count", new { id = new int[] {
1, 2, 3}, count = 1 });
string sqlStr = "select * from Order where ID in @id and
TotalPrice = @totalPrice";
var paramStr = new { id = new int[3] { 1, 2, 3 },
totalprice = 1000 };
var list = DapperRepository.GetList<OrderModel>(sqlStr, paramStr);
//2.条件查询
//IList<ISort> sort = new List<ISort>
// {
// new Sort { PropertyName = "CreateTime", Ascending = false }
// };
//var list1 = SqlDapperHelper.GetList<OrderModel>(new { OrderID =
"15202152", UserID = 1 }, sort).ToList();
////3.orm 拼接条件 查询 繁琐 不灵活 不太好用
//IList<IPredicate> predList = new List<IPredicate>();
//predList.Add(Predicates.Field<OrderModel>(p => p.OrderID,
Operator.Like, "4929452%"));
//predList.Add(Predicates.Field<OrderModel>(p => p.UserID,
Operator.Eq, 1));
//IPredicateGroup predGroup = Predicates.Group(GroupOperator.And,
predList.ToArray());
//var list = SqlDapperHelper.GetList<OrderModel>(predGroup);
string orderId = "1520251";
OrderModel order = new OrderModel
{
OrderID = orderId,
CreateTime = DateTime.Now,
remark = "测试"
};
//2.新增
int res = DapperRepository.Insert<OrderModel>(order, true);
//bool res = SqlDapperHelper.Insert<OrderModel>(order);
//3.修改
var orderEntity = DapperRepository.GetById<OrderModel>(res);
orderEntity.remark = "test";
bool res2 = DapperRepository.Update<OrderModel>(orderEntity);
////4.删除
order.OrderType = -1;// Domain.OrderTypeEnum.AbandonedOrders;
bool res3 = DapperRepository.Update<OrderModel>(orderEntity);
//bool res4 = DapperRepository.Delete<OrderModel>(orderEntity);
////5.其他
List<OrderModel> OrderModels = new List<OrderModel>();
order.remark = "123";
OrderModels.Add(order);
bool res5 =
DapperRepository.BulkUpdate<OrderModel>(OrderModels);
//orderId
orderId = "152021449";
List<OrderModel> orderModels2 = new List<OrderModel>();
order.remark = "123 BulkInsert";
order.OrderID = orderId;
order.ID = 0;
orderModels2.Add(order);
DapperRepository.BulkInsert<OrderModel>(orderModels2);
order.remark = "123456 BulkInsertTrans";
order.ID = 0;
List<OrderItemModel> OrderModels3 = new
List<OrderItemModel>();
for (var i = 0; i < 2; i++)
{
OrderItemModel OrderItem = new OrderItemModel
{
UserID = 7972,//用户ID
OrderID = orderId,
ProductID = 10,//产品ID
Price = 2700,//销售单价
ToNum = 0,//原料数量
};
OrderModels3.Add(OrderItem);
}
var res6 = DapperRepository.BulkInsertTrans<OrderModel,
OrderItemModel>(order, OrderModels3);
//批量删除测试
bool res7 =
DapperRepository.BulkDelete<OrderItemModel>(OrderModels3);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}