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