示例#1
0
        Task <PagedList <Banner> > IBannerStorage.GetPagedListAsync(int page, int pageSize, int type, string title, string orderBy)
        {
            var order     = base.GetSortInfo(orderBy, new SortInfo("id"), m_PagedOrderColumns);
            var condition = SqlClauses.Create().AndCaluse("type = @type", type > 0).AndCaluse("title like @title", !string.IsNullOrEmpty(title)).ToSql();

            string sql = $@"SELECT * FROM (SELECT ROW_NUMBER() OVER({order}) AS row, *  FROM banner {condition}
                            ) t where t.row between (@page - 1) * @pageSize + 1 AND @page * @pageSize ;
                     SELECT COUNT(1) FROM banner {condition} ;";

            return(base.GetPagedListAsync <Banner>(sql, new { page, pageSize, type, title = base.ToLikeParam(title) }));
        }
示例#2
0
        Task <PagedList <Intention> > IIntentionStorage.GetPagedListAsync(int page, int pageSize, string keyword, int[] intentions, string orderBy)
        {
            var order         = base.GetSortInfo(orderBy, new SortInfo("id"), m_PagedOrderColumns);
            var intentionSqls = new List <string>();

            if (intentions != null && intentions.Any())
            {
                foreach (var item in intentions.Distinct())
                {
                    intentionSqls.Add($" intention like '%{item}%' ");
                }
            }
            var condition = SqlClauses.Create()
                            .AndCaluse($"({string.Join(" or ", intentionSqls)})", intentionSqls.Any())
                            .AndCaluse("( name like @keyword or phone like @keyword)", !string.IsNullOrEmpty(keyword)).ToSql();
            string sql = $@"SELECT * FROM (SELECT ROW_NUMBER() OVER({order}) AS row, *  FROM intention {condition} 
                            ) t where t.row between (@page - 1) * @pageSize + 1 AND @page * @pageSize ;
                     SELECT COUNT(1) FROM intention {condition} ;";

            return(base.GetPagedListAsync <Intention>(sql, new { page, pageSize, keyword = base.ToLikeParam(keyword) }));
        }