/// <summary>
/// 获取分页
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public PagedListData <List <ShipInfo> > GetPage(PageSearch query)
{
var model = new PageListModel()
{
WithColumn = @"Id
,UserId
,Code
,Name
,Plat
,Description
,AppKey
,AppSecret
,DeliveryAddress
,InDate
,Contract
,Status
,Remark",
WithWhere = " Status=1 ",
LeftColumn = " B.UserName",
LeftJoin = @" LEFT JOIN SysUser B ON A.UserId=B.Id ",
};
return(this.PagedListQuery(model, query));
}
/// <summary>
/// 分页查询，传入参数
/// </summary>
/// <param name="obj">分页条件</param>
/// <param name="info">查询条件</param>
/// <returns></returns>
public PagedListData <List <T> > PagedListQuery(PageListModel obj, PageSearch info)
{
if (IsConnection())
{
var sql = $@"DECLARE @Sql NVARCHAR(MAX);
DECLARE @Order NVARCHAR(200);
IF ( @OrderBy IS NOT NULL AND @OrderBy<>'')
BEGIN
SET @Order = 'ORDER BY ' + @OrderBy;
END;
ELSE
BEGIN
SET @Order = ' ORDER BY InDate DESC ';
END;
IF ( @Where IS NULL)
BEGIN
SET @Where = '';
END;
SET @Sql = N'
WITH Select_List AS
(
SELECT ROW_NUMBER() OVER ( ' + @Order + ' ) RowNumber,
{obj.WithColumn}
FROM {GetTableName()}
WHERE {obj.WithWhere} ' + @Where + '
)
SELECT
A.* " ;
sql += !string.IsNullOrWhiteSpace(obj.LeftColumn) ? $@",{obj.LeftColumn}" : "";
sql += $@" FROM Select_List A
{obj.LeftJoin}
WHERE (RowNumber BETWEEN ( @PageSize * ( @CurrentPageIndex - 1 ) + 1 ) AND @PageSize * @CurrentPageIndex )
SELECT @Count = COUNT(1) FROM {GetTableName()} WHERE {obj.WithWhere} ' + @Where;
EXECUTE sp_executesql @Sql,N'
@CurrentPageIndex INT,
@PageSize INT,
@Where NVARCHAR(1000),
@OrderBy NVARCHAR(200),
@Count int OUT',
@CurrentPageIndex,
@PageSize,
@Where,
@OrderBy,
@Count OUTPUT; ";
var model = new PagedListData <List <T> >();
var dp = new DynamicParameters();
dp.Add("@CurrentPageIndex", info.PageIndex);
dp.Add("@PageSize", info.PageSize);
dp.Add("@Where", info.Where);
dp.Add("@OrderBy", info.OrderBy);
dp.Add("@Count", 0, DbType.Int32, ParameterDirection.Output);
var data = this.Query(sql, dp);
var pageInfo = new PagingInfo(info.PageSize, info.PageIndex);
if (data != null)
{
model.PagingData = data.ToList();
pageInfo.TotalCount = dp.Get <int>("@Count");
model.PagingInfo = pageInfo;
}
else
{
model.PagingData = new List <T>();
pageInfo.TotalCount = 0;
model.PagingInfo = pageInfo;
}
return(model);
}
else
{
return(null);
}
}