你的位置:首页 > ASP.net教程

[ASP.net教程]自己写的访问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, object primarykey)143     {144       #region 参数145       T entity = new T();146 147       string primaryKey = GetPrimarykey(connection, entity);148       List<SqlParameter> parameters = new List<SqlParameter>();149       parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); 150       #endregion151 152       StringBuilder sqlStr = new StringBuilder();153       sqlStr.Append("delete from ");154       sqlStr.Append(entity.GetType().Name);155       sqlStr.Append(" where ");156       sqlStr.Append(primaryKey + "=@" + primaryKey);157 158       int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());159       if (res > 0)160         return true;161       else162         return false;163     }164 165     /// <summary>166     /// 删除多条数据167     /// </summary>168     /// <param name="connection">连接字符串</param>169     /// <param name="base_idlist">主键值列表</param>170     /// <returns></returns>171     public virtual bool DeleteList(string connection, List<object> primarykeys)172     {173       #region 参数174       T entity = new T();175 176       string primaryKey = GetPrimarykey(connection, entity);177 178       string primaryKeys = "";179       foreach(var item in primarykeys)180       {181         primaryKeys += item.ToString();182       }183 184       List<SqlParameter> parameters = new List<SqlParameter>();185       parameters.Add(new SqlParameter("@" + primaryKey, primaryKeys));186       #endregion187 188       StringBuilder sqlStr = new StringBuilder();189       sqlStr.Append("delete from ");190       sqlStr.Append(entity.GetType().Name);191       sqlStr.Append(" where ");192       sqlStr.Append(primaryKey + " in(@" + primaryKey + ")");193 194       int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());195       if (res > 0)196         return true;197       else198         return false;199     }200 201     /// <summary>202     /// 得到一个对象实体203     /// </summary>204     /// <param name="connection">连接字符串</param>205     /// <param name="primarykey">主键值</param>206     /// <returns></returns>207     public virtual T GetModel(string connection, object primarykey)208     {209       #region 参数210       T entity = new T();211 212       string primaryKey = GetPrimarykey(connection, entity);213 214       List<SqlParameter> parameters = new List<SqlParameter>();215       parameters.Add(new SqlParameter("@table", entity.GetType().Name));216       parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); 217       #endregion218 219       StringBuilder sqlStr = new StringBuilder();220       sqlStr.Append("select * from ");221       sqlStr.Append(entity.GetType().Name);222       sqlStr.Append(" where ");223       sqlStr.Append(primaryKey + "=@" + primaryKey);224 225       return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]).FirstOrDefault();226     }227 228     /// <summary>229     /// 获得数据列表230     /// </summary>231     /// <param name="connection">连接字符串</param>232     /// <param name="strWhere">查询条件</param>233     /// <returns></returns>234     public virtual List<T> GetList(string connection, string strWhere)235     {236       #region 参数237       T entity = new T();238 239       List<SqlParameter> parameters = new List<SqlParameter>();240       parameters.Add(new SqlParameter("@where", strWhere));241       #endregion242 243       StringBuilder sqlStr = new StringBuilder();244       sqlStr.Append("select * from ");245       sqlStr.Append(entity.GetType().Name);246       if (strWhere.Trim().Length > 0)247         sqlStr.Append(" where @where");248 249       return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]);250     }251 252     /// <summary>253     /// 获得总数254     /// </summary>255     /// <param name="connection">连接字符串</param>256     /// <param name="strWhere">查询条件</param>257     /// <returns></returns>258     public virtual int GetRecordCount(string connection, string strWhere)259     {260       #region 参数261       T entity = new T();262 263       List<SqlParameter> parameters = new List<SqlParameter>();264       parameters.Add(new SqlParameter("@where", strWhere));265       #endregion266 267       StringBuilder sqlStr = new StringBuilder();268       sqlStr.Append("select count(1) FROM ");269       sqlStr.Append(entity.GetType().Name);270       if (strWhere.Trim().Length > 0)271         sqlStr.Append(" where @where");272 273       return int.Parse(DatabaseAccess.SqlHelper.ExecuteScalarText(connection, sqlStr.ToString(), parameters.ToArray()).ToString());274     }275 276     /// <summary>277     /// 执行sql语句278     /// </summary>279     /// <param name="connection"></param>280     /// <param name="cmdtype"></param>281     /// <param name="sql"></param>282     /// <param name="parameters"></param>283     /// <returns></returns>284     public virtual int ExecteNonQuerySQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters)285     {286       return DatabaseAccess.SqlHelper.ExecteNonQuery(connection, cmdtype, sql, parameters);287     }288 289     /// <summary>290     /// 获取类主键291     /// </summary>292     /// <param name="connection">连接字符串</param>293     /// <param name="entity">类</param>294     /// <returns></returns>295     public virtual string GetPrimarykey(string connection, T entity)296     {297       StringBuilder sqlStr = new StringBuilder();298       sqlStr.Append("EXEC sp_pkeys @table_name='" + entity.GetType().Name + "'");299 300       SqlParameter[] parameters = new SqlParameter[]301       {302        303       };304 305       return DatabaseAccess.SqlHelper.ExecuteDataSetText(connection,sqlStr.ToString(),parameters).Tables[0].Rows[0]["COLUMN_NAME"].ToString();306     }307 308     /// <summary>309     /// 执行sql语句310     /// </summary>311     /// <param name="connection"></param>312     /// <param name="cmdtype"></param>313     /// <param name="sql"></param>314     /// <param name="parameters"></param>315     /// <returns></returns>316     public virtual DataSet ExecuteDataSetSQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters)317     {318       return DatabaseAccess.SqlHelper.ExecuteDataSet(connection, cmdtype, sql, parameters);319     }320   }

View Code

这些方法中默认表都是有主键的,而且实体名称和表名一致,否则执行会报错.

这套方法已经被我用在一个旧项目的改造中,目前来看效果还是不错的,因此在这与大家分享,因为本人水平有限,而且没用太多时间去完善,这套代码肯定会有很多瑕疵,希望高手不吝赐教.