Noundry.Tuxedo

public class ProductService
{
    private readonly IDbConnection _connection;

    // CREATE - Insert new product
    public async Task<int> CreateProductAsync(Product product)
    {
        return await _connection.InsertAsync(product);
    }

    // READ - Get by ID
    public async Task<Product?> GetProductAsync(int id)
    {
        return await _connection.GetAsync<Product>(id);
    }

    // READ - Get all
    public async Task<IEnumerable<Product>> GetAllProductsAsync()
    {
        return await _connection.GetAllAsync<Product>();
    }

    // UPDATE - Update existing
    public async Task<bool> UpdateProductAsync(Product product)
    {
        return await _connection.UpdateAsync(product);
    }

    // DELETE - Remove by ID
    public async Task<bool> DeleteProductAsync(int id)
    {
        return await _connection.DeleteAsync<Product>(id);
    }
}

// Custom query with parameters
public async Task<IEnumerable<Product>> GetActiveProductsAsync(int categoryId)
{
    var sql = @"
        SELECT p.*, c.Name as CategoryName 
        FROM Products p 
        INNER JOIN Categories c ON p.CategoryId = c.Id
        WHERE p.IsActive = @IsActive 
        AND (@CategoryId IS NULL OR p.CategoryId = @CategoryId)
        ORDER BY p.CreatedAt DESC";
        
    return await _connection.QueryAsync<Product>(sql, new 
    { 
        IsActive = true, 
        CategoryId = categoryId == 0 ? null : categoryId 
    });
}

// Paged results for large datasets
public async Task<PagedResult<User>> GetUsersPagedAsync(int page, int pageSize)
{
    return await _connection.GetPagedAsync<User>(page, pageSize);
}

// Execute scalar queries
public async Task<int> GetProductCountAsync(int categoryId)
{
    var sql = "SELECT COUNT(*) FROM Products WHERE CategoryId = @CategoryId";
    return await _connection.ExecuteScalarAsync<int>(sql, new { CategoryId = categoryId });
}

// Transfer funds with transaction
public async Task TransferFundsAsync(int fromId, int toId, decimal amount)
{
    using var transaction = await _connection.BeginTransactionAsync();
    
    try
    {
        // Debit from source account
        await _connection.ExecuteAsync(
            "UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @Id", 
            new { Amount = amount, Id = fromId }, 
            transaction);
        
        // Credit to destination account
        await _connection.ExecuteAsync(
            "UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @Id", 
            new { Amount = amount, Id = toId }, 
            transaction);
        
        // Log the transaction
        await _connection.InsertAsync(new TransactionLog
        {
            FromAccountId = fromId,
            ToAccountId = toId,
            Amount = amount,
            TransactionDate = DateTime.UtcNow
        }, transaction);
        
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

using Noundry.Tuxedo.Contrib;
using System.ComponentModel.DataAnnotations;

[Table("Products")]
public class Product
{
    [Key]
    [ExplicitKey] // For manually assigned IDs
    public int Id { get; set; }
    
    [Required]
    [StringLength(100)]
    public string Name { get; set; }
    
    [Column("product_price")] // Custom column name
    public decimal Price { get; set; }
    
    [ForeignKey("CategoryId")]
    public int CategoryId { get; set; }
    
    [Computed] // Not included in INSERT/UPDATE
    public string CategoryName { get; set; }
    
    [Write(false)] // Read-only property
    public DateTime CreatedAt { get; set; }
}