利用反射将Datatable、SqlDataReader转换成List模型

1. DataTable转IList

public class DataTableToList<T>whereT :new()

{

///<summary>

///利用反射将Datatable转换成List模型

///</summary>

///<param name="dt"></param>

///<returns></returns>

public static List<T> ConvertToList(DataTabledt)

{

List<T> list =newList<T>();


Typetype =typeof(T);

stringtempName =string.Empty;

foreach(DataRowdrindt.Rows)

{

T t =newT();

PropertyInfo[] propertys =
t.GetType().GetProperties();

foreach(PropertyInfopiinpropertys)

{

tempName = pi.Name;

if(dt.Columns.Contains(tempName))

{

if(!pi.CanWrite)

{

continue;

}


var value = dr[tempName];

if(value !=DBNull.Value)

{

pi.SetValue(t, value,null);

}

}

}

list.Add(t);

}


returnlist;


}

}

2. SqlDataReader转IList

/// <summary>

/// 判断SqlDataReader是否存在某列

/// </summary>

/// <param name="dr">SqlDataReader</param>

/// <param name="columnName">列名</param>

/// <returns></returns>

private bool readerExists(SqlDataReader dr, string columnName)

{

dr.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";

return (dr.GetSchemaTable().DefaultView.Count > 0);

}

///<summary>

///利用反射和泛型将SqlDataReader转换成List模型

///</summary>

///<param name="sql">查询sql语句</param>

///<returns></returns>

public IList<T> ExecuteToList<T>(string sql) where T : new()

{

IList<T> list;

Type type = typeof (T);

string tempName = string.Empty;

using (SqlDataReader reader = ExecuteReader(sql))

{

if (reader.HasRows)

{

list = new List<T>();

while (reader.Read())

{

T t = new T();

PropertyInfo[] propertys = t.GetType().GetProperties();

foreach (PropertyInfo pi in propertys)

{

tempName = pi.Name;

if (readerExists(reader, tempName))

{

if (!pi.CanWrite)

{

continue;

}

var value = reader[tempName];

if (value != DBNull.Value)

{

pi.SetValue(t, value, null);

}

}

}

list.Add(t);

}

return list;

}

}

return null;

}

3、结果集从存储过程获取

/// <summary>

/// 处理存储过程

/// </summary>

/// <param name="spName">存储过程名</param>

/// <param name="parameters">参数数组</param>

/// <returns>sql数据流</returns>

protected virtual SqlDataReader ExecuteReaderSP(string spName, ArrayList parameters)

{

SqlDataReader result = null;

cmd.CommandText = spName;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Clear();

if (parameters != null)

{

foreach (SqlParameter param in parameters)

{

cmd.Parameters.Add(param);

}

}

try

{

Open();

result = cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

catch (Exception e)

{

if (result != null && (!result.IsClosed))

{

result.Close();

}

LogHelper.WriteLog("\r\n方法异常【ExecuteReaderSP(string spName, ArrayList parameters)】" + spName, e);

throw new Exception(e.Message);

}

return result;

}

///<summary>

///利用反射将SqlDataReader转换成List模型

///</summary>

///<param name="spName">存储过程名称</param>

///<returns></returns>

public IList<T> ExecuteQueryListSP<T>(string spName, params SqlParameter[] listParams) where T : new()

{

IList<T> list;

Type type = typeof(T);

string tempName = string.Empty;

using (SqlDataReader reader = ExecuteReaderSP(spName, new ArrayList(listParams)))

{

if (reader.HasRows)

{

list = new List<T>();

while (reader.Read())

{

T t = new T();

PropertyInfo[] propertys = t.GetType().GetProperties();

foreach (PropertyInfo pi in propertys)

{

tempName = pi.Name;

//for (int intField = 0; intField < reader.FieldCount; intField++)

//{//遍历该列名是否存在

//}

if (readerExists(reader, tempName))

{

if (!pi.CanWrite)

{

continue;

}

var value = reader[tempName];

if (value != DBNull.Value)

{

pi.SetValue(t, value, null);

}

}

}

list.Add(t);

}

return list;

}

}

return null;

}

转载：https://blog.csdn.net/dasihg/article/details/8943811