Biegral Blog

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;
}