/// <summary>
/// Saves this category to database
/// </summary>
/// <returns>Result of the save operation</returns>
public SaveResult Save()
{
bool parentChanged = false;
int savedParentId = this.ParentId;
Database database = Token.Instance.Database;
//GET THE CURRENT PARENT ID OF THIS CATEGORY
DbCommand selectCommand = database.GetSqlStringCommand("SELECT ParentId FROM ac_Categories WHERE CategoryId = @categoryId");
database.AddInParameter(selectCommand, "@categoryId", DbType.Int32, this.CategoryId);
object scalarResult = database.ExecuteScalar(selectCommand);
if (scalarResult != null)
{
int currentParentId = (int)scalarResult;
if (!currentParentId.Equals(this.ParentId))
{
//CHANGING THE PARENT ID SHOULD ONLY BE HANDLED BY THE SETPARENT FUNCTION
//RETAIN THE DESIRED PARENT ID AND RUN THE SAVE PROCEDURE USING THE CURRENT PARENT
//IN CASE THE SET PARENT FAILS THE EXISTING PARENTID REFERENCE WILL HOLD INTACT.
parentChanged = true;
this.ParentId = currentParentId;
}
}
else
{
//NEW RECORD, PARENT IS CHANGING
parentChanged = true;
}
SaveResult result = this.BaseSave();
if ((result != SaveResult.Failed) && parentChanged)
{
Category.UpdateParent(this.CategoryId, savedParentId);
this.ParentId = savedParentId;
this.IsDirty = false;
}
if (_CustomUrl != null)
{
if (!string.IsNullOrEmpty(_CustomUrl.Url))
{
_CustomUrl.CatalogNodeId = this.CategoryId;
_CustomUrl.CatalogNodeType = CatalogNodeType.Category;
_CustomUrl.Save();
}
else
{
_CustomUrl.Delete();
}
}
return(result);
}
/// <summary>
/// Updates the parent of the given category
/// </summary>
/// <param name="categoryId">Id of the category for which to update the parent</param>
/// <param name="parentId">Parent Id to set</param>
public static void UpdateParent(int categoryId, int parentId)
{
//GET DATABSAE REFERENCES
Database database = Token.Instance.Database;
DbCommand selectCommand;
//VERIFY THE SPECIFIED CATEGORY IS VALID
if (parentId != 0)
{
selectCommand = database.GetSqlStringCommand("SELECT CategoryId FROM ac_Categories WHERE CategoryId = @categoryId");
database.AddInParameter(selectCommand, "@categoryId", DbType.Int32, categoryId);
object scalarResult = database.ExecuteScalar(selectCommand);
if (scalarResult == null)
{
throw new ArgumentException("This category instance has not been saved to the database.", "CategoryId");
}
}
//VERIFY THE SELECTED PARENT IS VALID
if (parentId != 0)
{
selectCommand = database.GetSqlStringCommand("SELECT CategoryId FROM ac_Categories WHERE CategoryId = @categoryId");
database.AddInParameter(selectCommand, "@categoryId", DbType.Int32, parentId);
object scalarResult = database.ExecuteScalar(selectCommand);
if (scalarResult == null)
{
throw new ArgumentException("Invalid parent category specified.", "parentId");
}
}
//CHECK FOR CIRCULAR REFRENCES
if (categoryId.Equals(parentId))
{
throw new ArgumentException("Specified parent category would result in a circular reference.", "parentId");
}
selectCommand = database.GetSqlStringCommand("SELECT COUNT(*) As ChildReferences FROM ac_CategoryParents WHERE ParentId = @categoryId AND CategoryId = @parentId");
database.AddInParameter(selectCommand, "@categoryId", DbType.Int32, categoryId);
database.AddInParameter(selectCommand, "@parentId", DbType.Int32, parentId);
int childReferences = (int)database.ExecuteScalar(selectCommand);
if (childReferences > 0)
{
throw new ArgumentException("Specified parent category would result in a circular reference.", "parentId");
}
//UPDATE CATEGORY PARENT
DbCommand updateCommand = database.GetSqlStringCommand("UPDATE ac_Categories SET ParentId = @parentId WHERE CategoryId = @categoryId");
database.AddInParameter(updateCommand, "@categoryId", DbType.Int32, categoryId);
database.AddInParameter(updateCommand, "@parentId", DbType.Int32, parentId);
database.ExecuteNonQuery(updateCommand);
//DELETE ANY EXISTING PARENT ASSOCIATIONS
DbCommand deleteCommand = database.GetSqlStringCommand("DELETE FROM ac_CategoryParents WHERE CategoryId = @categoryId");
database.AddInParameter(deleteCommand, "@categoryId", DbType.Int32, categoryId);
database.ExecuteNonQuery(deleteCommand);
deleteCommand = database.GetSqlStringCommand("DELETE FROM ac_CatalogNodes WHERE CatalogNodeTypeId = 0 AND CatalogNodeId = @categoryId");
database.AddInParameter(deleteCommand, "@categoryId", DbType.Int32, categoryId);
database.ExecuteNonQuery(deleteCommand);
//NOW INSERT MODIFIED PARENT PATH
StringBuilder insertQuery;
DbCommand insertCommand;
int level;
if (parentId != 0)
{
insertQuery = new StringBuilder();
insertQuery.Append("INSERT INTO ac_CategoryParents (CategoryId, ParentId, ParentLevel, ParentNumber)");
insertQuery.Append(" SELECT @categoryId, ParentId, ParentLevel, (ParentNumber + 1)");
insertQuery.Append(" FROM ac_CategoryParents");
insertQuery.Append(" WHERE CategoryId = @parentCategoryId;");
insertCommand = database.GetSqlStringCommand(insertQuery.ToString());
database.AddInParameter(insertCommand, "@categoryId", DbType.Int32, categoryId);
database.AddInParameter(insertCommand, "@parentCategoryId", DbType.Int32, parentId);
database.ExecuteNonQuery(insertCommand);
//GET LEVEL OF NEW CATEGORY
selectCommand = database.GetSqlStringCommand("SELECT (ParentLevel + 1) FROM ac_CategoryParents WHERE CategoryId = @parentCategoryId AND ParentNumber=0");
database.AddInParameter(selectCommand, "@parentCategoryId", DbType.Int32, parentId);
level = AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
}
else
{
insertQuery = new StringBuilder();
insertQuery.Append("INSERT INTO ac_CategoryParents (CategoryId, ParentId, ParentLevel, ParentNumber)");
insertQuery.Append(" VALUES (@categoryId, 0, 0, 1)");
insertCommand = database.GetSqlStringCommand(insertQuery.ToString());
database.AddInParameter(insertCommand, "@categoryId", DbType.Int32, categoryId);
database.ExecuteNonQuery(insertCommand);
level = 1;
}
// NOW INSERT SELF-REFERENCING PATH
insertQuery = new StringBuilder();
insertQuery.Append("INSERT INTO ac_CategoryParents (CategoryId, ParentId, ParentLevel, ParentNumber)");
insertQuery.Append(" VALUES (@categoryId, @categoryId, @level, 0);");
insertCommand = database.GetSqlStringCommand(insertQuery.ToString());
database.AddInParameter(insertCommand, "@categoryId", DbType.Int32, categoryId);
database.AddInParameter(insertCommand, "@level", DbType.Int32, level);
database.ExecuteNonQuery(insertCommand);
//GET ORDER OF NEW CATEGORY
selectCommand = database.GetSqlStringCommand("SELECT (MAX(OrderBy) + 1) FROM ac_CatalogNodes WHERE CategoryId = @parentCategoryId");
database.AddInParameter(selectCommand, "@parentCategoryId", DbType.Int32, parentId);
Int16 order = AlwaysConvert.ToInt16(database.ExecuteScalar(selectCommand));
//ADD IN CATALOG NODE
insertQuery = new StringBuilder();
insertQuery.Append("INSERT INTO ac_CatalogNodes (CategoryId, CatalogNodeId, CatalogNodeTypeId, OrderBy)");
insertQuery.Append(" VALUES (@parentCategoryId, @categoryId, 0, @order)");
insertCommand = database.GetSqlStringCommand(insertQuery.ToString());
database.AddInParameter(insertCommand, "@parentCategoryId", DbType.Int32, parentId);
database.AddInParameter(insertCommand, "@categoryId", DbType.Int32, categoryId);
database.AddInParameter(insertCommand, "@order", DbType.Int16, order);
database.ExecuteNonQuery(insertCommand);
// GET ANY CHILD CATEGORIES OF CURRENT CATEGORY
List <int> catalogNodeIds = new List <int>();
selectCommand = database.GetSqlStringCommand("SELECT CategoryId FROM ac_Categories WHERE ParentId = @categoryId");
database.AddInParameter(selectCommand, "@categoryId", DbType.Int32, categoryId);
using (IDataReader reader = database.ExecuteReader(selectCommand))
{
while (reader.Read())
{
catalogNodeIds.Add(reader.GetInt32(0));
}
reader.Close();
}
foreach (int catalogNodeId in catalogNodeIds)
{
Category.UpdateParent(catalogNodeId, categoryId);
}
}
){kind=link}