// 获取用户推荐的商品列表
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);
}
