利用反射+自定义特性实现简单ORM(二）,“无SQL”的数据库增删改查

叁歲伎倆 2021-12-13 04:16 188阅读 0赞

d上一次写了简单的实现，还是基于写SQL的情况下，这次可以实现基本的单表的简单条件的(复杂条件构想中)数据增删改查，通过特性标识来完成实体与数据表的映射。  
有朋友上次提出多表间的关系映射，暂时还是通过(多实体类-视图)的映射实现，虽然写起来可能会麻烦些，不过应该也符合面向对象嘛！  
  
代码：  
实体插入：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] Code   
/// <summary>  
        /// 向数据库插入一个实体  
        /// </summary>  
        /// <typeparam name="TEntity">实体泛型</typeparam>  
        /// <param name="entity">实体</param>  
        /// <returns>影响行数</returns>  
        public static int InsertEntity<TEntity>(TEntity entity) where TEntity : new()  
        \{  
            StringBuilder sb = new StringBuilder(); //主SQL  
            StringBuilder sbParamer = new StringBuilder(); //SQL参数部分  
            sb.Append("INSERT INTO");  
            Type entityType = entity.GetType();   
            DataTbNameAttribute datatbNameAttribute = GetDataTbNameAttribute(entityType);  
            if (datatbNameAttribute == null)  
                throw new AssionException("实体类没有指定DataTbName表名特性！");  
            sb.Append(" " \+ datatbNameAttribute.TbName\+"("); //利用特性DataTbName生成表名  
            sbParamer.Append("(");  
            PropertyInfo\[\] propertyInfos = entityType.GetProperties();  
            List<DbParameter> paramerList = new List<DbParameter>(); //参数集合  
            for (int i = 0; i < propertyInfos.Length; i\++)  
            \{  
                DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(propertyInfos\[i\]);  
                DataInsertAttribute datainsertAttribute = GetDataInsertAttribute(propertyInfos\[i\]);  
                //必须同时拥有DataField和DataInsert两个特性才参与插入数据库字段  
                if (datafieldAttribute == null || datainsertAttribute == null)  
                    continue;  
                //利用特性DataField名生成字段和参数  
                sb.Append(datafieldAttribute.FieldName \+ ",");  
                sbParamer.Append("@" \+ datafieldAttribute.FieldName \+ ",");  
  
                //获取值  
                object oval=propertyInfos\[i\].GetValue(entity,null);  
                oval=oval==null?DBNull.Value:oval;  
  
                //向参数集合添加参数  
                paramerList.Add(DbHelper.CreateParamer("@" \+ datafieldAttribute.FieldName, oval));  
  
            \}  
            //截取掉最后一个多于的参数分隔','符号  
            sb.Remove(sb.Length \- 1, 1);  
            sbParamer.Remove(sbParamer.Length \- 1, 1);  
  
            //拼接最终SQL  
            sb.Append(") VALUES ");  
            sb.Append(sbParamer.ToString()\+")");  
  
            //调用数据库操作执行Insert SQL  
            return DbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, paramerList.ToArray());  
        \}

使用相应实体类需要三个特性：  
\[DataTbName("People")\] 表名,类唯一  
\[DataField("Name",IsPrimaryKey=true)\] 字段，主键可以加上IsPrimaryKey不参与Insert  
\[DataInsert()\] 参加Insert操作的字段必须添加此特性  
例：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] Code   
    \[DataTbName("People")\]  
    public class People  
    \{  
        \[DataField("SysNo", IsPrimaryKey = true)\]  
        public int SysNo \{ get; set; \}  
  
        \[DataField("PName")\]  
        \[DataInsert()\]  
        public string PName \{ get; set; \}  
  
        \[DataField("PSex")\]  
        \[DataInsert()\]  
        \[DataUpdate()\]  
        public string PSex \{ get; set; \}  
  
        \[DataField("PAge")\]  
        \[DataInsert()\]  
        public int PAge \{ get; set; \}  
  
    \}

使用示例：

People people  =   new  People();  
            people.PName  =   " 杨春来 " ;  
            people.PSex  =   " 男 " ;  
            people.PAge  =   21 ;  
            ExecuteEntity.InsertEntity < People > (people);

实体删除操作，主要就是跟据主键了，多条件的正在想一个好的解决方案，例：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] Code   
/// <summary>  
        /// 删除一个实体  
        /// </summary>  
        /// <typeparam name="TEntity">实体泛型</typeparam>  
        /// <param name="entity">实体</param>  
        /// <returns>影响行数</returns>  
        public static int DeleteEntity<TEntity>(TEntity entity)where TEntity:new()  
        \{  
            StringBuilder sb = new StringBuilder(); //SQL语句  
            Type entityType = entity.GetType();  
  
            //表名特性  
            DataTbNameAttribute datatbnameAttribute = GetDataTbNameAttribute(entityType);  
            if(datatbnameAttribute==null)  
                throw new AssionException("实体类没有指定DataTbName表名特性！");  
            sb.Append("DELETE " \+ datatbnameAttribute.TbName \+ " WHERE ");  
            PropertyInfo\[\] propertyInfos = entityType.GetProperties();  
            List<DbParameter> paramerList = new List<DbParameter>();  
            foreach (PropertyInfo property in propertyInfos)  
            \{  
                DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);  
                if (datafieldAttribute == null && datafieldAttribute.IsPrimaryKey != true)  
                    continue;  
                sb.Append(datafieldAttribute.FieldName \+ "=" \+ "@" \+ datafieldAttribute.FieldName);  
                object oval=property.GetValue(entity,null);  
                oval=oval==null?DBNull.Value:oval;  
                paramerList.Add(DbHelper.CreateParamer("@" \+ datafieldAttribute.FieldName, oval));  
                break;  
            \}  
            return DbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, paramerList.ToArray());              
        \}

实体类需要两个特性：  
\[DataTbName("People")\] 标识表名  
\[DataField("Name",IsPrimaryKey=true)\] 字段名和一个主键，根据主键删除  
使用示例：

People people = new  People();  
            people.SysNo = sysNo;  
ExecuteEntity.DeleteEntity < People > (people);

更新一个实体：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] Code   
 /// <summary>  
        /// 更新一个实体  
        /// </summary>  
        /// <typeparam name="TEntity">实体泛型</typeparam>  
        /// <param name="entity">实体</param>  
        /// <returns>影响行数</returns>  
        public static int UpdateEntity<TEntity>(TEntity entity)where TEntity:new()  
        \{  
            StringBuilder sb = new StringBuilder(); //SQL语句  
            StringBuilder sbWhere = new StringBuilder(); //条件SQL  
            Type entityType = entity.GetType();  
  
            //表名特性  
            DataTbNameAttribute datatbnameAttribute = GetDataTbNameAttribute(entityType);  
            if (datatbnameAttribute == null)  
                throw new AssionException("实体类没有指定DataTbName表名特性！");  
            sb.Append("UPDATE " \+ datatbnameAttribute.TbName \+ " SET ");  
            sbWhere.Append(" WHERE ");  
            PropertyInfo\[\] propertyInfos = entityType.GetProperties();  
            List<DbParameter> paramerList = new List<DbParameter>();  
            foreach (PropertyInfo property in propertyInfos)  
            \{  
                DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);  
                DataUpdateAttribute dataupdateAttribute = GetDataUpdateAttribute(property);  
                //没有DataField和DataUpdate特性或为主键的不参与更新  
                if (dataupdateAttribute == null || datafieldAttribute==null || datafieldAttribute.IsPrimaryKey==true)  
                    continue;  
                sb.Append(datafieldAttribute.FieldName \+ "=@" \+ datafieldAttribute.FieldName \+ ",");  
                object oval=property.GetValue(entity,null);  
                oval=oval==null?DBNull.Value:oval;  
                paramerList.Add(DbHelper.CreateParamer("@" \+ datafieldAttribute.FieldName, oval));  
            \}  
            foreach (PropertyInfo property in propertyInfos)  
            \{  
                DataFieldAttribute datafieldAttribute = GetDataFieldAttribute(property);  
                //没有DataField和DataUpdate特性或为主键的不参与更新  
                if (datafieldAttribute != null && datafieldAttribute.IsPrimaryKey == true)  
                \{  
                    sbWhere.Append(datafieldAttribute.FieldName \+ "=@" \+ datafieldAttribute.FieldName);  
                    object oval = property.GetValue(entity, null);  
                    oval = oval == null ? DBNull.Value : oval;  
                    paramerList.Add(DbHelper.CreateParamer("@" \+ datafieldAttribute.FieldName, oval));  
                    break;  
                \}  
                else  
                    continue;  
            \}  
            sb.Remove(sb.Length \- 1, 1);  
            sb.Append(sbWhere.ToString());  
            return DbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, paramerList.ToArray());  
        \}

需要特性：  
\[DataTbName("People")\] 表名  
\[DataField("Name",IsPrimaryKey=true)\] 字段名和一个主键  
\[DataUpdate()\] 参与更新的字段必须实现此特性  
  
例：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] Code   
\[DataTbName("People")\]  
    public class People  
    \{  
        \[DataField("SysNo", IsPrimaryKey = true)\]  
        public int SysNo \{ get; set; \}  
  
        \[DataField("PName")\]  
        \[DataUpdate()\]  
        public string PName \{ get; set; \}  
  
        \[DataField("PSex")\]  
        \[DataUpdate()\]  
        public string PSex \{ get; set; \}  
  
        \[DataField("PAge")\]  
        \[DataUpdate()\]  
        public int PAge \{ get; set; \}  
  
    \}

使用示例：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] Code   
 People people=new People();  
            people.SysNo=sysNo;  
            people.PName = "李宇春";  
            people.PSex = "男";  
            people.PAge = 30;  
ExecuteEntity.UpdateEntity<People>(people);

大至就是这样，还未完。实现更灵活的主法。  
PS：有很多人说反射性能很差很差，这个先保留意见下！

转载于:https://www.cnblogs.com/assion/archive/2009/07/27/1531694.html

[ContractedBlock.gif]: https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211210/5ada990ed8384817a5b9a79e6c8e1557.png