SQL Statement Constants (SqlStatementConstants) Class

Namespace: Cxt.Account.Settings.API.Constants

The SqlStatementConstants class centralizes SQL query strings for interacting with the AccountSettings database table, enhancing maintainability and readability of data access logic.

Purpose

• Centralization: Consolidates all SQL queries in a single location.
• Readability: Improves clarity by separating SQL from C# logic.
• Maintainability: Simplifies updates and reviews of SQL statements.
• Consistency: Ensures uniform use of table and column names.

SQL Query Constants

This class defines public const string fields for each SQL operation:

RetrieveAccountSettingSql

Retrieves all account settings for a specific Global Client Number (GCN).

SELECT * FROM AccountSettings WHERE GCN = @GCN

InsertAccountSettingSql

Inserts a new record into the AccountSettings table.

INSERT INTO AccountSettings (
    GCN, ProfileId, AccountName, AccountNumber, AccountType, 
    AccountStatus, ProductSegment, BusinessUnit, AccountHidden, 
    DateCreated, DateUpdated
) VALUES (
    @GCN, @ProfileId, @AccountName, @AccountNumber, @AccountType, 
    @AccountStatus, @ProductSegment, @BusinessUnit, @AccountHidden, 
    @DateCreated, @DateUpdated
) ON CONFLICT(AccountNumber, AccountType, ProfileId) DO NOTHING

• Key Feature: Uses ON CONFLICT(AccountNumber, AccountType, ProfileId) DO NOTHING (SQLite-specific). If a unique constraint violation occurs on the specified columns, the insert is ignored without raising an error.

UpdateAccountSettingSql

Updates AccountName, AccountHidden, and DateUpdated for a specific account setting.

UPDATE AccountSettings 
SET AccountName = @AccountName, 
    AccountHidden = @AccountHidden, 
    DateUpdated = @DateUpdated 
WHERE GCN = @GCN 
  AND AccountNumber = @AccountNumber 
  AND AccountType = @AccountType

UpdateAccountStatusSettingSql

Updates AccountStatus and DateUpdated for a specific account setting. (Note: Usage might be for specific internal processes or future features not detailed in the primary hide/unhide flow.)

UPDATE AccountSettings 
SET AccountStatus = @AccountStatus, 
    DateUpdated = @DateUpdated 
WHERE GCN = @GCN 
  AND AccountNumber = @AccountNumber 
  AND AccountType = @AccountType

DeleteAccountSettingSql

Deletes a specific account setting.

DELETE FROM AccountSettings 
WHERE GCN = @GCN 
  AND AccountNumber = @AccountNumber 
  AND AccountType = @AccountType

RetrieveSingleAccountSql

Counts records matching GCN, ProfileId, AccountNumber, and AccountType. Used to check for pre-existing settings before an insert.

SELECT COUNT(1) 
FROM AccountSettings 
WHERE GCN = @GCN 
  AND ProfileId = @ProfileId 
  AND AccountNumber = @AccountNumber 
  AND AccountType = @AccountType

Usage

These constants are primarily consumed by the AccountSettingsClient when constructing and executing database commands via IDatabaseConnection.

Example (Conceptual C#):

// Within AccountSettingsClient.cs (simplified)
public async Task<List<AccountSettingsPreferencesDto>> RetrievePreferences(string gcn)
{
    var parameters = new Dictionary<string, object> { { "@GCN", gcn } };
    string sql = SqlStatementConstants.RetrieveAccountSettingSql;
    
    // Assuming _databaseConnection is an IDatabaseConnection instance
    using (var reader = await _databaseConnection.ExecuteReaderAsync(sql, parameters))
    {
        // ... process reader ...
    }
    // ...
}

This approach standardizes SQL management, a beneficial practice for database-driven applications.