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
}
}
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());
}
}
/// <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
}