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