C# ADO.NET+反射读取数据库并转换为List
2024-09-04 11:05:27
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
最新文章
- runtime运行时
- Kosaraju算法---强联通分量
- contentOffset,frame,bounds,contentSize,ContentInset
- RF前端
- 移动端触摸滑动插件Swiper
- UFLDL实验报告3：Self-taught
- 代码中函数、变量、常量 / bss段、data段、text段 /sct文件、.map文件的关系[实例分析arm代码（mdk）]
- c++内存对齐 转载
- SQL三类语句
- Java 代码学习之理解数据类型中的坑
- FFmpeg源代码简单分析：avformat_alloc_output_context2()
- 前端面试之vue相关的面试题
- spring filter lister servlet
- bzoj 5418
- python 高阶函数学习， map、reduce
- 转JMeter ----数据库 not allowed to connect to this MySQL
- js开关插件使用
- 在EntityFramework6中管理DbContext的正确方式——1考虑的关键点(外文翻译)
- 判断h5页面是小程序环境还是微信环境
- IntelliJ IDEA 创建 hello world Java web Maven项目从头到尾都有图有真相2017版本