访问SQLServer的通用DAL层

首先是DataTable转List<T>的方法,这个方法通用性极强. #region Table转List /// <summary> /// Table转List /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dt"></param> /// <returns></returns> public static List<T> TableToList<T>(DataTable dt) where T : new() { // 定义集合 IList<T> list = new List<T>(); // 获得此模型的类型 Type type = typeof(T); foreach (DataRow dr in dt.Rows) { T t = new T(); // 获得公共属性 PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { // 判断此属性是否有Setter if (!pi.CanWrite) continue; object value = dr[pi.Name]; if (value != DBNull.Value) pi.SetValue(t, value, null); } list.Add(t); } return list.ToList(); } #endregion /// <summary> /// 数据访问基类:BaseService /// </summary> /// <typeparam name="T"></typeparam> public class BaseService<T> : IBaseService<T> where T : class ,new() { /// <summary> /// 得到某列最大值 /// </summary> /// <param name="connection"></param> /// <param name="fieldname"></param> /// <returns></returns> public virtual int GetMax(string connection,string fieldname) { #region 参数 T entity = new T(); SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@fieldname",fieldname) }; #endregion StringBuilder sqlStr=new StringBuilder(); sqlStr.Append("select max("); sqlStr.Append("@fieldname"); sqlStr.Append(") from "); sqlStr.Append(entity.GetType().Name); return int.Parse(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters).Tables[0].Rows[0][0].ToString()); } /// <summary> /// 是否存在该记录 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="primarykey">主键值</param> /// <returns></returns> public virtual bool Exists(string connection, object primarykey) { #region 参数 T entity = new T(); string primaryKey = GetPrimarykey(connection, entity); List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("select count(1) from "); sqlStr.Append(entity.GetType().Name); sqlStr.Append(" where "); sqlStr.Append(primaryKey + "=@" + primaryKey); int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray()); if (res > 0) return true; else return false; } /// <summary> /// 增加一条数据 /// </summary> public virtual bool Add(string connection, T entity) { #region 参数 List<SqlParameter> parameters = new List<SqlParameter>(); string fields = ""; string placeholders = ""; foreach (var item in entity.GetType().GetProperties()) { fields += item.Name + ","; placeholders += "@" + item.Name + ","; SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@" + item.Name; parameter.Value = item.GetValue(entity, null); parameters.Add(parameter); } #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("insert " + entity.GetType().Name + " ("); sqlStr.Append(fields.Substring(0, fields.Length - 1) + ")"); sqlStr.Append(" values ("); sqlStr.Append(placeholders.Substring(0, placeholders.Length - 1) + ")"); int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray()); if (res > 0) return true; else return false; } /// <summary> /// 更新一条数据 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="entity">类</param> /// <returns></returns> public virtual bool Update(string connection, T entity) { #region 参数 string primaryKey = GetPrimarykey(connection, entity); List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@"+primaryKey, entity.GetType().GetProperty(primaryKey).GetValue(entity,null))); string fields = ""; foreach (var item in entity.GetType().GetProperties()) { if (item.Name != primaryKey) { fields += item.Name + "=@" + item.Name + ","; SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@" + item.Name; parameter.Value = item.GetValue(entity, null); parameters.Add(parameter); } } #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("update " + entity.GetType().Name); sqlStr.Append(" set "); sqlStr.Append(fields.Substring(0,fields.Length-1)); sqlStr.Append(" where "); sqlStr.Append(primaryKey + "=@" + primaryKey); int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray()); if (res > 0) return true; else return false; } /// <summary> /// 删除一条数据 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="primaryKey">主键值</param> /// <returns></returns> public virtual bool Delete(string connection, object primarykey) { #region 参数 T entity = new T(); string primaryKey = GetPrimarykey(connection, entity); List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("delete from "); sqlStr.Append(entity.GetType().Name); sqlStr.Append(" where "); sqlStr.Append(primaryKey + "=@" + primaryKey); int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray()); if (res > 0) return true; else return false; } /// <summary> /// 删除多条数据 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="base_idlist">主键值列表</param> /// <returns></returns> public virtual bool DeleteList(string connection, List<object> primarykeys) { #region 参数 T entity = new T(); string primaryKey = GetPrimarykey(connection, entity); string primaryKeys = ""; foreach(var item in primarykeys) { primaryKeys += item.ToString(); } List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@" + primaryKey, primaryKeys)); #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("delete from "); sqlStr.Append(entity.GetType().Name); sqlStr.Append(" where "); sqlStr.Append(primaryKey + " in(@" + primaryKey + ")"); int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray()); if (res > 0) return true; else return false; } /// <summary> /// 得到一个对象实体 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="primarykey">主键值</param> /// <returns></returns> public virtual T GetModel(string connection, object primarykey) { #region 参数 T entity = new T(); string primaryKey = GetPrimarykey(connection, entity); List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@table", entity.GetType().Name)); parameters.Add(new SqlParameter("@" + primaryKey, primarykey)); #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("select * from "); sqlStr.Append(entity.GetType().Name); sqlStr.Append(" where "); sqlStr.Append(primaryKey + "=@" + primaryKey); return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]).FirstOrDefault(); } /// <summary> /// 获得数据列表 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="strWhere">查询条件</param> /// <returns></returns> public virtual List<T> GetList(string connection, string strWhere) { #region 参数 T entity = new T(); List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@where", strWhere)); #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("select * from "); sqlStr.Append(entity.GetType().Name); if (strWhere.Trim().Length > 0) sqlStr.Append(" where @where"); return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]); } /// <summary> /// 获得总数 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="strWhere">查询条件</param> /// <returns></returns> public virtual int GetRecordCount(string connection, string strWhere) { #region 参数 T entity = new T(); List<SqlParameter> parameters = new List<SqlParameter>(); parameters.Add(new SqlParameter("@where", strWhere)); #endregion StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("select count(1) FROM "); sqlStr.Append(entity.GetType().Name); if (strWhere.Trim().Length > 0) sqlStr.Append(" where @where"); return int.Parse(DatabaseAccess.SqlHelper.ExecuteScalarText(connection, sqlStr.ToString(), parameters.ToArray()).ToString()); } /// <summary> /// 执行sql语句 /// </summary> /// <param name="connection"></param> /// <param name="cmdtype"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public virtual int ExecteNonQuerySQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters) { return DatabaseAccess.SqlHelper.ExecteNonQuery(connection, cmdtype, sql, parameters); } /// <summary> /// 获取类主键 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="entity">类</param> /// <returns></returns> public virtual string GetPrimarykey(string connection, T entity) { StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("EXEC sp_pkeys @table_name='" + entity.GetType().Name + "'"); SqlParameter[] parameters = new SqlParameter[] { }; return DatabaseAccess.SqlHelper.ExecuteDataSetText(connection,sqlStr.ToString(),parameters).Tables[0].Rows[0]["COLUMN_NAME"].ToString(); } /// <summary> /// 执行sql语句 /// </summary> /// <param name="connection"></param> /// <param name="cmdtype"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public virtual DataSet ExecuteDataSetSQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters) { return DatabaseAccess.SqlHelper.ExecuteDataSet(connection, cmdtype, sql, parameters); } }