Account Settings Client (AccountSettingsClient) Class

Namespace: Cxt.Account.Settings.API.ClientsImplements: IAccountSettingsClient

Concrete implementation for IAccountSettingsClient, handling direct database interactions for CRUD operations on account visibility settings using IDatabaseConnection.

Purpose

• Performs CRUD operations against the database for account settings.
• Implements an "upsert" logic for adding preferences (updates if existing, else inserts).
• Abstracts database communication specifics from the service layer.

Dependencies

• IDatabaseConnection: Injected via constructor. Provides methods for database connection management and SQL execution. (Details in Database Connectivity Strategy Overview).
• SqlStatementConstants: Provides SQL query strings. (Details in SQL Statement Constants).

Method Implementations

Add Client Account Settings Preferences Client

public async Task<bool> AddClientAccountSettingsPreferencesClient(AddAccountSettingDto request)

• Description: Persists a new account setting preference. Implements an "upsert" logic: updates if the setting exists, otherwise inserts a new one.
• Parameters:

  Parameter	Type	Description
  request	AddAccountSettingDto	Contains details for the new setting (expects AccountHidden as an integer).

• Returns: Task<bool> - true if add/update is successful; false on failure (e.g., after all retries).
• Details:
  • Behavior:
    1. Checks for an existing setting using SqlStatementConstants.RetrieveSingleAccountSql (parameters: GCN, ProfileId, AccountNumber, AccountType from request).
    2. If an existing setting is found, it calls UpdateClientAccountSettingsPreferencesClient(request) to update it.
    3. If no existing setting is found, it uses SqlStatementConstants.InsertAccountSettingSql to insert a new record.
       • Maps fields from the AddAccountSettingDto to the SQL parameters.
       • Sets DateCreated and DateUpdated to DateTime.UtcNow (formatted as "yyyy-MM-dd HH:mm:ss").
  • Retry Logic:
    • Catches Microsoft.Data.Sqlite.SqliteException specifically for SqliteErrorCode == 5 (database locked/busy).
    • Retries the operation up to 3 times, with a 100ms delay (Task.Delay(100)) between each attempt.
  • Error Handling: Logs other exceptions to Console.WriteLine. The database connection is closed in a finally block associated with the database operations.

Retrieve Client Account Settings Preferences Client

public async Task<List<AccountSettingsPreferencesDto>> RetrieveClientAccountSettingsPreferencesClient(string gcn, CancellationToken cancellationToken)

• Description: Retrieves all stored account setting preferences for a client based on their GCN.
• Parameters:

  Parameter	Type	Description
  gcn	string	The Global Client Number.
  cancellationToken	CancellationToken	Token for observing cancellation requests.

• Returns: Task<List<AccountSettingsPreferencesDto>> - A list of AccountSettingsPreferencesDto objects. Returns an empty list if no settings are found or if an error occurs.
• Details:
  • Behavior:
    1. Uses SqlStatementConstants.RetrieveAccountSettingSql with the provided gcn as a parameter.
    2. Opens the database connection using _dbConnection.OpenAsync().
    3. Executes the query using _dbConnection.ExecuteReaderAsync().
    4. Iterates through the results from the DbDataReader:
       • For each row, it creates an AccountSettingsPreferencesDto object.
       • Maps database columns to the DTO properties: GCN, ProfileId, AccountName, AccountNumber, AccountType, AccountStatus, ProductSegment, BusinessUnit, AccountHidden (integer from DB), and DateUpdated.
    5. Adds each populated DTO to a list.
  • Error Handling: Logs exceptions to Console.WriteLine. The database connection is closed in a finally block.

Update Client Account Settings Preferences Client

public async Task<bool> UpdateClientAccountSettingsPreferencesClient(AddAccountSettingDto request)

• Description: Updates an existing account setting preference in the database.
• Parameters:

  Parameter	Type	Description
  request	AddAccountSettingDto	Contains the updated details. The target account is identified by GCN, AccountNumber, and ProfileId within the DTO.

• Returns: Task<bool> - true if the update is successful; false on failure.
• Details:
  • Behavior:
    1. Uses SqlStatementConstants.UpdateAccountSettingSql.
    2. Maps fields from the AddAccountSettingDto to SQL parameters:
       • GCN, AccountName (defaults to AccountNumber-AccountType if request.AccountName is empty/null), AccountNumber, AccountType, AccountHidden (integer value from DTO).
    3. Sets the DateUpdated SQL parameter to DateTime.UtcNow (formatted as "yyyy-MM-dd HH:mm:ss").
    4. Opens the connection and executes the non-query command.
  • Error Handling: Logs exceptions to Console.WriteLine. The database connection is closed in a finally block.

Delete Client Account Settings Preferences Client

public async Task<bool> DeleteClientAccountSettingsPreferencesClient(AccountSettingsDeleteRequest request)

• Description: Deletes an account setting preference from the database.
• Parameters:

  Parameter	Type	Description
  request	AccountSettingsDeleteRequest	Specifies the GCN, AccountNumber, and AccountType of the preference to delete.

• Returns: Task<bool> - true if the deletion is successful; false on failure.
• Details:
  • Behavior:
    1. Uses SqlStatementConstants.DeleteAccountSettingSql.
    2. Maps fields from the AccountSettingsDeleteRequest (GCN, AccountNumber, AccountType) to SQL parameters.
    3. Opens the connection and executes the non-query command.
  • Error Handling: Logs exceptions to Console.WriteLine. The database connection is closed in a finally block.

Key Operations and Logic

• Database Interaction: All database operations are delegated to the IDatabaseConnection service.
• SQL Centralization: SQL queries are sourced from SqlStatementConstants.
• Upsert for Add: The AddClient... method implements update-or-insert logic.
• Date Management: DateCreated and DateUpdated are set to DateTime.UtcNow and formatted as "yyyy-MM-dd HH:mm:ss" for database storage.
• Connection Lifecycle: Database connections are explicitly opened before an operation and closed in a finally block to ensure resource release.
• Logging: Current implementation uses Console.WriteLine for error logging. For production, a structured logging framework (e.g., Serilog, NLog, Microsoft.Extensions.Logging) is recommended.
• Backend Agnosticism: The use of IDatabaseConnection allows potential support for different database systems by providing a corresponding implementation.