在利用反射插入时会映射所有字段：但是插入时有主键自增不需要指定值
因此利用特性标记主键，将主键排除掉
1.编写属性
[AttributeUsage(AttributeTargets.Property)] public class KeysAuthAtturbute : Attribute { public KeysAuthAtturbute() {} }
2.标记属性
3.查询除KeysAuthAtturbute之外的字段（排除主键）
public static IEnumerable<PropertyInfo> PropertyInfoKeys(this Type type) { return type.GetProperties().Where(c => !c.IsDefined(typeof(KeysAuthAtturbute), true)); }
4.编写方法
insert用到了参数化拼接，防止SQL注入
例如：insert into Student
values(@Age,@Birthday,@Sex) 再给赋值
public bool insert<T>(T t) { Type type = typeof(T);//获取到具体实体类型 string TableName = type.Name; string fileName = string.Join(",",type.PropertyInfoKeys().Select(p=>string.Format("[{0}]", p.Name))); string valuesName = string.Join(",", type.PropertyInfoKeys().Select(p => $"@{p.Name}"));//将参数以@Age,@Sex方式列出 string sql = $@"INSERT INTO [{TableName}] ({fileName}) VALUES ({valuesName})"; SqlCommand cmd = new SqlCommand(sql, conn); //用于赋值参数 数据库允许为空的情况下,判断是否有值，没有的话赋值为空值 IEnumerable<SqlParameter> parameters = type.PropertyInfoKeys().Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t)??DBNull.Value)); cmd.Parameters.AddRange(parameters.ToArray()); if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } int result = cmd.ExecuteNonQuery(); return result == 1; }
5.调用
sqlserverDBhelper sqlserverD = new sqlserverDBhelper(); sqlserverD.insert<Student>( new Student { Name = "lw", Age = 20, Birthday = DateTime.Now, Sex = "女", });
在程序中每次调用都需要拼接SQL字符串，使程序变得缓慢
因此利用泛型缓存加载
1.新建一个类 复制之前的代码，初始化构造函数里存放sql语句
public class sqlhublider<T> { private static string selectsql = null; private static string insertsql = null; static sqlhublider() { Type type = typeof(T);//获取到具体类型 string fileName = string.Join(",", type.PropertyInfoKeys().Select(p => string.Format("[{0}]", p.Name))); string valuesName = string.Join(",", type.PropertyInfoKeys().Select(p => $"@{p.Name}")); insertsql = $@"INSERT INTO [{type.Name}] ({fileName}) VALUES ({valuesName})"; string filedstring = string.Join(",", type.GetProperties().Select(c => string.Format("[{0}]", c.Name))); selectsql = string.Format("select {0} from {1} where ID= ", filedstring, type.Name); } public static string Getsql(Sqltype sqltype) { switch (sqltype) { case sqlserver.Sqltype.selectsql: return selectsql; case sqlserver.Sqltype.insertsql: return insertsql; default: throw new Exception("sqltype"); } } } public enum Sqltype//枚举类型 不同类型的的SQL语句 { selectsql, insertsql }
2.方法改编
public bool insert<T>(T t) { Type type = typeof(T);//获取到具体类型 string sql= sqlhublider<T>.Getsql(Sqltype.insertsql); SqlCommand cmd = new SqlCommand(sql, conn); //用于赋值参数 数据库允许为空的情况下,判断是否有值，没有的话赋值为空值 IEnumerable<SqlParameter> parameters = type.PropertyInfoKeys().Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t)??DBNull.Value)); cmd.Parameters.AddRange(parameters.ToArray()); if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } int result = cmd.ExecuteNonQuery(); return result == 1; }
3.调用
sqlserverDBhelper sqlserverD = new sqlserverDBhelper(); sqlserverD.insert<Student>( new Student { Name = "lw", Age = 22, Birthday = DateTime.Now, Sex = "男", }); sqlserverD.insert<Student>(new Student { Name = "lw", Age = 21, Birthday = DateTime.Now, Sex = "男", });
调试时发现，第二次插入直接使用了之前的SQL语句，不需要再次拼装了，使性能得到了提升。
泛型缓存原理：JIT会为不同的实体构造不同的类型副本
