// 获取用户推荐的商品列表 public static List <Models.ItemModel> GetRecommend(string username) { SqlParameter[] sqlParameters = new SqlParameter[] { new SqlParameter("@Username", username) }; string sql = "select * from item where item.item_id in (select item_id from recommend where username = @Username)"; SqlDataReader data = util.SqlHelper.ExecuteTable(sql, sqlParameters); List <Models.ItemModel> result = new List <Models.ItemModel>(); if (data != null) { while (data.Read()) { Models.ItemModel item = new Models.ItemModel { ItemId = data.GetInt32(0), ItemCategoryId = data.GetInt32(1), ItemName = data.GetString(2), ItemPicture = data.GetString(3), ItemText = data.GetString(4), ItemPrice = data.GetDouble(5), ItemSales = data.GetInt32(6), ItemNumber = data.GetInt32(7), ItemKeyword = data.GetString(8), LastUpdateTime = data.GetDateTime(9) }; result.Add(item); } data.Close(); } ; return(result); }
// 返回某个类型最畅销(热门)的N个物品 public static List <Models.ItemModel> GetTopSaleItems(int itemCategoryId, int N) { string sql = $"select top {N} * from item where item_category_id={itemCategoryId} order by item_sales desc"; SqlDataReader data = SqlHelper.ExecuteTable(sql, null); List <Models.ItemModel> items = new List <Models.ItemModel>(); if (data == null) { return(items); } while (data.Read()) { Models.ItemModel item = new Models.ItemModel { ItemId = data.GetInt32(0), ItemCategoryId = data.GetInt32(1), ItemName = data.GetString(2), ItemPicture = data.GetString(3), ItemText = data.GetString(4), ItemPrice = data.GetDouble(5), ItemSales = data.GetInt32(6), ItemNumber = data.GetInt32(7), ItemKeyword = data.GetString(8), LastUpdateTime = data.GetDateTime(9) }; items.Add(item); } data.Close(); return(items); }
private static List <Models.ItemCategoryModel> ItemCategoriesCache = null; // 因为商品类型运行期间一般不会变化, 并且不多(<1000), 所以直接缓存起来 // 查询商品列表 public static List <Models.ItemModel> GetItems(int category, int nowCount, int size, out int totalCount) { SqlParameter[] sqlParameter1 = new SqlParameter[] { new SqlParameter("@C", category), }; string sql1 = "select count(*) from item where item_category_id = @C"; totalCount = (int?)SqlHelper.ExecuteScalar(sql1, sqlParameter1) ?? -1; SqlParameter[] sqlParameters2 = new SqlParameter[] { new SqlParameter("@C", category), new SqlParameter("@N", nowCount), new SqlParameter("@S", size), }; string sql = "select * from item where item_category_id = @C order by item_id offset @N rows fetch next @S rows only"; SqlDataReader sqlData = SqlHelper.ExecuteTable(sql, sqlParameters2); List <Models.ItemModel> items = new List <Models.ItemModel>(); if (sqlData == null) { return(items); } while (sqlData.Read()) { Models.ItemModel item = new Models.ItemModel { ItemId = sqlData.GetInt32(0), ItemCategoryId = sqlData.GetInt32(1), ItemName = sqlData.GetString(2), ItemPicture = sqlData.GetString(3), ItemText = sqlData.GetString(4), ItemPrice = sqlData.GetDouble(5), ItemSales = sqlData.GetInt32(6), ItemNumber = sqlData.GetInt32(7), ItemKeyword = sqlData.GetString(8), LastUpdateTime = sqlData.GetDateTime(9) }; items.Add(item); } sqlData.Close(); return(items); }
// 查询具体商品信息 public static TMall.Models.ItemModel GetItem(int item_id) { string sql = $"select * from item where item_id = {item_id}"; SqlDataReader sqlData = SqlHelper.ExecuteTable(sql, null); if (sqlData != null && sqlData.Read()) { Models.ItemModel item = new Models.ItemModel { ItemId = sqlData.GetInt32(0), ItemCategoryId = sqlData.GetInt32(1), ItemName = sqlData.GetString(2), ItemPicture = sqlData.GetString(3), ItemText = sqlData.GetString(4), ItemPrice = sqlData.GetDouble(5), ItemSales = sqlData.GetInt32(6), ItemNumber = sqlData.GetInt32(7), ItemKeyword = sqlData.GetString(8), LastUpdateTime = sqlData.GetDateTime(9) }; sqlData.Close(); return(item); } return(null); }