/// <summary>
/// delete relationship between product and category
/// </summary>
/// <param name="productId"></param>
/// <param name="catId"></param>
/// <returns></returns>
public static int DeleteProductLink(int productId, int catId)
{
SQLDataAccess.ExecuteNonQuery("[Catalog].[sp_RemoveProductFromCategory]", CommandType.StoredProcedure, new SqlParameter("@ProductID", productId), new SqlParameter("@CategoryID", catId));
CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(catId));
return(0);
}
/// <summary>
/// get category by categoryId from cache or db if cache null
/// </summary>
/// <param name="categoryId"></param>
/// <returns></returns>
public static Category GetCategory(int categoryId)
{
Category res;
string strCacheName = CacheNames.GetCategoryCacheObjectName(categoryId);
if (CacheManager.Contains(strCacheName))
{
res = CacheManager.Get <Category>(strCacheName);
if (res != null)
{
return(res);
}
}
// Return from db
res = GetCategoryFromDbByCategoryId(categoryId);
// Insert to cahce
if (res != null)
{
CacheManager.Insert(strCacheName, res);
}
else
{
CacheManager.Remove(strCacheName);
}
return(res);
}
/// <summary>
/// update category
/// </summary>
/// <param name="category"></param>
/// <param name="updateCache">refresh cache</param>
/// <returns></returns>
public static bool UpdateCategory(Category category, bool updateCache)
{
SQLDataAccess.ExecuteNonQuery("[Catalog].[sp_UpdateCategory]", CommandType.StoredProcedure,
new SqlParameter("@CategoryID", category.CategoryId),
new SqlParameter("@Name", category.Name),
new SqlParameter("@ParentCategory", category.ParentCategoryId),
new SqlParameter("@Description", category.Description),
new SqlParameter("@BriefDescription", category.BriefDescription),
new SqlParameter("@Enabled", category.Enabled),
new SqlParameter("@DisplayStyle", category.DisplayStyle),
new SqlParameter("@displayChildProducts", category.DisplayChildProducts),
new SqlParameter("@DisplayBrandsInMenu", category.DisplayBrandsInMenu),
new SqlParameter("@DisplaySubCategoriesInMenu", category.DisplaySubCategoriesInMenu),
new SqlParameter("@SortOrder", category.SortOrder),
//new SqlParameter("@Picture", !string.IsNullOrEmpty(category.Picture) ? category.Picture : ((object)DBNull.Value)),
//new SqlParameter("@MiniPicture", !string.IsNullOrEmpty(category.MiniPicture) ? category.MiniPicture : ((object)DBNull.Value)),
new SqlParameter("@UrlPath", category.UrlPath),
new SqlParameter("@Sorting", category.Sorting)
);
if (category.Meta != null)
{
if (category.Meta.Title.IsNullOrEmpty() && category.Meta.MetaKeywords.IsNullOrEmpty() && category.Meta.MetaDescription.IsNullOrEmpty() && category.Meta.H1.IsNullOrEmpty())
{
if (MetaInfoService.IsMetaExist(category.CategoryId, MetaType.Category))
{
MetaInfoService.DeleteMetaInfo(category.CategoryId, MetaType.Category);
}
}
else
{
MetaInfoService.SetMeta(category.Meta);
}
}
SetCategoryHierarchicallyEnabled(category.CategoryId);
// Work with cache
if (updateCache)
{
CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(category.CategoryId));
CacheManager.RemoveByPattern("MenuCatalog");
if (category.ParentCategoryId == 0)
{
var cacheName = CacheNames.GetBottomMenuCacheObjectName();
if (CacheManager.Contains(cacheName))
{
CacheManager.Remove(cacheName);
}
}
}
return(true);
}
public static int DeleteAllProductLink(int productId)
{
var res = SQLDataAccess.ExecuteReadList <int>("Select [CategoryID] FROM [Catalog].[ProductCategories] WHERE [ProductID] = @ProductId",
CommandType.Text,
reader => SQLDataHelper.GetInt(reader, "CategoryID"),
new SqlParameter("@ProductID", productId));
foreach (var item in res)
{
CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(item));
}
SQLDataAccess.ExecuteNonQuery("DELETE FROM [Catalog].[ProductCategories] WHERE [ProductID] = @ProductId", CommandType.Text, new SqlParameter("@ProductID", productId));
return(0);
}
/// <summary>
/// add category
/// </summary>
/// <param name="cat"></param>
/// <param name="updateCache"></param>
/// <param name="db"></param>
/// <returns></returns>
public static int AddCategory(Category cat, bool updateCache, SQLDataAccess db)
{
db.cmd.CommandText = "[Catalog].[sp_AddCategory]";
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.Parameters.Clear();
db.cmd.Parameters.AddWithValue("@Description", cat.Description ?? (object)DBNull.Value);
db.cmd.Parameters.AddWithValue("@BriefDescription", cat.BriefDescription ?? (object)DBNull.Value);
db.cmd.Parameters.AddWithValue("@Name", cat.Name);
db.cmd.Parameters.AddWithValue("@ParentCategory", cat.ParentCategoryId);
db.cmd.Parameters.AddWithValue("@SortOrder", cat.SortOrder);
db.cmd.Parameters.AddWithValue("@Enabled", cat.Enabled);
db.cmd.Parameters.AddWithValue("@DisplayStyle", cat.DisplayStyle ?? String.Empty);
db.cmd.Parameters.AddWithValue("@DisplayChildProducts", cat.DisplayChildProducts);
db.cmd.Parameters.AddWithValue("@DisplayBrandsInMenu", cat.DisplayBrandsInMenu);
db.cmd.Parameters.AddWithValue("@DisplaySubCategoriesInMenu", cat.DisplaySubCategoriesInMenu);
db.cmd.Parameters.AddWithValue("@UrlPath", cat.UrlPath);
db.cmd.Parameters.AddWithValue("@Sorting", cat.Sorting);
db.cnOpen();
var id = SQLDataHelper.GetInt(db.cmd.ExecuteScalar());
db.cnClose();
if (updateCache)
{
CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(cat.ParentCategoryId));
CacheManager.RemoveByPattern("MenuCatalog");
if (cat.ParentCategoryId == 0)
{
var cacheName = CacheNames.GetBottomMenuCacheObjectName();
if (CacheManager.Contains(cacheName))
{
CacheManager.Remove(cacheName);
}
}
}
return(id);
}
/// <summary>
/// delete category by categoryId
/// </summary>
/// <param name="categoryId"></param>
/// <param name="updateCache">refresh cache</param>
/// <returns>return list of file namme image</returns>
private static IEnumerable <int> DeleteCategory(int categoryId, bool updateCache)
{
if (categoryId == 0)
{
throw new Exception("deleting Root catregory");
}
if (updateCache)
{
CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(categoryId));
CacheManager.RemoveByPattern("MenuCatalog");
var cacheName = CacheNames.GetBottomMenuCacheObjectName();
if (CacheManager.Contains(cacheName))
{
CacheManager.Remove(cacheName);
}
}
return(SQLDataAccess.ExecuteReadIEnumerable("[Catalog].[sp_DeleteCategoryWithSubCategoies]",
CommandType.StoredProcedure,
reader => SQLDataHelper.GetInt(reader, "CategoryID"),
new SqlParameter("@id", categoryId)));
}