public List<T> QueryByADO<T>(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<T>(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 将DataTable数据源转换成实体类
/// </summary>
public List<T> ConvertToModel<T>(DataTable dt) where T : class, new()
{
List<T> ts = new List<T>();// 定义集合
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<T> QueryByADO<T>(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<T>(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 将DataTable数据源转换成实体类
/// </summary>
public List<T> ConvertToModel<T>(DataTable dt) where T : class, new()
{
List<T> ts = new List<T>();// 定义集合
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