自己寫的訪問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

下面貼出主要代碼

  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