UMB.DataBaseOperations 3.0.0

UMB.DataBaseOperations

It is a package that allows you to perform an operation on a SQL database using stores procedures or a direct query

Authors

• @umbconsultor

Documentation

Documentation and examples

Architecture

The package now includes an abstraction layer (ISqlCommandExecutor) that enables dependency injection and testing without requiring a live SQL Server connection.

Core Components

• IDataBaseOperations: Public interface for database operations (SetConnectionString, SpExecution, SpExecutionAsync, QueryExecution).
• DataBaseOperations: Implementation that delegates SQL execution to an injected ISqlCommandExecutor.
• ISqlCommandExecutor: Internal abstraction for Execute/ExecuteAsync methods; enables mocking in unit tests.
• SqlCommandExecutor: Default implementation using System.Data.SqlClient.
• DataBaseResponse: Result wrapper with error tracking, execution duration, row/table counts, and optional DataSet.

Design Patterns

• Dependency Injection: Constructors accept optional logger and command executor.
• Result Pattern: Operations return DataBaseResponse wrapping success/failure; CancellationToken propagates to async methods.
• Logging: Operations log errors and warnings via ILogger<T> when available (optional).

Testing

The DataBaseOperationsNUnitTests project contains 22+ unit tests (NUnit 4.3.2) using Moq to mock ISqlCommandExecutor. Tests cover:

• Input validation (null, whitespace connection string, procedure, query).
• Success scenarios (single/multiple tables, multiple rows).
• Error scenarios (executor exceptions, cancellation propagation).
• Edge cases (empty results, tables without rows).

All tests execute without requiring SQL Server. Tests run on net10.0, ensuring modern async/await compatibility.

Notes

• Targets: net6.0 and net48. The library keeps System.Data.SqlClient for compatibility with .NET Framework 4.8. Consider migrating to Microsoft.Data.SqlClient for .NET 6 in a separate PR (see PROPOSAL_MIGRATE_TO_MICROSOFT_DATA_SQLCLIENT.md).
• Usage: Call SetConnectionString before any operation.
• DeriveParameters: The library attempts to call SqlCommandBuilder.DeriveParameters; this requires appropriate permissions. If it fails, the operation continues and provided parameters are applied. Prefer passing explicit parameters when possible.
• Async behavior: SpExecutionAsync uses ExecuteReaderAsync but still loads results synchronously into a DataTable to populate the DataSet. For high-throughput scenarios consider mapping from DbDataReader directly to DTOs instead of DataSet.
• Testing Strategy: Use constructor overloads to inject mocks; do not instantiate ISqlCommandExecutor constructors directly in production code.