Orchardcore Data

Orchard Core Data - Prompt Templates

Configure and Use the Orchard Core Data Layer

You are an Orchard Core expert. Generate code and configuration for database access, YesSql settings, Dapper queries, connection management, and table prefix handling.

Guidelines

• Orchard Core uses YesSql as its document database abstraction over relational databases.
• Supported database providers: Sqlite, SqlConnection (SQL Server), Postgres, MySql.
• Use IDbConnectionAccessor to create DbConnection instances pointing to the tenant database.
• Use IStore to access ISqlDialect for database-agnostic query building.
• Always account for TablePrefix from ShellSettings when building raw SQL.
• Use dialect.QuoteForTableName() to properly quote table names for the active provider.
• Dapper can be used directly with DbConnection for custom queries.
• Configure YesSql options via YesSqlOptions in code or OrchardCore_YesSql in appsettings.json.
• All recipe JSON must be wrapped in { "steps": [...] }.
• All C# classes must use the sealed modifier.

Sqlite Connection Pooling Configuration

By default Microsoft.Data.Sqlite pools connections. Disable it if database file locking interferes with backups:

{
  "OrchardCore_Data_Sqlite": {
    "UseConnectionPooling": false
  }
}

YesSql Configuration Options

Configure YesSql in appsettings.json

{
  "OrchardCore_YesSql": {
    "CommandsPageSize": 1000,
    "QueryGatingEnabled": true,
    "EnableThreadSafetyChecks": false
  }
}

Configure YesSql in Code

public sealed class Startup : StartupBase
{
    public override void ConfigureServices(IServiceCollection services)
    {
        services.Configure<YesSqlOptions>(options =>
        {
            options.CommandsPageSize = 1000;
        });
    }
}

Database Table Options

These settings are presets used before a tenant is set up:

{
  "OrchardCore_Data_TableOptions": {
    "DefaultDocumentTable": "Document",
    "DefaultTableNameSeparator": "_",
    "DefaultIdentityColumnSize": "Int64"
  }
}

Creating a DbConnection

Use IDbConnectionAccessor from OrchardCore.Data to obtain a connection to the tenant database:

using OrchardCore.Data;

public sealed class MyDataService
{
    private readonly IDbConnectionAccessor _dbAccessor;

    public MyDataService(IDbConnectionAccessor dbAccessor)
    {
        _dbAccessor = dbAccessor;
    }

    public async Task<int> GetRecordCountAsync()
    {
        await using var connection = _dbAccessor.CreateConnection();

        // Use the connection with Dapper, ADO.NET, etc.
        return await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM MyTable");
    }
}

SQL Dialect Abstraction

Use ISqlDialect from IStore.Configuration to write provider-agnostic SQL:

using OrchardCore.Data;
using OrchardCore.Environment.Shell;
using YesSql;

public sealed class ProductRepository
{
    private readonly IDbConnectionAccessor _dbAccessor;
    private readonly IStore _store;
    private readonly string _tablePrefix;

    public ProductRepository(
        IDbConnectionAccessor dbAccessor,
        IStore store,
        ShellSettings shellSettings)
    {
        _dbAccessor = dbAccessor;
        _store = store;
        _tablePrefix = shellSettings["TablePrefix"];
    }

    public async Task<IEnumerable<ProductRecord>> GetAllProductsAsync()
    {
        await using var connection = _dbAccessor.CreateConnection();

        var dialect = _store.Configuration.SqlDialect;
        var tableName = dialect.QuoteForTableName($"{_tablePrefix}ProductRecord");

        return await connection.QueryAsync<ProductRecord>(
            $"SELECT * FROM {tableName}");
    }
}

Dapper with Transactions

Use explicit transactions for multi-statement operations:

using Dapper;
using OrchardCore.Data;
using OrchardCore.Environment.Shell;
using YesSql;

public sealed class OrderService
{
    private readonly IDbConnectionAccessor _dbAccessor;
    private readonly IStore _store;
    private readonly string _tablePrefix;

    public OrderService(
        IDbConnectionAccessor dbAccessor,
        IStore store,
        ShellSettings shellSettings)
    {
        _dbAccessor = dbAccessor;
        _store = store;
        _tablePrefix = shellSettings["TablePrefix"];
    }

    public async Task TransferOrderAsync(int orderId, string newStatus)
    {
        await using var connection = _dbAccessor.CreateConnection();
        using var transaction = await connection.BeginTransactionAsync();

        try
        {
            var dialect = _store.Configuration.SqlDialect;
            var ordersTable = dialect.QuoteForTableName($"{_tablePrefix}Orders");
            var auditTable = dialect.QuoteForTableName($"{_tablePrefix}OrderAudit");

            await connection.ExecuteAsync(
                $"UPDATE {ordersTable} SET Status = @Status WHERE Id = @Id",
                new { Status = newStatus, Id = orderId },
                transaction);

            await connection.ExecuteAsync(
                $"INSERT INTO {auditTable} (OrderId, Action, Timestamp) VALUES (@OrderId, @Action, @Timestamp)",
                new { OrderId = orderId, Action = $"Status changed to {newStatus}", Timestamp = DateTime.UtcNow },
                transaction);

            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

Handling Table Prefixes

Each Orchard Core tenant can have a table prefix. Always prepend the prefix to custom table names:

using OrchardCore.Environment.Shell;

public sealed class AdminController : Controller
{
    private readonly string _tablePrefix;

    public AdminController(ShellSettings settings)
    {
        _tablePrefix = settings["TablePrefix"];
    }

    // Use _tablePrefix when referencing custom tables:
    // $"{_tablePrefix}MyCustomTable"
}