/// <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); }