自己写的访问SqlServer数据库的通用DAL层如题,直接贴代码.
首先是DataTable转List<T>的方法,这个方法通用性极强.
1 #region Table转List 2 /// <summary> 3 /// Table转List 4 /// </summary> 5 /// <typeparam name="T"></typeparam> 6 /// <param name="dt"></param> 7 /// <returns></returns> 8 public static List<T> TableToList<T>(DataTable dt) where T : new() 9 {10 // 定义集合11 IList<T> list = new List<T>();12 13 // 获得此模型的类型14 Type type = typeof(T);15 16 foreach (DataRow dr in dt.Rows)17 {18 T t = new T();19 20 // 获得公共属性21 PRopertyInfo[] propertys = t.GetType().GetProperties();22 23 foreach (PropertyInfo pi in propertys)24 {25 // 判断此属性是否有Setter26 if (!pi.CanWrite) continue;27 28 object value = dr[pi.Name];29 if (value != DBNull.Value)30 pi.SetValue(t, value, null);31 }32 33 list.Add(t);34 }35 return list.ToList();36 }37 #endregion
View Code下面贴出主要代码
1 /// <summary> 2 /// 数据访问基类:BaseService 3 /// </summary> 4 /// <typeparam name="T"></typeparam> 5 public class BaseService<T> : IBaseService<T> where T : class ,new() 6 { 7 /// <summary> 8 /// 得到某列最大值 9 /// </summary> 10 /// <param name="connection"></param> 11 /// <param name="fieldname"></param> 12 /// <returns></returns> 13 public virtual int GetMax(string connection,string fieldname) 14 { 15 #region 参数 16 T entity = new T(); 17 SqlParameter[] parameters = new SqlParameter[] 18 { 19 new SqlParameter("@fieldname",fieldname) 20 }; 21 #endregion 22 23 StringBuilder sqlStr=new StringBuilder(); 24 sqlStr.Append("select max("); 25 sqlStr.Append("@fieldname"); 26 sqlStr.Append(") from "); 27 sqlStr.Append(entity.GetType().Name); 28 29 return int.Parse(Databaseaccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters).Tables[0].Rows[0][0].ToString()); 30 } 31 32 /// <summary> 33 /// 是否存在该记录 34 /// </summary> 35 /// <param name="connection">连接字符串</param> 36 /// <param name="primarykey">主键值</param> 37 /// <returns></returns> 38 public virtual bool Exists(string connection, object primarykey) 39 { 40 #region 参数 41 T entity = new T(); 42 43 string primaryKey = GetPrimarykey(connection, entity); 44 List<SqlParameter> parameters = new List<SqlParameter>(); 45 parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); 46 #endregion 47 48 StringBuilder sqlStr = new StringBuilder(); 49 sqlStr.Append("select count(1) from "); 50 sqlStr.Append(entity.GetType().Name); 51 sqlStr.Append(" where "); 52 sqlStr.Append(primaryKey + "=@" + primaryKey); 53 54 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray()); 55 if (res > 0) 56 return true; 57 else 58 return false; 59 } 60 61 /// <summary> 62 /// 增加一条数据 63 /// </summary> 64 public virtual bool Add(string connection, T entity) 65 { 66 #region 参数 67 List<SqlParameter> parameters = new List<SqlParameter>(); 68 string fields = ""; 69 string placeholders = ""; 70 foreach (var item in entity.GetType().GetProperties()) 71 { 72 fields += item.Name + ","; 73 placeholders += "@" + item.Name + ","; 74 75 SqlParameter parameter = new SqlParameter(); 76 parameter.ParameterName = "@" + item.Name; 77 parameter.Value = item.GetValue(entity, null); 78 parameters.Add(parameter); 79 } 80 #endregion 81 82 StringBuilder sqlStr = new StringBuilder(); 83 sqlStr.Append("insert " + entity.GetType().Name + " ("); 84 sqlStr.Append(fields.Substring(0, fields.Length - 1) + ")"); 85 sqlStr.Append(" values ("); 86 sqlStr.Append(placeholders.Substring(0, placeholders.Length - 1) + ")"); 87 88 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray()); 89 if (res > 0) 90 return true; 91 else 92 return false; 93 } 94 95 /// <summary> 96 /// 更新一条数据 97 /// </summary> 98 /// <param name="connection">连接字符串</param> 99 /// <param name="entity">类</param>100 /// <returns></returns>101 public virtual bool Update(string connection, T entity)102 {103 #region 参数104 string primaryKey = GetPrimarykey(connection, entity);105 List<SqlParameter> parameters = new List<SqlParameter>();106 parameters.Add(new SqlParameter("@"+primaryKey, entity.GetType().GetProperty(primaryKey).GetValue(entity,null)));107 string fields = "";108 foreach (var item in entity.GetType().GetProperties())109 {110 if (item.Name != primaryKey)111 {112 fields += item.Name + "=@" + item.Name + ",";113 114 SqlParameter parameter = new SqlParameter();115 parameter.ParameterName = "@" + item.Name;116 parameter.Value = item.GetValue(entity, null);117 parameters.Add(parameter);118 }119 }120 #endregion121 122 StringBuilder sqlStr = new StringBuilder();123 sqlStr.Append("update " + entity.GetType().Name);124 sqlStr.Append(" set ");125 sqlStr.Append(fields.Substring(0,fields.Length-1));126 sqlStr.Append(" where ");127 sqlStr.Append(primaryKey + "=@" + primaryKey);128 129 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());130 if (res > 0)131 return true;132 else133 return false;134 }135 136 /// <summary>137 /// 删除一条数据138 /// </summary>139 /// <param name="connection">连接字符串</param>140 /// <param name="primaryKey">主键值</param>141 /// <returns></returns>142 public virtual bool Delete(string connection, o
