欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.NetCore 3.1 Dapper

程序员文章站 2022-05-17 08:47:37
常用功能的封装 public class Dapper { public string ConnectionString { get; set; } public Dapper() { var Configuration = new ConfigurationBuilder() .Add(new JsonConfigurationSource { Path = "appsettings....

常用功能的封装

    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");

相关资料

  1. demo==> .net core 3.1 webapi + swagger + dapper
  2. demo中对SqlConnection的创建做了封装处理,可连接多个数据库
    demo参考资料 https://blog.csdn.net/qazlj/article/details/87283155

本文地址:https://blog.csdn.net/qq_42991184/article/details/107293343