Syrx.Commanders.Databases Documentation

Database command execution framework for .NET applications

The Syrx.Commanders.Databases ecosystem provides a comprehensive, high-performance database access framework built on top of Dapper. It enables type-safe, configuration-driven database operations with support for multiple database providers, connection management, and command resolution.

Architecture Overview

The Syrx framework follows a layered architecture that separates concerns between application code, command resolution, connection management, and database execution:

┌─────────────────────────────────────────────────────────────┐
│                    Application Layer                        │
│  ┌─────────────────┐    ┌─────────────────┐                 │
│  │   Repository    │    │   Repository    │   ...           │
│  │     Class       │    │     Class       │                 │
│  └─────────────────┘    └─────────────────┘                 │
└─────────────────┬───────────────┬───────────────────────────┘
                  │               │
                  ▼               ▼
┌─────────────────────────────────────────────────────────────┐
│                 Commander Layer                             │
│  ┌─────────────────────────────────────────────────────────┐│
│  │           ICommander<TRepository>                       ││
│  │                                                         ││
│  │  ┌─────────────────────────────────────────────────────┐││
│  │  │       DatabaseCommander<TRepository>                │││
│  │  └─────────────────────────────────────────────────────┘││
│  └─────────────────────────────────────────────────────────┘│
└─────────────────┬───────────────┬───────────────────────────┘
                  │               │
                  ▼               ▼
┌─────────────────────────────────────────────────────────────┐
│              Configuration Layer                            │
│  ┌──────────────────┐    ┌──────────────────┐               │
│  │ Command Settings │    │ Connection       │               │
│  │    Resolution    │    │   Management     │               │
│  └──────────────────┘    └──────────────────┘               │
└─────────────────┬───────────────┬───────────────────────────┘
                  │               │
                  ▼               ▼
┌─────────────────────────────────────────────────────────────┐
│                Database Layer                               │
│  ┌──────────────────┐    ┌──────────────────┐               │
│  │     Dapper       │    │  ADO.NET         │               │
│  │   (Micro-ORM)    │    │ Connections      │               │
│  └──────────────────┘    └──────────────────┘               │
└─────────────────────────────────────────────────────────────┘

Key Architectural Principles

Separation of Concerns: Each layer has distinct responsibilities
Configuration-Driven: SQL commands are externalized from code
Type Safety: Strong typing throughout the execution pipeline
Performance First: Built on Dapper for optimal database performance
Provider Agnostic: Support for multiple database providers
Dependency Injection: First-class DI container support

Core Concepts

Repository Pattern

Repositories define business operations that translate to database commands:

public class UserRepository
{
    private readonly ICommander<UserRepository> _commander;
    
    public UserRepository(ICommander<UserRepository> commander)
    {
        _commander = commander;
    }
    
    // Method name automatically maps to configured command
    public async Task<User> RetrieveAsync(int id, CancellationToken cancellationToken = default)
    {
      var result = await _commander.QueryAsync<User>(new { id }, cancellationToken);
        return result.FirstOrDefault();
    }
}

Command Resolution

Commands are resolved using the pattern: {Namespace}.{ClassName}.{MethodName}

For the example above:

Namespace: MyApp.Repositories
Class: UserRepository
Method: RetrieveAsync
Resolved Command: MyApp.Repositories.UserRepository.RetrieveAsync

Connection Management

Named connection strings are resolved by alias, enabling environment-specific configuration:

{
  "Connections": [
    {
      "Alias": "Primary",
      "ConnectionString": "Server=prod-db;Database=MyApp;..."
    },
    {
      "Alias": "ReadOnly",
      "ConnectionString": "Server=readonly-db;Database=MyApp;..."
    }
  ]
}

Transaction Management

Query Operations: Execute without transactions (read-only)
Execute Operations: Automatically wrapped in transactions with rollback on failure

Package Ecosystem

The Syrx.Commanders.Databases ecosystem consists of several interconnected packages:

Core Packages

Package	Purpose	Dependencies
Syrx.Commanders.Databases	Core command execution engine	Syrx, Dapper
Syrx.Commanders.Databases.Connectors	Database connection abstractions	Syrx.Connectors
Syrx.Commanders.Databases.Settings	Configuration model definitions	Syrx.Settings

Extension Packages

Package	Purpose	Use Case
Syrx.Commanders.Databases.Extensions	DI container registration	Service registration
Syrx.Commanders.Databases.Connectors.Extensions	Connector DI extensions	Connector registration

Configuration Packages

Package	Purpose	Configuration Format
Syrx.Commanders.Databases.Settings.Extensions	Recommended builder pattern APIs	Programmatic
Syrx.Commanders.Databases.Settings.Extensions.Json	Optional JSON configuration loader	JSON files
Syrx.Commanders.Databases.Settings.Extensions.Xml	Optional XML configuration loader	XML files

Utility Packages

Package	Purpose	Use Case
Syrx.Commanders.Databases.Builders	Schema modeling utilities	Code generation, tooling
Syrx.Commanders.Databases.Settings.Readers	Internal command resolution	Framework internals
Syrx.Commanders.Databases.Settings.Readers.Extensions	Reader DI extensions	Internal DI registration

Getting Started

1. Installation

Install the core package and the recommended builder configuration package:

# Core framework
dotnet add package Syrx.Commanders.Databases

# Recommended: builder-based configuration
dotnet add package Syrx.Commanders.Databases.Settings.Extensions

# Optional: file-based configuration loaders
dotnet add package Syrx.Commanders.Databases.Settings.Extensions.Json
dotnet add package Syrx.Commanders.Databases.Settings.Extensions.Xml

# Service registration extensions
dotnet add package Syrx.Commanders.Databases.Extensions

2. Configuration

Create configuration with the fluent builder API:

var settings = new CommanderSettingsBuilder()
  .AddConnectionString("DefaultConnection", connectionString)
  .AddNamespace("MyApp.Repositories", ns => ns
    .AddType("UserRepository", type => type
      .AddCommand("RetrieveAsync", cmd => cmd
        .UseCommandText("SELECT * FROM Users WHERE Id = @id")
        .UseConnectionAlias("DefaultConnection"))
      .AddCommand("CreateAsync", cmd => cmd
        .UseCommandText("INSERT INTO Users (Name, Email) VALUES (@Name, @Email)")
        .UseConnectionAlias("DefaultConnection"))))
  .Build();

Optional JSON file equivalent:

{
  "Connections": [
    {
      "Alias": "DefaultConnection",
      "ConnectionString": "Server=localhost;Database=MyApp;Trusted_Connection=true;"
    }
  ],
  "Namespaces": [
    {
      "Name": "MyApp.Repositories",
      "Types": [
        {
          "Name": "UserRepository",
          "Commands": {
            "RetrieveAsync": {
              "CommandText": "SELECT * FROM Users WHERE Id = @id",
              "ConnectionAlias": "DefaultConnection"
            },
            "CreateAsync": {
              "CommandText": "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)",
              "ConnectionAlias": "DefaultConnection"
            }
          }
        }
      ]
    }
  ]
}

3. Service Registration

Register services in your DI container:

public void ConfigureServices(IServiceCollection services)
{
  // Recommended: register builder-composed settings
  services.AddSingleton<ICommanderSettings>(settings);
  services.UseSyrx(_ => { });

  // Optional file-based registration
  // var configBuilder = new ConfigurationBuilder();
  // services.UseSyrx(builder => builder.UseFile("syrx.json", configBuilder));
    
    // Register your repositories
    services.AddScoped<UserRepository>();
}

4. Repository Implementation

Create your repository classes:

public class UserRepository
{
    private readonly ICommander<UserRepository> _commander;
    
    public UserRepository(ICommander<UserRepository> commander)
    {
        _commander = commander;
    }
    
    public async Task<User> RetrieveAsync(int id, CancellationToken cancellationToken = default)
    {
      var result = await _commander.QueryAsync<User>(new { id }, cancellationToken);
        return result.FirstOrDefault();
    }
    
    public async Task<User> CreateUserAsync(User user, CancellationToken cancellationToken = default)
    {
      return await _commander.ExecuteAsync(user, cancellationToken) ? user : null;
    }
}

Configuration

Configuration Hierarchy

Configuration follows a hierarchical structure:

CommanderSettings
├── Connections[]           # Named connection strings
│   ├── Alias              # Connection name/reference
│   └── ConnectionString   # Actual connection string
└── Namespaces[]           # Namespace-level grouping
    ├── Name               # Namespace name
    └── Types[]            # Type-level grouping
        ├── Name           # Class name
        └── Commands{}     # Method-to-command mappings
            └── [MethodName]
                ├── CommandText      # SQL command
                ├── ConnectionAlias  # Connection reference
                ├── CommandTimeout   # Timeout in seconds
                ├── CommandType      # Text/StoredProcedure/TableDirect
                ├── SplitOn         # Multi-mapping split columns
                └── IsolationLevel  # Transaction isolation

Configuration Sources

JSON Configuration

{
  "Connections": [...],
  "Namespaces": [...]
}

XML Configuration

<CommanderSettings>
  <Connections>...</Connections>
  <Namespaces>...</Namespaces>
</CommanderSettings>

Programmatic Configuration

var settings = new CommanderSettingsBuilder()
    .AddConnectionString("Default", connectionString)
    .AddNamespace("MyApp.Repositories", ns => ns
        .AddType("UserRepository", type => type
            .AddCommand("RetrieveAsync", cmd => cmd
                .UseCommandText("SELECT * FROM Users WHERE Id = @id")
                .UseConnectionAlias("Default"))))
    .Build();

Performance and Threading

Thread Safety

The Syrx.Commanders.Databases framework is fully thread-safe across all components:

Runtime Components: DatabaseCommander<T>, DatabaseConnector, and DatabaseCommandReader use ConcurrentDictionary for caching and maintain no mutable shared state
Builder Classes: CommanderSettingsBuilder, NamespaceSettingBuilder, and TypeSettingBuilder use ConcurrentDictionary internally and are safe for concurrent use
Configuration Models: All settings are immutable records after construction
Service Lifetimes: Can be safely registered as Singleton, Scoped, or Transient

Performance Optimizations

Command Caching

Commands are cached using thread-safe ConcurrentDictionary with the pattern {TypeFullName}.{MethodName}:

// Cached lookup - subsequent calls are extremely fast
var result = await _commander.QueryAsync<User>(new { id = 123 });

Connection String Caching

Connection strings are cached by alias to avoid repeated LINQ operations:

// First call: Searches settings collection
// Subsequent calls: Retrieved from cache
var connection = _connector.CreateConnection(commandSetting);

Recommended Service Lifetimes

// Recommended: Single instance with cached command resolution
services.AddSingleton<ICommander<UserRepository>, DatabaseCommander<UserRepository>>();

// Alternative: Scoped for web applications
services.AddScoped<ICommander<UserRepository>, DatabaseCommander<UserRepository>>();

Best Practices for High Performance

Use Async Methods: Prefer QueryAsync and ExecuteAsync for non-blocking operations
Connection Pooling: Configure appropriate connection pool sizes in connection strings
Command Timeouts: Set realistic timeouts to avoid unnecessary blocking
Parameter Objects: Use strongly-typed parameter objects for better performance

// Good: Strongly-typed parameters
await _commander.QueryAsync<User>(new { id = 123, active = true });

// Avoid: Anonymous objects with complex expressions
await _commander.QueryAsync<User>(new { id = users.Where(u => u.Active).First().Id });

Advanced Topics

Multi-mapping Queries

Handle complex object relationships:

// Two-table join
public async Task<IEnumerable<User>> RetrieveWithProfilesAsync(CancellationToken cancellationToken = default)
{
    return await _commander.QueryAsync<User, Profile, User>(
        (user, profile) => 
        {
            user.Profile = profile;
            return user;
    },
    cancellationToken: cancellationToken);
}

// Configure split column in settings
{
  "SplitOn": "ProfileId"
}

Multiple Result Sets

Process stored procedures returning multiple result sets:

public async Task<DashboardData> RetrieveDashboardAsync(int userId, CancellationToken cancellationToken = default)
{
    var (users, orders, notifications) = await _commander
  .QueryMultipleAsync<User, Order, Notification>(new { userId });
    
    return new DashboardData
    {
        User = users.FirstOrDefault(),
        Orders = orders.ToList(),
        Notifications = notifications.ToList()
    };
}

Connection Strategies

Read/Write Separation

{
  "Connections": [
    {
      "Alias": "Primary",
      "ConnectionString": "Server=write-db;..."
    },
    {
      "Alias": "ReadOnly", 
      "ConnectionString": "Server=read-db;..."
    }
  ]
}

// Use different connections for different operations
{
  "RetrieveUsersAsync": {
    "ConnectionAlias": "ReadOnly"
  },
  "CreateUserAsync": {
    "ConnectionAlias": "Primary"
  }
}

Custom Command Types

Stored Procedures

{
  "ProcessOrderAsync": {
    "CommandText": "sp_ProcessOrder",
    "CommandType": "StoredProcedure",
    "CommandTimeout": 300
  }
}

Table Direct Access

{
  "RetrieveUserTableAsync": {
    "CommandText": "Users",
    "CommandType": "TableDirect"
  }
}

Performance Considerations

Connection Pooling

Utilizes ADO.NET connection pooling automatically

Configure pool settings in connection strings:

"Server=localhost;Database=MyApp;Pooling=true;Max Pool Size=100;Min Pool Size=5;"

Command Caching

DatabaseCommander caches CommandSetting lookups per method
Eliminates repeated LINQ operations on settings collections
Uses ConcurrentDictionary for thread-safe caching

Dapper Optimizations

Leverages Dapper's compiled query caching
Uses buffered queries by default for better performance
Supports unbuffered queries for large result sets when needed

Best Practices

Use Appropriate Connection Lifetimes

// Scoped for web applications
services.AddScoped<UserRepository>();

// Singleton for cached data
services.AddSingleton<ConfigurationRepository>();

Optimize Query Parameters

// Good: Use specific parameters
await _commander.QueryAsync<User>(new { id, isActive = true });

// Avoid: Passing entire objects as parameters when not needed

Configure Appropriate Timeouts

{
  "CommandTimeout": 30,      // Default queries
  "CommandTimeout": 300      // Long-running operations
}

Migration Guide

From Version 2.x to 3.x

Package References
- Update all package references to 3.x versions
- No breaking changes in public APIs
Configuration Changes
- Configuration schema remains compatible
- New optional properties available
Service Registration
- Registration patterns remain the same
- New extension methods available

From Other ORMs

From Entity Framework

// Entity Framework
public async Task<User> RetrieveUserAsync(int id)
{
    return await _context.Users.FindAsync(id);
}

// Syrx
public async Task<User> RetrieveUserAsync(int id)
{
    var result = await _commander.QueryAsync<User>(new { id });
    return result.FirstOrDefault();
}

From Raw ADO.NET

// Raw ADO.NET
public async Task<User> RetrieveUserAsync(int id)
{
    using var connection = new SqlConnection(connectionString);
    using var command = new SqlCommand("SELECT * FROM Users WHERE Id = @id", connection);
    command.Parameters.AddWithValue("@id", id);
    // ... manual mapping
}

// Syrx
public async Task<User> RetrieveUserAsync(int id)
{
    var result = await _commander.QueryAsync<User>(new { id });
    return result.FirstOrDefault();
}

API Reference

Core Interfaces

ICommander<TRepository>: Primary interface for repository operations
IDatabaseConnector: Database connection creation
ICommanderSettings: Configuration container

Core Classes

DatabaseCommander<TRepository>: Main command executor
DatabaseConnector: Connection management
CommanderSettings: Configuration model

Configuration Models

CommandSetting: Individual command configuration
ConnectionStringSetting: Connection string configuration
NamespaceSetting: Namespace-level configuration

Examples

For complete working examples, see:

Contributing

See the Contributing Guide for information about:

Development setup
Coding standards
Pull request process
Testing requirements

Support

Documentation: Complete documentation
Issues: GitHub Issues
Discussions: GitHub Discussions

License

This project is licensed under the MIT License.

Name		Name	Last commit message	Last commit date
Latest commit History 139 Commits
.docs		.docs
.github		.github
.submodules		.submodules
.vscode		.vscode
src		src
tests		tests
.editorconfig		.editorconfig
.gitattributes		.gitattributes
.gitignore		.gitignore
.gitmodules		.gitmodules
Directory.Build.props		Directory.Build.props
LICENSE		LICENSE
Syrx.Commanders.Databases.sln		Syrx.Commanders.Databases.sln
readme.md		readme.md

Folders and files

Latest commit

History

Repository files navigation

Syrx.Commanders.Databases Documentation

Table of Contents

Architecture Overview

Key Architectural Principles

Core Concepts

Repository Pattern

Command Resolution

Connection Management

Transaction Management

Package Ecosystem

Core Packages

Extension Packages

Configuration Packages

Utility Packages

Getting Started

1. Installation

2. Configuration

3. Service Registration

4. Repository Implementation

Configuration

Configuration Hierarchy

Configuration Sources

JSON Configuration

XML Configuration

Programmatic Configuration

Performance and Threading

Thread Safety

Performance Optimizations

Command Caching

Connection String Caching

Recommended Service Lifetimes

Best Practices for High Performance

Advanced Topics

Multi-mapping Queries

Multiple Result Sets

Connection Strategies

Read/Write Separation

Custom Command Types

Stored Procedures

Table Direct Access

Performance Considerations

Connection Pooling

Command Caching

Dapper Optimizations

Best Practices

Migration Guide

From Version 2.x to 3.x

From Other ORMs

From Entity Framework

From Raw ADO.NET

API Reference

Core Interfaces

Core Classes

Configuration Models

Examples

Contributing

Support

License

About

Resources

License

Code of conduct

Contributing

Security policy

Uh oh!

Stars

Watchers

Forks

Releases 4

Packages 0

Uh oh!

Uh oh!

Contributors

Uh oh!

Languages

Packages