ado.net mysql 转义,C# ADO.NET+反射读取数据库并转换为List

public List QueryByADO(string connStr, string sql) where T : class, new()

{

using (SqlConnection conn = new SqlConnection())

{

try

{

conn.ConnectionString = connStr;

conn.Open();

SqlDataAdapter myda = new SqlDataAdapter(sql, conn);

DataTable dt = new DataTable();

myda.Fill(dt);

return ConvertToModel(dt);

}

catch (Exception ex)

{

throw ex;

}

finally

{

conn.Close();

}

}

}

///

/// 将DataTable数据源转换成实体类

///

public List ConvertToModel(DataTable dt) where T : class, new()

{

List ts = new List();// 定义集合

foreach (DataRow dr in dt.Rows)

{

T t = new T();

PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性

foreach (PropertyInfo pi in propertys)

{

if (dt.Columns.Contains(pi.Name))

{

if (!pi.CanWrite) continue;

var value = dr[pi.Name];

if (value != DBNull.Value)

{

if (pi.PropertyType.FullName.Contains("System.Nullable"))

{

pi.SetValue(t, Convert.ChangeType(value, (Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType)), null);

}

else

{

switch (pi.PropertyType.FullName)

{

case "System.Decimal":

pi.SetValue(t, decimal.Parse(value.ToString()), null);

break;

case "System.String":

pi.SetValue(t, value.ToString(), null);

break;

case "System.Char":

pi.SetValue(t, Convert.ToChar(value), null);

break;

case "System.Guid":

pi.SetValue(t,value, null);

break;

case "System.Int16":

pi.SetValue(t, Convert.ToInt16(value), null);

break;

case "System.Int32":

pi.SetValue(t, int.Parse(value.ToString()), null);

break;

case "System.Int64":

pi.SetValue(t, Convert.ToInt64(value), null);

break;

case "System.Byte[]":

pi.SetValue(t, Convert.ToByte(value), null);

break;

case "System.Boolean":

pi.SetValue(t,Convert.ToBoolean(value), null);

break;

case "System.Double":

pi.SetValue(t, Convert.ToDouble(value.ToString()), null);

break;

case "System.DateTime":

pi.SetValue(t, value ?? Convert.ToDateTime(value), null);

break;

default:

throw new Exception("类型不匹配:" + pi.PropertyType.FullName);

}

}

}

}

}

ts.Add(t);

}

return ts;

}

public List QueryByADO(string connStr, string sql) where T : class, new()

{

using (SqlConnection conn = new SqlConnection())

{

try

{

conn.ConnectionString = connStr;

conn.Open();

SqlDataAdapter myda = new SqlDataAdapter(sql, conn);

DataTable dt = new DataTable();

myda.Fill(dt);

return ConvertToModel(dt);

}

catch (Exception ex)

{

throw ex;

}

finally

{

conn.Close();

}

}

}

///

/// 将DataTable数据源转换成实体类

///

public List ConvertToModel(DataTable dt) where T : class, new()

{

List ts = new List();// 定义集合

foreach (DataRow dr in dt.Rows)

{

T t = new T();

PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性

foreach (PropertyInfo pi in propertys)

{

if (dt.Columns.Contains(pi.Name))

{

if (!pi.CanWrite) continue;

var value = dr[pi.Name];

if (value != DBNull.Value)

{

if (!pi.PropertyType.IsGenericType)

{

//非泛型

pi.SetValue(t, value==null ? null : Convert.ChangeType(value, pi.PropertyType), null);

}

else

{

//泛型Nullable<>

Type genericTypeDefinition = pi.PropertyType.GetGenericTypeDefinition();

if (genericTypeDefinition == typeof(Nullable<>))

{

pi.SetValue(t, value == null ? null : Convert.ChangeType(value, Nullable.GetUnderlyingType(pi.PropertyType)), null);

}

}

}

}

}

ts.Add(t);

}

return ts;

}

//参考连接 https://blog.csdn.net/xiaohan2826/article/details/8536074