示例#1
0
        private PageRowInnerParamsResultMultipleOrderBy GetListByPage_GetPageBeginRowMultipleOrderBy(PageRowInnerParamsResultMultipleOrderBy paras)
        {
            Dispose(false);
            string whereCompare        = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString());
            string whereCompareReverse = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString(), true);
            string sql       = string.Empty;
            var    thisIndex = 0;

            //向前取样
            if (paras.isGreater)
            {
                #region 向前取样
                thisIndex = (paras.RowIndex - paras.Begin) / paras.ConfigCount;
                sql       = string.Format(@"SELECT {0},RowIndex,{1}   FROM (
                                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {2}) AS  ROWINDEX  FROM ({3}) as sqlstr  WHERE  {4} ) t WHERE t.ROWINDEX={5}
             
                                                                                       ",
                                          paras.UnqueField /*0*/,
                                          paras.orderByFieldsString /*1*/,
                                          paras.FullOrderByStringReverse /*2*/,
                                          paras.Sql /*3*/,
                                          whereCompare /*4*/,
                                          thisIndex /*5*/);

                var rowList        = Taskable <DataTable>(sql, paras.WhereObj).MergeTable().ToList();
                int sampleRowIndex = 0;
                int i = -1;
                while (sampleRowIndex == 0)
                {
                    var isFirst = i == -1;
                    if (i == paras.SampleEachIndex)
                    {
                        continue;
                    }
                    var row = rowList[isFirst?paras.SampleEachIndex:i];
                    paras.Row           = row;
                    paras.UnqueValue    = row[0];
                    whereCompare        = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString());
                    whereCompareReverse = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString(), true);
                    sql = string.Format(@"SELECT  COUNT(1)  FROM (
                                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {0}) AS  ROWINDEX  FROM ({1}) as sqlstr ) t WHERE {2}
                                                                                                    ",
                                        paras.FullOrderByString /*0*/,
                                        paras.Sql /*1*/,
                                        whereCompare                                                     /*2*/
                                        );
                    var innerSampleRowIndex = Taskable <int>(sql, paras.WhereObj).Count();
                    var isLess = Math.Abs(innerSampleRowIndex - paras.Begin) < Math.Abs(paras.RowIndex - paras.Begin);
                    if (isLess)
                    {
                        sampleRowIndex = innerSampleRowIndex;
                    }
                }
                paras.RowIndex  = sampleRowIndex;
                paras.isGreater = sampleRowIndex > paras.Begin;
                if (sampleRowIndex == paras.Begin)
                {
                    return(paras);                              //如果相等返回BeginRow
                }
                if (Math.Abs((sampleRowIndex - paras.Begin) * paras.ConfigCount) < PageMaxHandleNumber)
                {
                    return(paras);
                }
                return(GetListByPage_GetPageBeginRowMultipleOrderBy(paras));

                #endregion
            }
            else//向后取样
            {
                #region 向后取样
                thisIndex = (paras.Begin - paras.RowIndex) / paras.ConfigCount;
                if (thisIndex == 0)
                {
                    return(paras);
                }

                sql = string.Format(@"SELECT {0},RowIndex,{1}   FROM (
                                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {2}) AS  ROWINDEX  FROM ({3}) as sqlstr WHERE {4}  ) t WHERE t.ROWINDEX={5}
             
                                                                                       ",
                                    paras.UnqueField /*0*/,
                                    paras.orderByFieldsString /*1*/,
                                    paras.FullOrderByString /*2*/,
                                    paras.Sql /*3*/,
                                    whereCompareReverse /*4*/,
                                    thisIndex /*5*/);
                var rowList = Taskable <DataTable>(sql, paras.WhereObj).MergeTable().OrderByDataRow(paras.OrderByTypes).ToList();
                var row     = rowList[paras.ConfigCount - paras.SampleEachIndex];
                paras.Row           = row;
                paras.UnqueValue    = row[0];
                whereCompare        = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString());
                whereCompareReverse = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString(), true);
                sql = string.Format(@"SELECT  COUNT(1)  FROM (
                                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {0}) AS  ROWINDEX  FROM ({1}) as sqlstr ) t WHERE  {2}
                                                                                                    ",
                                    paras.FullOrderByString /*0*/,
                                    paras.Sql /*1*/,
                                    whereCompare                                                     /*2*/
                                    );
                var maxRowIndex = Taskable <int>(sql, paras.WhereObj).Count();
                paras.RowIndex  = maxRowIndex;
                paras.isGreater = maxRowIndex > paras.Begin;
                Dispose(false);
                if (maxRowIndex == paras.Begin)
                {
                    return(paras);                           //如果相等返回BeginRow
                }
                if (Math.Abs(((maxRowIndex - paras.Begin) * paras.ConfigCount)) < PageMaxHandleNumber)
                {
                    return(paras);
                }
                return(GetListByPage_GetPageBeginRowMultipleOrderBy(paras));

                #endregion
            }
        }
示例#2
0
        private List <T> GetListByPage_GetPageListMultipleOrderBy <T>(PageRowInnerParamsResultMultipleOrderBy paras) where T : class
        {
            string whereCompareEqual        = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString(), false, true);
            string whereCompareReverseEqual = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString(), true, true);
            string sql = null;

            if (paras.RowIndex == paras.Begin)
            { //如果相等
                string whereCompare        = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString());
                string whereCompareReverse = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString(), true);
                sql = string.Format(@"SELECT  top {0}*  FROM (
                                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS  ROWINDEX  FROM ({2}) as sqlstr ) t WHERE  ({3}) 
                                                                                                    ",
                                    paras.PageSize * paras.ConfigCount + paras.PageSize * paras.PageSize /*0*/,
                                    paras.FullOrderByString /*1*/,
                                    paras.Sql /*2*/,
                                    whereCompareReverse /*3*/,
                                    paras.UnqueValue                                         /*4*/
                                    );
                return(Taskable <T>(sql, paras.WhereObj).MergeEntities().OrderBy(paras.OrderByTypes).Skip(0).Take(paras.PageSize).ToList());
            }
            else if (paras.isGreater)
            { //大于
                string whereCompareReverse = GetWhereCompare(string.Empty, paras.OrderByTypes, paras.Row, paras.UnqueField, paras.UnqueValue.ToString(), true);
                string AnotherPartSql      = null;
                var    createrValue        = (paras.RowIndex) - paras.Begin;
                sql = string.Format(@"SELECT TOP {0}  {1},{2} FROM ({3}) as  t WHERE {4}  ORDER BY {5}
                                                                       
                             ",
                                    createrValue * paras.ConfigCount /*0*/,
                                    paras.UnqueField /*1*/,
                                    paras.orderByFieldsString /*2*/,
                                    paras.Sql /*3*/,
                                    whereCompareEqual /*4*/,
                                    paras.FullOrderByStringReverse                         /*5*/
                                    );
                if (createrValue < paras.PageSize)
                {
                    AnotherPartSql = string.Format(@"SELECT TOP {0}  {1},{2} FROM ({3}) as  t WHERE {4}   ORDER BY {5}
                                                                       
                             ",
                                                   (paras.PageSize - createrValue) * paras.ConfigCount /*0*/,
                                                   paras.UnqueField /*1*/,
                                                   paras.orderByFieldsString /*2*/,
                                                   paras.Sql /*3*/,
                                                   whereCompareReverse /*4*/,
                                                   paras.FullOrderByString /*5*/,
                                                   paras.UnqueValue
                                                   );
                }
                var rows = Taskable <DataTable>(sql, paras.WhereObj).MergeTable().ToList();
                if (createrValue < paras.PageSize)
                {
                    var rows2 = Taskable <DataTable>(AnotherPartSql, paras.WhereObj).MergeTable().ToList();
                    rows.AddRange(rows2);
                }
                rows = rows.OrderByDataRow(paras.OrderByTypes, new OrderByDictionary()
                {
                    OrderByField = paras.UnqueField, OrderByType = OrderByType.asc
                });
                var maxRowIndex = rows.IndexOf(rows.Single(it => it[0].ToString().ToLower() == paras.UnqueValue.ToString().ToLower()));
                var revalRows   = rows.Skip(maxRowIndex - createrValue).Take(paras.PageSize).Select(it => it[0]).ToArray();
                sql = string.Format("SELECT * FROM ({0}) as  t WHERE {1} IN ({2})", paras.Sql, paras.UnqueField, revalRows.ToJoinSqlInVal());
                return(Taskable <T>(sql, paras.WhereObj).MergeEntities().OrderBy(paras.OrderByTypes).ThenBy(paras.UnqueField, OrderByType.asc).Take(paras.PageSize).ToList());
            }
            else
            { //小于
                var createrValue = paras.Begin - paras.RowIndex;
                sql = string.Format(@"SELECT TOP {0}  {1},{2} FROM ({3}) as  t WHERE  {4} ORDER BY {5}
                                                                                                    ",
                                    createrValue * paras.ConfigCount + paras.PageSize /*0*/,
                                    paras.UnqueField /*1*/,
                                    paras.orderByFieldsString /*2*/,
                                    paras.Sql /*3*/,
                                    whereCompareReverseEqual /*4*/,
                                    paras.FullOrderByString                         /*5*/
                                    );
                var rows = Taskable <DataTable>(sql, paras.WhereObj).MergeTable().OrderByDataRow(paras.OrderByTypes, new OrderByDictionary()
                {
                    OrderByField = paras.UnqueField, OrderByType = OrderByType.asc
                });
                var maxRowIndex = rows.IndexOf(rows.Single(it => it[0].ToString().ToLower() == paras.UnqueValue.ToString().ToLower()));
                var revalRows   = rows.Skip(maxRowIndex + createrValue).Take(paras.PageSize).Select(it => it[0]).ToArray();
                sql = string.Format("SELECT * FROM ({0}) as  t WHERE {1} IN ({2})", paras.Sql, paras.UnqueField, revalRows.ToJoinSqlInVal());
                return(Taskable <T>(sql, paras.WhereObj).MergeEntities().OrderBy(paras.OrderByTypes).ThenBy(paras.UnqueField, OrderByType.asc).Take(paras.PageSize).ToList());
            }
        }
示例#3
0
        /// <summary>
        /// 获取分页数据(注意:该函数不可以在事务内使用)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="unqueField">数据库中数据唯一的列(建议:主键GUID)</param>
        /// <param name="sql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="orderByTypes">排序信息集合</param>
        /// <param name="whereObj">参数 例如: new { id="1",name="张三"}</param>
        /// <returns></returns>
        public List <T> TaskableWithPage <T>(string unqueField, string sql, int pageIndex, int pageSize, ref int pageCount, List <OrderByDictionary> orderByTypes, object whereObj = null) where T : class
        {
            if (orderByTypes == null || orderByTypes.Count == 0)
            {
                throw new ArgumentNullException("CloudClient.TaskableWithPage.orderByTypes");
            }
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int    configCount = configList.Count;
            string sqlCount    = string.Format("SELECT COUNT(*) FROM ({0}) t ", sql);

            pageCount = Taskable <int>(sqlCount, whereObj).Count();
            if (pageCount == 0)
            {
                return(new List <T>());
            }
            int totalPage = (pageCount + pageSize - 1) / pageSize;
            var lastPage  = (totalPage - pageIndex) + 1;
            var isLast    = totalPage == pageIndex;

            string fullOrderByString        = string.Join(",", orderByTypes.Select(it => it.OrderByString)) + "," + unqueField + " ASC ";
            string fullOrderByStringReverse = string.Join(",", orderByTypes.Select(it => it.OrderByStringReverse)) + "," + unqueField + " DESC ";
            string orderByFieldsString      = string.Join(",", orderByTypes.Select(it => it.OrderByField));

            string[] orderByFieldArray = orderByTypes.Select(it => it.OrderByField).ToArray();

            string whereCompare = string.Join(" AND ", orderByTypes.Select(it => string.Format(" {0}{1}'$:->{0}<-:$' ", it.OrderByField, it.Symbol, it.Symbol)));

            /***one nodes***/
            #region one nodes
            var isOneNode = configCount == 1;
            if (isOneNode)
            {
                var connName = configList.Single().ConnectionString;
                var db       = new SqlSugarClient(connName);
                SettingConnection(db);
                var sqlPage = string.Format(@"SELECT * FROM (
                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS  ROWINDEX  FROM ({0}) as sqlstr ) t WHERE t.rowIndex BETWEEN {2} AND {3}
                                                         ", sql, fullOrderByString, (pageIndex - 1) * pageSize + 1, pageSize * pageIndex);
                var list    = db.SqlQuery <T>(sql, whereObj);
                return(list.OrderBy(orderByTypes).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
            }
            #endregion

            /***small data***/
            #region small data
            var isSmallData = pageCount <= this.PageMaxHandleNumber || int.MaxValue == pageSize;//page size等于int.MaxValue不需要分页
            if (isSmallData)
            {
                var tasks = Taskable <T>(sql + " ORDER BY " + fullOrderByString, whereObj);
                return(tasks.Tasks.SelectMany(it => it.Result.Entities).OrderBy(orderByTypes).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
            }
            #endregion

            /***small index***/
            #region small index
            var isSmallPageIndex = CloudPubMethod.GetIsSmallPageIndex(pageIndex, pageSize, configCount, this.PageMaxHandleNumber);
            if (isSmallPageIndex)
            {
                var sqlPage = string.Format(@"SELECT * FROM (
                                                                                        SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS  ROWINDEX  FROM ({0}) as sqlstr ) t WHERE t.rowIndex BETWEEN {2} AND {3}
                                                                                        ", sql, fullOrderByString, 1, pageSize * configCount);
                var tasks   = Taskable <T>(sqlPage, whereObj);
                return(tasks.Tasks.SelectMany(it => it.Result.Entities).OrderBy(orderByTypes).ThenBy(unqueField, OrderByType.asc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
            }
            #endregion

            /***small index  by reverse***/
            #region small index  by reverse
            var isSmallPageIndexByReverse = CloudPubMethod.GetIsSmallPageIndexByReverse(totalPage, pageIndex, pageSize, configCount, this.PageMaxHandleNumber);
            if (isSmallPageIndexByReverse)
            {
                var sqlPage      = string.Format(@"SELECT * FROM (
                                                                                        SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS  ROWINDEX  FROM ({0}) as sqlstr ) t WHERE t.rowIndex BETWEEN {2} AND {3}
                                                                                        ", sql, fullOrderByStringReverse, 1, lastPage * configCount * pageSize);
                var tasks        = Taskable <T>(sqlPage, whereObj);
                var lastPageSize = pageCount % pageSize;
                if (lastPageSize == 0)
                {
                    lastPageSize = pageSize;
                }

                var list = tasks.Tasks.SelectMany(it => it.Result.Entities).OrderByReverse(orderByTypes).ThenBy(unqueField, OrderByType.desc);
                if (isLast)
                {
                    return(list.Skip(0).Take(lastPageSize).OrderBy(orderByTypes).ThenBy(unqueField, OrderByType.asc).ToList());
                }
                else
                {
                    var skipIndex = (lastPage - 1) * pageSize + lastPageSize - pageSize;
                    return(list.Skip(skipIndex).Take(pageSize).OrderBy(orderByTypes).ThenBy(unqueField, OrderByType.asc).ToList());
                }
            }
            #endregion

            /***other***/
            #region other
            //单节点最大索引
            var maxDataIndex = pageIndex * pageSize * configCount;
            //分页最大索引
            var pageEnd   = pageIndex * pageSize;
            var pageBegin = pageIndex * pageSize - pageSize;
            //节点间距
            var dataSampleIndex = pageBegin / configCount;

            string sqlOtherPage = string.Format(@"SELECT {4},RowIndex,{3}   FROM (
                                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS  ROWINDEX  FROM ({0}) as sqlstr ) t WHERE t.rowIndex = ({2})
                                                                                                    ", sql /*0*/,
                                                fullOrderByString /*1*/,
                                                dataSampleIndex /*2*/,
                                                orderByFieldsString /*3*/,
                                                unqueField /*4*/);
            DataRow sampleRow           = null;
            int     sampleRowIndex      = 0;
            int     sampleEachIndex     = 0;
            int     nodeSpacing         = 6;
            var     innerDataSampleList = Taskable <DataTable>(sqlOtherPage, whereObj).MergeTable().OrderByDataRow(orderByTypes).ThenByDataRow(unqueField, OrderByType.asc).ToList();
            for (int i = 0; i < configCount; i++)
            {
                if (configCount < nodeSpacing)
                {
                    if (i != configCount / 2)
                    {
                        continue;
                    }
                }
                else if (i % nodeSpacing != 0)
                {
                    continue;
                }

                sampleRow = innerDataSampleList[i];

                whereCompare = GetWhereCompare(whereCompare, orderByTypes, sampleRow, unqueField, sampleRow[0].ToString());
                string whereCompareReverse = GetWhereCompare(null, orderByTypes, sampleRow, unqueField, sampleRow[0].ToString(), true);

                sqlOtherPage = string.Format(@"SELECT  COUNT(1)  FROM (
                                                                                                    SELECT *,ROW_NUMBER()OVER(ORDER BY {5}) AS  ROWINDEX  FROM ({2}) as sqlstr ) t WHERE {0}({3}) 
                                                                                                    ",
                                             null /*0*/,
                                             unqueField /*1*/,
                                             sql /*2*/,
                                             whereCompare /*3*/,
                                             sampleRow[0] /*4:UnqueValue*/,
                                             fullOrderByString /*5*/);
                var innerSampleRowIndex = Taskable <int>(sqlOtherPage, whereObj).Count();
                var isSet = sampleRowIndex == 0 || (Math.Abs(pageBegin - innerSampleRowIndex) < (Math.Abs(pageBegin - sampleRowIndex)));
                if (isSet)
                {
                    sampleRowIndex  = innerSampleRowIndex;
                    sampleEachIndex = i;
                }
            }
            sampleRow = innerDataSampleList[sampleEachIndex];
            //获取分页索引所需参数实体
            PageRowInnerParamsResultMultipleOrderBy beginEndRowParams = new PageRowInnerParamsResultMultipleOrderBy()
            {
                RowIndex                 = sampleRowIndex,
                Row                      = sampleRow,
                Begin                    = pageBegin,
                End                      = pageEnd,
                PageIndex                = pageIndex,
                PageSize                 = pageSize,
                Sql                      = sql,
                UnqueField               = unqueField,
                isGreater                = sampleRowIndex > pageBegin,
                UnqueValue               = sampleRow[0],
                FullOrderByString        = fullOrderByString,
                FullOrderByStringReverse = fullOrderByStringReverse,
                ConfigCount              = configCount,
                orderByFieldsString      = orderByFieldsString,
                OrderByTypes             = orderByTypes,
                WhereObj                 = whereObj,
                Count                    = pageCount,
                SampleEachIndex          = sampleEachIndex
            };

            PageRowInnerParamsResultMultipleOrderBy beginEndRow = null;
            var isBeginRow = (Math.Abs(sampleRowIndex - pageBegin) * configCount < PageMaxHandleNumber || Math.Abs(pageBegin - sampleRowIndex) * configCount < PageMaxHandleNumber);
            beginEndRow = isBeginRow?beginEndRowParams: GetListByPage_GetPageBeginRowMultipleOrderBy(beginEndRowParams);
            Dispose(false);
            var reval = GetListByPage_GetPageListMultipleOrderBy <T>(beginEndRow);
            Dispose(false);
            return(reval);

            #endregion
        }