Dapper的封裝、二次封裝、官方擴充套件包封裝，以及ADO.NET原生封裝

不想只會CURD的猿某人發表於2022-01-03

前幾天偶然看到了dapper，由於以前沒有用過，只用過ef core，稍微看了一下，然後寫了一些簡單的可複用的封裝。

Dapper的用法比較接近ADO.NET所以效能也是比較快。所以我們先來看看使用ADO.NET的時候我們怎麼實現程式碼複用的封裝。

一、ADO.NET的封裝案例

利用反射對ADO.NET進行封裝，看程式碼：
DBHelper.cs:這邊用的是mysql，如果要用sqlserver將MySqlConnection換成SqlConnection即可。
這個寫的比較簡單，如果有複雜的sql可能就支援不了了。讀取配置檔案的程式碼需要用到兩個包：

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 當appsettings.json被修改時重新載入            
            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>
        /// 查全部
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<T> GetDataAll<T>()
        {
            Init();
            Type type = typeof(T);
            //用type.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>
        /// 按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>
        /// 單條新增資料
        /// </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;
        }
    }
}

二、Dapper原生封裝

dapper框架是給IDBConnection寫了一些擴充套件方法，底層還是反射實現物件的關係對映。我們使用的時候只需要會用泛型即可。

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 當appsettings.json被修改時重新載入   
            _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
    }
}

使用：

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封裝
            DapperHelper dapper = new DapperHelper();

            #region 查詢集合

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

            #region 單個查詢
            Console.WriteLine("-----------------單個查詢-------------------");
            string sql = "select * from student where StudentName=@StudentName";
            var stu = await dapper.QueryFirstOrDefaultAsync<Student>(sql, new { StudentName = "小紅帽" });
            Console.WriteLine(JsonConvert.SerializeObject(stu));
            #endregion

            #region 新增
            Console.WriteLine("-----------------新增-------------------");
            Student student = new Student()
            {
                Id = Guid.NewGuid(),
                StudentName = "小紅帽",
                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 刪除
            Console.WriteLine("-----------------刪除-------------------");
            string deleteSql = "delete from student where studentname=@studentName";
            var result = await dapper.ExecuteAsync(deleteSql, new {studentName = "小紅帽"});
            Console.WriteLine($"結果：{result}");
            #endregion

            #region 修改
            Console.WriteLine("-----------------修改-------------------");
            string updateSql = "update student set studentname=@NewStudentName where studentName=@OldStudentName";
            var result = await dapper.ExecuteAsync(updateSql, new {NewStudentName = "杜甫", OldStudentName = "李白" });
            Console.WriteLine($"結果：{result}");
            #endregion

            #endregion 原生dapper封裝

        }
    }
}

三、Dapper的二次封裝（基於上一個）

利用反射對Dapper進行二次封裝：DapperSuperHelper.cs，通過繼承繼承dapper的封裝，我們可以即可用使用原始封裝又可以使用通用的對單表的增刪改查。

該封裝可以實現對單個表的增刪改查，以及分頁查詢，修改、刪除、查詢都是基於id查詢，id支援任意型別。

注意：修改的時候建議先查出資料再執行修改，因為目前封裝的程式碼無法實現只更改某個欄位，暫時是全部更改，必填項必須有值。非必填，不傳值，即為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>
        /// 查詢所有
        /// </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>
        /// 按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>
        /// 新增
        /// </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>
        /// 修改
        /// </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>
        /// 按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>
        /// 刪除全部
        /// </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>
        /// 單表分頁查詢
        /// </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>
        /// 單表分頁查詢
        /// </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 });
        }
    }
}

使用：

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二次封裝
            DapperSuperHelper<Student> superDapper = new DapperSuperHelper<Student>();
            //查詢所有
            List<Student> students = await superDapper.GetAllAsync();
            Console.WriteLine(JsonConvert.SerializeObject(students));

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

            //新增
            Student entity = new Student()
            {
                Id = Guid.NewGuid(),
                Birthday = DateTime.Now,
                CreateTime = DateTime.Now,
                Email = "hello@163.com",
                IsDelete = false,
                Sex = SexType.Female,
                StudentName = "夏花"
            };
            var result = await superDapper.InsertAsync(entity);
            Console.WriteLine($"新增結果，受影響的行數：{result}");

            //修改
            entity.StudentName = "小燕子";
            var updateResult = await superDapper.UpdateAsync(entity);
            Console.WriteLine($"修改結果，受影響的行數：{updateResult}");

            //刪除
            var did = Guid.Parse("b2847592-90d6-40a4-b3b0-c1ebf514e258");
            var deleteResult = await superDapper.DeleteAsync(did);
            Console.WriteLine($"刪除結果：{deleteResult}");

            //分頁查詢
            var pagedResult = await superDapper.GetPagedAsync(2, 2);
            Console.WriteLine(JsonConvert.SerializeObject(pagedResult));
            #endregion

        }
    }
}

四、Dapper官方提供的擴充套件包

原理和我通過反射進行的二次封裝差不多，但是肯定比我寫的更安全和規範、更復雜，不過官方的id我看了一下，好像只支援int型別。

可以看到我上面的二次封裝和官方的封裝其實差不多的功能，不過我還多了一個分頁，嘿嘿。

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>();
        }
    }
}

使用：

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，我們最好指定[Table("Student")]
            DapperExtHelper<Student> dapperExtHelper = new DapperExtHelper<Student>();
            var students = dapperExtHelper.GetAll();
            Console.WriteLine(JsonConvert.SerializeObject(students));
            //id查詢 只支援id為int
            //var stu = dapperExtHelper.Get(1);
            //刪除全部
            //dapperExtHelper.DeleteAll();
            //刪除
            var delEntity = new Student()
            {
                Id = Guid.Parse("c066dfce-d7cd-46b5-9fa3-d0aa4b165dde")
            };
            //dapperExtHelper.Delete(delEntity);
            //修改 全部欄位修改，需要傳遞必填的引數，否則報錯，未傳的引數修改為空，因此最好是先查出當實體，再修改單獨欄位
            var updEntity = new Student()
            {
                Id = Guid.Parse("b2847592-90d6-40a4-b3b0-c1ebf514e257"),
                StudentName = "李白222",
                CreateTime = DateTime.Now,
                Birthday = DateTime.Now
            };
            dapperExtHelper.Update(updEntity);
            //新增 這居然報錯，id不能傳null，就離譜，估計和型別有關
            Student InsertEntity = new Student()
            {
                Id = Guid.NewGuid(),
                Birthday = DateTime.Now,
                CreateTime = DateTime.Now,
                Email = "hello@163.com",
                IsDelete = false,
                Sex = SexType.Female,
                StudentName = "夏花"
            };
            dapperExtHelper.Insert(InsertEntity);



            #endregion
        }
    }
}

以上就是我對dapper的簡單的封裝。大家也可以自行封裝。

【封裝那些事】缺失封裝
2018-05-15
封裝
ajax原生js封裝
2020-07-19
JS封裝
原生js封裝Ajax
2020-10-03
JS封裝
【封裝小技巧】is 系列方法的封裝
2022-05-07
封裝
【JavaScript框架封裝】公共框架的封裝
2018-07-15
JavaScript框架封裝
Swift 核心動畫面向協議擴充套件封裝
2019-01-31
Swift動畫協議套件封裝
封裝
2024-08-01
封裝
WPF如何封裝一個可擴充套件的Window
2024-03-31
封裝套件
Sqlite封裝1-基本封裝-SqliteTool
2017-08-02
SQLite封裝
原生Ajax封裝隨筆
2013-10-19
封裝
Flutter 封裝：富文字 RichText 極簡封裝
2021-07-31
Flutter封裝
Flutter Dio二次封裝
2020-01-08
Flutter封裝
axios二次封裝
2019-02-16
iOS封裝
二次封裝WebDriverWait
2024-08-26
封裝WebAI
Java 封裝 SDK 以及使用
2021-07-16
Java封裝
iOS 封裝.framework 以及使用
2018-04-21
iOS封裝Framework
AVPlayer封裝
2019-03-05
封裝
ajax 封裝
2019-04-13
封裝
封裝OCX
2022-01-16
封裝
封裝介面
2018-11-07
封裝
sqlite封裝
2020-12-04
SQLite封裝
java封裝
2018-02-04
Java封裝
java 封裝
2018-04-12
Java封裝
EventBus封裝
2017-05-12
封裝
FMDB封裝
2017-11-24
封裝
websocket封裝
2018-01-12
Web封裝
5.1封裝
2015-04-30
封裝
09 #### 封裝
2024-09-27
封裝
axios的二次封裝
2020-12-24
iOS封裝
RxRetrofit - 終極封裝 - 深入淺出 & 擴充套件 String
2016-12-27
封裝套件
【封裝小技巧】列表處理函式的封裝
2022-05-14
封裝函式
Swift keychain 官方封裝Demo
2018-03-07
SwiftAI封裝
我的第一個擴充套件包釋出啦-封裝明道雲API
2022-01-04
套件封裝API
原生JS封裝AJAX請求
2017-12-14
JS封裝
【封裝小技巧】數字處理函式的封裝
2022-06-28
封裝函式
封裝JDBC—非框架開發必備的封裝類
2017-06-07
封裝JDBC框架
elment dialog二次封裝
2020-11-06
封裝
OpenResty Redis操作二次封裝
2022-08-29
RESTRedis封裝

Dapper的封裝、二次封裝、官方擴充套件包封裝，以及ADO.NET原生封裝

一、ADO.NET的封裝案例

二、Dapper原生封裝

三、Dapper的二次封裝（基於上一個）

四、Dapper官方提供的擴充套件包

相關文章