Working with Database Class

Summary

The Database class provides comprehensive data access functionality for querying, creating, updating, and deleting entity records within the Magentrix platform. This static class serves as the primary interface for all database operations in Active Controllers, Active Classes, and Triggers.

Where the Database Class is Used

The Database class is available in the following contexts:

Active Controllers: Use Database methods to retrieve and manipulate data for display in Active Pages, process form submissions, and implement custom business logic in controller actions.

Active Classes: Implement reusable data access patterns and business logic that can be called from multiple controllers or triggers, centralizing database operations in maintainable, testable code.

Triggers: Execute database operations in response to entity events (before insert, after update, etc.), enabling automated data validation, related record updates, and complex business rule enforcement.

Example - Active Controller:

public class AccountController : AspxController
{
    public ActionResponse GetPartners()
    {
        var partners = Database.Query<Account>()
            .Where(f => f.Type == "Partner")
            .OrderBy(f => f.Name)
            .ToList();
        
        return Json(partners);
    }
}

Example - Active Class:

public class AccountService
{
    public List<Account> GetHighValueAccounts(decimal minRevenue)
    {
        return Database.Query<Account>()
            .Where(f => f.AnnualRevenue >= minRevenue)
            .OrderByDescending(f => f.AnnualRevenue)
            .ToList();
    }
}

Core Capabilities

The Database class provides the following capabilities:

Querying Records: Execute LINQ-based queries or string-based queries to retrieve entity records with filtering, sorting, pagination, and eager-loading of related entities.

Full-Text Search: Perform full-text searches across searchable entity fields with additional filtering conditions and result ranking based on relevance.

Creating Records: Insert single records or bulk collections with validation, error handling, and optional transactional processing.

Updating Records: Modify existing records individually or in bulk with automatic validation and permission checking.

Upserting Records: Insert new records or update existing records based on ID matching or external ID field matching, streamlining data synchronization scenarios.

Deleting Records: Remove records with soft delete (recycle bin) or permanent delete options, supporting both single and bulk operations.

Restoring Records: Recover soft-deleted records from the recycle bin back to active status.

Counting Records: Efficiently count records matching query criteria without retrieving full record data.

Security Context

All Database operations execute within a security context that determines which records users can access and modify.

User Permission Mode (Default): By default, all queries and operations respect the current user's permissions and sharing rules. Users can only access records they have permission to view, and can only create, update, or delete records where their role grants the appropriate permissions. This ensures that data access follows organizational security policies and record-level sharing rules.

// Respects current user's permissions
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .ToList();

// Create fails if user lacks Create permission
Database.Create(account);

System Mode: Operations can optionally run in system mode by setting the SystemMode flag in DatabaseOptions. System mode bypasses user permissions and sharing rules, executing operations with administrator privileges. System mode should be used cautiously and only when business requirements necessitate operations that transcend normal user permissions, such as automated processes, integration scenarios, or administrative functions.

// Bypasses user permissions - use cautiously
var result = Database.Create(account, new DatabaseOptions 
{ 
    SystemMode = true
});

Always document why system mode is required when using it.