/* *
 * 
 * 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
    }
}