/// <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 = GetSqlSampleRowSql(unqueField, sql, fullOrderByString, orderByFieldsString, dataSampleIndex);
DataRow sampleRow = null;
int sampleRowIndex;
int sampleEachIndex;
List <DataRow> innerDataSampleList;
InitSampleRow(unqueField, sql, orderByTypes, whereObj, configCount, fullOrderByString, ref whereCompare, pageBegin, ref sqlOtherPage, ref sampleRow, out sampleRowIndex, out sampleEachIndex, out innerDataSampleList);
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
}
/// <summary>
/// 判段是否包含主键
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static bool IsPrimaryKey(SqlSugarClient db, string tableName)
{
return(GetPrimaryKeyByTableName(db, tableName) != null);
}
//public static string ToOracleTableName(this string value)
//{
// return value;
//}
//public static object ToOracleParValue(this object value)
//{
// if (value == null)
// {
// return DBNull.Value;
// }
// var type = value.GetType();
// if (type == SqlSugarTool.BoolType)
// {
// return Convert.ToBoolean(value) ? 1 : 0;
// }
// else if (type == SqlSugarTool.GuidType)
// {
// return value.ToString();
// }
// return value;
//}
internal static void SetParType(string typeName, bool isBinary, string name, OracleParameter par, SqlSugarClient db)
{
var colInfos = db.ClassGenerating.GetTableColumns(db, typeName.ToUpper()).Where(it => it.COLUMN_NAME.ObjToString().ToLower() == name.GetOracleParameterNameNoParSymbol().ToLower()).ToList();
if (colInfos.IsValuable())
{
var colInfo = colInfos.Single();
if (colInfo.DATA_TYPE.ObjToString() == "BFILE")
{
par.OracleDbType = OracleDbType.BFile;
}
else if (colInfo.DATA_TYPE.ObjToString() == "BLOB")
{
par.OracleDbType = OracleDbType.Blob;
}
else if (colInfo.DATA_TYPE.ObjToString() == "RAW")
{
par.OracleDbType = OracleDbType.Raw;
}
else if (colInfo.DATA_TYPE.ObjToString() == "LONGRAW")
{
par.OracleDbType = OracleDbType.LongRaw;
}
}
}
internal static void SetParType(string typeName, PropertyInfo prop, OracleParameter par, SqlSugarClient db)
{
var isBinary = prop.PropertyType == SqlSugarTool.ByteArrayType;
if (isBinary)
{
var colInfos = db.ClassGenerating.GetTableColumns(db, typeName.ToUpper()).Where(it => it.COLUMN_NAME.ObjToString().ToLower() == prop.Name.ToLower()).ToList();
if (colInfos.IsValuable())
{
var colInfo = colInfos.Single();
if (colInfo.DATA_TYPE.ObjToString() == "BFILE")
{
par.OracleDbType = OracleDbType.BFile;
}
else if (colInfo.DATA_TYPE.ObjToString() == "BLOB")
{
par.OracleDbType = OracleDbType.Blob;
}
else if (colInfo.DATA_TYPE.ObjToString() == "RAW")
{
par.OracleDbType = OracleDbType.Raw;
}
else if (colInfo.DATA_TYPE.ObjToString() == "LONGRAW")
{
par.OracleDbType = OracleDbType.LongRaw;
}
}
}
}
/// <summary>
/// 创建多语言视图，带有LanguageId=1的所有有视图1替换成languageValue 并且新创视图 名称为 原有视图名+_$_+suffix
/// </summary>
/// <returns></returns>
public static void UpdateView(PubModel.Language lan, SqlSugarClient db)
{
if (lan == null)
{
return;
}
if (lan.Suffix.IsNullOrEmpty())
{
Check.Exception(true, "LanguageHelper.lan.Suffix is Null Or Empty");
}
if (PreSuffix.IsNullOrEmpty())
{
Check.Exception(true, "LanguageHelper.PreSuffix is Null Or Empty");
}
if (!lan.Suffix.StartsWith(PreSuffix))
{
lan.Suffix = PreSuffix + lan.Suffix;
}
string sql = @"
--验证参数传递规则
if LEFT(ltrim(@Suffix),3)<>'" + PreSuffix + @"'
begin
raiserror('参数传递格式不规范',16,1)
return;
end
else
if(ISNULL(" + lan.LanguageValue + @",'')='')
begin
raiserror('参数传递格式不规范',16,1)
return;
end
declare
@name varchar(100), --视图名称
@definition varchar(max) --视图脚本
--删除数据库里面所有带传递参数几号的视图
declare my_cursor cursor for
select a.name,b.[definition] from sys.objects a
JOIN sys.sql_modules b on a.[object_id]=b.[object_id]
where [type]='v'
and b.[definition] like '%" + lan.ReplaceViewStringKey + @"%'
and a.name not like '%" + PreSuffix + @"%'
--打开处理器
open my_cursor
fetch next from my_cursor into @name,@definition
while @@FETCH_STATUS=0
begin
--脚本查询语言ID更改,并且更改新脚本语言的对象名称
set @definition=REPLACE(
REPLACE(
@definition,
'" + lan.ReplaceViewStringKey + @"',
'" + string.Format(lan.ReplaceViewStringValue, lan.LanguageValue) + @"'
),
@name,
@name+@Suffix
)
--判断新脚本语言的对象名称是否存在,存在删除
exec(
'
if object_id('''+@name+@Suffix+''',''v'') is not null
begin
drop view '+@name+@Suffix+'
end
'
)
exec(@definition)
fetch next from my_cursor into @name,@definition
end
close my_cursor
deallocate my_cursor
";
db.ExecuteCommand(sql, new { Suffix = lan.Suffix });
}