How to mock SqlConnection, SqlCommand?

Question

How to unit test this class? Or how it should be refactored to be unit testable?

public class DomainEventsMigrator : IDomainEventsMigrator
{
    private readonly string _sourceDbConnectionString;
    private readonly string _destinationDbConnectionString;
    private readonly ILogger<DomainEventsMigrator> _logger;

    public DomainEventsMigrator(string sourceDbConnectionString, string destinationDbConnectionString, ILogger<DomainEventsMigrator> logger)
    {
        _sourceDbConnectionString = sourceDbConnectionString;
        _destinationDbConnectionString = destinationDbConnectionString;
        _logger = logger;
    }

    public async Task MoveBatchAsync(MigrationBatch batch)
    {
        Stopwatch stopWatch = Stopwatch.StartNew();
        try
        {
            using SqlConnection sourceConnection = new(_sourceDbConnectionString);
            await sourceConnection.OpenAsync();
            var query =
                @"SELECT StreamId, MessageId, EventDate, EventDataType, EventPayloadJson, IsActive
                    FROM dbo.tblExecutionPathDomainEvents WITH (NOLOCK)
                WHERE Id >= @FromId AND Id < @ToId
                ORDER BY Id";
            SqlCommand commandSourceData = new(query, sourceConnection);
            commandSourceData.Parameters.AddWithValue("@FromId", batch.FromId);
            commandSourceData.Parameters.AddWithValue("@ToId", batch.ToId);
            SqlDataReader reader = await commandSourceData.ExecuteReaderAsync();

            stopWatch.Stop();
            _logger.LogInformation($"Read attempt successful in {stopWatch.Elapsed}, FromId = {batch.FromId}, ToId = {batch.ToId}");

            stopWatch = Stopwatch.StartNew();

            using SqlConnection destinationConnection = new(_destinationDbConnectionString);
            await destinationConnection.OpenAsync();

            using SqlBulkCopy bulkCopy = new(destinationConnection);
            bulkCopy.DestinationTableName = "dbo.tblExecutionPathDomainEvents";
            bulkCopy.BulkCopyTimeout = 3600;
            bulkCopy.BatchSize = 1000;

            try
            {
                await bulkCopy.WriteToServerAsync(reader);
                stopWatch.Stop();
                _logger.LogInformation($"Write attempt successful in {stopWatch.Elapsed}, FromId = {batch.FromId}, ToId = {batch.ToId}");

            }
            catch (Exception e)
            {
                stopWatch.Stop();
                _logger.LogError(e, $"Write attempt failed in {stopWatch.Elapsed}, FromId = {batch.FromId}, ToId = {batch.ToId}");
            }
            finally
            {
                reader.Close();
            }
        }
        catch (Exception ex)
        {
            stopWatch.Stop();
            _logger.LogError(ex, $"Read attempt failed in {stopWatch.Elapsed}, FromId = {batch.FromId}, ToId = {batch.ToId}");
        }
    }
}

Answer 1

With current design of DomainEventsMigrator, you cannot mock Sql objects (they are locally declared inside MoveBatchAsync).

You should ask yourself, why do I really want to mock them? You could, for example, refer to these objects as participants of some internal behavior, and control them by supplying test-oriented connection strings, e.g. in-memory database. However, establishing database (be it in-memory or not) for unit-testing purposes might get complicated when aiming at some scenarios, plus it becomes more like integration tests.

Given that creating database connections is considered to be "boring" implementation details, breaking your code into separate dependencies would be an elegant solution to your problem. In line with that, SqlConnection objects should function as dependencies, thus should be passed to constructor (in other words, an external object will be responsible to create and open the connections). And objects being passed from the outside can be mocked.

Yet some work is left to be done:

1. SqlCommand's ExecuteReaderAsync method should be mocked. In order to achieve that you can either create the SqlCommand object in the outside world and pass it to the constructor (instead of passing sourceConnection), or call SqlConnection.CreateCommand (instead of instantiating SqlCommand) and "override" CreateCommand in your test's setup code.
2. As for SqlBulkCopy object, it looks like your only option is to create it outside and pass it to the constructor, then override WriteToServerAsync to throw exception.