/// <summary>
/// 分页数据列表
/// </summary>
public List <XCLCMS.Data.Model.View.v_Article> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLCMS.Data.Model.Custom.ArticleSearchCondition condition)
{
string join_ArticleType = string.Empty;
var where = new List <string>();
string strSql = @"
DECLARE @_pageIndex INT=@PageIndex
DECLARE @_pageSize INT=@PageSize
DECLARE @Start INT=0
DECLARE @End INT=0
--获取总记录数
SELECT @TotalCount=COUNT(1) FROM v_Article AS tb_Article WITH(NOLOCK)
#join_ArticleType#
#where#
IF(@_pageIndex<=0) SET @_pageIndex=1
IF(@_pageSize<=0) SET @_pageSize=10
SET @Start=(@_pageIndex-1)*@_pageSize+1
SET @End=@Start+@_pageSize-1
IF(@Start>@TotalCount) SET @Start=@TotalCount
IF(@End>@TotalCount) SET @End=@TotalCount
--分页
;WITH Data AS
(
SELECT
tb_Article.*,
ROW_NUMBER() OVER (ORDER BY tb_Article.ArticleID DESC) AS RowNumber
FROM dbo.v_Article AS tb_Article WITH(NOLOCK)
#join_ArticleType#
#where#
)
SELECT
*
FROM Data
WHERE RowNumber >= @Start AND RowNumber <= @End
";
Database db = base.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, pageInfo.PageIndex);
db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pageInfo.PageSize);
db.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 4);
if (null != condition)
{
if (null != condition.ArticleTypeIDList && condition.ArticleTypeIDList.Count > 0)
{
condition.ArticleTypeIDList = condition.ArticleTypeIDList.Where(k => k > 0).Distinct().ToList();
}
if (null != condition.ArticleTypeIDList && condition.ArticleTypeIDList.Count > 0)
{
join_ArticleType = @"
inner join ArticleType as tb_ArticleType on tb_Article.ArticleID=tb_ArticleType.FK_ArticleID
inner join @TVP_ArticleTypeID as tvp_articleTypeID on tb_ArticleType.FK_TypeID=tvp_articleTypeID.ID
";
dbCommand.Parameters.Add(new SqlParameter("TVP_ArticleTypeID", SqlDbType.Structured)
{
TypeName = "TVP_IDTable",
Direction = ParameterDirection.Input,
Value = XCLNetTools.DataSource.DataTableHelper.ToSingleColumnDataTable <long, long>(condition.ArticleTypeIDList)
});
}
if (!string.IsNullOrEmpty(condition.RecordState))
{
where.Add("tb_Article.RecordState=@RecordState");
db.AddInParameter(dbCommand, "RecordState", DbType.AnsiString, condition.RecordState);
}
if (!string.IsNullOrEmpty(condition.VerifyState))
{
where.Add("tb_Article.VerifyState=@VerifyState");
db.AddInParameter(dbCommand, "VerifyState", DbType.AnsiString, condition.VerifyState);
}
if (!string.IsNullOrEmpty(condition.ArticleState))
{
where.Add("tb_Article.ArticleState=@ArticleState");
db.AddInParameter(dbCommand, "ArticleState", DbType.AnsiString, condition.ArticleState);
}
if (condition.MerchantID > 0)
{
where.Add("tb_Article.FK_MerchantID=@FK_MerchantID");
db.AddInParameter(dbCommand, "FK_MerchantID", DbType.Int64, condition.MerchantID);
}
}
if (where.Count > 0)
{
where[0] = " where " + where[0];
}
dbCommand.CommandText = strSql.Replace("#where#", string.Join(" and ", where.ToArray())).Replace("#join_ArticleType#", join_ArticleType);
var ds = db.ExecuteDataSet(dbCommand);
pageInfo.RecordCount = XCLNetTools.Common.DataTypeConvert.ToInt(dbCommand.Parameters["@TotalCount"].Value);
return(XCLNetTools.Generic.ListHelper.DataSetToList <XCLCMS.Data.Model.View.v_Article>(ds) as List <XCLCMS.Data.Model.View.v_Article>);
}
/// <summary>
/// 分页数据列表
/// </summary>
public List <XCLCMS.Data.Model.View.v_Article> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLCMS.Data.Model.Custom.ArticleSearchCondition condition)
{
return(dal.GetPageList(pageInfo, condition));
}