mysql new query_MySqlQueryList

//辅助查询列表，或实例

public classMySqlQueryList

{#region List ToList(string sql, params DbParameter[] parameters) where T : new()

///

///返回一个list///

/// 实体模型

/// sql语句

/// 参数

///

public static List ToList(string sql, params DbParameter[] parameters) where T : new()

{using (var read =MysqlHelp.ExecuteReader(sql, parameters))

{

List list = null;var type = typeof(T);if(read.HasRows)

{

list= new List();

}while(read.Read())

{

T t= newT();foreach (PropertyInfo item intype.GetProperties())

{for (int i = 0; i < read.FieldCount; i++)

{//属性名与查询出来的列名比较

if (item.Name.ToLower() != read.GetName(i).ToLower()) continue;var value =read[i];if (value !=DBNull.Value)

{

item.SetValue(t, value,null);

}break;

}

}//将创建的对象添加到集合中

list.Add(t);

}returnlist;

}

}#endregion

#region T FirstOrDefault(string sql, params DbParameter[] parameters)

///

///返回一个实体模型///

/// 实体模型

/// sql语句

/// 参数

///

public static T FirstOrDefault(string sql, params DbParameter[] parameters) where T : class,new()

{using (var read =MysqlHelp.ExecuteReader(sql, parameters))

{

Type type= typeof(T);if (!read.Read()) return null;

T t= newT();foreach (PropertyInfo item intype.GetProperties())

{for (int i = 0; i < read.FieldCount; i++)

{//属性名与查询出来的列名比较

if (item.Name.ToLower() != read.GetName(i).ToLower()) continue;object value =read[i];if (value !=DBNull.Value)

{

item.SetValue(t, value,null);

}break;

}

}returnt;

}

}#endregion

#region 分页查询信息 + List ToListAsPager(string sql,List parameters, out int pageCount) where T : class,new()

///

///分页查询信息GetTByPager///

/// 实体对象

/// 参数列表

/// 总页数

/// sql语句

/// IList

public static List ToListAsPager(string sql, List parameters, out int pageCount) where T : class,new()

{//存储过程名称

List contents = null;

parameters[parameters.Count- 1].Direction =System.Data.ParameterDirection.Output;using (var reader =MysqlHelp.ExecuteReader(sql, parameters.ToArray()))

{if (reader.HasRows) contents = new List();var type = typeof(T);while(reader.Read())

{var t = newT();//通用反射获取类的所有属性

foreach (var p intype.GetProperties())

{//循环遍历reader读取的所有字段

for (var i = 0; i < reader.FieldCount; i++)

{//属性名与查询出来的列名比较

if (p.Name.ToLower() != reader.GetName(i).ToLower()) continue;var value =reader[i];//如果该value不数据库的NULL,则保存到实体对象中

if (DBNull.Value !=value)

{

p.SetValue(t, value,null);

}break;

}

}if (contents != null) contents.Add(t);

}//执行 SqlHelp.ExecuteNonQuery(cmdText, parameters); 返回总页数 pageCount

SQLhelp.ExcuteNonQuery(sql, parameters.ToArray());//返回存储过程中的输出值

pageCount = Convert.ToInt32(parameters[parameters.Count - 1].Value);

}returncontents;

}#endregion}//使用DataTable,查询数据DataTable dt= MysqlHelp.ExecuteTable(sql, new SqlParameter("@sDelFlag", delflag));

List list = new List();if (dt.Rows.Count > 0)

{foreach (DataRow item indt.Rows)

{

TbStudentA ts= newTbStudentA();

td.ClassId= Convert.ToInt32(item["列名"]);

list.Add(ts);

}

}

return list;