.NetCore 3.1 Dapper

欠伸展肢体，吟咏心自愉。这篇文章主要讲述.NetCore 3.1 Dapper相关的知识，希望能为你提供帮助。
常用功能的简单封装

public class Dapper { public string ConnectionString { get; set; }public Dapper() { var Configuration = new ConfigurationBuilder() .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true }) .Build(); ConnectionString = Configuration["ConnectionStrings:mysql"]; }public async Task< bool> InsertAsync< T> (T t) where T : class { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.InsertAsync(t) > 0; } }public async Task< bool> InsertAsync< T> (List< T> list) where T : class { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.InsertAsync(list) > 0; } }public async Task< bool> DeleteAsync< T> (T t) where T : class { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.DeleteAsync(t); } }public async Task< bool> UpdateAsync< T> (T t) where T : class { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.UpdateAsync(t); } }public async Task< IEnumerable< T> > GetAllAsync< T> () where T : class { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.GetAllAsync< T> (); } }public async Task< T> GetByIDAsync< T> (int id) where T : class { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.GetAsync< T> (id); } }public async Task< int> ExecuteAsync(string path) { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { using (StreamReader streamReader = new StreamReader(path, System.Text.Encoding.UTF8)) { var script = await streamReader.ReadToEndAsync(); return await connection.ExecuteAsync(script); } } }public async Task< int> ExecuteAsync(string sql, object param = null) { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.ExecuteAsync(sql, param); } }public async Task< bool> ExecuteAsyncTransaction(List< string> list) { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { connection.Open(); IDbTransaction transaction = connection.BeginTransaction(); try { foreach (var sql in list) { await connection.ExecuteAsync(sql, null, transaction); }transaction.Commit(); return true; } catch (Exception e) { transaction.Rollback(); return false; } } }public async Task< bool> ExecuteAsyncTransaction(List< KeyValuePair< string, object> > list) { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { connection.Open(); IDbTransaction transaction = connection.BeginTransaction(); try { foreach (var item in list) { await connection.ExecuteAsync(item.Key, item.Value, transaction); }transaction.Commit(); return true; } catch (Exception e) { transaction.Rollback(); return false; } } }public async Task< IEnumerable< dynamic> > QueryAsync(string sql, object param = null) { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.QueryAsync(sql, param); } }public async Task< dynamic> QueryFirstOrDefaultAsync(string sql, object param = null) { using (IDbConnection connection = new MySqlConnection(ConnectionString)) { return await connection.QueryFirstOrDefaultAsync(sql, param); } } }

调用

Dapper dapper = new Dapper(); Student student = new Student() { sid = 1, sname = "张三", sage = 20, ssex = "男" }; List< Student> students = new List< Student> () { new Student(){sid = 1, sname = "张三", sage = 20,ssex = "男"}, new Student(){sid = 2, sname = "李思思", sage = 23,ssex = "女"}, new Student(){sid = 3, sname = "王五", sage = 27,ssex = "男"} }; //增加 await dapper.InsertAsync< Student> (student); await dapper.InsertAsync< Student> (students); //修改 / 删除 1、以[ExplicitKey] /[key]为条件 await dapper.UpdateAsync(new Student() { sid = 1, sname = "修改", sage = 20, ssex = "男" }); await dapper.DeleteAsync(new Student() { sid = 1 }); //修改 / 删除 2、直接写sql，灵活、简单 await dapper.ExecuteAsync(@"update student set sid=0 where sname=@sname or sage=@sage; ", new { sname = "李思思", sage = 27 }); await dapper.ExecuteAsync(@"delete from student where sname=@sname or sage=@sage; ", new { sname = "李思思", sage = 27 }); //简单事务: 无参数 List< string> list1 = new List< string> () { "insert into student(sid,sname) values(‘1‘,‘1‘)", "insert into teacher(tid,tname) values(‘1‘,‘1‘)" }; await dapper.ExecuteAsyncTransaction(list1); //简单事务: 有参数 List< KeyValuePair< string, object> > list2 = new List< KeyValuePair< string, object> > (); list2.Add(new KeyValuePair< string, object> ( "insert into student(sid,sname) values(@sid,@sname)", new { sid = 22, sname = "test1" })); list2.Add(new KeyValuePair< string, object> ( "insert into teacher(tid,tname) values(@tid,@tname)", new { tid = 33, tname = "test2" })); await dapper.ExecuteAsyncTransaction(list2); //查询 var result1 = dapper.GetAllAsync< Student> (); var result2 = dapper.GetByIDAsync< Student> (1); var result3 = dapper.QueryAsync(@"SELECT s.sid AS ‘学号‘, s.sname AS ‘姓名‘, COUNT(sc.cid) AS ‘课程数目‘, SUM(sc.score) AS ‘总分数‘ FROM student s, sc sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname");

.net core 3.1 webapi + swagger + dapper demo
【.NetCore 3.1 Dapper】demo中对SqlConnection的创建做了封装处理，demo参考资料 https://blog.csdn.net/qazlj/article/details/87283155