public class BaseDal<T> where T : class { #region 通用方法 /// <summary> /// 新增 /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual int Insert(T model) { int res = DbHelper.Insert<T>(model); return res; } /// <summary> /// 批量新增 /// </summary> /// <param name="list"></param> /// <returns></returns> public virtual bool InsertBatch(List<T> list) { bool res = DbHelper.InsertBatch<T>(list); return res; } /// <summary> /// 修改 /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual bool Update(T model) { bool res = DbHelper.Update<T>(model); return res; } /// <summary> /// 更新非空字段部分字段 默认第一个字段为更新主键 /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual bool UpdateNotNull(T model) { object val = ""; var p = new DynamicParameters(); StringBuilder sql = new StringBuilder(); sql.Append("UPDATE " + model.GetType().Name + " set "); Type t = typeof(T); System.Reflection.PropertyInfo[] properties = t.GetProperties(); foreach (System.Reflection.PropertyInfo info in properties) { val = model.GetType().GetProperty(info.Name).GetValue(model, null); if (val != null && (info.Name != properties[0].Name)) { sql.Append("" + info.Name + "=@" + info.Name + ","); p.Add(info.Name, val); } string str = "name=" + info.Name + ";" + "type=" + info.PropertyType.Name + "," + info.GetType().Name + ";value=" + model.GetType().GetProperty(info.Name).GetValue(model, null) + ""; } sql.Remove(sql.Length - 1, 1); sql.Append(" where " + properties[0].Name + "=@" + properties[0].Name + ""); p.Add(properties[0].Name, model.GetType().GetProperty(properties[0].Name).GetValue(model, null)); DbHelper.Execute(sql.ToString(), p); return true; } /// <summary> /// 删除 /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual bool Delete(T model) { bool res = DbHelper.Delete<T>(model); return res; } /// <summary> /// 批量删除 /// </summary> /// <param name="list"></param> /// <returns></returns> public virtual bool DeleteBatch(List<T> list) { bool res = DbHelper.DeleteBatch<T>(list); return res; } /// <summary> /// 查询一个实体 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual T Get(string id) { T res = DbHelper.Get<T>(id); return res; } /// <summary> /// 查询全部 /// </summary> /// <returns></returns> public virtual List<T> GetListAll() { List<T> res = DbHelper.GetListAll<T>(); return res; } #endregion #region 示例方法需重写 /// <summary> /// 以条件查询 /// </summary> /// <param name="where"></param> /// <returns></returns> public virtual List<T> GetList(JObject where) { //var where = new { UserID = 5 }; var p = new DynamicParameters(); IList<ISort> sort = new List<ISort>(); sort.Add(new Sort { PropertyName = "ID", Ascending = true }); if (where["ID"] != null) { p.Add("ID", new int[3] { 1, 5, 6 }); } if (where["Name"] != null) { p.Add("Name", "张三"); } List<T> res = DbHelper.GetList<T>(p, sort); return res; } /// <summary> /// 执行sql 查询 返回一个结果集 /// </summary> /// <param name="value"></param> /// <returns></returns> public virtual List<T> Query(JObject value) { var p = new DynamicParameters(); StringBuilder sb = new StringBuilder(); sb.Append(" where 1=1"); if (value["ID"] != null) { sb.Append(" and a.ID in @ID"); p.Add("ID", new int[3] { 1, 5, 6 }); } if (value["Name"] != null) { sb.Append(" and a.Name=@Name"); p.Add("Name", "张三"); } string sql = @"select a.*,b.[Name]as ProjectName from ServiceRecordReport a left join ProjectInfo b on a.ProjectId=b.ID "; return DbHelper.Query<T>(sql + sb.ToString(), p); } /// <summary> /// 查询第一行 /// </summary> /// <param name="value"></param> /// <returns></returns> public virtual T QueryFirstOrDefaultView(JObject value) { var p = new DynamicParameters(); StringBuilder sb = new StringBuilder(); sb.Append(" where 1=1"); if (!string.IsNullOrWhiteSpace(value["ID"].ToString())) { sb.Append(" and a.ID=@ID"); p.Add("ID", value["ID"].ToString()); } string sql = @"select a.*,b.[Name]as ProjectName from ServiceRecordReport a left join ProjectInfo b on a.ProjectId=b.ID "; return DbHelper.QueryFirstOrDefault<T>(sql + sb.ToString(), p); } /// <summary> /// Dapper扩展分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="allRowsCount"></param> /// <returns></returns> public virtual List<T> GetPage(int pageIndex, int pageSize, out long allRowsCount, JObject jObject = null) { var p = new DynamicParameters(); p.Add("CustomerName", "张三"); IList<ISort> sort = new List<ISort>(); sort.Add(new Sort { PropertyName = "ID", Ascending = true }); List<T> res = DbHelper.GetPage<T>(pageIndex, pageSize, out allRowsCount, p, sort).ToList(); return res; } /// <summary> /// SQL分页示例 /// </summary> /// <returns></returns> public virtual PageDataView<T> GetPageListForSQL(JObject value) { PageCriteria pageCriteria = new PageCriteria(); StringBuilder sb = new StringBuilder(); sb.Append("1=1"); //工程师（上传人） if (!string.IsNullOrWhiteSpace(value["EngineerSignature"].ToString())) { sb.Append(" and EngineerSignature=@EngineerSignature"); pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "EngineerSignature", ParamValue = value["EngineerSignature"].ToString() }); } //项目id if (!string.IsNullOrWhiteSpace(value["ProjectId"].ToString())) { sb.Append(" and a.ProjectId=@ProjectId"); pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "ProjectId", ParamValue = value["ProjectId"].ToString() }); } //上传日期 if (!string.IsNullOrWhiteSpace(value["CreaterDate"].ToString())) { sb.Append(" and CONVERT(varchar(100), a.CreaterDate, 23)=@CreaterDate"); pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "CreaterDate", ParamValue = value["CreaterDate"].ToString() }); } pageCriteria.Condition = sb.ToString(); pageCriteria.CurrentPage = Convert.ToInt32(value["CurrentPage"]); pageCriteria.Fields = " a.*,b.[Name]as ProjectName "; pageCriteria.PageSize = 20; //pageCriteria.PrimaryKey = " id"; pageCriteria.Sort = " a.ProjectId,a.ID desc"; pageCriteria.TableName = "ServiceRecordReport a left join ProjectInfo b on a.ProjectId = b.ID"; PageDataView<T> res = DbHelper.GetPageListForSQL<T>(pageCriteria); return res; } /// <summary> /// SQL分页示例 主要用于 MVC控制器 Layui /// </summary> /// <param name="page">当前页</param> /// <param name="limit">每页记录数</param> /// <param name="value">条件、排序等</param> /// <returns></returns> public virtual PageDataView<T> GetPageListForSQL(int page, int limit, T model) { PageCriteria pageCriteria = new PageCriteria(); StringBuilder sb = new StringBuilder(); sb.Append("1=1"); //if (!string.IsNullOrWhiteSpace(model.Name)) //{ // sb.Append(" and Name=@Name"); // pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "Name", ParamValue = model.Name }); //} pageCriteria.Condition = sb.ToString(); pageCriteria.CurrentPage = page; pageCriteria.Fields = " a.*,b.F_Name,b.F_Path,b.F_UploadTime,b.F_TargetID,b.F_TargetTable,b.F_TargetField "; pageCriteria.PageSize = limit; //pageCriteria.PrimaryKey = " id"; pageCriteria.Sort = "R_ID"; pageCriteria.TableName = " RepairRecord a left join FileManage b on a.R_SerialNumber = b.F_TargetID "; PageDataView<T> res = DbHelper.GetPageListForSQL<T>(pageCriteria); return res; } #endregion }