public List<TResult> SqlQueryForTable222<TResult>(string sql, params object[] paras) where TResult : new()
{
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = _context.Database.Connection.ConnectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (paras != null && paras.Length > 0)
{
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
#region DataTable转换为实体类型
Collection<TResult> collection = null; //定义实体类集合
if (table != null)
{
Collection<System.Reflection.PropertyInfo> properties = new Collection<System.Reflection.PropertyInfo>(); //定义属性集合
Type type = typeof(TResult);
Array.ForEach<System.Reflection.PropertyInfo>(type.GetProperties(), p =>
{
if (table.Columns.IndexOf(p.Name) != -1) { properties.Add(p); }
});
collection = new Collection<TResult>();
foreach (DataRow row in table.Rows) //循环DataTable的每行
{
TResult result = new TResult();
foreach (System.Reflection.PropertyInfo p in properties) //循环实体类的属性
{
object value = row[p.Name]; //根据实体类属性名,找到值并赋值
if (value != DBNull.Value) //如果有值
{
Type schemaType = table.Columns[p.Name].DataType; //根据此实体类属性名, 获取DataTable行的类型
if (schemaType != p.PropertyType) //如果类型不相等
{
try
{
value = Convert.ChangeType(row[p.Name], p.PropertyType); //强转类型,并将类型赋给value
}
catch
{
continue;
}
}
p.SetValue(result, value, null); //赋值给实体类的value字段
}
}
collection.Add(result); //将实体类对象放入
}
}
#endregion
IEnumerable<TResult> iEnum = collection;
List<TResult> reult = iEnum.ToList();
return reult;
}
{
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = _context.Database.Connection.ConnectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (paras != null && paras.Length > 0)
{
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
#region DataTable转换为实体类型
Collection<TResult> collection = null; //定义实体类集合
if (table != null)
{
Collection<System.Reflection.PropertyInfo> properties = new Collection<System.Reflection.PropertyInfo>(); //定义属性集合
Type type = typeof(TResult);
Array.ForEach<System.Reflection.PropertyInfo>(type.GetProperties(), p =>
{
if (table.Columns.IndexOf(p.Name) != -1) { properties.Add(p); }
});
collection = new Collection<TResult>();
foreach (DataRow row in table.Rows) //循环DataTable的每行
{
TResult result = new TResult();
foreach (System.Reflection.PropertyInfo p in properties) //循环实体类的属性
{
object value = row[p.Name]; //根据实体类属性名,找到值并赋值
if (value != DBNull.Value) //如果有值
{
Type schemaType = table.Columns[p.Name].DataType; //根据此实体类属性名, 获取DataTable行的类型
if (schemaType != p.PropertyType) //如果类型不相等
{
try
{
value = Convert.ChangeType(row[p.Name], p.PropertyType); //强转类型,并将类型赋给value
}
catch
{
continue;
}
}
p.SetValue(result, value, null); //赋值给实体类的value字段
}
}
collection.Add(result); //将实体类对象放入
}
}
#endregion
IEnumerable<TResult> iEnum = collection;
List<TResult> reult = iEnum.ToList();
return reult;
}