反射+特性 练习 (通用实体类CRUD)

主要实现 ：1.同一接口针对不同类操作不同数据，包括查询，新增，删除，修改（接口适合所有单一类）

                   2.实体类通过特性直接绑定数据库字段及表名

业务场景：如果新增查询对象，那么只需要添加一个实体类，调用查询接口就可以查询出数据

这个只是自己对反射+特性的一个学习，加强记忆，只是一个思路。

 AttributeHelper 类

using Common.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Common.Attributes
{
    public static class AttributeHelper
    {
        /// <summary>
        /// 获取实体类对应数据表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        //public static string GetTableName(this Type type)
        public static string GetTableName<T>() where T : BaseEntity
        {
            Type type = typeof(T);
            TableAttribute tableAttr = Attribute.GetCustomAttribute(type, typeof(TableAttribute)) as TableAttribute;
            return tableAttr == null ? type.Name : tableAttr.Name;
        }

        /// <summary>
        /// 获取实体数据库映射字段
        /// </summary>
        /// <param name="prop"></param>
        /// <returns></returns>
        public static string GetColumnName(this PropertyInfo prop)
        {
            if (prop.IsDefined(typeof(ColumnAttribute), true))
            {
                ColumnAttribute columnAttribute = prop.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;
                return columnAttribute.Name;
            }
            else
            {
                return prop.Name;
            }
        }

        /// <summary>
        /// 获取数据表主键
        /// </summary>
        /// <returns></returns>
        public static string GetColumnKey<T>() where T : BaseEntity
        {
            Type type = typeof(T);
            var prop = type.GetProperties();
            foreach (var item in prop)
            {
                if (item.IsDefined(typeof(KeyAttribute), true))
                {
                    ColumnAttribute columnAttribute = item.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;
                    return columnAttribute.Name;
                }
            }
            throw new Exception("实体映射测错");
        }

        /// <summary>
        /// 获取数据表主键值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static string GetColumnKeyValue<T>(T t) where T : BaseEntity
        {
            Type type = typeof(T);
            foreach (var item in type.GetProperties())
            {
                if (item.IsDefined(typeof(KeyAttribute), true))
                {
                    return item.GetValue(t).ToString();
                }
            }
            throw new Exception("获取主键值失败");
        }
    }
}

ConvertEx 扩展类

using Common.Attributes;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Common.ConvertEx
{
    public static class ConvertEx
    {
        /// <summary>
        /// SqlDataReader转实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static List<T> SqlDataReaderToEntitys<T>(this SqlDataReader reader)
        {
            Type type = typeof(T);
            List<T> lists = new List<T>();
            while (reader.Read())
            {
                T t = Activator.CreateInstance<T>();
                foreach (var prop in type.GetProperties())
                {
                    string columnName = prop.GetColumnName();
                    prop.SetValue(t, reader[columnName] is DBNull ? null : reader[columnName]);
                }
                lists.Add(t);
            }
            return lists;
        }
    }
}

BaseEntity 基类 

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

namespace Common.Model
{
    public class BaseEntity
    {
    }
}

 SqlHelper 类

using Common.Attributes;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using Common.ConvertEx;

namespace Common.SqlHelper
{
    public class SqlHelper
    {
        private SqlHelper()
        {
            connString = ConfigurationManager.ConnectionStrings["BaseDb"].ConnectionString;
        }

        private static string connString = null;

        public static SqlHelper Instance()
        {
            return new SqlHelper();
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <returns></returns>
        public List<T> ExecuteReader<T>(string sql)
        {
            List<T> list = new List<T>();
            using (SqlConnection sqlConnection = new SqlConnection(connString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                sqlConnection.Open();
                SqlDataReader reader = sqlCommand.ExecuteReader();
                list = reader.SqlDataReaderToEntitys<T>();
            }
            return list;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, IEnumerable<SqlParameter> sqlParameter = null)
        {
            int result = -1;
            using (SqlConnection sqlConnection = new SqlConnection(connString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                sqlConnection.Open();
                if (sqlParameter != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameter.ToArray());
                }
                result = sqlCommand.ExecuteNonQuery();
            }
            if (result == 0)
            {
                throw new Exception("修改数据失败");
            }
            return result;
        }

        /// <summary>
        /// (扩展)适用所有sql语句执行 增删查改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public T ExcuteSql<T>(string sql, Func<SqlCommand, T> func)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connString))
            {
                using (SqlCommand command = new SqlCommand(sql, sqlConnection))
                {
                    sqlConnection.Open();
                    SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
                    try
                    {
                        command.Transaction = sqlTransaction;
                        T tResult = func.Invoke(command);
                        sqlTransaction.Commit();
                        return tResult;
                    }
                    catch (Exception ex)
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}

IBaseDAL 接口类 

using Common.Model;
using Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IDAL
{
    /// <summary>
    /// 数据通用访问类
    /// </summary>
    public interface IBaseDAL
    {
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        T FindEntity<T>(string keyValue) where T : BaseEntity;

        /// <summary>
        /// 查询全部
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        List<T> FindAll<T>() where T : BaseEntity;

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        void Insert<T>(T t) where T : BaseEntity;

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        void Update<T>(T t) where T : BaseEntity;

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        void Delete<T>(string keyValue) where T : BaseEntity;
    }
}

BaseDAL 接口实现类

using Common.Attributes;
using Common.Model;
using Common.SqlHelper;
using IDAL;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using Common.ConvertEx;

namespace DAL
{
    public class BaseDAL : IBaseDAL
    {
        /// <summary>
        /// 根据主键删除实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        public void Delete<T>(string keyValue) where T : BaseEntity
        {
            string tableName = AttributeHelper.GetTableName<T>();
            string key = AttributeHelper.GetColumnKey<T>();
            string sql = $"delete from {tableName} where {key}='{keyValue}'";
            int iResult = SqlHelper.Instance().ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 查询全部实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> FindAll<T>() where T : BaseEntity
        {
            Type type = typeof(T);

            string tableName = AttributeHelper.GetTableName<T>();
            string cloumns = string.Join(",", type.GetProperties().Select(t => $"[{t.GetColumnName()}]"));
            string sql = $"select {cloumns} from [{tableName}]";
            List<T> objList = SqlHelper.Instance().ExecuteReader<T>(sql);
            return objList;
        }

        /// <summary>
        /// 查询单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public T FindEntity<T>(string keyValue) where T : BaseEntity
        {
            //方式一
            //Type type = typeof(T);
            //string tableName = AttributeHelper.GetTableName<T>();
            //string key = AttributeHelper.GetColumnKey<T>();
            //string cloumns = string.Join(",", type.GetProperties().Select(a => $"[{a.GetColumnName()}]"));
            //string sql = $"select {cloumns} from {tableName} where {key}='{keyValue}'";
            //return SqlHelper.Instance().ExecuteReader<T>(sql).FirstOrDefault();

            //方式二
            Func<SqlCommand, T> func = new Func<SqlCommand, T>(c =>
             {
                 return c.ExecuteReader().SqlDataReaderToEntitys<T>().FirstOrDefault();
             });
            string cloumns = string.Join(",", typeof(T).GetProperties().Select(a => $"[{a.GetColumnName()}]"));
            return SqlHelper.Instance().ExcuteSql(
                 $"select {cloumns} from {AttributeHelper.GetTableName<T>()} where {AttributeHelper.GetColumnKey<T>()}='{keyValue}'"
                 , func);
        }

        /// <summary>
        /// 新增实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        public void Insert<T>(T t) where T : BaseEntity
        {
            Type type = typeof(T);
            var props = type.GetProperties();
            string tableName = AttributeHelper.GetTableName<T>();
            string cloumns = string.Join(",", props.Select(a => $"[{a.GetColumnName()}]"));
            string values = string.Join(",", props.Select(p => $"@{p.GetColumnName()}"));
            string sql = $"insert into {tableName} ({cloumns}) values ({values})";
            IEnumerable<SqlParameter> sqlParameters = props.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t)));
            int iResult = SqlHelper.Instance().ExecuteNonQuery(sql, sqlParameters);
        }

        /// <summary>
        /// 修改实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        public void Update<T>(T t) where T : BaseEntity
        {
            Type type = typeof(T);
            var props = type.GetProperties();
            string columns = string.Join(",", props.Select(a => $"[{a.GetColumnName()}]=@{a.GetColumnName()}"));
            IEnumerable<SqlParameter> parameters = props.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value));
            string columnKey = AttributeHelper.GetColumnKey<T>();
            string columnKeyValue = AttributeHelper.GetColumnKeyValue<T>(t);
            string tableName = AttributeHelper.GetTableName<T>();
            string sql = $"update {tableName} set {columns} where {columnKey} = '{columnKeyValue}'";
            int iResult = SqlHelper.Instance().ExecuteNonQuery(sql, parameters);
        }
    }
}

User 实体类

using Common.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Model
{
    [Table("T_Base_User")] //映射表名
    public class User : BaseEntity
    {
        [Key] //标识是主键字段
        [Column("F_UserId")] //标识对应数据库字段
        public string Id { get; set; }

        [Column("F_RealName")]
        public string Name { get; set; }

        [Column("F_CreateDate")]
        public DateTime? CreateDate { get; set; }
    }
}

Program UI类 （前台） 

using DAL;
using IDAL;
using Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Project
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            IBaseDAL iDAL = new BaseDAL();
            //var list = iDAL.FindAll<Log>();
            //var entity = iDAL.FindEntity<Log>("ec157056-894a-4207-92ab-9f418a7fd807");
            //iDAL.Delete<Log>("ec157056-894a-4207-92ab-9f418a7fd807");

            //var list = iDAL.FindAll<User>();
            var entity = iDAL.FindEntity<User>("9890");
            //iDAL.Delete<User>("1064");

            //entity.Name = "李四";
            //iDAL.Update(entity);

            //User user = new User()
            //{
            //    Id = "9890",
            //    Name = "王五",
            //    CreateDate = DateTime.Now
            //};
            //iDAL.Insert(user);
        }

        public delegate List<User> UserDelegate();
    }
}

 App.Config 配置文件

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <connectionStrings>
    <add name="BaseDb" connectionString="Server=.;Initial Catalog=MyDB;User ID=sa;Password=root" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>