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