DbConnectionExtensions

Static extension methods for IDbConnection following FP patterns. All methods return Result types for explicit error handling.

public static class DbConnectionExtensions

Example

using var connection = new SqliteConnection("Data Source=mydb.db");
connection.Open();

// Execute a query with mapping
var result = connection.Query<Customer>(
    sql: "SELECT Id, Name FROM Customers WHERE Active = 1",
    mapper: reader => new Customer(
        Id: reader.GetInt32(0),
        Name: reader.GetString(1)
    )
);

// Pattern match on the result
var customers = result switch
{
    Result<IReadOnlyList<Customer>, SqlError>.Ok<IReadOnlyList<Customer>, SqlError> ok => ok.Value,
    Result<IReadOnlyList<Customer>, SqlError>.Error<IReadOnlyList<Customer>, SqlError> err => throw new Exception(err.Value.Message),
    _ => throw new InvalidOperationException()
};

Methods

Query<T>(IDbConnection, string, IEnumerable<IDataParameter>?, Func<IDataReader, T>?)

public static Result<IReadOnlyList<T>, SqlError> Query<T>(this IDbConnection connection, string sql, IEnumerable<IDataParameter>? parameters = null, Func<IDataReader, T>? mapper = null)

Execute a query and return results.

Parameters:

Returns: SqlError> - Result with list of T or error

Example

var result = connection.Query<Product>(
    sql: "SELECT * FROM Products WHERE Price > @minPrice",
    parameters: [new SqliteParameter("@minPrice", 10.00)],
    mapper: r => new Product(r.GetInt32(0), r.GetString(1), r.GetDecimal(2))
);

Execute(IDbConnection, string, IEnumerable<IDataParameter>?)

public static Result<int, SqlError> Execute(this IDbConnection connection, string sql, IEnumerable<IDataParameter>? parameters = null)

Execute a non-query command (INSERT, UPDATE, DELETE).

Parameters:

Returns: SqlError> - Result with rows affected or error

Example

var result = connection.Execute(
    sql: "UPDATE Products SET Price = @price WHERE Id = @id",
    parameters: [
        new SqliteParameter("@price", 19.99),
        new SqliteParameter("@id", 42)
    ]
);

if (result is Result<int, SqlError>.Ok<int, SqlError> ok)
    Console.WriteLine($"Updated {ok.Value} rows");

Scalar<T>(IDbConnection, string, IEnumerable<IDataParameter>?)

public static Result<T?, SqlError> Scalar<T>(this IDbConnection connection, string sql, IEnumerable<IDataParameter>? parameters = null)

Execute a scalar command

Parameters:

Returns: SqlError> - Result with scalar value or error

GetRecords<T>(IDbConnection, SelectStatement, Func<SelectStatement, Result<string, SqlError>>, Func<IDataReader, T>, IEnumerable<IDataParameter>?)

public static Result<IReadOnlyList<T>, SqlError> GetRecords<T>(this IDbConnection connection, SelectStatement statement, Func<SelectStatement, Result<string, SqlError>> sqlGenerator, Func<IDataReader, T> mapper, IEnumerable<IDataParameter>? parameters = null)

Execute a SelectStatement by generating platform-specific SQL and mapping results.

Parameters:

Returns: SqlError> - Result with list of T or SqlError on failure