Modern, testable database access library using DbProviderFactory with Result monad pattern for explicit error handling. Features IDbCommandExecutor interface for easy mocking, command factory pattern for separation of concerns, and fluent parameter API. Supports SQL Server, PostgreSQL, MySQL, Oracle, SQLite, and ODBC for universal database connectivity.
$ dotnet add package Voyager.DBConnectionVoyager.DBConnection is a library providing a structured and type-safe way to connect to SQL databases using DbProviderFactory. It implements the Command Factory pattern to encapsulate database operations and provides a clean abstraction over ADO.NET.
Key Features:
SqlCallEvent provides rich telemetry for logging, monitoring, and custom analyticsThis repository provides multiple NuGet packages in a monorepo structure:
| Voyager.DBConnection.Oracle | Oracle Database-specific implementation with OracleDbCommandExecutor and error mapping |
| Voyager.DBConnection.PostgreSql | PostgreSQL-specific implementation with PostgreSqlDbCommandExecutor and error mapping |
| Voyager.DBConnection.MySql | MySQL-specific implementation with MySqlDbCommandExecutor and error mapping |
| Voyager.DBConnection.Logging | Logging extension with support for both DbCommandExecutor and Connection |
All packages are developed and versioned together:
dotnet build builds all packages at onceThe project includes Docker Compose configuration for testing against multiple database providers:
See README.Docker.md for detailed Docker setup and usage instructions.
Quick Start:
# Start all databases
docker-compose up -d
# Initialize test schema (SQL Server)
.\scripts\run-init-script.ps1 -Database mssql
# Run integration tests
dotnet test --filter "Category=Integration"
# Stop all databases
docker-compose downThe modern way to use Voyager.DBConnection is through the DbCommandExecutor class with the IDbCommandExecutor interface. This approach provides:
IDbCommandExecutor interface - no need for complex database mockingIDbCommandFactory) keep command construction separate from execution logicResult<T> - no hidden exceptions or try-catch blocks// Create executor
var errorPolicy = new SqlServerErrorPolicy(); // Maps SQL exceptions to typed errors
var executor = new DbCommandExecutor(database, errorPolicy);
// Execute with fluent API and Result monad
executor.ExecuteNonQuery(
"InsertUser",
cmd => cmd
.WithInputParameter("Username", DbType.String, 50, username)
.WithInputParameter("Email", DbType.String, 100, email)
.WithOutputParameter("UserId", DbType.Int32, 0)
)
.Tap(rowsAffected => Console.WriteLine($"User created, {rowsAffected} rows affected"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));Better Testability: With IDbCommandExecutor interface, you can easily create unit tests:
// Unit test with mock - no real database needed
var mockExecutor = new Mock<IDbCommandExecutor>();
mockExecutor.Setup(x => x.ExecuteNonQuery(It.IsAny<IDbCommandFactory>(), null))
.Returns(Result<int>.Success(1));
var service = new UserService(mockExecutor.Object);
var result = service.CreateUser("john", "john@example.com");
Assert.That(result.IsSuccess, Is.True);Separation of Responsibilities: Command factories encapsulate command construction:
// Command factory - single responsibility: build the command
public class CreateUserCommandFactory : IDbCommandFactory
{
private readonly string username;
private readonly string email;
public CreateUserCommandFactory(string username, string email)
{
this.username = username;
this.email = email;
}
public DbCommand ConstructDbCommand(IDatabase db)
{
return db.GetStoredProcCommand("CreateUser")
.WithInputParameter("Username", DbType.String, 50, username)
.WithInputParameter("Email", DbType.String, 100, email);
}
}
// Service class - single responsibility: business logic
public class UserService
{
private readonly IDbCommandExecutor executor;
public UserService(IDbCommandExecutor executor) => this.executor = executor;
public Result<int> CreateUser(string username, string email)
{
var factory = new CreateUserCommandFactory(username, email);
return executor.ExecuteNonQuery(factory);
}
}The DbCommandExecutor uses the Result monad pattern to encapsulate either a successful value or an error, eliminating the need for exception handling.
Result<T> which contains either a success value or an errorTValue → Result<TValue> (success)Error → Result<TValue> (failure)Result<T>.Success() or Result<T>.Failure()Bind, Map, Ensure, OrElsevar executor = new DbCommandExecutor(database, errorPolicy);
var factory = new SetPilotiDoPowiadomieniaFactory(id, date, user, report);
Result<int> result = executor.ExecuteNonQuery(factory)
.Tap(rows => Console.WriteLine($"Rows affected: {rows}"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));Result<int> result = executor.ExecuteNonQuery(
db => db.GetStoredProcCommand("MyStoredProc"),
cmd => Console.WriteLine("Command executed")
);Result<int> result = executor.ExecuteNonQuery(
"MyStoredProc",
cmd => cmd
.WithInputParameter("Param1", DbType.String, 50, value1)
.WithInputParameter("Param2", DbType.Int32, value2)
.WithOutputParameter("RowCount", DbType.Int32, 0),
cmd => Console.WriteLine($"Command executed, rows affected: {cmd.GetParameterValue<int>("RowCount")}")
);executor.ExecuteScalar(
"GetUserCount",
cmd => cmd.WithInputParameter("Active", DbType.Boolean, true)
)
.Tap(value =>
{
int count = Convert.ToInt32(value);
Console.WriteLine($"User count: {count}");
})
.TapError(error => Console.WriteLine($"Error: {error.Message}"));// Using IResultsConsumer (classic approach)
var consumer = new RegionalSaleCommand(request);
executor.ExecuteReader(consumer, consumer)
.Tap(items =>
{
foreach (var item in items)
{
Console.WriteLine($"Sale: {item.GidRezerwacji}");
}
})
.TapError(error => Console.WriteLine($"Error: {error.Message}"));
// Using Func<IDataReader, TValue> (functional approach - recommended for simple cases)
executor.ExecuteReader(
"GetUsers",
cmd => cmd.WithInputParameter("Active", DbType.Boolean, true),
reader =>
{
var users = new List<User>();
while (reader.Read())
{
users.Add(new User
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Email = reader.GetString(2)
});
}
return users;
}
)
.Tap(users => users.ForEach(u => Console.WriteLine($"{u.Name}: {u.Email}")))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));
// Async version with functional approach
### DataReaderExtensions - Simplified Data Extraction
**New in v4.5+**: LINQ-like extension methods for `IDataReader` eliminate boilerplate code:
```csharp
// Before: Manual loop
var users = new List<User>();
while (reader.Read())
{
users.Add(new User { Id = reader.GetInt32(0), Name = reader.GetString(1) });
}
return users.ToArray();
// After: FetchAll extension
return reader.FetchAll(r => new User
{
Id = r.GetInt32(0),
Name = r.GetString(1)
});Available Methods:
| Method | Returns | Use Case | Example |
|---|---|---|---|
AsEnumerable<T>() | IEnumerable<T> | LINQ operations | reader.AsEnumerable(r => ...).Where(...).Take(10) |
FetchAll<T>() | T[] | Get all rows as array | reader.FetchAll(r => new User { ... }) |
FetchList<T>() | List<T> | Get modifiable list | reader.FetchList(r => new User { ... }) |
FetchFirst<T>() | T | First row (required) | reader.FetchFirst(r => new Config { ... }) |
FetchFirstOrDefault<T>() | T | First row (optional) | reader.FetchFirstOrDefault(r => ...) |
FetchSingle<T>() | T | Exactly one row | reader.FetchSingle(r => new User { ... }) |
FetchSingleOrDefault<T>() | T | 0-1 row | reader.FetchSingleOrDefault(r => ...) |
Usage with DbCommandExecutor:
// Get all users
var users = executor.ExecuteReader(
db => db.GetSqlCommand("SELECT UserId, Username, Email FROM Users"),
reader => reader.FetchAll(r => new User
{
Id = r.GetInt32(r.GetOrdinal("UserId")),
Name = r.GetString(r.GetOrdinal("Username")),
Email = r.GetString(r.GetOrdinal("Email"))
})
);
// LINQ filtering with AsEnumerable (NEW!)
var activeUsers = executor.ExecuteReader(
db => db.GetSqlCommand("SELECT * FROM Users"),
reader => reader
.AsEnumerable(r => new User
{
Id = r.GetInt32(0),
Name = r.GetString(1),
IsActive = r.GetBoolean(2)
})
.Where(u => u.IsActive)
.Take(10)
.ToList()
);
// Get single user by ID (throws if not found or duplicate)
var user = executor.ExecuteReader(
"GetUserById",
cmd => cmd.WithInputParameter("userId", DbType.Int32, 42),
reader => reader.FetchSingle(r => new User
{
Id = r.GetInt32(0),
Name = r.GetString(1),
Email = r.GetString(2)
})
);
// Optional config value
var theme = executor.ExecuteReader(
"GetUserPreference",
cmd => cmd
.WithInputParameter("userId", DbType.Int32, userId)
.WithInputParameter("key", DbType.String, "Theme"),
reader => reader.FetchFirstOrDefault(r => r.GetString(0))
);
var userTheme = theme.Value ?? "Dark"; // Use default if not set📖 Complete DataReaderExtensions Guide - Examples, best practices, and migration guide
// Async version with functional approach var result = await executor.ExecuteReaderAsync( "GetActiveUsers", cmd => cmd.WithInputParameter("MinAge", DbType.Int32, 18), reader => { var users = new List<User>(); while (reader.Read()) { users.Add(new User { Id = reader.GetInt32(reader.GetOrdinal("UserId")), Name = reader.GetString(reader.GetOrdinal("Name")), Age = reader.GetInt32(reader.GetOrdinal("Age")) }); } return users; }, afterCall: null, cancellationToken );
#### ExecuteAndBind - Binding Results
```csharp
executor.ExecuteAndBind(
factory,
cmd =>
{
int outputId = cmd.GetParameterValue<int>("OutputId");
if (outputId > 0)
return outputId; // Implicit conversion to Result<int>
return new Error("No output parameter"); // Implicit conversion to Result<int>
}
)
.Tap(id => Console.WriteLine($"Output ID: {id}"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));
// With CancellationToken
CancellationToken cancellationToken = GetCancellationToken();
Result<int> result = await executor.ExecuteNonQueryAsync(
factory,
afterCall: null,
cancellationToken
);
// ExecuteReaderAsync
Result<SaleItem[]> result = await executor.ExecuteReaderAsync(
"GetSalesReport",
cmd => cmd
.WithInputParameter("DateFrom", DbType.DateTime, dateFrom)
.WithInputParameter("DateTo", DbType.DateTime, dateTo),
consumer,
afterCall: null,
cancellationToken
);All execution methods (ExecuteNonQuery, ExecuteScalar, ExecuteReader, ExecuteAndBind) support three patterns:
ExecuteReader additionally supports two ways to process results:
Each pattern has both synchronous and asynchronous versions, with async versions supporting CancellationToken.
Example - All ExecuteReader patterns:
// Pattern 1: IDbCommandFactory + IResultsConsumer
var factory = new GetUsersCommandFactory(activeOnly: true);
var consumer = new UserListConsumer();
Result<List<User>> result1 = executor.ExecuteReader(factory, consumer);
// Pattern 2: IDbCommandFactory + Func<IDataReader, TValue>
var factory2 = new GetUsersCommandFactory(activeOnly: true);
Result<List<User>> result2 = executor.ExecuteReader(factory2, reader =>
{
var users = new List<User>();
while (reader.Read())
users.Add(MapUser(reader));
return users;
});
// Pattern 3: Func<IDatabase, DbCommand> + IResultsConsumer
Result<List<User>> result3 = executor.ExecuteReader(
db => db.GetStoredProcCommand("GetUsers")
.WithInputParameter("Active", DbType.Boolean, true),
new UserListConsumer()
);
// Pattern 4: Func<IDatabase, DbCommand> + Func<IDataReader, TValue>
Result<List<User>> result4 = executor.ExecuteReader(
db => db.GetStoredProcCommand("GetUsers")
.WithInputParameter("Active", DbType.Boolean, true),
reader =>
{
var users = new List<User>();
while (reader.Read())
users.Add(MapUser(reader));
return users;
}
);
// Pattern 5: string procedureName + IResultsConsumer
Result<List<User>> result5 = executor.ExecuteReader(
"GetUsers",
cmd => cmd.WithInputParameter("Active", DbType.Boolean, true),
new UserListConsumer()
);
// Pattern 6: string procedureName + Func<IDataReader, TValue> (most concise)
Result<List<User>> result6 = executor.ExecuteReader(
"GetUsers",
cmd => cmd.WithInputParameter("Active", DbType.Boolean, true),
reader =>
{
var users = new List<User>();
while (reader.Read())
users.Add(MapUser(reader));
return users;
}
);
static User MapUser(IDataReader reader) => new User
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Email = reader.GetString(2)
};The Result type provides powerful functional programming methods for validation, transformation, and error handling.
Use Ensure to validate input parameters before executing database operations. Use typed errors (Error.ValidationError, Error.BusinessError, etc.) to categorize failures:
// Validate user input before database operation
Result<User> CreateUser(string username, string email, int age)
{
return Result<User>.Success(new User { Username = username, Email = email, Age = age })
.Ensure(u => !string.IsNullOrWhiteSpace(u.Username),
Error.ValidationError("User.InvalidUsername", "Username cannot be empty"))
.Ensure(u => u.Username.Length >= 3,
Error.ValidationError("User.UsernameTooShort", "Username must be at least 3 characters"))
.Ensure(u => u.Email.Contains("@"),
Error.ValidationError("User.InvalidEmail", "Invalid email format"))
.Ensure(u => u.Age >= 18,
Error.BusinessError("User.AgeLimitNotMet", "User must be 18 or older"))
.Bind(user => executor.ExecuteAndBind(
db => db.GetStoredProcCommand("CreateUser")
.WithInputParameter("Username", DbType.String, 50, user.Username)
.WithInputParameter("Email", DbType.String, 100, user.Email)
.WithInputParameter("Age", DbType.Int32, user.Age)
.WithOutputParameter("UserId", DbType.Int32, 0),
cmd => new User
{
UserId = cmd.GetParameterValue<int>("UserId"),
Username = user.Username,
Email = user.Email,
Age = user.Age
}
));
}
// Usage
CreateUser("john", "john@example.com", 25)
.Tap(user => Console.WriteLine($"User created: {user.UserId}"))
.TapError(error => Console.WriteLine($"[{error.Type}] {error.Code}: {error.Message}"));Use Map to transform successful results without unwrapping:
// Map database result to DTO
executor.ExecuteAndBind(
db => db.GetStoredProcCommand("GetUserById")
.WithInputParameter("UserId", DbType.Int32, userId),
cmd => new User
{
UserId = cmd.GetParameterValue<int>("UserId"),
Username = cmd.GetParameterValue<string>("Username"),
Email = cmd.GetParameterValue<string>("Email"),
IsActive = cmd.GetParameterValue<bool>("IsActive")
}
)
.Map(user => new UserDTO // Transform User to UserDTO
{
Id = user.UserId,
DisplayName = user.Username.ToUpper(),
ContactEmail = user.Email,
Status = user.IsActive ? "Active" : "Inactive"
})
.Tap(dto => Console.WriteLine($"User: {dto.DisplayName} ({dto.Status})"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));Use Bind to chain dependent database operations:
// Create order and then add order items
Result<Order> CreateOrderWithItems(int userId, List<OrderItem> items)
{
return executor.ExecuteAndBind(
db => db.GetStoredProcCommand("CreateOrder")
.WithInputParameter("UserId", DbType.Int32, userId)
.WithInputParameter("OrderDate", DbType.DateTime, DateTime.Now)
.WithOutputParameter("OrderId", DbType.Int32, 0),
cmd => new Order
{
OrderId = cmd.GetParameterValue<int>("OrderId"),
UserId = userId,
OrderDate = DateTime.Now
}
)
.Bind(order =>
{
// Chain: Add items to the created order
var itemResults = items.Select(item =>
executor.ExecuteNonQuery(
db => db.GetStoredProcCommand("AddOrderItem")
.WithInputParameter("OrderId", DbType.Int32, order.OrderId)
.WithInputParameter("ProductId", DbType.Int32, item.ProductId)
.WithInputParameter("Quantity", DbType.Int32, item.Quantity)
.WithInputParameter("Price", DbType.Decimal, item.Price)
)
).ToList();
// If any item failed, return error; otherwise return order
var failedItem = itemResults.FirstOrDefault(r => !r.IsSuccess);
return failedItem != null
? failedItem.Error // Implicit conversion Error -> Result<Order>
: order; // Implicit conversion Order -> Result<Order>
});
}
// Usage with validation
CreateOrderWithItems(userId, orderItems)
.Ensure(order => order.OrderId > 0,
Error.BusinessError("Order.InvalidId", "Invalid order ID"))
.Tap(order => Console.WriteLine($"Order created: {order.OrderId}"))
.TapError(error => Console.WriteLine($"Order creation failed: {error.Message}"));Use OrElse to provide fallback values or alternative operations. Important: Use TapError before OrElse for logging, as OrElse always returns success and subsequent TapError won't execute:
// Try to get user from database, fallback to default user
Result<User> GetUserOrDefault(int userId)
{
return executor.ExecuteAndBind(
db => db.GetStoredProcCommand("GetUserById")
.WithInputParameter("UserId", DbType.Int32, userId),
cmd =>
{
var id = cmd.GetParameterValue<int>("UserId");
if (id == 0)
return Error.NotFoundError("User.NotFound", $"User {userId} not found");
return new User
{
UserId = id,
Username = cmd.GetParameterValue<string>("Username"),
Email = cmd.GetParameterValue<string>("Email")
};
}
)
.TapError(error => _logger.LogWarning($"User not found, using guest: {error.Message}"))
.OrElse(() => new User // Fallback to guest user
{
UserId = 0,
Username = "Guest",
Email = "guest@example.com"
});
}
// Usage - TapError won't execute because OrElse always succeeds
GetUserOrDefault(userId)
.Tap(user => Console.WriteLine($"User: {user.Username}"));
// Fallback to alternative database query
Result<Product> GetProductWithFallback(int productId)
{
return executor.ExecuteAndBind(
db => db.GetStoredProcCommand("GetActiveProduct")
.WithInputParameter("ProductId", DbType.Int32, productId),
cmd => MapProduct(cmd)
)
.TapError(error => _logger.LogInformation($"Active product not found, trying archived: {error.Message}"))
.OrElse(() =>
// Fallback: Try to get archived product
executor.ExecuteAndBind(
db => db.GetStoredProcCommand("GetArchivedProduct")
.WithInputParameter("ProductId", DbType.Int32, productId),
cmd => MapProduct(cmd)
)
);
}
static Product MapProduct(DbCommand cmd) => new Product
{
ProductId = cmd.GetParameterValue<int>("ProductId"),
Name = cmd.GetParameterValue<string>("Name"),
Price = cmd.GetParameterValue<decimal>("Price")
};// Complete workflow with validation, mapping, and error logging
Result<OrderSummary> ProcessOrder(OrderRequest request)
{
// Step 1: Validate input
return Result<OrderRequest>.Success(request)
.Ensure(r => r.UserId > 0,
Error.ValidationError("Order.InvalidUserId", "Invalid user ID"))
.Ensure(r => r.Items?.Count > 0,
Error.ValidationError("Order.EmptyItems", "Order must contain at least one item"))
.Ensure(r => r.Items.All(i => i.Quantity > 0),
Error.ValidationError("Order.InvalidQuantity", "All items must have positive quantity"))
// Step 2: Execute database operation
.Bind(validRequest => executor.ExecuteAndBind(
db => db.GetStoredProcCommand("CreateOrder")
.WithInputParameter("UserId", DbType.Int32, validRequest.UserId)
.WithInputParameter("TotalAmount", DbType.Decimal, validRequest.TotalAmount)
.WithOutputParameter("OrderId", DbType.Int32, 0)
.WithOutputParameter("OrderNumber", DbType.String, 50),
cmd => new OrderResult
{
OrderId = cmd.GetParameterValue<int>("OrderId"),
OrderNumber = cmd.GetParameterValue<string>("OrderNumber"),
TotalAmount = validRequest.TotalAmount
}
))
// Step 3: Ensure database operation succeeded
.Ensure(result => result.OrderId > 0,
Error.DatabaseError("Order.CreationFailed", "Order creation failed"))
// Step 4: Transform to summary DTO
.Map(orderResult => new OrderSummary
{
OrderId = orderResult.OrderId,
OrderNumber = orderResult.OrderNumber,
Total = orderResult.TotalAmount,
Status = "Created",
CreatedDate = DateTime.Now
});
}
// Usage - TapError is useful here since we don't use OrElse
ProcessOrder(orderRequest)
.Tap(summary =>
{
_logger.LogInformation($"Order processed: {summary.OrderNumber} - ${summary.Total}");
Console.WriteLine($"Order #{summary.OrderNumber} created successfully");
})
.TapError(error =>
{
_logger.LogError($"[{error.Type}] {error.Code}: {error.Message}");
Console.WriteLine($"Failed to process order: {error.Message}");
});
// Alternative: Using fallback with TapError BEFORE OrElse for logging
Result<OrderSummary> ProcessOrderWithFallback(OrderRequest request)
{
return ProcessOrder(request)
.TapError(error => _logger.LogWarning($"Order creation failed, creating draft: {error.Message}"))
.OrElse(() => new OrderSummary // Fallback to draft order
{
OrderId = 0,
OrderNumber = "DRAFT",
Total = request.TotalAmount,
Status = "Draft",
CreatedDate = DateTime.Now
});
}
// Usage - After OrElse, TapError won't execute
ProcessOrderWithFallback(orderRequest)
.Tap(summary => Console.WriteLine($"Order: {summary.OrderNumber} ({summary.Status})"));These patterns enable:
The library uses typed errors to categorize failures:
Error.ValidationError: Input validation failures (empty fields, format errors)Error.BusinessError: Business rule violations (age restrictions, insufficient funds)Error.DatabaseError: Database operation failures (mapped from exceptions via IMapErrorPolicy)Error.NotFoundError: Entity not found errorsError.ConflictError: Concurrency or uniqueness violationsError.UnauthorizedError: Authentication failuresError.PermissionError: Authorization failuresError.TimeoutError: Operation timeoutError.CancelledError: Operation cancelledError.UnavailableError: Service temporarily unavailableError.UnexpectedError: Unexpected system errorsThe DbCommandExecutor uses IMapErrorPolicy to map database exceptions to domain-specific errors. This ensures that low-level database exceptions are converted to meaningful, typed errors.
public interface IMapErrorPolicy
{
Error MapError(Exception ex);
}
// Example: Custom error policy for SQL Server
public class SqlServerErrorPolicy : IMapErrorPolicy
{
public Error MapError(Exception ex)
{
return ex switch
{
// Unique constraint violations - conflicts that can be handled by application logic
SqlException sqlEx when sqlEx.Number == 2627 || sqlEx.Number == 2601 =>
Error.ConflictError("Database.UniqueConstraint", "Record already exists"),
// Foreign key constraint violations - business rule violations, not retryable
SqlException sqlEx when sqlEx.Number == 547 =>
Error.BusinessError("Database.ForeignKeyViolation", "Referenced record does not exist or cannot be deleted due to existing references"),
// Deadlock - transient error, can be retried
SqlException sqlEx when sqlEx.Number == 1205 =>
Error.DatabaseError("Database.Deadlock", "Deadlock detected, operation can be retried"),
// Timeout - transient error, can be retried
SqlException sqlEx when sqlEx.Number == -2 =>
Error.TimeoutError("Database.Timeout", "Database operation timed out"),
TimeoutException =>
Error.TimeoutError("Database.ConnectionTimeout", ex.Message),
InvalidOperationException =>
Error.DatabaseError("Database.InvalidOperation", ex.Message),
_ =>
Error.UnexpectedError("Database.UnexpectedError", ex.Message)
};
}
}
// Usage: Create executor with custom error policy
var errorPolicy = new SqlServerErrorPolicy();
var executor = new DbCommandExecutor(database, errorPolicy);
// Database errors are automatically mapped
executor.ExecuteNonQuery(
"InsertUser",
cmd => cmd
.WithInputParameter("Email", DbType.String, 100, email)
.WithInputParameter("Username", DbType.String, 50, username)
)
.TapError(error =>
{
// Error is typed based on the exception - handle differently based on whether retry makes sense
switch (error.Type)
{
case ErrorType.Conflict:
_logger.LogWarning($"Duplicate user: {error.Message}");
// Conflict (unique constraint) - can be handled by application logic, no retry
break;
case ErrorType.Business:
_logger.LogWarning($"Business rule violation: {error.Message}");
// Business error (e.g., foreign key violation) - indicates domain rule violation, no retry
break;
case ErrorType.Timeout:
_logger.LogError($"Database timeout: {error.Message}");
// Timeout - transient error, can be retried
break;
case ErrorType.Database:
// Database errors (e.g., deadlock) - transient errors that can be retried
if (error.Code.Contains("Deadlock"))
{
_logger.LogWarning($"Deadlock detected, will retry: {error.Message}");
}
else
{
_logger.LogError($"Database error: {error.Message}");
}
break;
default:
_logger.LogError($"Unexpected error: {error.Message}");
break;
}
});
// Example: Retry logic for transient errors only
Result<int> InsertUserWithRetry(string email, string username)
{
int maxRetries = 3;
int attempt = 0;
while (attempt < maxRetries)
{
var result = executor.ExecuteNonQuery(
"InsertUser",
cmd => cmd
.WithInputParameter("Email", DbType.String, 100, email)
.WithInputParameter("Username", DbType.String, 50, username)
);
if (result.IsSuccess)
return result;
// Only retry for transient errors (Database, Timeout)
var shouldRetry = result.Error.Type == ErrorType.Database ||
result.Error.Type == ErrorType.Timeout;
if (!shouldRetry || attempt >= maxRetries - 1)
{
_logger.LogError($"Operation failed: {result.Error.Message}");
return result;
}
attempt++;
_logger.LogWarning($"Transient error, retrying (attempt {attempt}/{maxRetries}): {result.Error.Message}");
Thread.Sleep(TimeSpan.FromMilliseconds(100 * attempt)); // Exponential backoff
}
return Error.UnexpectedError("Retry.MaxAttemptsExceeded", "Maximum retry attempts exceeded");
}Error Type Guidelines for Retry Logic:
Retryable (transient errors):
ErrorType.Database - Deadlocks, connection issuesErrorType.Timeout - Operation timeoutsErrorType.Unavailable - Service temporarily unavailableNot Retryable (permanent errors):
ErrorType.Validation - Input validation failuresErrorType.Business - Business rule violations (including foreign key constraints)ErrorType.Conflict - Unique constraint violationsErrorType.NotFound - Entity not foundErrorType.Permission - Authorization failuresErrorType.Unauthorized - Authentication failuresIf no custom policy is provided, DbCommandExecutor uses DefaultMapError which maps all exceptions to generic database errors.
The library provides fluent API extension methods for managing DbCommand parameters through the DbCommandExtensions class. These extensions simplify parameter handling and automatically handle parameter naming conventions for different database providers.
The extensions automatically detect and apply the correct parameter prefix:
@ prefix: prefix// Basic input parameter
command.WithInputParameter("UserId", DbType.Int32, 123);
// Input parameter with size (for strings, etc.)
command.WithInputParameter("UserName", DbType.String, 50, "John Doe");
// Fluent chaining
var result = executor.ExecuteNonQuery(
db => db.GetStoredProcCommand("UpdateUser")
.WithInputParameter("UserId", DbType.Int32, userId)
.WithInputParameter("UserName", DbType.String, 50, userName)
.WithInputParameter("Email", DbType.String, 100, email)
);// Add output parameter
command.WithOutputParameter("NewId", DbType.Int32, 0);
// Using with ExecuteAndBind to read output
var result = executor.ExecuteAndBind(
db => db.GetStoredProcCommand("CreateUser")
.WithInputParameter("UserName", DbType.String, 50, "John Doe")
.WithOutputParameter("NewId", DbType.Int32, 0),
cmd => cmd.GetParameterValue<int>("NewId") // Implicit conversion to Result<int>
);// Add input/output parameter
command.WithInputOutputParameter("Counter", DbType.Int32, currentCount);
// With size specification
command.WithInputOutputParameter("Status", DbType.String, 20, "Pending");
// Example usage
var result = executor.ExecuteAndBind(
db => db.GetStoredProcCommand("ProcessOrder")
.WithInputParameter("OrderId", DbType.Int32, orderId)
.WithInputOutputParameter("Status", DbType.String, 20, "Processing"),
cmd => cmd.GetParameterValue<string>("Status") // Implicit conversion to Result<string>
);// Full parameter configuration for standard types
command.WithParameter(
name: "CustomParam",
dbType: DbType.Decimal,
size: 18,
direction: ParameterDirection.ReturnValue,
value: 0
);
// For non-standard parameters (table-valued, structured types, etc.)
// use SqlParameter directly
var result = executor.ExecuteNonQuery(
"BulkInsertProducts",
cmd =>
{
// Table-valued parameter - requires SqlParameter
var tableParam = new SqlParameter("@ProductTable", SqlDbType.Structured)
{
TypeName = "dbo.ProductTableType",
Value = productDataTable
};
cmd.Parameters.Add(tableParam);
// Regular parameters - use fluent API
cmd.WithInputParameter("BatchId", DbType.Int32, batchId)
.WithInputParameter("ProcessedBy", DbType.String, 50, userName);
}
);// Read parameter value with type conversion
int newId = command.GetParameterValue<int>("NewId");
string status = command.GetParameterValue<string>("Status");
decimal? amount = command.GetParameterValue<decimal?>("Amount");
// Returns default(T) for null/DBNull values
int count = command.GetParameterValue<int>("Count"); // Returns 0 if null// Read parameter value as object
object value = command.GetParameterValue("SomeParam");
// Manual type conversion
if (value != null && value != DBNull.Value)
{
int intValue = Convert.ToInt32(value);
}var executor = new DbCommandExecutor(database, errorPolicy);
// Example 1: Insert with output parameter
executor.ExecuteAndBind(
db => db.GetStoredProcCommand("InsertProduct")
.WithInputParameter("ProductName", DbType.String, 100, "Laptop")
.WithInputParameter("Price", DbType.Decimal, 999.99m)
.WithInputParameter("CategoryId", DbType.Int32, 5)
.WithOutputParameter("ProductId", DbType.Int32, 0),
cmd => cmd.GetParameterValue<int>("ProductId") // Implicit conversion to Result<int>
)
.Tap(productId => Console.WriteLine($"New Product ID: {productId}"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));
// Example 2: Update with input/output parameter
executor.ExecuteAndBind(
db => db.GetStoredProcCommand("UpdateInventory")
.WithInputParameter("ProductId", DbType.Int32, productId)
.WithInputOutputParameter("Quantity", DbType.Int32, requestedQuantity),
cmd => cmd.GetParameterValue<int>("Quantity") // Implicit conversion to Result<int>
)
.Tap(actualQuantity => Console.WriteLine($"Actual quantity updated: {actualQuantity}"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));
// Example 3: Complex stored procedure call
executor.ExecuteAndBind(
db => db.GetStoredProcCommand("ProcessOrder")
.WithInputParameter("OrderId", DbType.Int32, orderId)
.WithInputParameter("UserId", DbType.Int32, userId)
.WithInputParameter("TotalAmount", DbType.Decimal, totalAmount)
.WithOutputParameter("TransactionId", DbType.Int32, 0)
.WithOutputParameter("Status", DbType.String, 50)
.WithInputOutputParameter("AvailableCredit", DbType.Decimal, currentCredit),
cmd => new OrderProcessingResult // Implicit conversion to Result<OrderProcessingResult>
{
TransactionId = cmd.GetParameterValue<int>("TransactionId"),
Status = cmd.GetParameterValue<string>("Status"),
RemainingCredit = cmd.GetParameterValue<decimal>("AvailableCredit")
}
)
.Tap(result => Console.WriteLine($"Order processed: TX#{result.TransactionId}, Status: {result.Status}, Credit: {result.RemainingCredit}"))
.TapError(error => Console.WriteLine($"Error processing order: {error.Message}"));GetParameterValue<T> handles type conversion and null values⚠️ Warning: The
Connectionclass API is being phased out. While still supported, it is recommended to useDbCommandExecutorwithIDbCommandExecutorinterface for new development.Why switch to DbCommandExecutor?
- Better Testability:
IDbCommandExecutorinterface makes unit testing easier with mocking frameworks- Separation of Concerns: Command factories (
IDbCommandFactory) separate command construction from execution- No Exception Handling: Result monad pattern (
Result<T>) provides explicit error handling without try-catch blocks- Cleaner Code: Fluent parameter API and implicit conversions reduce boilerplate
The ICommandFactory interface allows you to encapsulate database command construction:
public interface ICommandFactory : IReadOutParameters
{
DbCommand ConstructDbCommand(Database db);
}Example implementation for executing a stored procedure:
internal class SetPilotiDoPowiadomieniaFactory : ICommandFactory
{
private readonly int idBusMapRNo;
private readonly DateTime busMapDate;
private readonly string idAkwizytor;
private readonly string raport;
public SetPilotiDoPowiadomieniaFactory(int idBusMapRNo, DateTime busMapDate, string idAkwizytor, string raport)
{
this.idBusMapRNo = idBusMapRNo;
this.busMapDate = busMapDate;
this.idAkwizytor = idAkwizytor;
this.raport = raport;
}
public DbCommand ConstructDbCommand(Database db)
{
var cmd = db.GetStoredProcCommand("BusMap.p_SetPilotiDoPowiadomienia");
db.AddInParameter(cmd, "IdBusMapRNo", DbType.Int32, this.idBusMapRNo);
db.AddInParameter(cmd, "BusMapDate", DbType.Date, this.busMapDate);
db.AddInParameter(cmd, "IdAkwizytor", DbType.AnsiString, this.idAkwizytor);
db.AddInParameter(cmd, "Raport", DbType.AnsiString, this.raport);
return cmd;
}
public void ReadOutParameters(Database db, DbCommand command) { }
}Execute the command using Connection:
var factory = new SetPilotiDoPowiadomieniaFactory(
tagItem.IdBusMapRNp,
tagItem.BusMapDate,
tagItem.IdAkwizytor,
tagItem.Raport
);
connection.ExecuteNonQuery(factory); // Throws exception on errorFor reading data, implement the IResultsConsumer interface:
internal class RegionalSaleCommand : ICommandFactory, IResultsConsumer<SaleItem[]>
{
private RaportRequest request;
public RegionalSaleCommand(RaportRequest request)
{
this.request = request;
}
public DbCommand ConstructDbCommand(Database db)
{
var cmd = db.GetStoredProcCommand("[dbo].[TestSaleReport]");
db.AddInParameter(cmd, "IdAkwizytorRowNo", DbType.Int32, request.IdAkwizytorRowNo);
db.AddInParameter(cmd, "IdPrzewoznikRowNo", DbType.Int32, request.IdPrzewoznikRowNo);
db.AddInParameter(cmd, "DataPocz", DbType.DateTime, request.DateFrom);
db.AddInParameter(cmd, "DataKon", DbType.DateTime, request.DateTo);
return cmd;
}
public SaleItem[] GetResults(IDataReader dataReader)
{
var lista = new List<SaleItem>();
while (dataReader.Read())
{
int col = 0;
var item = new SaleItem
{
GidRezerwacji = dataReader.GetString(col++),
GIDL = dataReader.GetString(col++),
// ... more field mappings
};
lista.Add(item);
}
return lista.ToArray();
}
public void ReadOutParameters(Database db, DbCommand command) { }
}Call the GetReader method:
public class RaportDB
{
private readonly Connection connection;
public RaportDB(Connection connection)
{
this.connection = connection;
}
public RaportResponse GetRaport(RaportRequest request)
{
var command = new RegionalSaleCommand(request);
return new RaportResponse
{
Items = connection.GetReader(command, command) // Throws exception on error
};
}
}Migration Tip: To migrate from
ConnectiontoDbCommandExecutor:
- Keep your
ICommandFactoryimplementations unchanged- Replace
ConnectionwithIDbCommandExecutorin your constructors- Change method calls to use
Result<T>return types:
connection.ExecuteNonQuery(factory)→executor.ExecuteNonQuery(factory).Tap(...).TapError(...)connection.GetReader(factory, consumer)→executor.ExecuteReader(factory, consumer).Tap(...).TapError(...)- Replace try-catch blocks with
.TapError()for error handlingNote: The legacy
IGetConsumer<T>interface is still supported but deprecated. It will be removed in version 5.0. Please useIResultsConsumer<T>instead.
Note:
Voyager.DBConnection.Loggingis available as a separate NuGet package with comprehensive documentation including Application Insights integration. See src/Voyager.DBConnection.Logging/README.md for detailed usage examples and advanced scenarios.
The Voyager.DBConnection.Logging extension provides logging capabilities for database operations. It supports both the modern DbCommandExecutor and legacy Connection classes.
Installation:
dotnet add package Voyager.DBConnection.Loggingusing Microsoft.Extensions.Logging;
using Voyager.DBConnection;
// Create executor with logging
var database = new Database(factory, connectionString);
var executor = new DbCommandExecutor(database, errorPolicy);
// Add logger - logs all database operations
executor.AddLogger(logger);
// Now all operations are automatically logged
executor.ExecuteNonQuery(
"InsertUser",
cmd => cmd
.WithInputParameter("Username", DbType.String, 50, "john")
.WithInputParameter("Email", DbType.String, 100, "john@example.com")
)
.Tap(rows => Console.WriteLine($"{rows} row(s) inserted"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));using Microsoft.Extensions.Logging;
using Voyager.DBConnection;
var connection = new Connection(database, exceptionPolicy);
connection.AddLogger(logger);The logging extension uses the SqlCallEvent event system, which provides rich telemetry data for each database operation:
Available event data (SqlCallEvent):
CommandText - SQL command or stored procedure nameCommandType - Type of command (StoredProcedure, Text, etc.)ExecutionTime - Precise execution duration (TimeSpan)Parameters - Dictionary of parameter names and valuesDatabaseName - Target database nameIsError - Success/failure indicatorErrorMessage - Detailed error information (if failed)Use cases for events:
AddLogger() extensionSee the Voyager.DBConnection.Logging README for detailed examples of Application Insights integration and custom telemetry features
Voyager.DBConnection fully supports ODBC connections through System.Data.Odbc. ODBC enables connectivity to virtually any database with an ODBC driver, including legacy systems and databases without native .NET providers.
using System.Data.Common;
using System.Data.Odbc;
using Voyager.DBConnection;
// Register ODBC provider (if not already registered)
DbProviderFactories.RegisterFactory("System.Data.Odbc", OdbcFactory.Instance);
// Create database with ODBC
var factory = DbProviderFactories.GetFactory("System.Data.Odbc");
// DSN-based connection string
var connectionString = "DSN=MyDataSource;UID=user;PWD=password";
// OR driver-based connection string
// var connectionString = "Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Database=mydb;Uid=user;Pwd=password;";
var database = new Database(factory, connectionString);
var executor = new DbCommandExecutor(database, new DefaultMapError());
// Use fluent API with named parameters - ODBC supports @ prefix
executor.ExecuteNonQuery(
"INSERT INTO Users (Username, Email, Age) VALUES (@Username, @Email, @Age)",
cmd => cmd
.WithInputParameter("Username", DbType.String, 50, "john")
.WithInputParameter("Email", DbType.String, 100, "john@example.com")
.WithInputParameter("Age", DbType.Int32, 25)
)
.Tap(rows => Console.WriteLine($"{rows} row(s) inserted"))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));
// Reading data
executor.ExecuteReader(
"SELECT Username, Email FROM Users WHERE Age > @MinAge",
cmd => cmd.WithInputParameter("MinAge", DbType.Int32, 18),
new UserListConsumer()
)
.Tap(users => users.ForEach(u => Console.WriteLine($"{u.Username}: {u.Email}")))
.TapError(error => Console.WriteLine($"Error: {error.Message}"));System.Data.Odbc in .NET supports named parameters using the @ prefix, even though native ODBC uses positional ? parameters. The .NET provider automatically converts named parameters to the correct format.
Key points:
@ prefix for parameter names (handled automatically by fluent API)ODBC works on all platforms:
The NuGet package Voyager.DBConnection.MsSql provides a ready-to-use implementation for MS SQL Server connections:
namespace Voyager.DBConnection.MsSql
{
public class SqlConnection : Connection
{
public SqlConnection(string sqlConnectionString)
: base(new SqlDatabase(sqlConnectionString), new ExceptionFactory())
{
}
}
}