Dapper Packaging, Secondary Packaging, Official Extension Package Packaging, and ADO.NET Native Packaging

A few days ago I came across dapper. Since I had not used it before, I just used ef core, looked at it a little, and wrote some simple reusable encapsulations.

Dapper's usage is close to ADO.NET, so its performance is faster. So let's first look at how we can implement code reuse encapsulation when using ADO.NET.

1. ADO.NET Encapsulation Cases

Encapsulate ADO.NET using reflection to see the code:

DBHelper.cs: mysql is used here, if you want to replace MySqlConnection with SqlConnection using sqlserver.

This is easy to write and may not be supported if you have complex sql. Two packages are required to read the configuration file code:

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 Reload when appsettings.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);
            //Replace table name with 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>
        ///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 individually
        /// </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 Packaging

The dapper framework writes some extensions to IDBConnection, the bottom level or reflection to implement the relational mapping of objects. All we need to do is 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 Reload when appsettings.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 packaging
            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 Cap" });
            Console.WriteLine(JsonConvert.SerializeObject(stu));
            #endregion

            #region Added
            Console.WriteLine("-----------------Newly added-------------------");
            Student student = new Student()
            {
                Id = Guid.NewGuid(),
                StudentName = "Little Red Cap",
                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 Cap"});
            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)

Second encapsulation of Dapper using reflection: DapperSuperHelper<T>.cs

The encapsulation can be used to add, delete, and change individual tables, as well as paging queries. The modification, deletion and query are all based on id queries, and id supports any type.

Note: When modifying, it is recommended to check out the data before performing the modification, because currently encapsulated code cannot change only one field, temporarily all changes, and the required items must have values. Not required, no value passed, null.

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

namespace Model
{
    public class DapperSuperHelper<T>
    {
        static DapperSuperHelper()
        {
            _dapper = new DapperHelper();
        }

        private static DapperHelper _dapper;
        /// <summary>
        ///Query all
        /// </summary>
        /// <returns></returns>
        public async Task<List<T>> GetAllAsync()
        {
            var sql = $"select * from {typeof(T).Name}";
            return await _dapper.QueryAsync<T>(sql);
        }
        public List<T> GetAll()
        {
            var sql = $"select * from {typeof(T).Name}";
            return _dapper.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 _dapper.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 _dapper.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 _dapper.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 _dapper.Execute(sql, t);
        }
        /// <summary>
        ///Modify
        /// </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 _dapper.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 _dapper.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 _dapper.ExecuteAsync(sql, new { Id = id }) > 0;
        }

        public bool Delete(object id)
        {
            var sql = $"delete from {typeof(T).Name} where id=@Id";
            return _dapper.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 _dapper.ExecuteAsync(sql) > 0;
        }

        public bool DeleteAll()
        {
            var sql = $"delete * from {typeof(T).Name}";
            return _dapper.Execute(sql) > 0;
        }
        /// <summary>
        ///Form 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 _dapper.QueryAsync<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($"Add results, number of rows affected:{result}");

            //modify
            entity.StudentName = "Young swallow";
            var updateResult = await superDapper.UpdateAsync(entity);
            Console.WriteLine($"Modify results, number of rows affected:{updateResult}");

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

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

        }
    }
}

 

4. Extension Pack Officially Provided by Dapper  

The principle is similar to my secondary encapsulation through reflection, but certainly safer and more canonical than what I wrote, but I looked at the official id and only supported the int type.

I can see that the second encapsulation above actually does the same thing as the official encapsulation, but I have one more page break, Hey.

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 (List<T>)_dbConnection.GetAll<T>();
        }
        public async Task<List<T>> GetAllAsync()
        {
            return (List<T>)await _dbConnection.GetAllAsync<T>();
        }
        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)
        {
            

            #The official Extension Pack for the region dapper
            //Check all, the official default is to add an s after the class name, so 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 id as 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 field modifications, you need to pass in the required parameters, otherwise an error will occur and the unpassed parameters will be modified to empty, so it is better to find out the 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);
            //Add this unexpectedly wrong, id cannot pass null, it is out of range, estimate and type related
            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
        }
    }
}

That's what I learned about dapper these two days. You can also encapsulate it yourself.

Source code can be used to group 831181779@Group owners  

Tags: ASP.NET C# .NET orm

Posted on Sun, 28 Nov 2021 17:01:22 -0500 by VDarkAzN