ADO.NET ASP.NET 将 SQLDatareader 转换成 List

模型中的属性的数据类型支持基本类型(int string 等等)并且包括 枚举类型

模型中的属性名必须与reader中的列名一致,否则不一致的属性无法赋值.

 

/// <summary> /// 模型对象组装类 /// </summary> public class Fabricate { /// <summary> /// 判断某列是否存在并且有无数据 /// </summary> /// <param name="table"></param> /// <param name="reader"></param> /// <param name="columnName"></param> /// <returns></returns> public static bool ReaderExists(System.Collections.Hashtable table, System.Data.SqlClient.SqlDataReader reader, string columnName) { if (table.Contains(columnName.ToLower()) && !Convert.IsDBNull(reader[columnName])) { return true; } return false; } /// <summary> /// 组装一个模型对象 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader"></param> /// <param name="table"></param> /// <returns></returns> public static T Fill<T>(System.Data.SqlClient.SqlDataReader reader, System.Collections.Hashtable table) { T t = System.Activator.CreateInstance<T>(); if (table == null || table.Count == 0) { table = FillTable(reader); } System.Reflection.PropertyInfo[] propertys = typeof(T).GetProperties(); foreach (System.Reflection.PropertyInfo item in propertys) { if (ReaderExists(table, reader, item.Name)) { try { item.SetValue(t, Convert.ChangeType(reader[item.Name], item.PropertyType), null); } catch { item.SetValue(t, Enum.Parse(item.PropertyType, Convert.ToString(reader[item.Name])), null); } } } return t; } /// <summary> /// 组装一个模型对象 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader"></param> /// <returns></returns> public static T Fill<T>(System.Data.SqlClient.SqlDataReader reader) { if (reader != null && !reader.IsClosed && reader.HasRows && reader.Read()) { return Fill<T>(reader, null); } else { return default(T);//System.Activator.CreateInstance<T>(); } } /// <summary> /// 获取模型对象集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader"></param> /// <returns></returns> public static List<T> FillList<T>(System.Data.SqlClient.SqlDataReader reader) { List<T> list = new List<T>(); if (reader != null && !reader.IsClosed && reader.HasRows) { System.Collections.Hashtable table = FillTable(reader); while (reader.Read()) { list.Add(Fill<T>(reader, table)); } reader.Close(); } return list; } /// <summary> /// 获取reader中列名集合 /// </summary> /// <param name="reader"></param> /// <returns></returns> public static System.Collections.Hashtable FillTable(System.Data.SqlClient.SqlDataReader reader) { System.Collections.Hashtable table = new System.Collections.Hashtable(); table = new System.Collections.Hashtable(); int count = reader.FieldCount; for (int i = 0; i < count; i++) { table.Add(reader.GetName(i).ToLower(), null); } return table; } /// <summary> /// 获取模型对象集合 /// 自动关闭连接 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="commandType"></param> /// <param name="sqlText"></param> /// <param name="param"></param> /// <returns></returns> public static List<T> GetList<T>(System.Data.CommandType commandType, string sqlText, params SqlParameter[] param) { using (SqlDataReader reader = SQLHelp.ExecuteReader(commandType,sqlText,param)) { return FillList<T>(reader); } } /// <summary> /// 组装一个模型对象 /// 自动关闭连接 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="commandType"></param> /// <param name="sqlText"></param> /// <param name="param"></param> /// <returns></returns> public static T Get<T>(System.Data.CommandType commandType, string sqlText, params SqlParameter[] param) { using (SqlDataReader reader = SQLHelp.ExecuteReader(commandType,sqlText,param)) { return Fill<T>(reader); } } }

==========================土豆的华丽分割线=======================

下面是调用示例

/// <summary> /// 获取所有Deal列表 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <returns></returns> public List<Deal> GetListForAdmin(int pageIndex, int pageSize, out int rowCount) { string sql = "Select Count(ID) From dbo.Deal"; rowCount = Convert.ToInt32(SQLHelp.ExecuteScalar(CommandType.Text, sql)); sql = @" With cte As ( Select *,row_number() Over (Order By ID Desc) inx From dbo.Deal ) Select * From cte Where inx Between " + ((pageIndex - 1) * pageSize + 1) + " And " + (pageIndex * pageSize); return Fabricate.GetList<Deal>(CommandType.Text, sql); }

===========================土豆的华丽分割线========================

在没有封装方法先都是手工封装的

private Deal Fill(SqlDataReader reader) { Deal deal = new Deal(); deal.ID = Convert.ToInt64(reader["ID"]); deal.Keywords = reader["Keywords"].ToString(); deal.LogoPath = reader["LogoPath"].ToString(); deal.Name = reader["Name"].ToString(); deal.SortID = Convert.ToInt32(reader["SortID"]); deal.Status = (Deal.DealStatus)Convert.ToInt32(reader["Status"]); deal.TargetID = Convert.ToInt64(reader["TargetID"]); deal.TargetUrl = reader["TargetUrl"].ToString(); deal.Type = (Deal.DealType)reader["Type"]; deal.ClassValue = Convert.ToInt32(reader["ClassValue"]); deal.Content = Convert.IsDBNull(reader["Content"]) ? "" : Convert.ToString(reader["Content"]); return deal; }

==========================土豆的华丽分割线========================

每次都要非常繁琐的循环掉用

/// <summary> /// 获取所有Deal列表 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <returns></returns> public List<Deal> GetListForAdmin(int pageIndex, int pageSize, out int rowCount) { string sql = "Select Count(ID) From dbo.Deal"; rowCount = Convert.ToInt32(SQLHelp.ExecuteScalar(CommandType.Text, sql)); sql = @" With cte As ( Select *,row_number() Over (Order By ID Desc) inx From dbo.Deal ) Select * From cte Where inx Between " + ((pageIndex - 1) * pageSize + 1) + " And " + (pageIndex * pageSize); using (SqlDataReader reader = SQLHelp.ExecuteReader(CommandType.Text, sql)) { List<Deal> list = new List<Deal>(); if (reader != null && !reader.IsClosed && reader.HasRows) { while (reader.Read()) { list.Add(Fill(reader)); } reader.Close(); } return list; }