访问SQLServer的通用DAL层
来源：互联网 发布：淘宝网飞机杯货到付款 编辑：程序博客网 时间：2024/05/22 07:05
首先是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); } } 0 0
- 访问SQLServer的通用DAL层
- Java通用数据访问层 Uncode-DAL
- Java通用数据访问层Fastser-DAL推荐
- 通用数据访问层--SQLServer版
- 通用数据访问层--SQLServer版
- 【转】通用数据访问层--SQLServer版
- bll层访问dal层的类出错
- 分布式数据访问层(DAL)
- DAL层的代码
- DAL-自动生成数据访问层代码
- 数据访问层DAL实现过程
- Access数据访问层（DAL）
- CodeSmith DAL层的写法
- 三层开发，数据访问层代码（DAL 层）
- MVC入门 DAL层-数据库访问层 (3)
- 业务逻辑层(BLL)、DAL工厂、DAL接口、实体类、SQLServerDAL数据访问层
- 用C#实现通用的DAL Insert
- SQLServer数据层通用类
- domain、entity和model的package的不同用法
- 【九度】题目1153：括号匹配问题
- Android性能优化回顾
- 微信小程序开发官方文档
- java将float数据写入csv 并在python中读取csv文件
- 访问SQLServer的通用DAL层
- DEDECMS之LOOP大循环三
- 根据参数动态加载bean（一个接口多个实现的调用方法）
- 记录ButterKnife：8.1.0的使用
- RabbitMQ服务之运行管理篇
- Groovy脚本基础全攻略
- mysql的安装问题以及一些常用的指令（1）
- Mysql5.7编译安装
- Nginx入门（一）
