ORM映射框架总结--数据操作(七)

2. 数据库操作实现类 SqlHelper

/**
 * 
 * 2009-4-22
 * 
 * 
 * 数据库操作的公共类
 * */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using CommonData.Model;

namespace CommonData.Data
{
    public class SqlHelper:BaseEntityHelper,IDbHelper
    {
        private static SqlHelper instance = null;

        public static SqlHelper Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new SqlHelper();
                }
                return instance;
            }
        }
        #region(数据库操作)
        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(IDbProvider provider, string sqlString)
        {
            return ExecuteNonQuery(provider,sqlString,false,null);
        }

        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <returns></returns>
        public int ExecuteNonQuery(IDbProvider provider, string sqlString, bool isProcedure)
        {
            return ExecuteNonQuery(provider,sqlString,isProcedure,null);
        }

        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="param">sql语句对应参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(IDbProvider provider, string sqlString, params IDataParameter[] param)
        {
            return ExecuteNonQuery(provider,sqlString,false,param);
        }

        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程,true 为存储过程</param>
        /// <param name="param">sql语句对应参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(IDbProvider provider, string sqlString, bool isProcedure, params IDataParameter[] param)
        {
            provider.Connection.Open();
            provider.Command.CommandText = sqlString;
            if (isProcedure)
            {
                provider.Command.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                provider.Command.CommandType = CommandType.Text;
            }
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(param);
            int line = provider.Command.ExecuteNonQuery();
            return line;
        }

        /// <summary>
        /// 返回查询语句第一行第一列
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public object ExecuteScalar(IDbProvider provider, string sqlString)
        {
            return ExecuteScalar(provider,sqlString,false,null);
        }

        /// <summary>
        /// 返回查询语句第一行第一列
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否是存储过程</param>
        /// <returns></returns>
        public object ExecuteScalar(IDbProvider provider, string sqlString, bool isProcedure)
        {
            return ExecuteScalar(provider,sqlString,isProcedure,null);
        }

        /// <summary>
        /// 返回查询语句第一行第一列
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="param">sql语句对应输入参数</param>
        /// <returns></returns>
        public object ExecuteScalar(IDbProvider provider, string sqlString, params IDataParameter[] param)
        {
            return ExecuteScalar(provider,sqlString,false,param);
        }

        /// <summary>
        /// 返回查询语句第一行第一列
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <param name="param">sql语句对应输入参数</param>
        /// <returns></returns>
        public object ExecuteScalar(IDbProvider provider, string sqlString, bool isProcedure, params IDataParameter[] param)
        {
            provider.Connection.Open();
            provider.Command.CommandText = sqlString;
            if (isProcedure)
            {
                provider.Command.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                provider.Command.CommandType = CommandType.Text;
            }
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(param);
            object result = provider.Command.ExecuteScalar();
            return result;
        }

        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(IDbProvider provider, string sqlString)
        {
            return ExecuteDataReader(provider,sqlString,false, null);
        }

        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(IDbProvider provider, string sqlString, bool isProcedure)
        {
            return ExecuteDataReader(provider,sqlString,isProcedure,null);
        }

        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="param">sql语句对应输入参数</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(IDbProvider provider, string sqlString, params IDataParameter[] param)
        {
            return ExecuteDataReader(provider,sqlString,false,param);
        }

        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <param name="param">sql语句对应输入参数</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(IDbProvider provider, string sqlString, bool isProcedure, params IDataParameter[] param)
        {
            provider.Connection.Open();
            provider.Command.CommandText = sqlString;
            if (isProcedure)
            {
                provider.Command.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                provider.Command.CommandType = CommandType.Text;
            }
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(param);
            IDataReader reader = provider.Command.ExecuteReader();
            return reader;
        }

        /// <summary>
        /// 获得数据表结构集合
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public DataTable ExecuteTable(IDbProvider provider, string sqlString)
        {
            return ExecuteTable(provider,sqlString,false,null);
        }

        /// <summary>
        ///  获得数据表结构集合
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <returns></returns>
        public DataTable ExecuteTable(IDbProvider provider, string sqlString, bool isProcedure)
        {
            return ExecuteTable(provider,sqlString,isProcedure,null);
        }

        /// <summary>
        /// 获得数据表结构集合
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="param">sql语句对应参数</param>
        /// <returns></returns>
        public DataTable ExecuteTable(IDbProvider provider, string sqlString, params IDataParameter[] param)
        {
            return ExecuteTable(provider,sqlString,false,param);
        }

        /// <summary>
        /// 获得数据表结构集合
        /// </summary>
        /// <param name="provider">数据提供加载驱动</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <param name="param">sql语句对应参数</param>
        /// <returns></returns>
        public DataTable ExecuteTable(IDbProvider provider, string sqlString, bool isProcedure, params IDataParameter[] param)
        {
            provider.Connection.Open();
            provider.Command.CommandText = sqlString;
            if (isProcedure)
            {
                provider.Command.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                provider.Command.CommandType = CommandType.Text;
            }
            provider.Command.Parameters.Clear();
            provider.Command.Parameters.AddRange(param);
            DataSet ds = new DataSet();
            provider.Adapter.Fill(ds);
            return ds.Tables[0];
        }
        #endregion

        #region(创建占位符参数)
        /// <summary>
        /// 根据占位符名称创建参数
        /// </summary>
        /// <param name="name">占位符名称</param>
        /// <returns></returns>
        public IDataParameter CreateParameter(string name)
        {
            return CreateParameter(name, null);
        }

        /// <summary>
        /// 根据占位符和值创建参数
        /// </summary>
        /// <param name="name">占位符名称</param>
        /// <param name="value">占位符的值</param>
        /// <returns></returns>
        public IDataParameter CreateParameter(string name, object value)
        {
            SqlParameter p = new SqlParameter(name, value);
            return p;
        }

        /// <summary>
        /// 根据占位符名称，类型和值创建参数
        /// </summary>
        /// <param name="name">占位符名称</param>
        /// <param name="type">参数的类型</param>
        /// <param name="value">参数的值</param>
        /// <returns></returns>
        public IDataParameter CreateParameter(string name, DataType type, object value)
        {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            p.Value = value;
            return p;
        }

        /// <summary>
        /// 根据占位符的名称，类型和大小创建参数
        /// </summary>
        /// <param name="name">占位符名称</param>
        /// <param name="type">参数类型</param>
        /// <param name="size">参数值大小</param>
        /// <returns></returns>
        public IDataParameter CreateParameter(string name, DataType type, int size)
        {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            if (size > 0)
                p.Size = size;
            return p;
        }

        /// <summary>
        /// 根据占位符的名称，类型，大小和值创建参数
        /// </summary>
        /// <param name="name">占位符名称</param>
        /// <param name="type">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public IDataParameter CreateParameter(string name, DataType type, int size, object value)
        {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            if (size > 0)
                p.Size = size;
            p.Value = value;
            return p;
        }

        /// <summary>
        /// 根据占位符名称和类型创建参数
        /// </summary>
        /// <param name="name">占位符名称</param>
        /// <param name="type">占位符类型</param>
        /// <returns></returns>
        public IDataParameter CreateParameter(string name, DataType type)
        {
            SqlParameter p = new SqlParameter(name, ConvertType(type));
            return p;
        }
        #endregion

        #region(自动生成sql语句)
        /// <summary>
        /// 创建插入的sql语句
        /// </summary>
        /// <param name="entity">实体的公共接口</param>
        /// <param name="param">数据值数组</param>
        /// <returns></returns>
        public string CreateInsertSql(IEntity entity, out IDataParameter[] param)
        {
            StringBuilder sbColumns = new StringBuilder("");
            StringBuilder sbValues = new StringBuilder("");
            ColumnAttribute[] columnAttribute = GetColumnAttribute(entity);
            TableAttribute tableAttribute = GetTableAttribute(entity);

            List<IDataParameter> list=new List<IDataParameter>();

            sbColumns.AppendFormat("insert into {0} (",tableAttribute.Name);
            sbValues.Append(" values (");
            for (int i = 0; i < columnAttribute.Length; i++)
            {
                if (columnAttribute[i].AutoIncrement == false)
                {
                    if (i == columnAttribute.Length - 1)
                    {
                        sbColumns.AppendFormat("{0}", columnAttribute[i].Name);
                        sbValues.Append("@"+columnAttribute[i].Name+"");
                    }
                    else
                    {
                        sbColumns.AppendFormat("{0},", columnAttribute[i].Name);
                        sbValues.Append("@" + columnAttribute[i].Name + ",");
                    }
                    list.Add(CreateParameter("@"+columnAttribute[i].Name,columnAttribute[i].Type,GetPropertyValue(entity,columnAttribute[i].Name)));
                }
            }
            sbColumns.Append(")");
            sbValues.Append(")");
            param = list.ToArray();
            return sbColumns.ToString()+sbValues.ToString();
        }

        /// <summary>
        /// 创建修改的sql语句
        /// </summary>
        /// <param name="entity">公共实体接口</param>
        /// <param name="param">修改参数值</param>
        /// <returns></returns>
        public string CreateUpdateSql(IEntity entity, out IDataParameter[] param)
        {
            StringBuilder sbColumn = new StringBuilder();
            StringBuilder sbWhere = new StringBuilder();
            ColumnAttribute[] columnAttribute = GetColumnAttribute(entity);
            TableAttribute tableAttribute = GetTableAttribute(entity);

            List<IDataParameter> list = new List<IDataParameter>();
            sbColumn.AppendFormat("update {0} set ",tableAttribute.Name);
            for (int i = 0; i < columnAttribute.Length; i++)
            {
                if (columnAttribute[i].PrimaryKey == true)
                {
                    sbWhere.Append(" where " + columnAttribute[i].Name + "=@" + columnAttribute[i].Name + " ");
                    list.Add(CreateParameter("@" + columnAttribute[i].Name, columnAttribute[i].Type, GetPropertyValue(entity, columnAttribute[i].Name)));
                }
                else
                {
                    if (columnAttribute[i].AutoIncrement == false)
                    {
                        if (i == columnAttribute.Length - 1)
                        {
                            sbColumn.AppendFormat(columnAttribute[i].Name + "=@" + columnAttribute[i].Name + " ");
                        }
                        else
                        {
                            sbColumn.AppendFormat(columnAttribute[i].Name + "=@" + columnAttribute[i].Name + ", ");
                        }
                        list.Add(CreateParameter("@" + columnAttribute[i].Name, columnAttribute[i].Type, GetPropertyValue(entity, columnAttribute[i].Name)));
                    }
                }
            }
            param = list.ToArray();
            return sbColumn.ToString()+sbWhere.ToString();
        }

        /// <summary>
        /// 创建删除的sql语句(根据主键删除)
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public string CreateDeleteSql(IEntity entity, out IDataParameter[] param)
        {
            StringBuilder sbTable = new StringBuilder("");
            StringBuilder sbWhere = new StringBuilder("");
            ColumnAttribute[] columnAttribute = GetColumnAttribute(entity);
            TableAttribute tableAttribute = GetTableAttribute(entity);

            List<IDataParameter> list = new List<IDataParameter>();
            sbTable.AppendFormat("delete from {0} ",tableAttribute.Name);
            foreach (ColumnAttribute ca in columnAttribute)
            {
                if (ca.PrimaryKey == true)
                {
                    sbWhere.AppendFormat(" where {0}=@{1} ",ca.Name,ca.Name);
                    list.Add(CreateParameter("@"+ca.Name,ca.Type,GetPropertyValue(entity,ca.Name)));
                }
            }
            param = list.ToArray();
            return sbTable.ToString() + sbWhere.ToString();
        }


        /// <summary>
        /// 创建查询单个实体的sql语句
        /// </summary>
        /// <param name="entity">实体公共接口</param>
        /// <param name="param">占位符参数</param>
        /// <returns></returns>
        public string CreateSingleSql(IEntity entity, ref IDataParameter[] param)
        {
            return CreateSingleSql(entity.GetType(),ref param);
        }

        /// <summary>
        /// 创建查询单个实体的sql语句
        /// </summary>
        /// <param name="type">实体类型</param>
        /// <param name="param">占位符参数</param>
        /// <returns></returns>
        public string CreateSingleSql(Type type, ref IDataParameter[] param)
        {
            StringBuilder sb = new StringBuilder("");
            ColumnAttribute[] columnAttribute = GetColumnAttribute(type);
            TableAttribute tableAttribute = GetTableAttribute(type);
            sb.AppendFormat("select * from {0} where ",tableAttribute.Name);
            List<IDataParameter> list = new List<IDataParameter>();
            foreach (ColumnAttribute ca in columnAttribute)
            {
                if (ca.PrimaryKey)
                {
                    sb.AppendFormat("{0}=@{1}",ca.Name,ca.Name);
                    list.Add(CreateParameter("@"+ca.Name,ca.Type,null));
                }
            }
            param = list.ToArray();
            return sb.ToString();
        }

        /// <summary>
        /// 创建查询所有数据的sql语句
        /// </summary>
        /// <param name="entity">公共实体借口</param>
        /// <returns></returns>
        public string CreateSingleSql(IEntity entity)
        {
            return CreateSingleSql(entity.GetType());
        }

        /// <summary>
        /// 创建查询所有数据的sql语句
        /// </summary>
        /// <param name="type">实体类型</param>
        /// <returns></returns>
        public string CreateSingleSql(Type type)
        {
            StringBuilder sb = new StringBuilder("");
            TableAttribute tableAttribute = GetTableAttribute(type);
            sb.AppendFormat("select * from {0}",tableAttribute.Name);
            return sb.ToString();
        }

        /// <summary>
        /// 根据对象的属性创建sql查询语句
        /// </summary>
        /// <param name="entity">实体公共接口</param>
        /// <param name="propertyName">实体属性名称</param>
        /// <param name="value">实体属性值</param>
        /// <returns></returns>
        public string CreateQueryByPropertySql(IEntity entity, string propertyName, object value, out IDataParameter[] param)
        {
            Type type = entity.GetType();
            return CreateQueryByPropertySql(type,propertyName,value,out param);
        }

        /// <summary>
        /// 根据对象的属性创建sql查询语句
        /// </summary>
        /// <param name="type">实体的类型</param>
        /// <param name="propertyName">实体属性名称</param>
        /// <param name="value">实体属性值</param>
        /// <returns></returns>
        public string CreateQueryByPropertySql(Type type, string propertyName, object value,out IDataParameter[] param)
        {
            TableAttribute tableAttribute = GetTableAttribute(type);
            StringBuilder sb = new StringBuilder("");
            sb.AppendFormat("select * from {0} where ",tableAttribute.Name);
            sb.Append(propertyName + "=@" + propertyName);
            List<IDataParameter> list = new List<IDataParameter>();
            list.Add(CreateParameter("@"+propertyName,value));
            param = list.ToArray();
            return sb.ToString();
        }

        /// <summary>
        /// 根据多个属性创建sql查询语句
        /// </summary>
        /// <param name="entity">公共实体接口</param>
        /// <param name="dic">属性值</param>
        /// <returns></returns>
        public string CreateQueryByPropertySql(IEntity entity, Dictionary<string, object> dic,out IDataParameter[] param)
        {
            return CreateQueryByPropertySql(entity.GetType(),dic,out param);
        }

        /// <summary>
        /// 根据多个属性创建sql查询语句
        /// </summary>
        /// <param name="type">实体类型</param>
        /// <param name="dic">属性值</param>
        /// <returns></returns>
        public string CreateQueryByPropertySql(Type type, Dictionary<string, object> dic, out IDataParameter[] param)
        {
            TableAttribute tableAttribute = GetTableAttribute(type);
            StringBuilder sb = new StringBuilder("");
            List<IDataParameter> list = new List<IDataParameter>();
            sb.AppendFormat("select * from {0} where ", tableAttribute.Name);
            for (int i = 0; i < dic.Keys.Count; i++)
            {
                string key = dic.Keys.ElementAt<string>(i);
                if (i == dic.Keys.Count - 1)
                {
                    sb.Append(key + "=@" + key + " ");
                    list.Add(CreateParameter("@" + key, dic[key]));
                }
                else
                {
                    sb.Append(key + "=@" + key + " and ");
                    list.Add(CreateParameter("@" + key, dic[key]));
                }
            }
            param = list.ToArray();

            return sb.ToString();
        }

        /// <summary>
        /// 根据某属性查询该属性值的数据行数
        /// </summary>
        /// <param name="entity">实体公共接口</param>
        /// <param name="propertyName">实体属性名称</param>
        /// <param name="value">实体属性值</param>
        /// <returns></returns>
        public string CreateQueryCountSql(IEntity entity, string propertyName, object value, out IDataParameter[] param)
        {
            TableAttribute tableAttribute = GetTableAttribute(entity.GetType());
            StringBuilder sb = new StringBuilder("");
            List<IDataParameter> list = new List<IDataParameter>();
            if(propertyName!=null && propertyName!="")
            {
                sb.AppendFormat("select count(*) from {0} where ",tableAttribute.Name);
                sb.Append(propertyName + "=@" + propertyName);
                list.Add(CreateParameter("@" + propertyName, value));
            }
            else
            {
                sb.AppendFormat("select count(*) from {0} ", tableAttribute.Name);
            }
            param = list.ToArray();
            return sb.ToString();

        }

        /// <summary>
        /// 查询某实体的数据行数
        /// </summary>
        /// <param name="entity">实体公共接口</param>
        /// <returns></returns>
        public string CreateQueryCountSql(IEntity entity)
        {
            IDataParameter[] param = null;
            return CreateQueryCountSql(entity,null,null,out param);
        }

        /// <summary>
        /// 更具实体对象创建分页查询语句
        /// </summary>
        /// <param name="entity">实体公共接口</param>
        /// <param name="page">翻页对象</param>
        /// <returns></returns>
        public string CreateQueryPageSql(IEntity entity, CommonPage page)
        {
            return CreateQueryPageSql(entity.GetType(),page);
        }

        /// <summary>
        /// 更具实体类型创建分页查询语句
        /// </summary>
        /// <param name="type">实体类型</param>
        /// <param name="page">翻页对象</param>
        /// <returns></returns>
        public string CreateQueryPageSql(Type type, CommonPage page)
        {
            TableAttribute tableAttribute = GetTableAttribute(type);
            ColumnAttribute[] columnAttribute = GetColumnAttribute(type);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select top {0} * from {1} ", page.PageSize, tableAttribute.Name);
            foreach (ColumnAttribute ca in columnAttribute)
            {
                if (ca.PrimaryKey)
                {
                    sb.AppendFormat("where {0} not in (select top {1} {2} from {3} )", ca.Name, (page.PageIndex - 1) * page.PageSize, ca.Name, tableAttribute.Name);
                    break;
                }
            }
            return sb.ToString();
        }
        #endregion





        #region(对象和集合的操作)
        /// <summary>
        /// 根据一个泛型类型获得实体对象
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="reader">只读数据流</param>
        /// <returns></returns>
        public T ConvertToEntity<T>(IDataReader reader)
        {
            T entity = default(T);
            Dictionary<int, LinkTableAttribute> dic = new Dictionary<int, LinkTableAttribute>();
            if (reader.Read())
            {
                entity = EntityFactory.CreateInstance<T>();
                PropertyInfo[] propertyInfos = GetTableInfo(entity.GetType()).Properties;
                dic.Clear();

                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    if (propertyInfos[i].GetCustomAttributes(typeof(LinkTableAttribute), false).Length > 0)
                    {
                        LinkTableAttribute linkTable = propertyInfos[i].GetCustomAttributes(typeof(LinkTableAttribute), false)[0] as LinkTableAttribute;
                        //dic.Add(linkTable.SqlPrefix, linkTable);
                        dic.Add(i, linkTable);
                    }
                }

                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    if (propertyInfos[i].GetCustomAttributes(typeof(ColumnAttribute), false).Length > 0)
                    {
                        object id = ConvertValue(propertyInfos[i].PropertyType, reader[propertyInfos[i].Name]);
                        propertyInfos[i].SetValue(entity, id, null);
                        ColumnAttribute column = propertyInfos[i].GetCustomAttributes(typeof(ColumnAttribute), false)[0] as ColumnAttribute;

                        foreach (int index in dic.Keys)
                        {
                            if (dic[index].SqlPrefix == column.Name)
                            {
                                Type entityType = dic[index].TableType;
                                IDataParameter[] param = new IDataParameter[] { };
                                string sql = CreateSingleSql(entityType, ref param);
                                param[0].Value = id;
                                IDbProvider provider = new SqlProvider();
                                using (IDataReader read = ExecuteDataReader(provider, sql, param))
                                {
                                    ConstructorInfo ci = entityType.GetConstructor(new Type[] { });
                                    Object result = ci.Invoke(new object[] { });
                                    if (read.Read())
                                    {
                                        for (int j = 0; j < read.FieldCount; j++)
                                        {
                                            string name = read.GetName(j);
                                            foreach (PropertyInfo pi in GetTableInfo(entityType).Properties)
                                            {
                                                if (pi.Name == name)
                                                {
                                                    pi.SetValue(result, ConvertValue(pi.PropertyType, read[name]), null);
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                    propertyInfos[index].SetValue(entity, result, null);
                                }
                            }
                        }
                    }
                }
            }
            return entity;
        }

        /// <summary>
        /// 根据一个泛型类型查询一个集合
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="reader">只读数据流</param>
        /// <returns></returns>
        public IList<T> ConvertToList<T>(IDataReader reader)
        {
            IList<T> list = new List<T>();
            //Dictionary<string, LinkTableAttribute> dic = new Dictionary<string, LinkTableAttribute>();
            Dictionary<int, LinkTableAttribute> dic = new Dictionary<int, LinkTableAttribute>();
            while (reader.Read())
            {
                T entity = EntityFactory.CreateInstance<T>();
                PropertyInfo[] propertyInfos = GetTableInfo(entity.GetType()).Properties;
                dic.Clear();

                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    if (propertyInfos[i].GetCustomAttributes(typeof(LinkTableAttribute), false).Length > 0)
                    {
                        LinkTableAttribute linkTable = propertyInfos[i].GetCustomAttributes(typeof(LinkTableAttribute), false)[0] as LinkTableAttribute;
                        //dic.Add(linkTable.SqlPrefix, linkTable);
                        dic.Add(i, linkTable);
                    }
                }

                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    if (propertyInfos[i].GetCustomAttributes(typeof(ColumnAttribute), false).Length > 0)
                    {
                        object id=ConvertValue(propertyInfos[i].PropertyType, reader[propertyInfos[i].Name]);
                        propertyInfos[i].SetValue(entity, id, null);
                        ColumnAttribute column = propertyInfos[i].GetCustomAttributes(typeof(ColumnAttribute), false)[0] as ColumnAttribute;

                        foreach (int index in dic.Keys)
                        {
                            if (dic[index].SqlPrefix==column.Name)
                            {
                                Type entityType = dic[index].TableType;
                                IDataParameter[] param = new IDataParameter[] { };
                                string sql = CreateSingleSql(entityType, ref param);
                                param[0].Value = id;
                                IDbProvider provider = new SqlProvider();
                                using (IDataReader read = ExecuteDataReader(provider, sql, param))
                                {
                                    ConstructorInfo ci = entityType.GetConstructor(new Type[] { });
                                    Object result = ci.Invoke(new object[] { });
                                    if (read.Read())
                                    {
                                        for (int j = 0; j < read.FieldCount; j++)
                                        {
                                            string name = read.GetName(j);
                                            foreach (PropertyInfo pi in GetTableInfo(entityType).Properties)
                                            {
                                                if (pi.Name == name)
                                                {
                                                    pi.SetValue(result, ConvertValue(pi.PropertyType, read[name]), null);
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                    propertyInfos[index].SetValue(entity,result,null);
                                }
                            }
                        }
                    }
                }
                list.Add(entity);
            }
            return list;
        }


        /// <summary>
        /// 根据实体共同接口获得属性值
        /// </summary>
        /// <param name="entity">实体公共接口</param>
        /// <param name="name">属性名称</param>
        /// <returns></returns>
        public object GetPropertyValue(IEntity entity, string name)
        {
            PropertyInfo pi = entity.GetType().GetProperty(name);
            object result = null;
            if (result == null)
            {
                result = pi.GetValue(entity,null);
            }
            return result;
        }

        /// <summary>
        /// 根据泛型类型获得实体属性值
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="entity">实体公共接口</param>
        /// <param name="name">实体属性名称</param>
        /// <returns></returns>
        public T GetPropertyValue<T>(IEntity entity, string name)
        {
            object result = GetPropertyValue(entity,name);
            if (result == null)
            {
                return default(T);
            }
            else
            {
                return (T)result;
            }
        }
        #endregion

        #region(对象和数据一些其他操作)
        /// <summary>
        /// 根据公共实体接口类型设置该实体某个属性的值
        /// </summary>
        /// <param name="entity">公共实体接口</param>
        /// <param name="name">属性名称</param>
        /// <param name="value">属性的值</param>
        public void SetPropertyValue(IEntity entity, string name, object value)
        {
            PropertyInfo pi = entity.GetType().GetProperty(name);
            if (pi != null)
            {
                pi.SetValue(entity,value,null);
            }
        }

        /// <summary>
        /// 释放内存空间
        /// </summary>
        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 数据库类型的转化
        /// </summary>
        /// <param name="type">程序中的类型</param>
        /// <returns></returns>
        private SqlDbType ConvertType(DataType type)
        {
            SqlDbType sqlType = SqlDbType.BigInt;
            switch (type)
            {
                case DataType.Binary:
                    sqlType = SqlDbType.Binary;
                    break;
                case DataType.Bit:
                    sqlType = SqlDbType.Bit;
                    break;
                case DataType.Char:
                    sqlType = SqlDbType.Char;
                    break;
                case DataType.Date:
                    sqlType = SqlDbType.Date;
                    break;
                case DataType.DateTime:
                    sqlType = SqlDbType.DateTime;
                    break;
                case DataType.Decimal:
                    sqlType = SqlDbType.Decimal;
                    break;
                case DataType.Double:
                    sqlType = SqlDbType.Float;
                    break;
                case DataType.Float:
                    sqlType = SqlDbType.Float;
                    break;
                case DataType.GUID:
                    sqlType = SqlDbType.UniqueIdentifier;
                    break;
                case DataType.Image:
                    sqlType = SqlDbType.Image;
                    break;
                case DataType.Int16:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.Int32:
                    sqlType = SqlDbType.Int;
                    break;
                case DataType.Int4:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.Int64:
                    sqlType = SqlDbType.BigInt;
                    break;
                case DataType.Int8:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.Text:
                    sqlType = SqlDbType.NText;
                    break;
                case DataType.UnsignedInt16:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.UnsignedInt32:
                    sqlType = SqlDbType.Int;
                    break;
                case DataType.UnsignedInt4:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.UnsignedInt8:
                    sqlType = SqlDbType.TinyInt;
                    break;
                case DataType.VarChar:
                    sqlType = SqlDbType.NVarChar;
                    break;
            }
            return sqlType;
        }

        /// <summary>
        /// 根据数据类型转化
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private object ConvertValue(Type type, object value)
        {
            if (value == DBNull.Value)
                return null;
            return Convert.ChangeType(value, type);
        }


        public T ConvertValue<T>(Type type, object value)
        {
            object result = ConvertValue(type, value);
            if (result == null)
                return default(T);
            else
                return (T)result;
        }
        #endregion
    }
}

 

 

 

方法参数再上一章都介绍了，这里不再讲解