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