自己动手写ORM框架(六):实现查询功能FindById方法

通过配置实体与表的关系映射后，就可以实现对表的操作了，接下来实现简单对象的新增功能。下面代码1-1是定义的操作数据对象方法的接口：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace System.Orm.EntityManager
{
    public interface EntityManager
    {
       //事物
        IDbTransaction Transaction { get; set; }

       //查询表所有数据 

        List<T> FindAll<T>() where T : new(); 

       //自定义SQL查询 

        List<T> FindBySql<T>(string strSql) where T : new(); 

       //通过主键ID查询 

        List<T> FindById<T>(object id) where T : new(); 

       //新增
        int Save<T>(T entity);

       //修改
        int Update<T>(T entity);

       //删除
        int Remove<T>(T entity);

       //根据ID删除数据
        int Remove<T>(object id) where T : new();
    }
}

    下面先实现比较简单的查询方法，代码1-2：

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Orm.CustomAttributes;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using System.Orm.DBUtility;
using System.Orm.Common;

namespace System.Orm.EntityManager
{
    public class EntityManagerImpl : EntityManager
    {
        //......实现EntityManager接口...                               
    }
}

    下面是PropertyImpl 类中FindById方法的代码1-3：

#region 通过主键ID查询数据
public List<T> FindById<T>(object id) where T : new()
{
    List<T> listArr = new List<T>();

    //获取泛型T对象的类型

  Type type = new T().GetType();

    //获取属性信息数组

    PropertyInfo[] properties = ReflectionUtils.GetProperties(type);
    

    //获取实体对象中的表名、列名、列值信息并存于TableInfo对象中
    TableInfo tableInfo = DbEntityUtils.GetTableInfo(type, DbOperateType.SELECT);
    

    //生成查询SQL语句
    String strSql = DbEntityUtils.GetFindByIdSql(tableInfo);

    //创建参数数组，并将主键ID键值存入参数数组中

    IDbDataParameter[] parms = DbFactory.CreateDbParameters(1);
    parms[0].ParameterName = tableInfo.Id.Key;
    parms[0].Value = id;            

    IDataReader sdr = null;
    try
    {

        sdr = AdoHelper.ExecuteReader(AdoHelper.ConnectionString, CommandType.Text, strSql, parms);

        while (sdr.Read())
        {
            //创建实体类对象

              T entity = new T();
            foreach (PropertyInfo property in properties)
            {
              //通过实体类属性名称获取Column自定义属性配置的映射列名

                sttring name = tableInfo.PropToColumn[property.Name].ToString();

              //通过获取的列名从dataReader中检索值，并赋给实体对象属性

                ReflectionUtils.SetPropertyValue(entity, property, sdr[name]);
            }

            //将实体对象添加到List中

            listArr.Add(entity);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (sdr != null) sdr.Close();
    }

    return listArr;
}
#endregion

 

    1-3中第一行代码：TableInfo tableInfo = DbEntityUtils.GetTableInfo(entity,DbOperateType.SELECT);

将实体对象和操作类型传入DbEntityUtils.GetTableInfo方法中，下面是GetTableInfo代码1-4:

public static TableInfo GetTableInfo(object entity,DbOperateType dbOpType)
{
    

    bool breakForeach = false;//是否跳出forach循环
    

    string strPrimaryKey = string.Empty;//主键变量 

    Type type = entity.GetType();//获取实体对象类型             
    

    TableInfo tableInfo = new TableInfo();//存储表信息的对象
           
    tableInfo.TableName = GetTableName(type);//获取表名
    

    //获取属性信息数组

    PropertyInfo[] properties = ReflectionUtils.GetProperties(type);
    foreach (PropertyInfo property in properties)
    {
        object propvalue = null;//属性值
        string columnName = string.Empty;//列名(数据库表结构)
        string propName = columnName = property.Name;//属性名称
        

        //如果不是做查询操作，获取属性值

        if(dbOpType != DbOperateType.SELECT)
            propvalue = ReflectionUtils.GetPropertyValue(entity, property);

        //获取实体对象属性自定义属性数组(如Column、Id、Table)                                
        object[] propertyAttrs = property.GetCustomAttributes(false);
        for (int i = 0; i < propertyAttrs.Length; i++) 
        {
            object propertyAttr = propertyAttrs[i];
            

            //判断是否忽略列，即不插入或更新到数据库中(IsInsert=false、IsUpdate=false)

            if (DbEntityUtils.IsCaseColumn(propertyAttr, dbOpType))
                break;

            //获取Column自定义属性中配置的Name值(表的列名)

            string tempName = GetColumnName(propertyAttr);
            

            //如果属性上未配置Column或Column上未配置Name，则取属性名称作为列名

              columnName = tempName == string.Empty ? propName : tempName;                    

            //判断自定义属性是否为Id
            if (propertyAttr is IdAttribute)
            {
                if (dbOpType == DbOperateType.INSERT)
                {                    

                    if (CommonUtils.IsNullOrEmpty(propvalue))
                    {

                                      //获取主键生成方式，存入tableInfo.Strategy属性中

                                      IdAttribute idAttr = propertyAttr as IdAttribute;
                                      tableInfo.Strategy = idAttr.Strategy;

                        //如果是插入操作，且主键ID值为空，则根据主键生成策略生成主键值，

                           //默认生成策略为自动增长，这种情况不处理，有数据库来处理

                           strPrimaryKey = DbEntityUtils.GetPrimaryKey(propertyAttr);
                        if (!string.IsNullOrEmpty(strPrimaryKey))
                            propvalue = strPrimaryKey;
                    }
                }

                //将列名，属性值加入到tableInfo的Id对象中
                  tableInfo.Id.Key = columnName;
                tableInfo.Id.Value = propvalue;

                //将列名、属性名作对应关系，保存于tableInfo.PropToColumn中，以作后用

                  tableInfo.PropToColumn.Put(propName, columnName);
                

                breakForeach = true;//如果为Id，则跳出Foreach循环
            }                        
        }

        //如果breakForeach=true跳出进入下一次循环continue
        if (breakForeach) { breakForeach = false; continue; }
        

        //将列名、属性值加入到tableInfo.Columns中

         tableInfo.Columns.Put(columnName, propvalue);
        

        //将属性名、列名作对应关系，以作后用

         tableInfo.PropToColumn.Put(propName, columnName);
    }            

    return tableInfo;
}

    在代码1-3中用到了获取实体对象中自定义属性所配置的表名、列名代码分别为1-5：

public static string GetTableName(Type classType)
{                        
    string strTableName = string.Empty;
    string strEntityName = string.Empty;

    strEntityName = classType.FullName;

    object classAttr = classType.GetCustomAttributes(false)[0];
    if (classAttr is TableAttribute)
    {
        TableAttribute tableAttr = classAttr as TableAttribute;
        strTableName = tableAttr.Name;
    }
    if (string.IsNullOrEmpty(strTableName))
    {
        throw new Exception("实体类:" + strEntityName + "的属性配置[Table(name=\"tablename\")]错误或未配置");
    }

    return strTableName;
}

    上面是获取表名，下面这段代码是获取列名，如下代码1-6：

public static string GetColumnName(object attribute)
{
    string columnName = string.Empty;
    if (attribute is ColumnAttribute)
    {
        ColumnAttribute columnAttr = attribute as ColumnAttribute;
        columnName = columnAttr.Name;
    }
    if (attribute is IdAttribute)
    {
        IdAttribute idAttr = attribute as IdAttribute;
        columnName = idAttr.Name;
    }

    return columnName;
}

1-3中第二行代码：String strSql = DbEntityUtils.GetFindByIdSql(tableInfo);
根据已经存入表信息的对象tableInfo来生成SQL语句，代码1-7：

public static string GetFindByIdSql(TableInfo tableInfo)
{
    StringBuilder sbColumns = new StringBuilder();

    if (tableInfo.Columns.ContainsKey(tableInfo.Id.Key))            
        tableInfo.Columns[tableInfo.Id.Key] = tableInfo.Id.Value;
    else
        tableInfo.Columns.Put(tableInfo.Id.Key, tableInfo.Id.Value);

    //根据Columns中的keys生成例如"studentid,studentno,name,sex..”字符串

    foreach (String key in tableInfo.Columns.Keys)
    {
        sbColumns.Append(key).Append(",");
    }

    sbColumns.Remove(sbColumns.ToString().Length - 1, 1);

    string strSql = "SELECT {0} FROM {1} WHERE {2} = " + AdoHelper.DbParmChar + "{2}";
    strSql = string.Format(strSql, sbColumns.ToString(), tableInfo.TableName, tableInfo.Id.Key);

    //得到如strSql=”select studentid,studentno,name,sex.. from student 

    where studentid=@studentid”;(oracle数据库参数前用:,sqlserver用@)

    return strSql;
}

   

下面是几个工具类代码，ReflectionUtils，根据反射获取或设置对象属性值、DynamicMethodCompiler动态方法编译，可提高反射的性能。

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;

namespace System.Orm.Common
{
    public class ReflectionUtils
    {
        public static PropertyInfo[] GetProperties(Type type)
        {
            return type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
        }

        public static FieldInfo[] GetFields(Type type)
        {
            return type.GetFields(BindingFlags.Public | BindingFlags.Instance);
        }

        public static void SetPropertyValue(object obj, PropertyInfo property, object value)
        {
            //创建Set委托
              SetHandler setter = DynamicMethodCompiler.CreateSetHandler(obj.GetType(),property);
                        
            //先获取该私有成员的数据类型
              Type type = property.PropertyType;

            //通过数据类型转换
              value = TypeUtils.ConvertForType(value, type);

            //将值设置到对象中
              setter(obj, value);            
        }       

        public static object GetPropertyValue(object obj, PropertyInfo property)
        {
            //创建Set委托
              GetHandler getter = DynamicMethodCompiler.CreateGetHandler(obj.GetType(), property);

            //获取属性值
              return getter(obj);
            
        }

        public static void SetFieldValue(object obj, FieldInfo field, object value)
        {
            //创建Set委托
              SetHandler setter = DynamicMethodCompiler.CreateSetHandler(obj.GetType(), field);
            
            //先获取该私有成员的数据类型
              Type type = field.FieldType;

            //通过数据类型转换
              value = TypeUtils.ConvertForType(value, type);

            //将值设置到对象中
              setter(obj, value);
        }

        public static object GetFieldValue(object obj, FieldInfo field)
        {
            //创建Set委托
              GetHandler getter = DynamicMethodCompiler.CreateGetHandler(obj.GetType(), field);

            //获取字段值
              return getter(obj);           
        }        
    }
}

DynamicMethodCompiler代码：

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;

namespace System.Orm.Common
{
    //定义GetHandler委托
     public delegate object GetHandler(object source);

    //定义SetHandler委托
     public delegate void SetHandler(object source, object value);

    //定义InstantiateObjectHandler委托
     public delegate object InstantiateObjectHandler();

    public sealed class DynamicMethodCompiler
    {
        // DynamicMethodCompiler
        private DynamicMethodCompiler() { }
        
        // 创建InstantiateObject委托
        internal static InstantiateObjectHandler CreateInstantiateObjectHandler(Type type)
        {
            ConstructorInfo constructorInfo = type.GetConstructor(BindingFlags.Public |
                   BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[0], null);

            if (constructorInfo == null)
            {
                throw new ApplicationException(string.Format("The type {0} must declare an empty constructor (the constructor may be private, internal, protected, protected internal, or public).", type));
            }

            DynamicMethod dynamicMethod = new DynamicMethod("InstantiateObject",
                    MethodAttributes.Static |
                  MethodAttributes.Public, CallingConventions.Standard, typeof(object),
                    null, type, true);

            ILGenerator generator = dynamicMethod.GetILGenerator();
            generator.Emit(OpCodes.Newobj, constructorInfo);
            generator.Emit(OpCodes.Ret);
            return (InstantiateObjectHandler)dynamicMethod.CreateDelegate
                    (typeof(InstantiateObjectHandler));
        }

        // 创建Get委托
        internal static GetHandler CreateGetHandler(Type type, PropertyInfo propertyInfo)
        {
            MethodInfo getMethodInfo = propertyInfo.GetGetMethod(true);
            DynamicMethod dynamicGet = CreateGetDynamicMethod(type);
            ILGenerator getGenerator = dynamicGet.GetILGenerator();

            getGenerator.Emit(OpCodes.Ldarg_0);
            getGenerator.Emit(OpCodes.Call, getMethodInfo);
            BoxIfNeeded(getMethodInfo.ReturnType, getGenerator);
            getGenerator.Emit(OpCodes.Ret);

            return (GetHandler)dynamicGet.CreateDelegate(typeof(GetHandler));
        }

        // 创建Get委托
        internal static GetHandler CreateGetHandler(Type type, FieldInfo fieldInfo)
        {
            DynamicMethod dynamicGet = CreateGetDynamicMethod(type);
            ILGenerator getGenerator = dynamicGet.GetILGenerator();

            getGenerator.Emit(OpCodes.Ldarg_0);
            getGenerator.Emit(OpCodes.Ldfld, fieldInfo);
            BoxIfNeeded(fieldInfo.FieldType, getGenerator);
            getGenerator.Emit(OpCodes.Ret);

            return (GetHandler)dynamicGet.CreateDelegate(typeof(GetHandler));
        }

        // 创建Set委托
        internal static SetHandler CreateSetHandler(Type type, PropertyInfo propertyInfo)
        {
            MethodInfo setMethodInfo = propertyInfo.GetSetMethod(true);
            DynamicMethod dynamicSet = CreateSetDynamicMethod(type);
            ILGenerator setGenerator = dynamicSet.GetILGenerator();

            setGenerator.Emit(OpCodes.Ldarg_0);
            setGenerator.Emit(OpCodes.Ldarg_1);
            UnboxIfNeeded(setMethodInfo.GetParameters()[0].ParameterType, setGenerator);
            setGenerator.Emit(OpCodes.Call, setMethodInfo);
            setGenerator.Emit(OpCodes.Ret);

            return (SetHandler)dynamicSet.CreateDelegate(typeof(SetHandler));
        }

        // 创建Set委托
        internal static SetHandler CreateSetHandler(Type type, FieldInfo fieldInfo)
        {
            DynamicMethod dynamicSet = CreateSetDynamicMethod(type);
            ILGenerator setGenerator = dynamicSet.GetILGenerator();

            setGenerator.Emit(OpCodes.Ldarg_0);
            setGenerator.Emit(OpCodes.Ldarg_1);
            UnboxIfNeeded(fieldInfo.FieldType, setGenerator);
            setGenerator.Emit(OpCodes.Stfld, fieldInfo);
            setGenerator.Emit(OpCodes.Ret);

            return (SetHandler)dynamicSet.CreateDelegate(typeof(SetHandler));
        }

        // 创建Get动态方法
        private static DynamicMethod CreateGetDynamicMethod(Type type)
        {
            return new DynamicMethod("DynamicGet", typeof(object),
                  new Type[] { typeof(object) }, type, true);
        }

        // 创建Set动态方法
        private static DynamicMethod CreateSetDynamicMethod(Type type)
        {
            return new DynamicMethod("DynamicSet", typeof(void),
                  new Type[] { typeof(object), typeof(object) }, type, true);
        }

        // BoxIfNeeded
        private static void BoxIfNeeded(Type type, ILGenerator generator)
        {
            if (type.IsValueType)
            {
                generator.Emit(OpCodes.Box, type);
            }
        }

        // UnboxIfNeeded
        private static void UnboxIfNeeded(Type type, ILGenerator generator)
        {
            if (type.IsValueType)
            {
                generator.Emit(OpCodes.Unbox_Any, type);
            }
        }
    }
}

 

TypeUtils代码：

using System;
using System.Collections.Generic;
using System.Text;

namespace System.Orm.Common
{
    public class TypeUtils
    {
        public static object ConvertForType(object value,Type type)
        {
            switch (type.FullName)
            {
                case "System.String":
                    value = value.ToString();
                    break;
                case "System.Boolean":
                    value = bool.Parse(value.ToString());
                    break;
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                    value = int.Parse(value.ToString());
                    break;
                case "System.Double":
                    value = double.Parse(value.ToString());
                    break;
                case "System.Decimal":
                    value = new decimal(double.Parse(value.ToString()));
                    break;                                                             
            }

            return value;
        }
    }
}

 

    好了，FindById方法所需的代码和工具类代码都在上面，到此已经完成。