help.axcms.netAxinom Logo
Save Save Chapter Send Feedback
KB10050: How to add categories in the database
This article explains how to add new categories via SQL.

SYMPTOMS

You would like to add categories directly in the AxCMS.net database.

CAUSES

In most cases you will not want to create categories on the database level. There are plenty of other ways to do it:

  • AxCMS.net GUI via browser
  • Import categories via XML
  • Programmatically via API
  • From Windows Explorer via WebDAV

But in some integration szenarios you still might want to add a category manually.

SOLUTION

To add a category please follow these steps. Preferably run all the statements below in one transaction (BEGIN TRANSACTION / COMMIT TRANSACTION) to avoid possible conflicts with the web application running at the same time. All database manipulations should be done in the Management System database. Then you can publish the categories using AxCMS.net GUI.

1. Take the next available CategoryID. The IDs are stored in the table Sequence.

DECLARE @ID int
SET @ID = (SELECT NextVal FROM Sequence WHERE Name='Category')

2.Insert new category:

INSERT INTO [dbo].[AxCategory] (
[CategoryID],
[ParentID], // ID of the parent category
[ExternID], 
[SequenceNr],  // SequenceNr orders the nodes under the same parent. Values are arbitrary, important is only the order
[Name],
[Level], // will be re-calculated later based on the ParentID
[OrderNr], // will be re-calculated later based on the SequenceNr
[Created], // use getdate() for now or any specific datetime
[PublicationState], // use 4 = Never Published
[Published], // use NULL
[Addition]
)
VALUES (@ID, [Parent], [ExternID], [SequenceNr], [Name], [Level], [OrderNr], getdate(), 4, NULL, [Addition])

If you need to add more than one category, repeat the INSERT-statement.

Update the next available CategoryID:

UPDATE Sequence SET NextVal = NextVal + 1 WHERE Name = 'Category' /* If you added more than one category, replace 1 with the number of added categories */

Update Category Tree

For efficient access to categories there are some redundand structures. If you add categories manually you have to care yourself for updating the structure. For this call these 2 strored procedures (in this order):

EXEC GenerateCategoryTree // updates the table AxCategoryTree
EXEC RecalculateOrder // updates fields OrderNr in AxCategory

Update Application Cache

Category Tree is cached in the web application (MS and LS). If you change categories in the database manually you have to indalidate the cache manually. The cache has a file dependency on the file /publish/publish.category.cache. It is enough to "touch" this file or modify it. For example, WebDAV adds a line with a timestamp every time it changes the category tree in the database.

Publish Categories

Go to the Management System. Under Edit / Navigaiton you will see your newly added categories. Publish them.

Alternatively repeat the same steps in the Live System database. You should set in this case PublicationState=1 (Active) and Published=getdate(). The disadvantage of this approach is - the tree structure in MS and LS can be a bit different. E.g. different OrderNr can be generated.

MORE INFORMATION:

APPLIES TO

AxCMS.net Version: all Versions