/// <summary>
/// 分页数据列表
/// </summary>
public List <XCLCMS.Data.Model.Article> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition)
{
condition.TableName = "Article";
DataTable dt = XCLCMS.Data.DAL.Common.Common.GetPageList(pageInfo, condition);
return(XCLNetTools.Generic.ListHelper.DataTableToList <XCLCMS.Data.Model.Article>(dt) as List <XCLCMS.Data.Model.Article>);
}
/// <summary>
/// 分页数据列表
/// </summary>
public List <XCLCMS.Data.Model.View.v_SysWebSetting> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition)
{
condition.TableName = "v_SysWebSetting";
DataTable dt = XCLCMS.Data.DAL.Common.Common.GetPageList(pageInfo, condition);
return(XCLNetTools.Generic.ListHelper.DataTableToList <XCLCMS.Data.Model.View.v_SysWebSetting>(dt) as List <XCLCMS.Data.Model.View.v_SysWebSetting>);
}
/// <summary>
/// 分页
/// </summary>
public static DataTable GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition)
{
condition.PageIndex = pageInfo.PageIndex;
condition.PageSize = pageInfo.PageSize;
var db = new XCLCMS.Data.DAL.Common.BaseDAL().CreateDatabase();
string strSql = XCLNetTools.DataBase.SQLLibrary.CreatePagerQuerySqlString(condition);
var dbCommand = db.GetSqlStringCommand(strSql);
db.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 4);
var ds = db.ExecuteDataSet(dbCommand);
pageInfo.RecordCount = XCLNetTools.Common.DataTypeConvert.ToInt(dbCommand.Parameters["@TotalCount"].Value);
return(null != ds && ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
/// <summary>
/// 分页
/// </summary>
public static List <T> GetPageList <T>(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition) where T : new()
{
condition.PageIndex = pageInfo.PageIndex;
condition.PageSize = pageInfo.PageSize;
var db = new XCLCMS.Data.DAL.Common.BaseDAL().CreateDatabase();
string strSql = XCLNetTools.DataBase.SQLLibrary.CreatePagerQuerySqlString(condition);
var dbCommand = db.GetSqlStringCommand(strSql);
db.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 4);
using (var dr = db.ExecuteReader(dbCommand))
{
var lst = XCLNetTools.DataSource.DataReaderHelper.DataReaderToList <T>(dr) as List <T>;
pageInfo.RecordCount = XCLNetTools.Common.DataTypeConvert.ToInt(dbCommand.Parameters["@TotalCount"].Value);
return(lst);
}
}
/// <summary>
/// 分页
/// </summary>
public static List <T> GetPageList <T>(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition) where T : new()
{
condition.PageIndex = pageInfo.PageIndex;
condition.PageSize = pageInfo.PageSize;
string strSql = XCLNetTools.DataBase.SQLLibrary.CreatePagerQuerySqlString(condition);
using (var db = new XCLCMS.Data.DAL.Common.BaseDAL().CreateSqlConnection())
{
var ps = new DynamicParameters();
ps.Add("@TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (var dr = db.ExecuteReader(strSql, ps))
{
var lst = XCLNetTools.DataSource.DataReaderHelper.DataReaderToList <T>(dr);
pageInfo.RecordCount = ps.Get <int>("@TotalCount");
return(lst);
}
}
}
/// <summary>
/// 分页(可按非主键排序)
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pageInfo">分页参数信息</param>
/// <param name="strWhere"> 查询条件 (注意: 不要加 where)</param>
/// <param name="fieldName">列名，若为空，则取所有列</param>
///<param name="fieldKey">主键名</param>
///<param name="fieldOrder">排序字段，可加DESC/ASC</param>
/// <returns>DataTable</returns>
public static DataTable GetPageList(string tableName, XCLNetTools.Entity.PagerInfo pageInfo, string strWhere, string fieldName, string fieldKey, string fieldOrder)
{
Database db = new XCLCMS.Data.DAL.Common.BaseDAL().CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("sp_Pager");
db.AddOutParameter(dbCommand, "RecordCount", DbType.Int32, 4);
db.AddOutParameter(dbCommand, "PageCount", DbType.Int32, 4);
db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pageInfo.PageSize);
db.AddInParameter(dbCommand, "PageCurrent", DbType.Int32, pageInfo.PageIndex);
db.AddInParameter(dbCommand, "tbname", DbType.String, tableName);
db.AddInParameter(dbCommand, "FieldShow", DbType.String, fieldName);
db.AddInParameter(dbCommand, "Where", DbType.String, strWhere);
db.AddInParameter(dbCommand, "FieldOrder", DbType.String, fieldOrder);
db.AddInParameter(dbCommand, "FieldKey", DbType.String, fieldKey);
DataSet ds = db.ExecuteDataSet(dbCommand);
pageInfo.RecordCount = XCLNetTools.Common.DataTypeConvert.ToInt(dbCommand.Parameters["@RecordCount"].Value);
return(null != ds && ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
/// <summary>
/// 分页数据列表
/// </summary>
public List <XCLCMS.Data.Model.View.v_MerchantApp> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition)
{
return(dal.GetPageList(pageInfo, condition));
}
/// <summary>
/// 分页数据列表
/// </summary>
public List <XCLCMS.Data.Model.UserInfo> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition)
{
condition.TableName = "UserInfo";
return(XCLCMS.Data.DAL.Common.Common.GetPageList <XCLCMS.Data.Model.UserInfo>(pageInfo, condition));
}
/// <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, string strWhere, string fieldName, string fieldKey, string fieldOrder)
{
DataTable dt = XCLCMS.Data.DAL.Common.Common.GetPageList("v_Article", pageInfo, strWhere, fieldName, fieldKey, fieldOrder);
return(XCLNetTools.Generic.ListHelper.DataTableToList <XCLCMS.Data.Model.View.v_Article>(dt) as List <XCLCMS.Data.Model.View.v_Article>);
}
/// <summary>
/// 分页列表
/// </summary>
public List <XCLCMS.Data.Model.Article> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition)
{
return(dal.GetPageList(pageInfo, condition));
}
/// <summary>
/// 分页数据列表
/// </summary>
public List <XCLCMS.Data.Model.View.v_MerchantApp> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, XCLNetTools.Entity.SqlPagerConditionEntity condition)
{
condition.TableName = "v_MerchantApp";
return(XCLCMS.Data.DAL.Common.Common.GetPageList <XCLCMS.Data.Model.View.v_MerchantApp>(pageInfo, condition));
}
/// <summary>
/// 分页列表
/// </summary>
public List <XCLCMS.Data.Model.Article> GetPageList(XCLNetTools.Entity.PagerInfo pageInfo, string strWhere, string fieldName, string fieldKey, string fieldOrder)
{
return(dal.GetPageList(pageInfo, strWhere, fieldName, fieldKey, fieldOrder));
}
/// <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));
}
