利用反射将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;        }