.NET/C# 高级开发（三） 课程小结

任务需求

一，提供数据库访问方法类，BaseModel约束
二，封装一个方法，在控制台输出任意实体的全部属性和属性值
三，提供泛型数据库实体插入，实体更新，ID删除数据的数据库访问方法
四，写一个实体自动生成器
五，将数据库访问层抽象，使用简单工厂+配置文件+反射的方法，来提供对数据库的访问
六，每个实体类的基础增删改查Sql语句不变，用泛型缓存实现

（一）数据库访问方法类

先把约束写出来，要它有一个自增的键id

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

namespace Ruanmou.Libraries.Model
{
    public class BaseModel
    {
        public int identification { get; set; }
    }
}

然后根据两个表写两个类
类名属性要与表名属性一一对应

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

namespace Ruanmou.Libraries.Model
{
    public class Company: BaseModel
    {
        public string password { get; set; }
        public string nickname { get; set; }
        public string gender { get; set; }
        /// <summary>
        /// ?  可控字段
        /// </summary>
        public DateTime? birthday { get; set; }
        public int? property { get; set; }//Nullable<int>


    }
}

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

namespace Ruanmou.Libraries.Model
{
    public class User: BaseModel
    {
        public string password { get; set; }
        public string nickname { get; set; }
        public int? age { get; set; }
        public int? vip { get; set; }
        public DateTime? registration { get; set; }
    }
}

（二）控制台输出任意实体的全部属性和属性值

public static class ConsoleExtend
    {
        //封装一个方法，在控制台输出任意实体的全部属性和属性值
        public static void Show<T>(this T t)
        {
            Type type = t.GetType();
            Console.WriteLine("*******************************************");
            foreach (var prop in type.GetProperties())//获取当前对象全部属性
            {
                Console.WriteLine($"{type.Name}.{prop.Name}={prop.GetValue(t)}");
                
            }
            Console.WriteLine("*******************************************");
        }
    }

（三）泛型数据库实体插入，实体更新，ID删除数据的数据库访问方法

首先是对数据库的约束

using Ruanmou.Libraries.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ruanmou.Libraries.IDAL
{
    public interface IBaseDAL
    {
        T Find<T>(int id) where T : BaseModel;
        List<T> FindAll<T>() where T : BaseModel;
        bool Add<T>(T t) where T : BaseModel;
        bool Updata<T>(T t) where T : BaseModel;
        bool Delete<T>(T t) where T : BaseModel;
    }
}

然后来实现方法

using Ruanmou.Framework;
using Ruanmou.Libraries.IDAL;
using Ruanmou.Libraries.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Ruanmou.Libraries.DAL
{
    /// <summary>
    /// 约束调用者，避免其他实体传递进来
    /// BaseModel 保证一定有ID 而且是int 自增主键
    /// </summary>
    public class BaseDAL : IBaseDAL
    {
        public bool Add<T>(T t) where T : BaseModel
        {
            Type type = t.GetType();
            //id是自增的不能新增，要排除id   
            //方法一
            //string columnString = string.Join(",", type.GetProperties()
            //    .Where(p =>!p.Name.Equals("identification"))//如果等于当前属性就去掉，where 返回表达式成立项
            //    .Select(p => $"[{p.Name}]"));//这里的p是Select的参数，取自type.GetProperties()中的每一个成员

            //方法二
            string columnString = string.Join(",", type.GetProperties(BindingFlags.DeclaredOnly|BindingFlags.Instance|BindingFlags.Public)
                .Select(p => $"[{p.Name}]"));//这里的p是Select的参数，取自type.GetProperties()中的每一个成员

            string ValuesColumn = string.Join(",", type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
                 .Select(p => $"'@{p.GetValue(t)}'"));//将属性值拼接成字符串返回
            //sqlserver中任意值都可以加引号
            //这里要注意sql注入，Name's 比如这样的，含有注入风险，所以要参数话
            List<SqlParameter> parameterList = type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)//检索当前类的所有属性，当前级别层的 当前实例的 公共的
                .Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t)??DBNull.Value))//使用检索到的成员实例化成Parameter  参数化p的名字？ 判断p的值是否为空，空就传入DBNull
                .ToList();//生成一个新List集合返回

            string sql = $"Insert [{type.Name}] ({columnString}) values({ValuesColumn})";

            using (SqlConnection conn = new SqlConnection(StaticField.ConnectionStringCostomers))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);//创建sql语句
                command.Parameters.AddRange(parameterList.ToArray());//???
                var reader = command.ExecuteReader();//执行sql语句
                return command.ExecuteNonQuery() == 1;
            }

        }

        public bool Delete<T>(T t) where T : BaseModel
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// ID查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Find<T>(int id) where T : BaseModel
        {
            Type type = typeof(T);
            //查询语句
            string sql = $"SELECT {string.Join(",", type.GetProperties().Select(p => $"[{p.Name}]"))} FROM [{type.Name}] WHERE identification = {id}";
            object oObject = Activator.CreateInstance(type);
            using (SqlConnection conn = new SqlConnection(StaticField.ConnectionStringCostomers))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);//创建sql语句
                var reader = command.ExecuteReader();//执行sql语句
                if (reader.Read())//将数据指向下一条，默认-1
                {
                    return this.Trans<T>(type,reader);
                }
                else
                {
                    return null;//如果数据库查询不到，应该返回null 而不是默认对象
                }
            }
        }
        /// <summary>
        /// 全部查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> FindAll<T>() where T : BaseModel
        {
            Type type = typeof(T);
            //查询语句
            //string sql = $"SELECT {string.Join(",", type.GetProperties().Select(p => $"[{p.Name}]"))} FROM [{type.Name}]";
            string sql = SqlBuilder<T>.FindAllSql;
            
            using (SqlConnection conn = new SqlConnection(StaticField.ConnectionStringCostomers))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);//创建sql语句
                var reader = command.ExecuteReader();//执行sql语句
                List<T> tList = new List<T>();
                while (reader.Read())//将数据指向下一条，默认-1
                {
                    tList.Add(this.Trans<T>(type, reader));
                }
                return tList;

            }
        }

        public bool Updata<T>(T t) where T : BaseModel
        {
            throw new NotImplementedException();
        }
        #region Private Method
        private T Trans<T>(Type type, SqlDataReader reader)
        {
            object oObject = Activator.CreateInstance(type);//反射创建泛型参数的实例
            foreach (var prop in type.GetProperties())//获取当前对象全部属性
            {
                //prop.SetValue(oObject, reader[prop.Name]);
                //可空类型，如果数据库存储的是null，返回的是DBnull，DBnull不能直接赋值给可空类型（null），直接SetValue会报错
                //    //处理方法  reader[prop.Name] is DBNull?null: reader[prop.Name]  利用三元运算来确定返回的值是null还是自身的值
                prop.SetValue(oObject, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
            }
            return (T)oObject;
        }
        #endregion

    }
}

删除和更新就不写了，太简单了

（四）写一个实体自动生成器

public static class AutoCreateModel
    {
        //写一个实体自动生成器
        
        private static string GetAllTableSql = "SELECT name FROM sys.tables where type ='U'";
        private static string GetTableInfoSql = @"SELECT DISTINCT 
                                                A.COLUMN_NAME colname,
                                                A.DATA_TYPE typename,
                                                A.IS_NULLABLE isnullable 
                                                From INFORMATION_SCHEMA.Columns A LEFT JOIN 
                                                INFORMATION_SCHEMA.KEY_COLUMN_USAGE B ON 
                                                A.TABLE_NAME=B.TABLE_NAME";


        public static void BatchMappingModel()
        {
            using (SqlConnection conn=new SqlConnection(StaticField.ConnectionStringCostomers))
            {
                SqlCommand sqlCommand = new SqlCommand(GetAllTableSql, conn);
                conn.Open();
                SqlDataReader reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    MappingModel(reader["name"].ToString());
                }
            }
        }
        public static void MappingModel(string tableName)
        {
            string sql = $"{GetTableInfoSql} where a.table_name='{tableName}'";
            using (SqlConnection conn=new SqlConnection(StaticField.ConnectionStringCostomers))
            {
                SqlCommand sqlcommand = new SqlCommand(sql, conn);
                conn.Open();
                SqlDataReader reader = sqlcommand.ExecuteReader();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append($"public class {tableName} \r\n{{\r\n");
                while (reader.Read())
                {
                    stringBuilder.Append($"public {GetTypeOFColumn(reader["typename"].ToString(), reader["isnullable"].ToString())} {reader["colname"]} {{get;set;}}\r\n");
                }
                stringBuilder.Append("}");
                CommonMethod.CreateTxt(StaticField.ModelFilePath + "\\" + tableName + ".txt", stringBuilder.ToString());
            }
        }
        public static string GetTypeOFColumn(string type,string nullAble)
        {
            if (type.Equals("int") && nullAble.Equals("NO"))
                return "int";
            if(type.Equals("int") && nullAble.Equals("YES"))
                return "int?";
            if (type.Equals("datetime") && nullAble.Equals("NO"))
                return "datetime";
            if (type.Equals("datetime") && nullAble.Equals("YES"))
                return "datetime?";
            if (type.Equals("nvarchar") || type.Equals("varchar") || type.Equals("text"))
                return "string";
            else throw new Exception($"暂不支持_类型:{type}_可空:{nullAble}_");
        }
    }
    public static class CommonMethod
    {
        public static void CreateTxt(string a,string b)
        {
            //根据路径和文本内容生成文本
            FileStream fs = new FileStream(a,FileMode.Create);
            StreamWriter sw = new StreamWriter(fs);
            sw.Write(b);
            sw.Flush();
            sw.Close();
            fs.Close();
        }
    }

（五）使用简单工厂+配置文件+反射的方法，来提供对数据库的访问

配置文件类

public static class StaticField
    {
        //配置文件常量类
        /// <summary>
        /// 数据库读写类配置文件
        /// </summary>
        public readonly static string IBaseDALConfig = ConfigurationManager.AppSettings["IBaseDALConfig"];
        /// <summary>
        /// 实体生成器路径配置文件
        /// </summary>
        public readonly static string ModelFilePath = ConfigurationManager.AppSettings["ModelFilePath"];
        /// <summary>
        /// 数据库连接账号配置文件
        /// </summary>
        public readonly static string ConnectionStringCostomers = ConfigurationManager.ConnectionStrings["ConnectionStringCostomers"].ConnectionString;
        /// <summary>
        /// 数据表接口配置文件
        /// </summary>
        public static class BaseModelConfig
        {
            private readonly static string BaseModelConfigString= ConfigurationManager.AppSettings["BaseModelConfig"];
            /// <summary>
            /// 完整DLL名称
            /// </summary>
            public readonly static string DllName ;
            /// <summary>
            /// 类名称
            /// </summary>
            public readonly static string ClassName ;
            static BaseModelConfig()
            {
            DllName = BaseModelConfigString.Split(',')[0];//用逗号分隔一下，分隔后的数组第一个就是dll名了
            ClassName = BaseModelConfigString.Split(',')[1];//第二个就是类名了
            }
        }
    }

简单工厂+反射

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

namespace Ruanmou.Libraries.Factory
{
    public static class ReflectClass<T> where T : class
    {
        private static T _class;
        private static Type _type;
        public static T foundClass (string dllName, string className, params object[] obj)
        {
            if (_class != null) return _class;
            return found(dllName, className, obj);
        }
        public static T foundNewClass (string dllName, string className, params object[] obj)
        {
            if (_type != null) return Activator.CreateInstance(_type, obj) as T;
            return found(dllName, className, obj);
        }
        
        #region found
        private static T found(string dllName, string className, params object[] obj)
        {
            className = dllName + "." + className;
            Type[] type = typeof(T).GenericTypeArguments;
            Assembly assembly0 = Assembly.Load(dllName);
            _type = assembly0.GetType(className);
            if (typeof(T).IsGenericType)
                _type = _type.MakeGenericType(type);
            object oDBHelper = Activator.CreateInstance(_type, obj);
            _class = oDBHelper as T;
            return _class;
        }
        #endregion

    }
    public static class ReflectMethod
    {
        public static object InvokeMethod(this object function, string method, params object[] obj)
        {
            List<Type> listType = new List<Type>();
            Type[] type = new Type[] { };
            foreach (var o in obj)
            {
                listType.Add(o.GetType());
            }
            type = listType.ToArray();
            MethodInfo method_0 = function.GetType().GetMethod(method, type);
            MethodInfo newMethod = method_0.MakeGenericMethod(new Type[] { typeof(int), typeof(string), typeof(DateTime) });
            return method_0.Invoke(function, obj);
        }

    }
}

（六）泛型缓存实现实体类的增删改查Sql

using Ruanmou.Libraries.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Ruanmou.Libraries.DAL
{
    public class SqlBuilder<T>where T:BaseModel
    {
        public static string FindSql = null;
        public static string FindAllSql = null;
        public static string AddSql = null;
        public static string DeleteSql = null;
        public static string UpDataSql = null;
        static SqlBuilder()
        {
            FindSql = $"SELECT {string.Join(",", typeof(T).GetProperties().Select(p => $"[{p.Name}]"))} FROM [{typeof(T).Name}] WHERE identification = @id";

            FindAllSql = $"SELECT {string.Join(",", typeof(T).GetProperties().Select(p => $"[{p.Name}]"))} FROM [{typeof(T).Name}]";
#region
            string columnString = string.Join(",", typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
                .Select(p => $"[{p.Name}]"));//这里的p是Select的参数，取自type.GetProperties()中的每一个成员
            string ValuesColumn = string.Join(",", typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
                 .Select(p => $"'@{p.Name}'"));//将属性值拼接成字符串返回
#endregion
            AddSql = $"Insert [{typeof(T).Name}] ({columnString}) values({ValuesColumn})";

            DeleteSql = $"";

            UpDataSql = $"";
        }
    }
}

（七）整体结构图