/// <summary> /// 用户列表 /// </summary> /// <typeparam name="UserListModel">用户列表实体</typeparam> /// <param name="searchDto">用户列表查询实体</param> /// <returns></returns> public PageDataView <UserListDto> GetList(UserSearchDto searchDto) { PageDataView <UserListDto> result = new PageDataView <UserListDto>() { }; StringBuilder select = new StringBuilder(); StringBuilder where = new StringBuilder(); DynamicParameters parameters = new DynamicParameters(); select.Append("SELECT * FROM [User] "); where.Append("WHERE 1=1 "); if (!string.IsNullOrEmpty(searchDto.DomainAccount)) { where.Append($"AND DomainAccount LIKE @DomainAccount "); parameters.Add("@DomainAccount", "%" + searchDto.DomainAccount + "%"); } if (!string.IsNullOrEmpty(searchDto.RealName)) { where.Append($"AND RealName LIKE @RealName "); parameters.Add("@RealName", "%" + searchDto.RealName + "%"); } Dictionary <string, bool> ordery = new Dictionary <string, bool>(); ordery.Add("createdAt", false); result = _unitOfWork.GetPageData <UserListDto>(select.ToString() + where.ToString(), ordery, searchDto.PageIndex, searchDto.PageSize, parameters); return(result); }
/// <summary> /// 公共分页方法 /// </summary> /// <typeparam name="T">表名</typeparam> /// <param name="criteria">分页对象</param> /// <returns></returns> public static PageDataView <T> GetPageData <T>(PageCriteria criteria) { using (OracleConnection conn = GetConnString()) { conn.Open(); DynamicParameters parms = new DynamicParameters(); parms.Add("@TableName", criteria.TableName, DbType.String, ParameterDirection.Input, null); parms.Add("@PrimaryKey", criteria.PrimaryKey, DbType.String, ParameterDirection.Input, null); parms.Add("@Fields", criteria.Fields, DbType.String, ParameterDirection.Input, null); parms.Add("@Condition", criteria.Condition, DbType.String, ParameterDirection.Input, null); parms.Add("@CurrentPage", criteria.CurrentPage, DbType.Int32, ParameterDirection.Input, null); parms.Add("@PageSize", criteria.PageSize, DbType.Int32, ParameterDirection.Input, null); parms.Add("@Sort", criteria.Sort, DbType.String, ParameterDirection.Input, null); parms.Add("@RecordCount", criteria.RecordCount, DbType.Int32, ParameterDirection.Output, null); string proName = "ProcGetPageData"; var pageData = new PageDataView <T>(); pageData.Items = conn.Query <T>(proName, parms, CommandType.StoredProcedure).ToList(); conn.Close(); pageData.TotalNum = parms.Get <int>("RecordCount"); pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return(pageData); } }
/// <summary> /// 分页 /// </summary> /// <param name="criteria">表名等集合</param> /// <returns>分页数据</returns> public async Task <PageDataView <T> > GetPageDataAsync <T>(PageCriteria criteria) where T : class, new() { using (var conn = await DataBaseConfig.GetNpgSqlConnectionAsync(_connectionstr)) { var p = new DynamicParameters(); var proName = "Module_Common_PagerNew"; p.Add("@tableName", criteria.TableName); p.Add("@tableFields", criteria.Fields); p.Add("@sqlWhere", criteria.Condition); p.Add("@orderFields", criteria.Sort); p.Add("@pageSize", criteria.PageSize); p.Add("@pageIndex", criteria.CurrentPage); p.Add("@totalPage", dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add("@totalRecord", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView <T> { Items = await conn.QueryAsync <T>(proName, p, commandType : CommandType.StoredProcedure), TotalNum = p.Get <int>("@totalRecord"), TotalPageCount = p.Get <int>("@totalPage") }; pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return(pageData); } }
/// <summary> /// 分页方法 /// </summary> /// <typeparam name="T">查询实体</typeparam> /// <param name="connectionString">连接字符串</param> /// <param name="pageCriteria">查询条件</param> /// <param name="totalNum">总数</param> /// <returns></returns> public static PageDataView <T> GetPageListForSQL <T>(string connectionString, PageCriteria pageCriteria) { var result = new PageDataView <T>(); string sql = "SELECT * from(SELECT " + pageCriteria.Fields + ",row_number() over(order by " + pageCriteria.Sort + ") rownum FROM " + pageCriteria.TableName + " where " + pageCriteria.Condition + ") t where rownum>@minrownum and rownum<=@maxrownum"; string countSql = "select count(1) from " + pageCriteria.TableName + " where " + pageCriteria.Condition; int minrownum = (pageCriteria.CurrentPage - 1) * pageCriteria.PageSize; int maxrownum = minrownum + pageCriteria.PageSize; var p = new DynamicParameters(); p.Add("minrownum", minrownum); p.Add("maxrownum", maxrownum); if (pageCriteria.ParameterList != null) { foreach (var param in pageCriteria.ParameterList) { p.Add(param.ParamName, param.ParamValue); } } var reader = MultyQuery(connectionString, sql + ";" + countSql, p); result.Items = reader.Read <T>().ToList(); result.TotalNum = reader.Read <int>().First <int>(); result.CurrentPage = pageCriteria.CurrentPage; result.TotalPageCount = result.TotalNum / pageCriteria.PageSize + (result.TotalNum % pageCriteria.PageSize == 0 ? 0 : 1); return(result); }
public virtual PageDataView <TEntity> GetPage(PageCriteria criteria, object param = null, IDbTransaction transaction = null) { var p = new DynamicParameters(); string proName = "ProcGetPageData"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Condition", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Sort", criteria.Sort); p.Add("IsDesc", criteria.IsDesc); p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView <TEntity>(); if (transaction == null) { pageData.Items = _dbConnection.GetConnection("read").Query <TEntity>(proName, p, commandType: CommandType.StoredProcedure).ToList(); } else { pageData.Items = transaction.Connection.Query <TEntity>(proName, p, commandType: CommandType.StoredProcedure).ToList(); } pageData.TotalNum = p.Get <int>("RecordCount"); pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return(pageData); }
/// <summary> /// 获取分页数据 /// </summary> /// <typeparam name="TM"></typeparam> /// <param name="criteria"></param> /// <param name="param"></param> /// <returns></returns> public PageDataView <TM> GetPageData <TM>(PageCriteria criteria, object param = null) where TM : class, IEntityBase, new() { using (var conn = UnitOfWork.GetDbConnection()) { var p = new DynamicParameters(); const string proName = "ProcGetPageData"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Condition", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Sort", criteria.Sort); p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView <TM> { Items = conn.Query <TM>(proName, p, commandType: CommandType.StoredProcedure).ToList(), RecordCount = p.Get <int>("RecordCount") }; pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.RecordCount * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return(pageData); } }
public PageDataView <TEntity> GetPageData <TEntity>(PageCriteria criteria, object param = null) where TEntity : class { var p = new DynamicParameters(); string proName = "CCHRWebApiProcGetPageData"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Condition", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Sort", criteria.Sort); p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView <TEntity>(); int excuteTimes = 0; //QUERY: try { pageData.Items = Connection.Query <TEntity>(proName, p, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList(); pageData.TotalNum = p.Get <int>("RecordCount"); pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; } catch (Exception e) { excuteTimes++; if (e.Message.Contains("找不到存储过程 'CCHRWebApiProcGetPageData'") && excuteTimes <= 3) { FileStream fileStream = new FileStream("CCHRWebApiProcGetPageData.sql", FileMode.Open); StreamReader sr = new StreamReader(fileStream); string sql = sr.ReadToEnd(); if (fileStream != null) { fileStream.Close(); } if (sr != null) { sr.Close(); } if (!string.IsNullOrEmpty(sql)) { Connection.Execute(sql); // goto QUERY; } throw; } else if ((e.Message.Contains("远程主机强迫关闭了一个现有的连接") || e.Message.Contains("指定的网络名不再可用")) && excuteTimes <= 3) { System.Threading.Thread.Sleep(excuteTimes * 1000); //goto QUERY; } else { throw; } } return(pageData); }
public PageDataView <TReturn> GetJoinPageData <TFirst, TSecond, TThird, TReturn>(PageCriteria criteria, Func <TFirst, TSecond, TThird, TReturn> map, string splitOnString) where TReturn : class { var p = new DynamicParameters(); string proName = "CCHRWebApiProcGetPageData"; p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Condition", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Sort", criteria.Sort); p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView <TReturn>(); pageData.Items = Connection.Query <TFirst, TSecond, TThird, TReturn>(proName, map, p, splitOn: splitOnString, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList(); pageData.TotalNum = p.Get <int>("RecordCount"); pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return(pageData); }
protected static PageDataView <T> GetPageData <T>(PageCriteria criteria) { using (var conn = new SqlConnection(ConnString)) { var p = new DynamicParameters(); p.Add("TableName", criteria.TableName); p.Add("PrimaryKey", criteria.PrimaryKey); p.Add("Fields", criteria.Fields); p.Add("Condition", criteria.Condition); p.Add("CurrentPage", criteria.CurrentPage); p.Add("PageSize", criteria.PageSize); p.Add("Sort", criteria.Sort); p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView <T>(); pageData.Items = conn.Query <T>("sp_GetPageData", p, commandType: CommandType.StoredProcedure).ToList(); pageData.TotalNum = p.Get <int>("RecordCount"); pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return(pageData); } }
public PageDataView <CustomerPVListDto> GetList(CustomerPVSearchDto dto) { PageDataView <CustomerPVListDto> result = new PageDataView <CustomerPVListDto>() { }; StringBuilder select = new StringBuilder(); StringBuilder where = new StringBuilder(); DynamicParameters parameters = new DynamicParameters(); select.Append("SELECT B.* ,P.ProjectName FROM CustomerPV AS B JOIN Project AS P ON B.webMonitorId=P.KeyId "); where.Append("WHERE 1=1 "); if (!string.IsNullOrEmpty(dto.WebMonitorId)) { where.Append($"AND WebMonitorId = @WebMonitorId "); parameters.Add("@WebMonitorId", dto.WebMonitorId); } if (!string.IsNullOrEmpty(dto.CustomerKey)) { where.Append($"AND (B.CustomerKey = @CustomerKey OR B.UserID = @CustomerKey) "); parameters.Add("@CustomerKey", dto.CustomerKey); } if (dto.StartTime.HasValue) { where.Append($"AND B.createdAt >= @StartTime "); parameters.Add("@StartTime", dto.StartTime); } if (dto.EndTime.HasValue) { where.Append($"AND B.createdAt <= @EndTime "); parameters.Add("@EndTime", dto.EndTime); } Dictionary <string, bool> ordery = new Dictionary <string, bool>(); ordery.Add("createdAt", false); result = _unitOfWork.GetPageData <CustomerPVListDto>(select.ToString() + where.ToString(), ordery, dto.PageIndex, dto.PageSize, parameters); return(result); }
// [Route("ddd/12.html")] // GET: Default public ActionResult Index() { //List<Contact> modelList= _contactService.GetModels(new Contact() { ID = 0 }); PageCriteria criteria = new PageCriteria(); criteria.TableName = "Contact"; criteria.Fields = "*"; criteria.PrimaryKey = "ContactID"; criteria.CurrentPage = 1; criteria.PageSize = 5; criteria.Sort = "ID desc "; criteria.Condition = " ID>0"; PageDataView <Contact> pageList = _contactService.GetModelsByPage(criteria); List <Contact> modelList = pageList.Items; ViewBag.TotalNum = pageList.TotalNum; ViewBag.CurrentPage = pageList.CurrentPage; ViewBag.TotalPageCount = pageList.TotalPageCount; return(View(modelList)); }
public PageDataView <ProjectListDto> GetList(SearchProjectDto dto) { PageDataView <ProjectListDto> result = new PageDataView <ProjectListDto>() { }; StringBuilder select = new StringBuilder(); StringBuilder where = new StringBuilder(); DynamicParameters parameters = new DynamicParameters(); select.Append("SELECT * FROM Project "); where.Append("WHERE 1=1 "); if (!string.IsNullOrEmpty(dto.ProjectName)) { where.Append($"AND ProjectName LIKE @ProjectName "); parameters.Add("@ProjectName", "%" + dto.ProjectName + "%"); } Dictionary <string, bool> ordery = new Dictionary <string, bool>(); ordery.Add("createdAt", false); result = _unitOfWork.GetPageData <ProjectListDto>(select.ToString() + where.ToString(), ordery, dto.PageIndex, dto.PageSize, parameters); return(result); }
/// <summary> /// SQL语句分页查询 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="sqlDistinct">sql语句</param> /// <param name="sort">排序字段集合</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">页条数</param> /// <param name="parameters">查询参数</param> /// <returns></returns> public PageDataView <TEntity> GetPageData <TEntity>(string sqlDistinct, Dictionary <string, bool> sort, int?pageIndex = null, int?pageSize = null, DynamicParameters parameters = null, bool readOnly = false) where TEntity : class { try { if (string.IsNullOrEmpty(sqlDistinct)) { throw new Exception("查询语句为空"); } StringBuilder select = new StringBuilder(); int skipNum = 0; if (pageIndex.HasValue && pageSize.HasValue) { select.AppendFormat("SELECT TOP ({0}) * ", pageSize.Value); skipNum = pageSize.Value * (pageIndex.Value - 1); } else { select.Append("SELECT * "); } StringBuilder order = new StringBuilder(); foreach (string key in sort.Keys) { if (key.Contains(",")) { string[] arr = key.Split(','); foreach (string a in arr) { if (!string.IsNullOrEmpty(a)) { order.AppendFormat("{0} {1},", a, sort[key] == true ? "ASC" : "DESC"); } } } else { order.AppendFormat("{0} {1},", key, sort[key] == true ? "ASC" : "DESC"); } } order.Remove(order.Length - 1, 1); //返回当前页数据 StringBuilder sql = new StringBuilder(); sql.Append(select); sql.AppendFormat("FROM (SELECT *,row_number() OVER (ORDER BY {0}) AS row_number ", order); sql.AppendFormat("FROM ({0}) AS t1) AS t2 ", sqlDistinct); sql.AppendFormat("WHERE row_number>{0} ", skipNum); sql.AppendFormat("ORDER BY {0} ", order); var pageData = new PageDataView <TEntity>(); //查询数据 //pageData.Items = Connection.Query<TEntity>(sql.ToString(), parameters, commandType: CommandType.Text).ToList(); pageData.Items = this.GetUnitOfWork(readOnly).Query <TEntity>(sql.ToString(), parameters).ToList(); if (pageData.Items.Count > 0 && pageIndex.HasValue && pageSize.HasValue) { pageData.TotalNum = this.GetPageCount(sqlDistinct, parameters); pageData.CurrentPage = pageIndex.Value; } return(pageData); } catch { throw; } }
public static MvcHtmlString PageLinks <T>(this HtmlHelper html, PageDataView <T> pageData, Func <int, string> pageUrl, int showMaxPageNum = 20) { const string tplPageNumLink = "<a href=\"{0}\">{1}</a>"; const string tmplPageNumLinkCurrent = "<span class=\"current\">{0}</span>"; var result = new StringBuilder(); if (pageData.TotalPageCount > 0) { result.AppendFormat(@"第 {0} 页,共 {1} 页,共{2}条", (pageData.TotalPageCount < pageData.CurrentPage ? 0 : pageData.CurrentPage) , pageData.TotalPageCount , pageData.TotalNum); int showStartPageNum, showEndPageNum; if (pageData.CurrentPage <= 1) { showStartPageNum = 1; showEndPageNum = showMaxPageNum; if (showEndPageNum > pageData.TotalPageCount) { showEndPageNum = pageData.TotalPageCount; } } else if (pageData.CurrentPage >= pageData.TotalPageCount) { showStartPageNum = pageData.TotalPageCount - showMaxPageNum + 1; showEndPageNum = pageData.TotalPageCount; if (showStartPageNum <= 0) { showStartPageNum = 1; } } else { int beforeCount = Convert.ToInt32(Math.Ceiling(showMaxPageNum * 1.0 / 2)); showStartPageNum = pageData.CurrentPage - beforeCount; if (showStartPageNum <= 0) { showStartPageNum = 1; } showEndPageNum = showStartPageNum + showMaxPageNum - 1; if (showEndPageNum > pageData.TotalPageCount) { showEndPageNum = pageData.TotalPageCount; } if (showStartPageNum > pageData.TotalPageCount) { showEndPageNum = pageData.TotalPageCount; } } result.AppendFormat(tplPageNumLink, pageUrl(1), "首页"); if (pageData.CurrentPage > 1) { result.AppendFormat(tplPageNumLink, pageUrl(pageData.CurrentPage - 1), "上一页"); } for (int i = showStartPageNum; i <= showEndPageNum; i++) { if (i == pageData.CurrentPage) { result.AppendFormat(tmplPageNumLinkCurrent, i); } else { result.AppendFormat(tplPageNumLink, pageUrl(i), i); } } if (pageData.CurrentPage < pageData.TotalPageCount) { result.AppendFormat(tplPageNumLink, pageUrl(pageData.CurrentPage + 1), "下一页"); } result.AppendFormat(tplPageNumLink, pageUrl(pageData.TotalPageCount), "末页"); } return(MvcHtmlString.Create(result.ToString())); }