JLH/JLHHJSvr/DBA/DAL_SQLite/DbSqlHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;

namespace LJLib.DAL.SQL
{
    /// <summary>
    /// 全局初始化时，需要添加要支持的引擎(SqlServer/Sqlite)
    /// </summary>
    internal static class DbSqlHelper
    {
        /// <summary>
        /// 从数据库获取当前时间
        /// SQL Server  :SELECT GETDATE()
        /// SQLite      :SELECT DATETIME() 
        /// </summary>
        public static DateTime GetServerTime<TCommand>(TCommand cmd) where TCommand : DbCommand
        {
            var engine = GetEngine(cmd.GetType());
            return engine.GetServerTime(cmd);
        }

        /// <summary>
        /// 获取单表中符合条件的行数
        /// </summary>
        public static int SelectCount<TCommand>(TCommand cmd, string table, string wherestr, IDictionary<string, object> sqlparams = null) where TCommand : DbCommand
        {
            string strcmd = "SELECT COUNT(0) FROM " + table;
            if (!string.IsNullOrEmpty(wherestr))
            {
                wherestr = wherestr.Trim();
                strcmd += " WHERE " + wherestr;
            }
            cmd.CommandText = strcmd;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            if (sqlparams != null)
            {
                foreach (var sqlparam in sqlparams)
                {
                    var value = sqlparam.Value;
                    if (value == null)
                    {
                        value = DBNull.Value;
                    }
                    AddWithValue(cmd, sqlparam.Key, value);
                }
            }
            return Convert.ToInt32(cmd.ExecuteScalar());
        }

        /// <summary>
        /// 添加参数值
        /// </summary>
        private static void AddWithValue<TCommand>(TCommand cmd, string name, object value) where TCommand : DbCommand
        {
            var engine = GetEngine(cmd.GetType());
            engine.AddWithValue(cmd, name, value);
            if (cmd.Parameters[name].Value == null)
            {
                cmd.Parameters[name].Value = DBNull.Value;
            }
        }


        public static void SelectJoin<TCommand, T>(TCommand cmd, string selectStr, string whereStr, IDictionary<string, object> parms, string orderByStr, string outputFields, int pageindex, int pagesize, List<T> returnList)
            where TCommand : DbCommand
            where T : new()
        {
            cmd.CommandText = SqlStrHelper.BuildSelectStr(selectStr, ref outputFields, whereStr, orderByStr, pageindex, pagesize); ;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            if (parms != null)
            {
                foreach (var parm in parms)
                {
                    AddWithValue(cmd, parm.Key, parm.Value);
                }
            }

            var properties = GetProperties(typeof(T), outputFields);
            GetValues(cmd, returnList, properties);
        }
        public static void SelectJoin<TCommand, T>(TCommand cmd, string selectStr, string whereStr, IDictionary<string, object> parms, string orderByStr, string outputFields, int pageindex, int pagesize, List<T> returnList, ref int totalcnt)
            where TCommand : DbCommand
            where T : new()
        {
            var parseResult = SqlStrHelper.ParseSelectStr(selectStr);
            if (parseResult.selectStr.ToUpper().Contains("DISTINCT"))
            {
                throw new NotImplementedException("不要过分依赖DISTINCT");
            }
            cmd.CommandText = SqlStrHelper.BuildSelectStr(parseResult, outputFields, whereStr, orderByStr, pageindex, pagesize);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            if (parms != null)
            {
                foreach (var parm in parms)
                {
                    AddWithValue(cmd, parm.Key, parm.Value);
                }
            }
            var properties = GetProperties(typeof(T), outputFields);
            GetValues(cmd, returnList, properties);

            string strcmd = parseResult.selectStr + " COUNT(0) " + parseResult.fromStr;
            if (!string.IsNullOrEmpty(whereStr))
            {
                whereStr = whereStr.Trim();
                strcmd += " WHERE " + whereStr;
            }
            cmd.CommandText = strcmd;
            //cmd.CommandType = CommandType.Text;
            //cmd.Parameters.Clear();
            try
            {
                totalcnt = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("ex:{0}\r\ncmd:{1}", ex, cmd.CommandText));
            }
        }

        private static object ConvertValue(Type parmType, object value)
        {
            if (!parmType.IsAssignableFrom(value.GetType()))
            {
                if (parmType.IsAssignableFrom(typeof(byte)))
                {
                    value = Convert.ToByte(value);
                }
                else if (parmType.IsAssignableFrom(typeof(sbyte)))
                {
                    value = Convert.ToSByte(value);
                }
                else if (parmType.IsAssignableFrom(typeof(short)))
                {
                    value = Convert.ToInt16(value);
                }
                else if (parmType.IsAssignableFrom(typeof(ushort)))
                {
                    value = Convert.ToUInt16(value);
                }
                else if (parmType.IsAssignableFrom(typeof(int)))
                {
                    value = Convert.ToInt32(value);
                }
                else if (parmType.IsAssignableFrom(typeof(uint)))
                {
                    value = Convert.ToUInt32(value);
                }
                else if (parmType.IsAssignableFrom(typeof(long)))
                {
                    value = Convert.ToInt64(value);
                }
                else if (parmType.IsAssignableFrom(typeof(ulong)))
                {
                    value = Convert.ToUInt64(value);
                }
                else if (parmType.IsAssignableFrom(typeof(float)))
                {
                    value = Convert.ToSingle(value);
                }
                else if (parmType.IsAssignableFrom(typeof(double)))
                {
                    value = Convert.ToDouble(value);
                }
                else if (parmType.IsAssignableFrom(typeof(decimal)))
                {
                    value = Convert.ToDecimal(value);
                }
                else if (parmType.IsAssignableFrom(typeof(bool)))
                {
                    value = Convert.ToBoolean(value);
                }
                else if (parmType.IsAssignableFrom(typeof(bool)))
                {
                    value = Convert.ToBoolean(value);
                }
                else if (parmType.IsAssignableFrom(typeof(char)))
                {
                    value = Convert.ToChar(value);
                }
                else if (parmType.IsAssignableFrom(typeof(DateTime)))
                {
                    value = Convert.ToDateTime(value);
                }
                else if (parmType.IsAssignableFrom(typeof(string)))
                {
                    value = Convert.ToString(value);
                }
                else if (parmType.IsEnum)
                {
                    value = Enum.ToObject(parmType, value);
                }
                else
                {
                    throw new Exception(string.Format("不能将{0}转换成{1}", value.GetType(), parmType));
                }
            }
            return value;
        }

        public static int Select<TCommand, T>(TCommand cmd, string tablename, string whereStr, IDictionary<string, object> parms, string orderStr, int pagesize, int pageindex, IList<T> modles, IDictionary<string, string> maps, string fields)
            where TCommand : DbCommand
            where T : new()
        {
            var strCmd = string.Format("SELECT {0} FROM {1}", fields, tablename);
            cmd.CommandText = SqlStrHelper.BuildSelectStr(strCmd, ref fields, whereStr, orderStr, pageindex, pagesize);

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            if (parms != null)
            {
                foreach (var parm in parms)
                {
                    AddWithValue(cmd, parm.Key, parm.Value);
                }
            }

            var targetMaps = GetTargetMaps<T>(maps, fields);

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    T modle = new T();
                    GetValue(reader, modle, targetMaps);
                    modles.Add(modle);
                }
            }

            return modles.Count;
        }

        private static void GetValue(DbDataReader reader, object modle, Dictionary<string, PropertyInfo> targetMaps)
        {
            foreach (var property in targetMaps)
            {
                MethodInfo setMethod = property.Value.GetSetMethod();
                if (setMethod == null)
                {
                    continue;
                }
                object value = null;
                try
                {
                    value = reader[property.Key];
                }
                catch (Exception)
                {
                    continue;
                }
                if (value == null || value == DBNull.Value)
                {
                    continue;
                }
                if (value is string)
                {
                    value = value.ToString().Trim();
                }
                var parmType = setMethod.GetParameters()[0].ParameterType;
                value = ConvertValue(parmType, value);
                setMethod.Invoke(modle, new object[] { value });
            }
        }

        public static int Select<TCommand, T>(TCommand cmd, IList<T> modles, string wherestr, string fields = "*")
            where TCommand : DbCommand
            where T : new()
        {
            return Select(cmd, modles, wherestr, null, null, fields, 0, 0);
        }

        public static int Select<TCommand, T>(TCommand cmd, IList<T> modles, string wherestr, IDictionary<string, object> sqlparams, string fields = "*")
            where TCommand : DbCommand
            where T : new()
        {
            return Select(cmd, modles, wherestr, sqlparams, null, fields, 0, 0);
        }

        public static int Select<TCommand, T>(TCommand cmd, IList<T> modles, string wherestr, IDictionary<string, object> sqlparams, int pageindex, int pagesize, string orderstr, string fields = "*")
            where TCommand : DbCommand
            where T : new()
        {
            return Select(cmd, modles, wherestr, sqlparams, orderstr, fields, pageindex, pagesize);
        }

        public static int Select<TCommand, T>(TCommand cmd, IList<T> modles, string whereStr, IDictionary<string, object> sqlparams, string orderByStr, string outputFields, int pageindex, int pagesize)
            where TCommand : DbCommand
            where T : new()
        {
            if (modles.IsReadOnly)
            {
                throw new ArgumentException("参数modles不能为只读");
            }

            var propertyies = GetProperties(typeof(T), outputFields);
            if (propertyies.Count == 0)
            {
                propertyies = new List<PropertyInfo>(GetProperties(typeof(T)).Values);
            }
            var strCmd = string.Empty;
            foreach (var propertyInfo in propertyies)
            {
                if (string.IsNullOrEmpty(strCmd))
                {
                    strCmd = propertyInfo.Name;
                }
                else
                {
                    strCmd += "," + propertyInfo.Name;
                }
            }
            strCmd = "SELECT " + strCmd + " FROM " + typeof(T).Name;

            var outCmdStr = SqlStrHelper.BuildSelectStr(strCmd, ref outputFields, whereStr, orderByStr, pageindex, pagesize);
            cmd.CommandText = outCmdStr;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            if (sqlparams != null)
            {
                foreach (var parm in sqlparams)
                {
                    AddWithValue(cmd, parm.Key, parm.Value);
                }
            }

            GetValues(cmd, modles, propertyies);
            return modles.Count;
        }

        private static void GetValues<TCommand, T>(TCommand cmd, IList<T> modles, IList<PropertyInfo> propertyies)
            where TCommand : DbCommand
            where T : new()
        {
            try
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        T modle = new T();
                        GetValue(reader, modle, propertyies);
                        modles.Add(modle);
                    }
                }
            }
            catch (Exception)
            {
                //Debug.Write(cmd.CommandText);
                throw;
            }
        }

        private static void GetValue<T>(DbDataReader reader, T modle, IList<PropertyInfo> propertyies)
        {
            foreach (PropertyInfo property in propertyies)
            {
                MethodInfo setMethod = property.GetSetMethod();
                if (setMethod == null)
                {
                    continue;
                }
                object value = null;
                try
                {
                    value = reader[property.Name];
                }
                catch (Exception)
                {
                    continue;
                }
                if (value == null || value == DBNull.Value)
                {
                    continue;
                }
                if (value is string)
                {
                    value = value.ToString().Trim();
                }
                var parmType = setMethod.GetParameters()[0].ParameterType;
                value = ConvertValue(parmType, value);
                setMethod.Invoke(modle, new object[] { value });
            }
        }

        private static object _pksyncRoot = new object();
        private static Dictionary<Type, string[]> _type_pks = new Dictionary<Type, string[]>();

        private static string[] GetPKs(Type type)
        {
            // DONE: 返回模型类的主键
            lock (_pksyncRoot)
            {
                if (_type_pks.ContainsKey(type))
                {
                    return _type_pks[type];
                }
                var attrs = type.GetCustomAttributes(typeof(PK), true);
                if (attrs.Length == 0)
                {
                    _type_pks[type] = new string[] { };
                }
                else
                {
                    PK pk = attrs[0] as PK;
                    _type_pks[type] = pk.keys;
                }
                return _type_pks[type];
            }
        }

        public static int SelectOne<TCommand, T>(TCommand cmd, string tablename, string whereStr, IDictionary<string, object> parms, T Modle, string fields) where TCommand : DbCommand
        {
            // 生成语句
            var propertyies = GetProperties(typeof(T), fields);
            if (propertyies.Count == 0)
            {
                propertyies = new List<PropertyInfo>(GetProperties(typeof(T)).Values);
            }
            var strCmd = string.Empty;
            foreach (var propertyInfo in propertyies)
            {
                if (string.IsNullOrEmpty(strCmd))
                {
                    strCmd = propertyInfo.Name;
                }
                else
                {
                    strCmd += "," + propertyInfo.Name;
                }
            }
            strCmd = "SELECT " + strCmd + " FROM " + tablename;

            if (!string.IsNullOrEmpty(whereStr))
            {
                strCmd += " WHERE " + whereStr;
            }

            cmd.CommandText = strCmd;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            if (parms != null)
            {
                foreach (var kvp in parms)
                {
                    AddWithValue(cmd, kvp.Key, kvp.Value);
                }
            }

            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                {
                    return 0;
                }
                GetValue(reader, Modle, propertyies);
                return 1;
            }
        }

        public static int SelectOne<TCommand, T>(TCommand cmd, T modle, string fields = "*") where TCommand : DbCommand
        {
            // 查找非空主键
            var fpkpropertyies = GetNotNullProperties(modle);

            // 生成语句
            var propertyies = GetProperties(typeof(T), fields);
            if (propertyies.Count == 0)
            {
                propertyies = new List<PropertyInfo>(GetProperties(typeof(T)).Values);
            }
            var strCmd = string.Empty;
            foreach (var propertyInfo in propertyies)
            {
                if (string.IsNullOrEmpty(strCmd))
                {
                    strCmd = propertyInfo.Name;
                }
                else
                {
                    strCmd += "," + propertyInfo.Name;
                }
            }
            strCmd = "SELECT " + strCmd + " FROM " + typeof(T).Name;

            strCmd += string.Format(" WHERE {0}=@{0}", fpkpropertyies[0].Name);

            for (int i = 1; i < fpkpropertyies.Count; i++)
            {
                strCmd += string.Format(" AND {0}=@{0}", fpkpropertyies[i].Name);
            }

            cmd.CommandText = strCmd;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            foreach (var property in fpkpropertyies)
            {
                object value = property.GetGetMethod().Invoke(modle, null);
                AddWithValue(cmd, "@" + property.Name, value);
            }

            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                {
                    return 0;
                }
                GetValue(reader, modle, propertyies);
                return 1;
            }
        }

        /// <summary>
        /// 查找非空主键
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="modle"></param>
        /// <returns></returns>
        private static IList<PropertyInfo> GetNotNullProperties<T>(T modle)
        {
            var pks = GetPKs(typeof(T));
            if (pks == null || pks.Length == 0)
            {
                throw new Exception(string.Format("模型类{0}没有定义主键", typeof(T).FullName));
            }
            IList<PropertyInfo> fpkpropertyies = null;
            foreach (var pk in pks)
            {
                if (string.IsNullOrEmpty(pk))
                {
                    throw new Exception(string.Format("模型类{0}的主键不能为空", typeof(T).FullName));
                }
                var withnullpk = false;
                var pkproperties = GetProperties(typeof(T), pk);
                if (pkproperties.Count == 0)
                {
                    throw new Exception(string.Format("模型类{0}的主键与字段名不符", typeof(T).FullName));
                }
                foreach (var propertyInfo in pkproperties)
                {
                    var getMethod = propertyInfo.GetGetMethod();
                    var value = getMethod.Invoke(modle, null);
                    if (value == null)
                    {
                        withnullpk = true;
                        break;
                    }
                }
                if (!withnullpk)
                {
                    fpkpropertyies = pkproperties;
                    break;
                }
            }

            if (fpkpropertyies == null)
            {
                throw new Exception("转入模型主键为空");
            }
            return fpkpropertyies;
        }

        public static int Delete<TCommand, T>(TCommand cmd, T modle) where TCommand : DbCommand
        {
            var fpkpropertyies = GetNotNullProperties(modle);

            var strCmd = string.Empty;
            strCmd = "DELETE FROM " + typeof(T).Name;
            strCmd += string.Format(" WHERE {0}=@{0}", fpkpropertyies[0].Name);
            for (int i = 1; i < fpkpropertyies.Count; i++)
            {
                strCmd += string.Format(" AND {0}=@{0}", fpkpropertyies[i].Name);
            }

            cmd.CommandText = strCmd;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            foreach (var property in fpkpropertyies)
            {
                object value = property.GetGetMethod().Invoke(modle, null);
                AddWithValue(cmd, "@" + property.Name, value);
            }

            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 异构对象插入数据库
        /// </summary>
        public static int Insert<TCommand, T>(TCommand cmd, string tablename, Dictionary<string, string> maps, T modle, string fields = "*") where TCommand : DbCommand
        {
            if (string.IsNullOrEmpty(tablename))
            {
                tablename = typeof(T).Name;
            }

            // DONE: 处理大小写问题
            var targetfields = GetTargetMaps<T>(maps, fields);

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();

            string tablefields = string.Empty;
            string valuefields = string.Empty;
            foreach (var targetfield in targetfields)
            {
                if (string.IsNullOrEmpty(tablefields))
                {
                    tablefields = targetfield.Key;
                    valuefields = "@" + targetfield.Key;
                }
                else
                {
                    tablefields += "," + targetfield.Key;
                    valuefields += ",@" + targetfield.Key;
                }
                AddWithValue(cmd, "@" + targetfield.Key, targetfield.Value.GetGetMethod().Invoke(modle, null));
            }

            if (string.IsNullOrEmpty(tablefields))
            {
                throw  new Exception("没有一个有效插入字段");
            }

            cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})", tablename, tablefields, valuefields);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Trace.Write(ex.ToString());
                Trace.Write(ModleToString(modle));
                throw;
            }
        }

        public static int Insert<TCommand, T>(TCommand cmd, T modle, string fields = "*") where TCommand : DbCommand
        {
            return Insert(cmd, null, null, modle, fields);
;        }

        private static Dictionary<string, PropertyInfo> GetTargetMaps<T>(IDictionary<string, string> maps, string fields)
        {
            var newmaps = new Dictionary<string, string>();
            if (maps != null)
            {
                foreach (var map in maps)
                {
                    newmaps[map.Key.Trim().ToLower()] = map.Value.Trim().ToLower();
                }
            }

            var targetfields = new Dictionary<string, PropertyInfo>();
            var orgfields = GetProperties(typeof (T));
            string[] arr_fields;
            if (fields.Equals("*"))
            {
                arr_fields = orgfields.Keys.ToArray();
            }
            else
            {
                arr_fields = fields.Split(',');
            }
            foreach (var field in arr_fields)
            {
                var key = field.Trim().ToLower();
                if (targetfields.ContainsKey(key))
                {
                    continue;
                }
                if (newmaps.ContainsKey(key) && orgfields.ContainsKey(newmaps[key]))
                {
                    targetfields[key] = orgfields[newmaps[key]];
                }
                else if (orgfields.ContainsKey(key))
                {
                    targetfields[key] = orgfields[key];
                }
            }
            return targetfields;
        }

        public static int Update<TCommand, T>(TCommand cmd, string tablename, Dictionary<string, string> maps, T modle, string pkfields, string fields = "*") where TCommand : DbCommand
        {
            // DONE: 处理大小写问题
            var pkfields_dir = GetTargetMaps<T>(maps, pkfields);
            var targetfields = GetTargetMaps<T>(maps, fields);

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();

            string keyFields = string.Empty;
            foreach (var targetfield in pkfields_dir)
            {
                if (string.IsNullOrEmpty(keyFields))
                {
                    keyFields = string.Format("{0} = @{0}", targetfield.Key);
                }
                else
                {
                    keyFields += string.Format(" AND {0} = @{0}", targetfield.Key);
                }
                AddWithValue(cmd, "@" + targetfield.Key, targetfield.Value.GetGetMethod().Invoke(modle, null));
            }

            if (string.IsNullOrEmpty(keyFields))
            {
                throw new Exception("没有一个有效的条件字段");
            }

            string setFields = string.Empty;
            foreach (var targetfield in targetfields)
            {
                if (string.IsNullOrEmpty(setFields))
                {
                    setFields = string.Format("{0} = @{0}", targetfield.Key);
                }
                else
                {
                    setFields += string.Format(", {0} = @{0}", targetfield.Key);
                }
                AddWithValue(cmd, "@" + targetfield.Key, targetfield.Value.GetGetMethod().Invoke(modle, null));
            }

            if (string.IsNullOrEmpty(setFields))
            {
                throw new Exception("没有一个有效更新字段");
            }

            cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tablename, setFields, keyFields);

            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Trace.Write(ex.ToString());
                Trace.Write(ModleToString(modle));
                throw;
            }
        }

        public static int Update<TCommand, T>(TCommand cmd, T modle, string fields = "*") where TCommand : DbCommand
        {
            var pks = GetPKs(typeof(T));
            if (pks == null || pks.Length == 0)
            {
                throw new Exception(string.Format("当前类{0}没有定义关键字段", typeof(T).Name));
            }

            var pkfields = pks[0];
            if (pks.Length > 0)
            {
                foreach (var pk in pks)
                {
                    if (string.IsNullOrEmpty(pk))
                    {
                        throw new Exception(string.Format("模型类{0}的主键不能为空", typeof(T).FullName));
                    }
                    var withnullpk = false;
                    var pkproperties = GetProperties(typeof(T), pk);
                    if (pkproperties.Count == 0)
                    {
                        throw new Exception(string.Format("模型类{0}的主键与字段名不符", typeof(T).FullName));
                    }
                    foreach (var propertyInfo in pkproperties)
                    {
                        var getMethod = propertyInfo.GetGetMethod();
                        var value = getMethod.Invoke(modle, null);
                        if (value == null)
                        {
                            withnullpk = true;
                            break;
                        }
                    }
                    if (!withnullpk)
                    {
                        pkfields = pk;
                        break;
                    }
                }
            }
            
            return Update(cmd, typeof (T).Name, null, modle, pkfields, fields);
        }

        public static int InsertOrUpdate<TCommand, T>(TCommand cmd, T modle, string fields = "*") where TCommand : DbCommand
        {
            try
            {
                // 查找非空主键
                var ifinsert = false;

                var pks = GetPKs(typeof(T));
                if (pks == null || pks.Length == 0)
                {
                    ifinsert = true;
                }

                IList<PropertyInfo> fpkpropertyies = null;
                if (!ifinsert)
                {
                    foreach (var pk in pks)
                    {
                        if (string.IsNullOrEmpty(pk))
                        {
                            throw new Exception(string.Format("模型类{0}的主键不能为空", typeof(T).FullName));
                        }
                        var withnullpk = false;
                        var pkproperties = GetProperties(typeof(T), pk);
                        if (pkproperties.Count == 0)
                        {
                            throw new Exception(string.Format("模型类{0}的主键与字段名不符", typeof(T).FullName));
                        }
                        foreach (var propertyInfo in pkproperties)
                        {
                            var getMethod = propertyInfo.GetGetMethod();
                            var value = getMethod.Invoke(modle, null);
                            if (value == null)
                            {
                                withnullpk = true;
                                break;
                            }
                        }
                        if (!withnullpk)
                        {
                            fpkpropertyies = pkproperties;
                            break;
                        }
                    }

                    if (fpkpropertyies == null)
                    {
                        ifinsert = true;
                    }
                }

                List<PropertyInfo> propertyies = null;
                if (!ifinsert)
                {
                    // 按主键更新
                    propertyies = GetProperties(typeof(T), fields);
                    if (propertyies.Count == 0)
                    {
                        propertyies = new List<PropertyInfo>(GetProperties(typeof(T)).Values);
                    }

                    var pknames = new HashSet<string>();
                    foreach (var fpk in fpkpropertyies)
                    {
                        pknames.Add(fpk.Name);
                    }

                    for (int i = propertyies.Count - 1; i >= 0; i--)
                    {
                        if (pknames.Contains(propertyies[i].Name))
                        {
                            propertyies.RemoveAt(i);
                        }
                    }

                    if (propertyies.Count == 0)
                    {
                        string strCmd = string.Format("SELECT COUNT(0) FROM " + typeof(T).Name + " WHERE {0}=@{0}", fpkpropertyies[0].Name);
                        for (int i = 1; i < fpkpropertyies.Count; i++)
                        {
                            strCmd += string.Format(" AND {0}=@{0}", fpkpropertyies[i].Name);
                        }

                        cmd.CommandText = strCmd;
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Clear();
                        foreach (var pk in fpkpropertyies)
                        {
                            object value = pk.GetGetMethod().Invoke(modle, null);
                            AddWithValue(cmd, "@" + pk.Name, value);
                        }

                        var cnt = Convert.ToUInt32(cmd.ExecuteScalar());
                        if (cnt == 0)
                        {
                            ifinsert = true;
                        }
                    }
                    else
                    {
                        string strCmd = string.Format("UPDATE " + typeof(T).Name + " SET {0}=@{0}", propertyies[0].Name);
                        for (int i = 1; i < propertyies.Count; i++)
                        {
                            strCmd += string.Format(",{0}=@{0}", propertyies[i].Name);
                        }
                        strCmd += string.Format(" WHERE {0}=@{0}", fpkpropertyies[0].Name);
                        for (int i = 1; i < fpkpropertyies.Count; i++)
                        {
                            strCmd += string.Format(" AND {0}=@{0}", fpkpropertyies[i].Name);
                        }

                        cmd.CommandText = strCmd;
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Clear();
                        foreach (var pk in fpkpropertyies)
                        {
                            object value = pk.GetGetMethod().Invoke(modle, null);
                            AddWithValue(cmd, "@" + pk.Name, value);
                        }
                        foreach (var field in propertyies)
                        {
                            object value = field.GetGetMethod().Invoke(modle, null);
                            AddWithValue(cmd, "@" + field.Name, value);
                        }

                        var cnt = cmd.ExecuteNonQuery();
                        if (cnt == 0)
                        {
                            ifinsert = true;
                        }
                        else
                        {
                            return cnt;
                        }
                    }
                }

                if (ifinsert)
                {
                    // 插入
                    if (propertyies == null) // 只有在无主键的情况下才会为空
                    {
                        propertyies = GetProperties(typeof(T), fields);
                        if (propertyies.Count == 0)
                        {
                            propertyies = new List<PropertyInfo>(GetProperties(typeof(T)).Values);
                        }
                    }
                    if (fpkpropertyies != null)
                    {
                        propertyies.AddRange(fpkpropertyies);
                    }

                    if (propertyies.Count == 0)
                    {
                        throw new Exception(string.Format("模型{0}没有字段定义", typeof(T).FullName));
                    }

                    string strCmd = string.Format("INSERT INTO " + typeof(T).Name + "({0}", propertyies[0].Name);
                    for (int i = 1; i < propertyies.Count; i++)
                    {
                        strCmd += string.Format(",{0}", propertyies[i].Name);
                    }
                    strCmd += string.Format(") VALUES(@{0}", propertyies[0].Name);
                    for (int i = 1; i < propertyies.Count; i++)
                    {
                        strCmd += string.Format(",@{0}", propertyies[i].Name);
                    }
                    strCmd += ")";

                    cmd.CommandText = strCmd;
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Clear();
                    foreach (var field in propertyies)
                    {
                        object value = field.GetGetMethod().Invoke(modle, null);
                        AddWithValue(cmd, "@" + field.Name, value);
                    }

                    return cmd.ExecuteNonQuery();
                }

                return 0;
            }
            catch (Exception ex)
            {
                Trace.Write(ex.ToString());
                Trace.Write(ModleToString(modle));
                throw;
            }
        }

        private static object ModleToString(object modle)
        {
            if (modle == null)
            {
                return "{}";
            }
            var list = GetProperties(modle.GetType()).Values;

            StringBuilder rslt = new StringBuilder();
            rslt.Append(modle.GetType().ToString() + ":{");
            bool hasone = false;

            foreach (var pro in list)
            {
                var getMethod = pro.GetGetMethod();
                if (getMethod == null)
                {
                    continue;
                }
                var value = getMethod.Invoke(modle, null);
                var curent = string.Format("{0}:\"{1}\"", pro.Name, value ?? "nil");
                if (!hasone)
                {
                    rslt.Append(curent);
                    hasone = true;
                }
                else
                {
                    rslt.Append("," + curent);
                }
            }

            rslt.Append("}");

            return rslt.ToString();
        }

        private static List<PropertyInfo> GetProperties(Type type, string outputFields)
        {
            List<PropertyInfo> rslt = new List<PropertyInfo>();
            Dictionary<string, PropertyInfo> properties = GetProperties(type);
            string[] fields = outputFields.Split(',');
            foreach (string field in fields)
            {
                string key = field.Trim().ToLower();
                if (properties.ContainsKey(key))
                {
                    rslt.Add(properties[key]);
                }
            }
            return rslt;
        }

        private static Dictionary<Type, Dictionary<string, PropertyInfo>> _typeinfoCache = new Dictionary<Type, Dictionary<string, PropertyInfo>>();
        private static object _syncRoot_typeinfoCache = new object();
        private static Dictionary<string, PropertyInfo> GetProperties(Type type)
        {
            lock (_syncRoot_typeinfoCache)
            {
                if (_typeinfoCache.ContainsKey(type))
                {
                    return _typeinfoCache[type];
                }

                PropertyInfo[] properties = type.GetProperties();
                Dictionary<string, PropertyInfo> dirProperties = new Dictionary<string, PropertyInfo>();
                foreach (PropertyInfo property in properties)
                {
                    string key = property.Name.Trim().ToLower();
                    if (!dirProperties.ContainsKey(key))
                    {
                        dirProperties[key] = property;
                    }
                }
                _typeinfoCache[type] = dirProperties;
                return dirProperties;
            }
        }

        private static Dictionary<Type, ISqlEngine> _cmd_engines = new Dictionary<Type, ISqlEngine>();
        private static object _eng_root = new object();

        public static void Add<T>(SqlEngine<T> engine) where T : DbCommand
        {
            if (engine == null)
            {
                throw new Exception("engine参数不能为null");
            }
            lock (_eng_root)
            {
                _cmd_engines[typeof(T)] = engine;
            }
        }

        private static ISqlEngine GetEngine(Type type)
        {
            lock (_eng_root)
            {
                if (!_cmd_engines.ContainsKey(type))
                {
                    throw new Exception(string.Format("{0}未关联数据库引擎", type.FullName));
                }
                return _cmd_engines[type];
            }
        }
    }
}