I happened to see dapper a few days ago. Because I haven't used it before, I only used ef core. I looked at it a little, and then wrote some simple reusable packages.

The usage of Dapper is closer to ADO Net, so the performance is also relatively fast. So let's take a look at using ADO Net, how do we realize the encapsulation of code reuse.

1, ADO Net package case

Using reflection to ADO Net, see the code:
DBHelper.cs: mysql is used here. If you want to use sqlserver to replace mysql connection with SqlConnection.
This is relatively simple to write. If there is complex sql, it may not be supported. Two packages are required to read the code of the configuration file:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using MySqlConnector;

namespace DB.Model
{
    public class DBHelper
    {
        private static IConfiguration Configuration { get; set; }
        private static readonly string ConnStr = null;
        private static MySqlConnection conn;
        static DBHelper()
        {
            //ReloadOnChange = true when Appsettings Reload when JSON is modified            
            Configuration = new ConfigurationBuilder()
            .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true }).Build();
            ConnStr = Configuration.GetConnectionString("MySql");
        }
        public static void Init()
        {
            if (conn==null)
            {
                conn = new MySqlConnection(ConnStr);
                conn.Open();
            }
            else if (conn.State== ConnectionState.Closed)
            {
                conn.Open();
            }
            else if(conn.State==ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }
        /// <summary>
        ///Check all
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<T> GetDataAll<T>()
        {
            Init();
            Type type = typeof(T);
            //Use type Name instead of table name
            string sql = $"select * from {type.Name}";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            List<T> Data = new List<T>();
            MySqlDataReader reader= cmd.ExecuteReader();
            while (reader.Read())
            {
                object obj = Activator.CreateInstance(type);
                foreach (PropertyInfo property in type.GetProperties())
                {
                    property.SetValue(obj,reader[property.Name]);
                }
                Data.Add((T)obj);
            }
            reader.Close();
            conn.Close();
            return Data;
        }
        /// <summary>
        ///Query by id
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public static T GetDataById<T>(int id)
        {
            Init();
            Type type = typeof(T);
            string sql = $"select * from {type.Name} where id={id}";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            object obj = Activator.CreateInstance(type);
            while (reader.Read())
            {
                foreach (PropertyInfo property in type.GetProperties())
                {
                    property.SetValue(obj,reader[property.Name]);
                }
            }
            reader.Close();
            conn.Close();
            return (T) obj;
        }
        /// <summary>
        ///Add data to a single
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int Add<T>(T t)
        {
            Init();
            Type type = t.GetType();
            Func<PropertyInfo, object> f = (x) =>
            {
                if (x.GetValue(t).GetType().Equals(typeof(string)))
                {
                    return $"'{x.GetValue(t)}'";
                }
                else
                {
                    return x.GetValue(t);
                }
            };
            string sql = $"insert into {type.Name} " +
                         $"({string.Join(",", type.GetProperties().Select(n => $"`{n.Name}`"))}) " +
                         $"values({string.Join(",", type.GetProperties().Select(n => $"{f(n)}"))})";
            MySqlCommand cmd = new MySqlCommand(sql,conn);
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }
    }
}

2, Dapper native encapsulation

The dapper framework writes some extension methods for IDBConnection, and the underlying reflection implements the relational mapping of objects. When we use it, we only need to be able to use generics.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;

namespace Model
{
    public class DapperHelper
    {
        static DapperHelper()
        {
            //ReloadOnChange = true when Appsettings Reload when JSON is modified   
            _dbConnection = new SqlConnection();
            _dbConnection.ConnectionString = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json").Build().GetConnectionString("Default");
        }

        private static readonly IDbConnection _dbConnection;

        #region Query
        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            return await _dbConnection.QueryFirstOrDefaultAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
        public T QueryFirstOrDefault<T>(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            return _dbConnection.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
        }
        public async Task<List<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            return (await _dbConnection.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType)).ToList();
        }
        public List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return _dbConnection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
        }
        #endregion

        #region Excute
        public Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            return _dbConnection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
        }
        public int Execute(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            return _dbConnection.Execute(sql, param, transaction, commandTimeout, commandType);
        }


        #endregion
    }
}

use:

using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace dapperDemo
{
    class Program
    {
        static async Task Main(string[] args)
        {
            #region native dapper encapsulation
            DapperHelper dapper = new DapperHelper();

            #region query collection

            Console.WriteLine("-----------------Query set-------------------");
            var students = await dapper.QueryAsync<Student>("select * from Student");
            Console.WriteLine(JsonConvert.SerializeObject(students));
            #endregion

            #region single query
            Console.WriteLine("-----------------Single query-------------------");
            string sql = "select * from student where StudentName=@StudentName";
            var stu = await dapper.QueryFirstOrDefaultAsync<Student>(sql, new { StudentName = "Little Red Riding Hood" });
            Console.WriteLine(JsonConvert.SerializeObject(stu));
            #endregion

            #region new
            Console.WriteLine("-----------------newly added-------------------");
            Student student = new Student()
            {
                Id = Guid.NewGuid(),
                StudentName = "Little Red Riding Hood",
                Sex = SexType.Male,
                CreateTime = DateTime.Now,
                IsDelete = false,
                Birthday = DateTime.Now
            };
            string excuteSql = "insert into student(id,studentname,sex,createtime,isdelete,birthday)" +
                               " values(@Id,@StudentName,@Sex,@CreateTime,@IsDelete,@Birthday)";
            var result = await dapper.ExecuteAsync(excuteSql, student);
            Console.WriteLine(result);
            #endregion

            #region delete
            Console.WriteLine("-----------------delete-------------------");
            string deleteSql = "delete from student where studentname=@studentName";
            var result = await dapper.ExecuteAsync(deleteSql, new {studentName = "Little Red Riding Hood"});
            Console.WriteLine($"result:{result}");
            #endregion

            #region modification
            Console.WriteLine("-----------------modify-------------------");
            string updateSql = "update student set studentname=@NewStudentName where studentName=@OldStudentName";
            var result = await dapper.ExecuteAsync(updateSql, new {NewStudentName = "Du Fu", OldStudentName = "Li Bai" });
            Console.WriteLine($"result:{result}");
            #endregion

            #endregion native dapper encapsulation

        }
    }
}

3, Dapper's secondary packaging (based on the previous one)

Secondary encapsulation of dapper using reflection: dapper super helper CS, by inheriting the encapsulation of dapper, we can use both the original encapsulation and the general addition, deletion, modification and query of a single table.

The package can realize the addition, deletion, modification and query of a single table and paging query. The modification, deletion and query are based on id query, and id supports any type.

Note: when modifying, it is recommended to find out the data first and then modify it, because the currently encapsulated code cannot change only one field, temporarily all changes, and the required items must have values. If it is not required, no value is passed, that is, it is null.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace Model
{
    public class DapperSuperHelper<T>:DapperHelper
    {
        /// <summary>
        ///Query all
        /// </summary>
        /// <returns></returns>
        public async Task<List<T>> GetAllAsync()
        {
            var sql = $"select * from {typeof(T).Name}";
            return await QueryAsync<T>(sql);
        }
        public List<T> GetAll()
        {
            var sql = $"select * from {typeof(T).Name}";
            return Query<T>(sql);
        }
        /// <summary>
        ///Query by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Get(object id)
        {
            var sql = $"select * from {typeof(T).Name} where id=@Id";
            return QueryFirstOrDefault<T>(sql, new {Id = id});
        }

        public async Task<T> GetAsync(object id)
        {
            var sql = $"select * from {typeof(T).Name} where id=@Id";
            return await QueryFirstOrDefaultAsync<T>(sql, new { Id = id });
        }
        /// <summary>
        ///Add
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<int> InsertAsync(T t)
        {
            Type type = t.GetType();
            var sql = $"insert into {type.Name}" +
                      $"({string.Join(",",type.GetProperties().Select(n=>n.Name))})"+
                      $" values({string.Join(",",type.GetProperties().Select(n=>$"@{n.Name}"))})";
            return await ExecuteAsync(sql,t);
        }
        public int Insert(T t)
        {
            Type type = t.GetType();
            var sql = $"insert into {type.Name}" +
                      $"({string.Join(",", type.GetProperties().Select(n => n.Name))})" +
                      $" values({string.Join(",", type.GetProperties().Select(n => $"@{n.Name}"))})";
            return Execute(sql, t);
        }
        /// <summary>
        ///Modification
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(T t)
        {
            Type type = t.GetType();
            var sql = $"update {type.Name} set " +
                      $"{string.Join(",", type.GetProperties().Select(n => $"{n.Name}=@{n.Name}"))} " +
                      $"where id=@Id";
            return await ExecuteAsync(sql,t);
        }
        public int Update(T t)
        {
            Type type = t.GetType();
            var sql = $"update {type.Name} set " +
                      $"{string.Join(",", type.GetProperties().Select(n => $"{n.Name}=@{n.Name}"))} " +
                      $"where id=@Id";
            return Execute(sql, t);
        }
        /// <summary>
        ///Delete by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(object id)
        {
            var sql = $"delete from {typeof(T).Name} where id=@Id";
            return await ExecuteAsync(sql, new { Id = id }) > 0;
        }

        public bool Delete(object id)
        {
            var sql = $"delete from {typeof(T).Name} where id=@Id";
            return Execute(sql, new { Id = id }) > 0;
        }
        /// <summary>
        ///Delete all
        /// </summary>
        /// <returns></returns>
        public async Task<bool> DeleteAllAsync()
        {
            var sql = $"delete from {typeof(T).Name}";
            return await ExecuteAsync(sql) > 0;
        }

        public bool DeleteAll()
        {
            var sql = $"delete from {typeof(T).Name}";
            return Execute(sql) > 0;
        }
        /// <summary>
        ///Single table paging query
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<List<T>> GetPagedAsync(int pageIndex,int pageSize)
        {
            var skipRow = (pageIndex - 1) * pageSize;
            var sql = $"select * from {typeof(T).Name} order by Id " +
                      $"offset @skipRow rows fetch next @PageSize rows only";
            return await QueryAsync<T>(sql, new {skipRow, pageSize});
        }
        /// <summary>
        ///Single table paging query
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<T> GetPaged(int pageIndex, int pageSize)
        {
            var skipRow = (pageIndex - 1) * pageSize;
            var sql = $"select * from {typeof(T).Name} order by Id " +
                      $"offset @skipRow rows fetch next @PageSize rows only";
            return Query<T>(sql, new { skipRow, pageSize });
        }
    }
}

use:

using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace dapperDemo
{
    class Program
    {
        static async Task Main(string[] args)
        {
            #region dapper secondary packaging
            DapperSuperHelper<Student> superDapper = new DapperSuperHelper<Student>();
            //Query all
            List<Student> students = await superDapper.GetAllAsync();
            Console.WriteLine(JsonConvert.SerializeObject(students));

            //Query by id
            var id = Guid.Parse("b2847592-90d6-40a4-b3b0-c1ebf514e258");
            var stu = await superDapper.GetAsync(id);
            Console.WriteLine(JsonConvert.SerializeObject(stu));

            //newly added
            Student entity = new Student()
            {
                Id = Guid.NewGuid(),
                Birthday = DateTime.Now,
                CreateTime = DateTime.Now,
                Email = "hello@163.com",
                IsDelete = false,
                Sex = SexType.Female,
                StudentName = "Summer flower"
            };
            var result = await superDapper.InsertAsync(entity);
            Console.WriteLine($"New results, number of rows affected:{result}");

            //modify
            entity.StudentName = "young swallow";
            var updateResult = await superDapper.UpdateAsync(entity);
            Console.WriteLine($"Number of rows affected by modification result:{updateResult}");

            //delete
            var did = Guid.Parse("b2847592-90d6-40a4-b3b0-c1ebf514e258");
            var deleteResult = await superDapper.DeleteAsync(did);
            Console.WriteLine($"Delete result:{deleteResult}");

            //Paging query
            var pagedResult = await superDapper.GetPagedAsync(2, 2);
            Console.WriteLine(JsonConvert.SerializeObject(pagedResult));
            #endregion

        }
    }
}

4, Dapper Official Expansion Pack

The principle is similar to my secondary encapsulation through reflection, but it is certainly safer, more standardized and more complex than what I wrote. However, I looked at the official id and it seems that it only supports int type.

You can see that the secondary packaging above has the same functions as the official packaging, but I also have one more page, hehe.

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading.Tasks;
using Dapper.Contrib.Extensions;
using Microsoft.Extensions.Configuration;

namespace Model
{
    public class DapperExtHelper<T> where T:class
    {
        static DapperExtHelper()
        {
            _dbConnection = new SqlConnection();
            _dbConnection.ConnectionString = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json").Build().GetConnectionString("Default");
        }

        private static readonly IDbConnection _dbConnection;

        public List<T> GetAll()
        {
            return _dbConnection.GetAll<T>().ToList();
        }
        public async Task<List<T>> GetAllAsync()
        {
            return (await _dbConnection.GetAllAsync<T>()).ToList();
        }
        public T Get(int id)
        {
            return _dbConnection.Get<T>(id);
        }

        public bool Update(T entity)
        {
            return _dbConnection.Update(entity);
        }

        public async Task<bool> UpdateAsync(T entity)
        {
            return await _dbConnection.UpdateAsync(entity);
        }
        public long Insert(T entity)
        {
            return _dbConnection.Insert(entity);
        }
        public async Task<long> InsertAsync(T entity)
        {
            return await _dbConnection.InsertAsync(entity);
        }

        public bool Delete(T entity)
        {
            return _dbConnection.Delete(entity);
        }
        public async Task<bool> DeleteAsync(T entity)
        {
            return await _dbConnection.DeleteAsync(entity);
        }
        public bool DeleteAll()
        {
            return _dbConnection.DeleteAll<T>();
        }
        public async Task<bool> DeleteAllAsync()
        {
            return await _dbConnection.DeleteAllAsync<T>();
        }
    }
}

use:

using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace dapperDemo
{
    class Program
    {
        static async Task Main(string[] args)
        {

            #region dapper's official expansion package
            //Check all. The official default is to add an s after the class name. We'd better specify [Table("Student")]
            DapperExtHelper<Student> dapperExtHelper = new DapperExtHelper<Student>();
            var students = dapperExtHelper.GetAll();
            Console.WriteLine(JsonConvert.SerializeObject(students));
            //id query only supports int
            //var stu = dapperExtHelper.Get(1);
            //Delete all
            //dapperExtHelper.DeleteAll();
            //delete
            var delEntity = new Student()
            {
                Id = Guid.Parse("c066dfce-d7cd-46b5-9fa3-d0aa4b165dde")
            };
            //dapperExtHelper.Delete(delEntity);
            //To modify all fields, you need to pass the required parameters, otherwise an error will be reported, and the parameters that have not been transmitted will be modified to be empty. Therefore, it is best to find out the current entity first, and then modify the individual fields
            var updEntity = new Student()
            {
                Id = Guid.Parse("b2847592-90d6-40a4-b3b0-c1ebf514e257"),
                StudentName = "Li Bai 222",
                CreateTime = DateTime.Now,
                Birthday = DateTime.Now
            };
            dapperExtHelper.Update(updEntity);
            //An error is reported when adding this. The id cannot be passed to null, which is outrageous. It is estimated that it is related to the type
            Student InsertEntity = new Student()
            {
                Id = Guid.NewGuid(),
                Birthday = DateTime.Now,
                CreateTime = DateTime.Now,
                Email = "hello@163.com",
                IsDelete = false,
                Sex = SexType.Female,
                StudentName = "Summer flower"
            };
            dapperExtHelper.Insert(InsertEntity);



            #endregion
        }
    }
}

The above is my simple encapsulation of dapper. You can also package it yourself.