Create a Store support label with a Znode 9 SQL migration

Znode 9
Written By GCG

Recipe 02 / Znode 9

Create a Store support label with a Znode 9 SQL migration

Add a real Store text attribute with its locale and group mappings, make the migration repeatable, read the saved value through the native portal model, and render it safely in Razor.

SQL / C# / Razor10 implementation stepsIntermediate
  1. SchemaSQL migration
  2. MappingStore group + family
  3. ContentAdmin value
  4. ExperienceTyped Razor output

The complete implementation

What you are building

A distributor wants each Store to show its own short support message above the footer. An administrator should be able to change the message without a code release. The developer needs the attribute definition to move consistently between environments.

This recipe creates the GcgSupportLabel metadata, connects it to the Store attribute group and family, and reads the saved value from PortalAgent.CurrentPortal. The migration defines the field. An administrator supplies each Store's message through the normal Store editor.

The result is a plain-text message, such as Need help selecting a fitting? Contact our product team. Empty values render nothing, and Razor encodes the message instead of treating it as HTML.

Version and setup

Znode 9.7.4 / SQL Server / .NET Framework 4.8 / C# 6

Before you start

  • A Znode 9 database and .NET Framework 4.8 WebStore matching the inspected 9.7.4 global-attribute schema and native portal model. Review table columns and constraints against your installed patch before including this migration in its release.
  • An existing Store family with FamilyCode Store, a Store attribute group with GroupCode GcgContent, and an active en-US locale. The steps below show how to prepare the group through Admin.
  • The UserId of an existing active deployment actor for metadata audit fields. Set @AuditUserId explicitly; the script does not create an account or guess the actor.
  • A dedicated SQL deployment connection with permission to insert the listed metadata and acquire the application lock. The script owns its transaction and rejects an outer transaction.
  • A configured legacy WebStore solution with Znode.WebStore.Custom and access to edit the active theme. Source paths below are relative to its Projects directory.

Step 01

Prepare the group and reuse the Store family

In Admin > Global Attributes > Attribute Groups, create a group for the Store entity with the code GcgContent and the localized name Store content. If that exact group already exists, use it. Groups collect related fields so administrators can find them together.

Under Global Attribute Families, identify the existing Store family and confirm its code is Store. Znode maintains one family for the Store entity, so reuse it. The migration will connect GcgContent to that family and create any missing group mappings.

This is a Store global attribute, not a PIM product attribute. Its value belongs to a Store and is independent of individual product records.

Expected resultExactly one intended Store entity, GcgContent group, Store family, and active en-US locale exist before deployment.

Step 02

Add the complete metadata migration

Save the script in the release's database-migration directory. Set @AuditUserId to the approved active actor and confirm the locale, group, and family codes at the top. The NULL actor is an intentional precondition that prevents an unconfigured copy from making changes.

The transaction first acquires a named application lock, then resolves the existing entity, type, locale, group, and family. The lock coordinates deployments that use the same resource name. It does not block every possible administrator or script from editing attributes.

The migration adds a localizable, optional Text definition, an en-US editor label, and the attribute-to-group, group-to-family, and group-to-entity mappings. It preserves existing translated labels and rejects a conflicting definition rather than silently changing its type or meaning.

Rerunning the same script keeps the existing attribute ID and fills only missing metadata. A duplicate row is treated as a data issue to investigate. The script does not write a Store's support message, delete attributes, or republish the Store.

Database/Migrations/001-GcgSupportLabel.sqlsql

-- Original GCG example. Znode 9 global-attribute schema pattern.
-- Configure these inputs after checking the target database and patch.
-- Run on a dedicated deployment connection without an outer transaction.
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @AuditUserId int = NULL; -- Required: existing deployment actor.
DECLARE @LocaleCode nvarchar(50) = N'en-US';
DECLARE @GroupCode varchar(200) = 'GcgContent';
DECLARE @FamilyCode varchar(200) = 'Store';
DECLARE @Code nvarchar(300) = N'GcgSupportLabel';
DECLARE @Label nvarchar(300) = N'Support label';
DECLARE @EntityId int, @TypeId int, @LocaleId int,
@GroupId int, @FamilyId int, @AttributeId int,
@LockResult int, @Now datetime = GETDATE();
IF @@TRANCOUNT <> 0
THROW 51000, 'This example owns its transaction. Review runner configuration.', 1;
IF @AuditUserId IS NULL OR NOT EXISTS
(SELECT 1 FROM dbo.ZnodeUser WHERE UserId = @AuditUserId AND IsActive = 1)
THROW 51001, 'Set an existing active deployment actor.', 1;
BEGIN TRY
BEGIN TRANSACTION;
EXEC @LockResult = sys.sp_getapplock
@Resource = N'GCG.StoreAttributeMetadata',
@LockMode = 'Exclusive', @LockOwner = 'Transaction',
@LockTimeout = 10000;
IF @LockResult < 0
THROW 51002, 'Another cooperating attribute deployment is running.', 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalEntity
WHERE EntityName = N'Store' AND IsActive = 1) <> 1
THROW 51003, 'Expected exactly one active Store entity.', 1;
SELECT @EntityId = GlobalEntityId FROM dbo.ZnodeGlobalEntity
WHERE EntityName = N'Store' AND IsActive = 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeAttributeType
WHERE AttributeTypeName = 'Text' AND IsPimAttributeType = 0) <> 1
THROW 51004, 'Expected exactly one non-PIM Text type.', 1;
SELECT @TypeId = AttributeTypeId FROM dbo.ZnodeAttributeType
WHERE AttributeTypeName = 'Text' AND IsPimAttributeType = 0;
IF (SELECT COUNT(*) FROM dbo.ZnodeLocale
WHERE Code = @LocaleCode AND IsActive = 1) <> 1
THROW 51005, 'Expected exactly one active locale.', 1;
SELECT @LocaleId = LocaleId FROM dbo.ZnodeLocale
WHERE Code = @LocaleCode AND IsActive = 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeGroup
WHERE GroupCode = @GroupCode AND GlobalEntityId = @EntityId) <> 1
THROW 51006, 'Create or select one Store group before deployment.', 1;
SELECT @GroupId = GlobalAttributeGroupId FROM dbo.ZnodeGlobalAttributeGroup
WHERE GroupCode = @GroupCode AND GlobalEntityId = @EntityId;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeFamily
WHERE FamilyCode = @FamilyCode AND GlobalEntityId = @EntityId) <> 1
THROW 51007, 'Expected exactly one intended Store family.', 1;
SELECT @FamilyId = GlobalAttributeFamilyId FROM dbo.ZnodeGlobalAttributeFamily
WHERE FamilyCode = @FamilyCode AND GlobalEntityId = @EntityId;
IF EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code
AND (GlobalEntityId <> @EntityId OR GlobalEntityId IS NULL))
THROW 51014, 'Attribute code is already used by another entity.', 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code AND GlobalEntityId = @EntityId) > 1
THROW 51008, 'Duplicate attribute definitions require investigation.', 1;
SELECT @AttributeId = GlobalAttributeId FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code AND GlobalEntityId = @EntityId;
IF @AttributeId IS NOT NULL AND EXISTS
(SELECT 1 FROM dbo.ZnodeGlobalAttribute WHERE GlobalAttributeId = @AttributeId
AND (AttributeTypeId IS NULL OR AttributeTypeId <> @TypeId
OR ISNULL(IsSystemDefined, 1) <> 0 OR ISNULL(IsRequired, 1) <> 0
OR ISNULL(IsLocalizable, 0) <> 1 OR ISNULL(IsActive, 0) <> 1))
THROW 51009, 'Existing definition conflicts with this migration.', 1;
IF @AttributeId IS NULL
BEGIN
INSERT dbo.ZnodeGlobalAttribute
(AttributeTypeId, AttributeCode, IsRequired, IsLocalizable, IsActive,
DisplayOrder, HelpDescription, CreatedBy, CreatedDate,
ModifiedBy, ModifiedDate, IsSystemDefined, GlobalEntityId)
VALUES (@TypeId, @Code, 0, 1, 1, 100, N'Customer-facing support label',
@AuditUserId, @Now, @AuditUserId, @Now, 0, @EntityId);
SET @AttributeId = CONVERT(int, SCOPE_IDENTITY());
END;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeLocale
WHERE GlobalAttributeId = @AttributeId AND LocaleId = @LocaleId) > 1
THROW 51010, 'Duplicate localized labels require investigation.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttributeLocale
WHERE GlobalAttributeId = @AttributeId AND LocaleId = @LocaleId)
INSERT dbo.ZnodeGlobalAttributeLocale
(LocaleId, GlobalAttributeId, AttributeName, Description,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@LocaleId, @AttributeId, @Label, NULL,
@AuditUserId, @Now, @AuditUserId, @Now);
-- An existing translated label belongs to the administrator. Preserve it.
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeGroupMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalAttributeId = @AttributeId) > 1
THROW 51011, 'Duplicate attribute/group mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttributeGroupMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalAttributeId = @AttributeId)
INSERT dbo.ZnodeGlobalAttributeGroupMapper
(GlobalAttributeGroupId, GlobalAttributeId, AttributeDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@GroupId, @AttributeId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalFamilyGroupMapper
WHERE GlobalAttributeFamilyId = @FamilyId AND GlobalAttributeGroupId = @GroupId) > 1
THROW 51012, 'Duplicate family/group mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalFamilyGroupMapper
WHERE GlobalAttributeFamilyId = @FamilyId AND GlobalAttributeGroupId = @GroupId)
INSERT dbo.ZnodeGlobalFamilyGroupMapper
(GlobalAttributeFamilyId, GlobalAttributeGroupId, AttributeGroupDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@FamilyId, @GroupId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalGroupEntityMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalEntityId = @EntityId) > 1
THROW 51013, 'Duplicate group/entity mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalGroupEntityMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalEntityId = @EntityId)
INSERT dbo.ZnodeGlobalGroupEntityMapper
(GlobalAttributeGroupId, GlobalEntityId, AttributeGroupDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@GroupId, @EntityId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
COMMIT TRANSACTION;
SELECT @Code AS AttributeCode, @AttributeId AS AttributeId,
@GroupCode AS GroupCode, @FamilyCode AS FamilyCode, @LocaleCode AS LocaleCode;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
Expected resultThe script commits one compatible GcgSupportLabel definition and returns its attribute ID plus the selected group, family, and locale codes.

Step 03

Run the migration on its own connection

Use sqlcmd from the Projects directory. Supply the reviewed server and database names at the prompts, and run with the existing Windows deployment identity. The first command prints the resolved target. The second opens a fresh connection for the migration and returns a failure exit code if SQL raises an error.

Do not wrap this file in BEGIN TRANSACTION in a migration runner. Its transaction protects the complete metadata change, and its CATCH block rolls back before rethrowing an error. Keep the result and deployment log with the release.

PowerShell at Projectspowershell

$sqlServer = Read-Host "Reviewed SQL Server name"
$sqlDatabase = Read-Host "Reviewed Znode database name"
sqlcmd -S $sqlServer -d $sqlDatabase -E -b -r 1 -Q "SELECT @@SERVERNAME AS SqlServer, DB_NAME() AS DatabaseName;"
if ($LASTEXITCODE -ne 0) { throw "Could not verify the database connection." }
sqlcmd -S $sqlServer -d $sqlDatabase -E -b -r 1 -l 30 -t 60 -i .\Database\Migrations\001-GcgSupportLabel.sql
if ($LASTEXITCODE -ne 0) { throw "The support-label migration failed. Review the SQL error." }
Expected resultThe verified database accepts the migration, and sqlcmd returns exit code 0 with the attribute metadata summary.

Step 04

Verify every metadata link

Save and execute this read-only query against the same database. It walks from the Store entity to the attribute, localized label, group, family, and their mapper rows.

Expect exactly one result row and non-null identifiers in every selected mapping column. No result means the definition was not found for the Store entity. Multiple rows or a missing mapper ID identify an incomplete or ambiguous setup before the view is deployed.

Rerun the migration once in the test environment, then repeat this query. The same IDs should remain and the row count should still be one.

Database/Verification/verify-support-mapping.sqlsql

DECLARE @AttributeCode nvarchar(300) = N'GcgSupportLabel';
DECLARE @GroupCode varchar(200) = 'GcgContent';
DECLARE @FamilyCode varchar(200) = 'Store';
DECLARE @LocaleCode nvarchar(50) = N'en-US';
SELECT a.GlobalAttributeId, a.AttributeCode,
l.GlobalAttributeLocaleId, l.AttributeName,
g.GlobalAttributeGroupId,
ag.GlobalAttributeGroupMapperId,
f.GlobalAttributeFamilyId,
fg.GlobalFamilyGroupMapperId,
ge.GlobalGroupEntityId
FROM dbo.ZnodeGlobalEntity AS e
JOIN dbo.ZnodeGlobalAttribute AS a
ON a.GlobalEntityId = e.GlobalEntityId
LEFT JOIN dbo.ZnodeLocale AS loc
ON loc.Code = @LocaleCode AND loc.IsActive = 1
LEFT JOIN dbo.ZnodeGlobalAttributeLocale AS l
ON l.GlobalAttributeId = a.GlobalAttributeId AND l.LocaleId = loc.LocaleId
LEFT JOIN dbo.ZnodeGlobalAttributeGroup AS g
ON g.GroupCode = @GroupCode AND g.GlobalEntityId = e.GlobalEntityId
LEFT JOIN dbo.ZnodeGlobalAttributeGroupMapper AS ag
ON ag.GlobalAttributeGroupId = g.GlobalAttributeGroupId
AND ag.GlobalAttributeId = a.GlobalAttributeId
LEFT JOIN dbo.ZnodeGlobalAttributeFamily AS f
ON f.FamilyCode = @FamilyCode AND f.GlobalEntityId = e.GlobalEntityId
LEFT JOIN dbo.ZnodeGlobalFamilyGroupMapper AS fg
ON fg.GlobalAttributeFamilyId = f.GlobalAttributeFamilyId
AND fg.GlobalAttributeGroupId = g.GlobalAttributeGroupId
LEFT JOIN dbo.ZnodeGlobalGroupEntityMapper AS ge
ON ge.GlobalAttributeGroupId = g.GlobalAttributeGroupId
AND ge.GlobalEntityId = e.GlobalEntityId
WHERE e.EntityName = N'Store' AND e.IsActive = 1
AND a.AttributeCode = @AttributeCode;

PowerShell using the reviewed connection variablespowershell

sqlcmd -S $sqlServer -d $sqlDatabase -E -b -r 1 -i .\Database\Verification\verify-support-mapping.sql
if ($LASTEXITCODE -ne 0) { throw "The metadata verification query failed." }
Expected resultOne complete mapping row is returned before and after a repeat migration, with stable identifiers.

Step 05

Read the Store value into a named view model

Add this file to the custom library. The helper starts with the Store already resolved by the current WebStore request and reads its GlobalAttributes.Attributes collection. It does not open a database connection or accept a caller-supplied Store ID.

SingleOrDefault requires a unique attribute code. The helper trims whitespace, rejects values longer than 160 characters, and returns a SupportLabelViewModel with named Label and IsVisible properties. An absent or blank value produces a hidden label.

The 160-character rule is enforced by this reader. Keep administrator-entered values within that limit. A duplicate code or overlong value is surfaced as a configuration error so the deployment can be corrected.

Libraries/Znode.WebStore.Custom/StoreSettings/StoreSupportSettings.cscsharp

using System;
using System.Linq;
using Znode.Engine.WebStore.Agents;
namespace Gcg.Recipes.StoreSettings
{
public sealed class SupportLabelViewModel
{
public string Label { get; private set; }
public bool IsVisible { get { return !string.IsNullOrWhiteSpace(Label); } }
public SupportLabelViewModel(string label)
{
Label = label;
}
}
public static class StoreSupportSettings
{
public static SupportLabelViewModel Read()
{
var portal = PortalAgent.CurrentPortal;
if (portal == null)
throw new InvalidOperationException("The request has no resolved Store.");
var attributes = portal.GlobalAttributes == null ? null : portal.GlobalAttributes.Attributes;
var row = attributes == null ? null : attributes.SingleOrDefault(attribute =>
string.Equals(attribute.AttributeCode, "GcgSupportLabel", StringComparison.OrdinalIgnoreCase));
string label = row == null || row.AttributeValue == null ? null : row.AttributeValue.Trim();
if (label != null && label.Length > 160)
throw new InvalidOperationException("GcgSupportLabel must be at most 160 characters.");
return new SupportLabelViewModel(label);
}
}
}
Expected resultThe view receives one typed SupportLabelViewModel for the current Store without generic dictionaries or anonymous data bags.

Step 06

Render the text in a shared partial

Create this partial under the host's root Views/Shared directory. It calls the helper once and renders a paragraph only when the label is visible. The ordinary @support.Label expression HTML-encodes the stored value.

Use the field for a short sentence, not rich HTML. For example, saving Product support: valves & fittings should show the ampersand as text. A value containing angle brackets should also remain text.

Znode.Engine.WebStore/Views/Shared/_GcgSupportLabel.cshtmlcshtml

@using Gcg.Recipes.StoreSettings
@{
SupportLabelViewModel support = StoreSupportSettings.Read();
}
@if (support.IsVisible)
{
<p class="gcg-support-label">@support.Label</p>
}
Expected resultA populated label produces one encoded paragraph. A blank field produces no paragraph or empty wrapper.

Step 07

Place the label above the active theme's footer

In the active theme's shared layout, insert this partial call immediately before the existing FooterContent render action. In the inspected B2B theme, the insertion point is after the main layout-content div and before the Footer Widgets section.

The Bootstrap container gives the sentence the same horizontal alignment as the legacy theme. Keep the existing footer action and its Store, locale, profile, and catalog parameters intact. If your Store uses another theme, make the same insertion in that theme's layout.

Znode.Engine.WebStore/Views/Themes/B2B/Views/Shared/_Layout.cshtmlcshtml

<div class="container">
@Html.Partial("~/Views/Shared/_GcgSupportLabel.cshtml")
</div>
Expected resultPages using the Store's main layout show the support label directly above the existing footer.

Step 08

Include the files and rebuild the host

Add StoreSupportSettings.cs as a Compile item in the custom library and the shared partial as Content in the host. Visual Studio normally adds these entries when you create the files through the project. Avoid duplicate entries.

Use the solution's existing Znode references and package sources. This feature adds no NuGet dependency. Rebuild the host, then deploy the updated custom assembly, shared partial, and active theme layout together.

Libraries/Znode.WebStore.Custom/Znode.WebStore.Custom.csprojxml

<ItemGroup>
<Compile Include="StoreSettings\StoreSupportSettings.cs" />
</ItemGroup>

Znode.Engine.WebStore/Znode.Engine.WebStore.csprojxml

<ItemGroup>
<Content Include="Views\Shared\_GcgSupportLabel.cshtml" />
</ItemGroup>

Visual Studio Developer PowerShell at Projectspowershell

msbuild .\Znode.Engine.WebStore\Znode.Engine.WebStore.csproj /t:Restore,Rebuild /p:RestorePackagesConfig=true /p:Configuration=Release /m
Expected resultThe host builds with the new typed helper and deploys both Razor changes.

Step 09

Set the message in Admin and publish Store settings

Open Stores and Reps > Stores, select the intended test Store, and open Additional Store Attributes. In the Store content group, set Support label to Need help selecting a fitting? Contact our product team. Save the Store and publish its Store settings using the existing release workflow.

The attribute's localized editor label is separate from the Store's saved message. The SQL file creates the en-US field label. Use the Admin locale controls for any additional translated labels and values needed by the Store.

Open the Store's public hostname after publication. The helper reads the portal model supplied by the native WebStore pipeline, so verify that the current Store and published settings are the ones you edited.

Expected resultThe saved support sentence appears above the footer on the intended Store. Another Store retains its own value or no label.

Step 10

Exercise empty values and changes

Change the message to a different short sentence, save, and publish again. The storefront should display the new text without a binary change. Then clear the field, save, and publish; the paragraph should disappear.

Restore the approved message when finished. If the feature needs to be withdrawn, first remove its partial call and deploy the layout. Retain the metadata and saved Store values until their use has been reviewed rather than deleting shared attribute rows as an automatic rollback.

Expected resultThe content owner can update or hide the message through Admin while code and metadata stay stable.

Verify the complete result

Check Action Expected result
First migration Run the configured SQL file against the reviewed test database. One compatible Text attribute is created with the complete locale, group, family, and entity mapping chain.
Repeat migration Run the same file again and execute verify-support-mapping.sql. The same IDs remain and verification returns one row. No translated label or Store message is overwritten.
Missing prerequisite In an isolated test database, select a nonexistent group code or leave @AuditUserId unset. The script fails with a specific precondition message and commits no partial metadata.
Store isolation Save different short labels for two test Stores and publish each Store's settings. Each hostname displays its own label. The helper uses the resolved portal, not a shared hard-coded Store ID.
Encoding Save Product support: valves & fittings, then a harmless value containing <b>text</b>. Both display as literal text. The second value does not create a bold HTML element.
Empty and updated values Update the field, publish, then clear the field and publish again. The message updates, then disappears without an empty paragraph.
Length rule Check that the chosen administrator-entered message is at most 160 characters after trimming. The helper accepts the message. Longer values are configuration errors and must be corrected before release.

Troubleshooting

The migration says to create or select a Store group

Confirm that GcgContent exists for the Store entity. A group with the same display name under another entity is not the same group.

The migration rejects the existing definition

Compare its entity, Text type, localizable flag, active flag, required flag, and system-defined flag with the migration contract. Resolve the conflict instead of removing the guard or forcing an update.

The field is missing from Additional Store Attributes

Run the verification query. Check the group-to-family and group-to-entity mappings, the Store family code, and the administrator's access to the Store editor.

The saved message is missing or stale on the storefront

Verify the hostname resolves to the edited Store, Store settings were published, the active theme contains the partial call, and the deployed custom assembly includes the helper. Use the site's normal Store-settings cache refresh process if its published portal data is stale.

The view reports a configuration exception

Correct a duplicate GcgSupportLabel entry, an over-160-character value, or a request outside a resolved Store. The helper intentionally does not choose an arbitrary duplicate or truncate content silently.

SQL reports an existing transaction

Run the file on a dedicated connection without a wrapping transaction. The example owns its transaction and application lock.

References and further reading

Original examples and independent guidance from GCG. The sample data is fictional. Znode and Microsoft are trademarks of their respective owners. Discuss your implementation with GCG.

GCG