public IList <VWProductSpecialDetailsEntity> GetSpecialDetailsForMenuAD(int specialid, int num)
{
string where = " where 1=1 and IsActive=1 ";
if (specialid > 0)
{
where += " and SpecialId=@SpecialId ";
}
string sql = @"SELECT top " + num + @" [Id],[SpecialId],[ProductDetailId],[Sort],[IsActive] from dbo.[ProductSpecialDetails] WITH(NOLOCK)
" + where + " ORDER BY Sort desc";
IList <VWProductSpecialDetailsEntity> entityList = new List <VWProductSpecialDetailsEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
if (specialid > 0)
{
db.AddInParameter(cmd, "@SpecialId", DbType.Int32, specialid);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
VWProductSpecialDetailsEntity entity = new VWProductSpecialDetailsEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.SpecialId = StringUtils.GetDbInt(reader["SpecialId"]);
entity.ProductDetailId = StringUtils.GetDbInt(reader["ProductDetailId"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]);
entityList.Add(entity);
}
}
return(entityList);
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <VWProductSpecialDetailsEntity> GetProductSpecialDetailsList(int pageindex, int pagesize, ref int recordCount, int specialid, int isactive)
{
string where = " where 1=1 ";
if (specialid > 0)
{
where += " and SpecialId=@SpecialId ";
}
if (isactive > 0)
{
where += " and IsActive=@IsActive ";
}
string sql = @"SELECT [Id],[SpecialId],[ProductDetailId],[Sort],[IsActive]
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Sort desc) AS ROWNUMBER,
[Id],[SpecialId],[ProductDetailId],[Sort],[IsActive] from dbo.[ProductSpecialDetails] WITH(NOLOCK)
" + where + @" ) as temp
where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ;
string sql2 = @"Select count(1) from dbo.[ProductSpecialDetails] with (nolock) " + where;
IList <VWProductSpecialDetailsEntity> entityList = new List <VWProductSpecialDetailsEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageindex);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pagesize);
if (specialid > 0)
{
db.AddInParameter(cmd, "@SpecialId", DbType.Int32, specialid);
}
if (isactive > 0)
{
db.AddInParameter(cmd, "@IsActive", DbType.Int32, isactive);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
VWProductSpecialDetailsEntity entity = new VWProductSpecialDetailsEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.SpecialId = StringUtils.GetDbInt(reader["SpecialId"]);
entity.ProductDetailId = StringUtils.GetDbInt(reader["ProductDetailId"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]);
entityList.Add(entity);
}
}
cmd = db.GetSqlStringCommand(sql2);
if (specialid > 0)
{
db.AddInParameter(cmd, "@SpecialId", DbType.Int32, specialid);
}
if (isactive > 0)
{
db.AddInParameter(cmd, "@IsActive", DbType.Int32, isactive);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
recordCount = StringUtils.GetDbInt(reader[0]);
}
else
{
recordCount = 0;
}
}
return(entityList);
}
