Dapper 简单封装

using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using common.core.config;
using Npoi.Core.SS.Formula.Functions;

namespace common.core.sqlserver
{
    public class BaseService<TService, TEntity> where TService : BaseService<TService, TEntity>, new()
    {
        /// <summary>
        /// 默认实例
        /// </summary>
        /// <returns>服务实例</returns>
        public static TService Instance() => new TService();
 
        /// <summary>
        /// 插入多个
        /// </summary>
        /// <param name="listModel"></param>
        public virtual int InsertMany(List<TEntity> listModel)
        {
            if (listModel == null || listModel.Count <= 0)
            {
                throw new Exception("插入数据不可为空");
            }
            TEntity model = listModel.FirstOrDefault();
            var ps = model.GetType().GetProperties();
            List<string> @colms = new List<string>();
            List<string> @params = new List<string>();

            foreach (var p in ps)
            {
                if (p.CustomAttributes.All(x => x.AttributeType != typeof(PrimaryKeyAttribute)) && p.CustomAttributes.All(x => x.AttributeType != typeof(DBIgnoreAttribute)))
                {
                    @colms.Add(string.Format("[{0}]", p.Name));
                    @params.Add(string.Format("@{0}", p.Name));
                }
            }
            var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", typeof(TEntity).Name, string.Join(", ", @colms), string.Join(", ", @params));
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                IDbTransaction transaction = _conn.BeginTransaction();
                return _conn.Execute(sql, listModel, transaction, null, null);
            }

        }

        /// <summary>
        /// 插入一个
        /// </summary>
        /// <param name="model"></param>
        public virtual int InsertOne(TEntity model)
        {
            if (model == null)
            {
                throw new Exception("插入数据不可为空");
            }
            var ps = model.GetType().GetProperties();
            List<string> @colms = new List<string>();
            List<string> @params = new List<string>();

            foreach (var p in ps)
            {
                if (p.CustomAttributes.All(x => x.AttributeType != typeof(PrimaryKeyAttribute)) && p.CustomAttributes.All(x => x.AttributeType != typeof(DBIgnoreAttribute)))
                {
                    @colms.Add(string.Format("[{0}]", p.Name));
                    @params.Add(string.Format("@{0}", p.Name));
                }
            }
            var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", typeof(TEntity).Name, string.Join(", ", @colms), string.Join(", ", @params));
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Execute(sql, model, null, null, null);
            }
        }

        /// <summary>
        /// 查询一个
        /// </summary>
        /// <param name="whereProperties"></param>
        /// <returns></returns>
        public virtual TEntity GetOne(object whereProperties)
        {
            string where = "";
            var listPropert = whereProperties.GetType().GetProperties();
            if (listPropert.Length > 0)
            {
                where += " where ";
                listPropert.ToList().ForEach(e =>
                {
                    where += $" {e.Name} = @{e.Name} and";
                });
            }
            where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
            //返回单条信息
            string query = $"SELECT * FROM { typeof(TEntity).Name}{where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.QuerySingleOrDefault<TEntity>(query, whereProperties);
            }
        }

        /// <summary>
        /// 查询一个
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual TEntity GetOne(string where)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where = $" where 1=1 and {where}";
            }
            //返回单条信息
            string query = $"SELECT * FROM { typeof(TEntity).Name}  {where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.QuerySingleOrDefault<TEntity>(query);
            }
        }

        /// <summary>
        /// 查询多个
        /// </summary>
        /// <param name="whereProperties"></param>
        /// <returns></returns>
        public virtual List<TEntity> GetMany(object whereProperties)
        {
            string where = "";
            var listPropert = whereProperties.GetType().GetProperties();
            if (listPropert.Length > 0)
            {
                where += " where ";
                listPropert.ToList().ForEach(e =>
                {
                    where += $" {e.Name} = @{e.Name} and";
                });
            }
            where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
            string query = $"SELECT * FROM { typeof(TEntity).Name}{where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Query<TEntity>(query, whereProperties)?.ToList();
            }
        }

        /// <summary>
        /// 查询多个
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual List<TEntity> GetMany(string where)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where = $" where 1=1 and {where}";
            }
            string query = $"SELECT * FROM { typeof(TEntity).Name}  {where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Query<TEntity>(query)?.ToList();
            }
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="whereProperties"></param>
        /// <returns></returns>
        public virtual bool Exists(object whereProperties)
        {
            return GetMany(whereProperties).Count > 0;
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual bool Exists(string where)
        {
            return GetMany(where).Count > 0;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int DeleteById(TEntity entity)
        {
            if (entity == null)
            {
                throw new Exception("删除内容不可为空");
            }
            string where = "";
            var listPropert = entity.GetType().GetProperties();
            if (listPropert.Length > 0)
            {
                listPropert.ToList().ForEach(p =>
                {
                    var primaryKey = p.CustomAttributes.FirstOrDefault(x => x.AttributeType == typeof(PrimaryKeyAttribute));
                    if (primaryKey != null)
                    {
                        where += $" {p.Name} = @{p.Name} and";
                    }
                });
            }

            where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
            if (string.IsNullOrEmpty(where))
            {
                throw new Exception("未找到Id");
            }
            string query = $"DELETE FROM { typeof(TEntity).Name} where {where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Execute(query, entity);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="whereProperties"></param>
        /// <returns></returns>
        public virtual int Delete(object whereProperties)
        {
            string where = "";
            var listPropert = whereProperties.GetType().GetProperties();
            if (listPropert.Length > 0)
            {
                listPropert.ToList().ForEach(e =>
                {
                    where += $"{e.Name} = @{e.Name} and";
                });
            }
            where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
            if (string.IsNullOrEmpty(where))
            {
                throw new Exception("条件不可为空");
            }
            string query = $"DELETE FROM { typeof(TEntity).Name} where {where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Execute(query, whereProperties);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual int Delete(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                throw  new Exception("条件不可为空");
            }
            string query = $"DELETE FROM { typeof(TEntity).Name} where {where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Execute(query);
            }
        }

        /// <summary>
        /// 根据Id更新
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int UpdateById(TEntity entity)
        {
            if (entity == null)
            {
                throw new Exception("更新内容不可为空");
            }
            string where = "";
            var listPropert = entity.GetType().GetProperties();
            if (listPropert.Length > 0)
            {
                listPropert.ToList().ForEach(p =>
                {
                    var primaryKey = p.CustomAttributes.FirstOrDefault(x => x.AttributeType == typeof(PrimaryKeyAttribute));
                    if (primaryKey!=null)
                    {
                        where += $" {p.Name} = @{p.Name} and";
                    }
                });
            }

            where=where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
            if (string.IsNullOrEmpty(where))
            {
                throw new Exception("未找到Id");
            }

            string update = "";
            var listPropertUpdate = entity.GetType().GetProperties();
            if (listPropertUpdate.Length > 0)
            {
                update += "";
                listPropertUpdate.ToList().ForEach(e =>
                {
                    if (e.CustomAttributes.All(x => x.AttributeType != typeof(PrimaryKeyAttribute)) && e.CustomAttributes.All(x => x.AttributeType != typeof(DBIgnoreAttribute)))
                    {
                        update += $"{e.Name} = @{e.Name} ,";
                    }
                });
            }
            update = update.TrimEnd(',');
            if (string.IsNullOrEmpty(update))
            {
                throw new Exception("无更新内容");
            }
            string query = $"update { typeof(TEntity).Name} set {update} where {where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Execute(query, entity);
            }

        }

        /// <summary>
        /// 根据条件更新
        /// </summary>
        /// <param name="updateProperty"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual int Update(object updateProperty, string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                throw new Exception("需输入条件");
            }
            string update = "";
            var listPropertUpdate = updateProperty.GetType().GetProperties();
            if (listPropertUpdate.Length > 0)
            {
                update += "";
                listPropertUpdate.ToList().ForEach(e =>
                {
                    update += $"{e.Name} = @{e.Name} ,";
                });
            }
            update = update.TrimEnd(',');
            if (string.IsNullOrEmpty(update))
            {
                throw new Exception("无更新内容");
            }
            string query = $"update { typeof(TEntity).Name} set {update} where {where}";
            using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
            {
                return _conn.Execute(query, updateProperty);
            }

        }
    }
}