C# 数据库数据动态插入（反射）

 /// <summary>
    /// 提供将MySqlDataReader转成T类型的扩展方法
    /// </summary>
    public static class MySqlDataReaderExt
    {
        private static readonly object Sync = new object();

        /// <summary>
        /// 属性反射信息缓存 key:类型的hashCode,value属性信息
        /// </summary>
        private static readonly Dictionary<int, Dictionary<string, PropertyInfo>> PropInfoCache =
            new Dictionary<int, Dictionary<string, PropertyInfo>>();

        /// <summary>
        /// 将MySqlDataReader转成T类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="readers"></param>
        /// <returns></returns>
        public static T To<T>(this MySqlDataReader reader,bool IsInner=false)
            where T : new()
        {
           
            if (reader == null || reader.HasRows == false) return default(T);
            if (!IsInner)
            {
                reader.Read();
            }
            var res = new T();
            var propInfos = GetFieldnameFromCache<T>();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                var n = reader.GetName(i).ToLower();
                if (propInfos.ContainsKey(n))
                {
                    PropertyInfo prop = propInfos[n];
                    var isValueType = prop.PropertyType.IsValueType;
                    object defaultValue = null; //引用类型或可空值类型的默认值
                    if (isValueType)
                    {
                        if ((!prop.PropertyType.IsGenericType)||
                            (prop.PropertyType.IsGenericType &&
                             prop.PropertyType.GetGenericTypeDefinition() != typeof(Nullable<>)))
                        {
                            defaultValue = 0; //非空值类型的默认值
                        }
                    }
                   var type= reader.GetFieldType(i);
                    var v = reader.GetValue(i);
                    dynamic temp=null;
                    if (prop.PropertyType.Name == "Int32" && v != DBNull.Value)
                    {
                        temp = Convert.ToInt32(v);
                    }
                    else if (prop.PropertyType.Name == "Boolean" && v != DBNull.Value)
                    {
                        if ((type == typeof(int) || type == typeof(long)))
                        {
                            temp = Convert.ToInt32(v) == 1;
                        }
                    }
       
                    temp = temp ?? v;
                    prop.SetValue(res, (Convert.IsDBNull(temp) ? defaultValue : temp));
                }
            }

            return res;
        }

        private static Dictionary<string, PropertyInfo> GetFieldnameFromCache<T>()
        {
            var hashCode = typeof (T).GetHashCode();
            var filedNames = GetFieldName<T>();
            Dictionary<string, PropertyInfo> res;
            lock (Sync)
            {
                if (!PropInfoCache.ContainsKey(hashCode))
                {
                    PropInfoCache.Add(hashCode, filedNames);
                }
                res = PropInfoCache[hashCode];
            }
            return res;
        }

        /// <summary>
        /// 获取一个类型的对应数据表的字段信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private static Dictionary<string, PropertyInfo> GetFieldName<T>()
        {
            var props = typeof (T).GetProperties();
            return props.ToDictionary(item => item.GetFieldName());
        }

        /// <summary>
        /// 将MySqlDataReader转成List类型
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="reader">数据读取器</param>
        /// <returns></returns>
        public static List<T> ToList<T>(this MySqlDataReader reader)
            where T : new()
        {
            if (reader == null || reader.HasRows == false) return null;
            var res = new List<T>();
            while (reader.Read())
            {
                res.Add(reader.To<T>(true));
            }
            return res;
        }

        /// <summary>
        /// 获取该属性对应到数据表中的字段名称
        /// </summary>
        /// <param name="propInfo"></param>
        /// <returns></returns>
        public static string GetFieldName(this PropertyInfo propInfo)
        {
            var fieldname = propInfo.Name;
            var attr = propInfo.GetCustomAttributes(false);
            foreach (var a in attr)
            {
                if (a is DataFieldAttribute)
                {
                    fieldname = (a as DataFieldAttribute).Name;
                    break;
                }
            }
            return fieldname.ToLower();
        }
        public static string ToUpdateSql(this object model, string key, ref List<KeyValuePair<string, object>> list)
        {
            try
            {

                StringBuilder sql = new StringBuilder();
                string fileds = "";
                Type m = model.GetType();
                PropertyInfo[] property = m.GetProperties();
                sql.Append("update " + m.Name + " set ");
                for (int i = 0; i < property.Length; i++)
                {
                    if (property[i].Name == key)
                        continue;
                    if (property[i].GetValue(model, null) != null)
                    {
                        fileds += property[i].Name + "=@s" + i + " ,";

                        list.Add(new KeyValuePair<string, object>("@s" + i, property[i].GetValue(model, null)));
                    }

                }
                fileds = fileds.Substring(0, fileds.LastIndexOf(",", StringComparison.Ordinal));
                sql.Append(fileds);
                sql.Append(" where " + key + "=@key");
                list.Add(new KeyValuePair<string, object>("@key", m.GetProperty(key).GetValue(model, null).ToString()));
                return sql.ToString();
            }
            catch
            {
                return "";
            }

        }

        public static string ToAddSql(this object model, string key, ref List<KeyValuePair<string, object>> list)
        {
            try
            {

                StringBuilder sql = new StringBuilder();
                Type m = model.GetType();
                PropertyInfo[] property = m.GetProperties();

                string values = string.Empty;
                string keys = string.Empty;
                for (int i = 0; i < property.Length; i++)
                {
                    if (property[i].Name == key || property[i].GetValue(model, null) == null)
                        continue;
                    keys += property[i].Name + " ,";
                    values += "@s" + i + ",";

                    list.Add(new KeyValuePair<string, object>("@s" + i, property[i].GetValue(model, null)));
                }
                keys = keys.Substring(0, keys.LastIndexOf(','));
                values = values.Substring(0, values.LastIndexOf(','));
                sql.AppendFormat("insert into " + m.Name + "({0}) values({1});select @@IDENTITY", keys, values);
                //list.Add(new KeyValuePair<string, object>("@key", M.GetProperty(key).GetValue(model, null).ToString()));
                return sql.ToString();
            }
            catch
            {
                return "";
            }

        }

        public static string ToAddSql(this object model, string key)
        {
            try
            {

                StringBuilder sql = new StringBuilder();
                Type m = model.GetType();
                PropertyInfo[] property = m.GetProperties();

                string values = string.Empty;
                string keys = string.Empty;
                for (int i = 0; i < property.Length; i++)
                {
                    if (property[i].Name == key)
                        continue;
                    if (property[i].GetValue(model, null) != null)
                    {

                        keys += property[i].Name + " ,";
                        if (property[i].PropertyType.Name.Contains("String") ||
                            property[i].PropertyType.FullName.Contains("DateTime"))
                        {
                            values += "'" + property[i].GetValue(model, null) + "',";
                        }
                        else
                        {
                            values += property[i].GetValue(model, null) + ",";
                        }
                    }
                }
                keys = keys.Substring(0, keys.LastIndexOf(','));
                values = values.Substring(0, values.LastIndexOf(','));
                sql.AppendFormat("insert into " + m.Name + "({0}) values({1});select @@IDENTITY;", keys, values);
                return sql.ToString();
            }
            catch
            {
                return "";
            }
        }
    }

    public class DataFieldAttribute : Attribute
    {
        public string Name { get; set; }

        public DataFieldAttribute()
        {

        }

        public DataFieldAttribute(string name)
        {
            Name = name;
        }
    }

 

 /// <summary>
        /// inset
        /// </summary>
        /// <param name="ID">主键id</param>
        /// <returns>int返回ID</returns>
        public static string Insert(object Model, string ID)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            StringBuilder commandText = new StringBuilder(" insert into ");
            Type type = Model.GetType();
            //T mode = Activator.CreateInstance<T>();
            string tableName = type.Name;
            PropertyInfo[] pros = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            StringBuilder filedStr = new StringBuilder();
            StringBuilder paramStr = new StringBuilder();
            int len = pros.Length;
            //if (!string.IsNullOrEmpty(ID))
            //{ param = new MySqlParameter[len - 1]; }
            //else
            //{
            //    param = new MySqlParameter[len - 1];
            //}
            //int paramLindex = 0;
            for (int i = 0; i < len; i++)
            {
                string fieldName = pros[i].Name;
                if (!fieldName.ToUpper().Equals(ID.ToUpper()) && pros[i].GetValue(Model, null) != null)
                {
                    filedStr.Append(fieldName);
                    string paramName = "@" + fieldName;
                    paramStr.Append(paramName);
                    filedStr.Append(",");
                    paramStr.Append(",");
                    object val = type.GetProperty(fieldName).GetValue(Model, null);
                    if (val == null)
                    {
                        val = DBNull.Value;
                    }
                    param.Add(new MySqlParameter(fieldName, val));
                    //paramLindex++;
                }
            }

            commandText.Append(tableName);
            commandText.Append("(");
            commandText.Append(filedStr.ToString().Substring(0, filedStr.ToString().LastIndexOf(',')));
            commandText.Append(") values (");
            commandText.Append(paramStr.ToString().Substring(0, paramStr.ToString().LastIndexOf(',')));
            commandText.Append(");select @@IDENTITY");
            string InsertID = DbHelperSQL.ExecuteScalar(commandText.ToString(), param.ToArray());
            return InsertID;

        }
        ///// <summary>
        ///// inset
        ///// </summary>
        ///// <param name="ID">主键id</param>
        ///// <returns>int返回ID</returns>
        //public static string InsertTemp(object Model, string ID)
        //{
        //    List<MySqlParameter> param = new List<MySqlParameter>();
        //    StringBuilder commandText = new StringBuilder(" insert into ");
        //    Type type = Model.GetType();
        //    //T mode = Activator.CreateInstance<T>();
        //    string tableName = type.Name;
        //    PropertyInfo[] pros = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
        //    StringBuilder filedStr = new StringBuilder();
        //    StringBuilder paramStr = new StringBuilder();
        //    int len = pros.Length;
        //    //if (!string.IsNullOrEmpty(ID))
        //    //{ param = new MySqlParameter[len - 1]; }
        //    //else
        //    //{
        //    //    param = new MySqlParameter[len - 1];
        //    //}
        //    //int paramLindex = 0;
        //    for (int i = 0; i < len; i++)
        //    {
        //        string fieldName = pros[i].Name;
        //        if (!fieldName.ToUpper().Equals(ID.ToUpper()) && pros[i].GetValue(Model, null) != null)
        //        {
        //            filedStr.Append(fieldName);
        //            string paramName = "@" + fieldName;
        //            paramStr.Append(paramName);
        //            filedStr.Append(",");
        //            paramStr.Append(",");
        //            object val = type.GetProperty(fieldName).GetValue(Model, null);
        //            if (val == null)
        //            {
        //                val = DBNull.Value;
        //            }
        //            param.Add(new MySqlParameter(fieldName, val));
        //            //paramLindex++;
        //        }
        //    }

        //    commandText.Append(tableName);
        //    commandText.Append("(");
        //    commandText.Append(filedStr.ToString().Substring(0, filedStr.ToString().LastIndexOf(',')));
        //    commandText.Append(") values (");
        //    commandText.Append(paramStr.ToString().Substring(0, paramStr.ToString().LastIndexOf(',')));
        //    commandText.Append(");select @@IDENTITY");
        //    string InsertID = DbHelperSQL.ExecuteScalarTem(commandText.ToString(), param.ToArray());
        //    return InsertID;

        //}
        
        /// <summary>
        /// update
        /// </summary>
        /// <param name="ID">主键id</param>
        /// <returns>int返回影响条数</returns>
        public static int Update(object Model, string ID)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            Type type = Model.GetType();
            string tableName =type.Name;
            //T model = Activator.CreateInstance<T>();
            StringBuilder commandText = new StringBuilder(" update " + tableName + " set ");
            PropertyInfo[] pros = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            StringBuilder filedStr = new StringBuilder();
            //int HaveNUM = 0;
            int len = pros.Length;
            //for (int i = 0; i < len; i++)
            //{
            //    if (type.GetProperty(pros[i].Name).GetValue(Model, null) != null)
            //    {
            //        HaveNUM++;
            //    }
            //}
            if (type.GetProperty(ID).GetValue(Model, null) == null)
            {
                return 0;
            }
            else if (type.GetProperty(ID).GetValue(Model, null).ToString() == "0")
            {
                return 0;
            }
            for (int i = 0; i < len; i++)
            {

                string fieldName = pros[i].Name;
                if (!fieldName.ToUpper().Equals(ID.ToUpper()))
                {
                    if (type.GetProperty(fieldName).GetValue(Model, null) != null)
                    {
                        filedStr.Append(fieldName + "=@" + fieldName);
                        filedStr.Append(",");
                        object val = type.GetProperty(fieldName).GetValue(Model, null);
                        if (val == null)
                        {

                            val = DBNull.Value;
                        }

                        param.Add(new MySqlParameter(fieldName, val));

                    }
                }
            }

            param.Add(new MySqlParameter(ID, type.GetProperty(ID).GetValue(Model, null)));
            commandText.Append(filedStr.ToString().Substring(0, filedStr.ToString().LastIndexOf(',')));
            commandText.Append(" where " + ID + "=@" + ID);
            object obj2 = DbHelperSQL.ExecuteSql(commandText.ToString(), param.ToArray());
            if (obj2 == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj2);
            }

        }
        /// <summary>
        /// updateList 事务修改
        /// </summary>
        /// <param name="ID">主键id</param>
        /// <returns>int</returns>
        public static int Update<T>(List<T> List, string ID)
        {
            List<CommandInfo> ListComd = new List<CommandInfo>();
            List<KeyValuePair<string, object>> listparam;
            //CommandInfo Model = new CommandInfo();
            List<MySqlParameter> sqlParam;
            MySqlParameter param;
            string sql = "";
            foreach (var item in List)
            {
                listparam = new List<KeyValuePair<string, object>>();
                sqlParam = new List<MySqlParameter>();
                sql = item.ToUpdateSql(ID, ref listparam);
                foreach (var Keyvalue in listparam)
                {
                    param = new MySqlParameter(Keyvalue.Key, Keyvalue.Value.GetType());
                    param.Value = Keyvalue.Value;
                    sqlParam.Add(param);
                }
                ListComd.Add(new CommandInfo(sql, sqlParam.ToArray(), EffentNextType.ExcuteEffectRows));
            }
            return DbHelperSQL.ExecuteSqlTran(ListComd);
        }
        /// <summary>
        /// updateList 事务增加
        /// </summary>
        /// <param name="ID">主键id</param>
        /// <returns>int</returns>
        public static int Insert<T>(List<T> List, string ID)
        {
            List<CommandInfo> ListComd = new List<CommandInfo>();
            List<KeyValuePair<string, object>> listparam;
            //CommandInfo Model = new CommandInfo();
            List<MySqlParameter> sqlParam;
            MySqlParameter param;
            string sql = "";
            foreach (var item in List)
            {
                listparam = new List<KeyValuePair<string, object>>();
                sqlParam = new List<MySqlParameter>();
                sql = item.ToAddSql(ID, ref listparam);
                foreach (var Keyvalue in listparam)
                {
                    //param = new MySqlParameter(Keyvalue.Key, GetDbType(Keyvalue.Value.GetType()));
                    param = new MySqlParameter(Keyvalue.Key, Keyvalue.Value);
                    //param.Value = Keyvalue.Value;
                    sqlParam.Add(param);
                }
                ListComd.Add(new CommandInfo(sql, sqlParam.ToArray(), EffentNextType.ExcuteEffectRows));
            }
            return DbHelperSQL.ExecuteSqlTran(ListComd);
        }
        /// <summary>
        /// 获取类型
        /// </summary>
        /// <returns></returns>
       private static MySqlDbType GetDbType(Type t)
       {
            if (t==typeof(string))
            {
                return MySqlDbType.String;
            }
            if (t==typeof(int))
            {
                return MySqlDbType.Int32;
            }
            if (t==typeof(bool))
            {
                return MySqlDbType.Int16;
            }
            if (t==typeof(DateTime))
            {
                return MySqlDbType.DateTime;
            }
            return MySqlDbType.String;
       }