Database Unit Test

UnitTest1.cs

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

using System.Data;

namespace Database.TestRunner;

public class SqlDatabase : IDisposable, IAsyncDisposable
{
    private static string ReadConnectionString()
    {
        // dotnet user-secrets init
        // dotnet user-secrets set "SQL_CONNECTION_STRING" "Data Source=(localdb)\\MSSQLLocalDB;Database=HR;Integrated Security=True;"
        // Microsoft.Extensions.Configuration
        // Microsoft.Extensions.Configuration.UserSecrets
        var configuration = new ConfigurationBuilder().AddUserSecrets<SqlDatabase>().Build();
        var connectionString = configuration["SQL_CONNECTION_STRING"];
        ArgumentNullException.ThrowIfNull(connectionString, "SQL_CONNECTION_STRING not set");
        return connectionString;
    }

    private readonly SqlConnection _connection;
    private bool _disposed = false;

    public SqlDatabase()
    {
        _connection = new SqlConnection(ReadConnectionString());
        _connection.Open();
    }

    public async Task ExecuteNonQueryAsync(string sql, CancellationToken token)
    {
        ArgumentException.ThrowIfNullOrEmpty(sql, nameof(sql));

        using var command = new SqlCommand(sql, _connection);
        await command.ExecuteNonQueryAsync(token);
    }

    public TheoryData<string> AllTests(string schemaName)
    {
        ArgumentException.ThrowIfNullOrEmpty(schemaName, nameof(schemaName));

        string sql = """
            SELECT CONCAT('EXEC [', s.name, '].[', p.name, '];') as proc_name
            FROM sys.procedures AS p
            JOIN sys.schemas AS s ON p.schema_id = s.schema_id
            WHERE s.name = @SchemaName
            """;

        using var command = new SqlCommand(sql, _connection);
        command.Parameters.AddWithValue("@SchemaName", schemaName);

        using var reader = command.ExecuteReader();
        var result = new TheoryData<string>();

        while (reader.Read())
        {
            result.Add(reader.GetString(0));
        }
        return result;
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    public async ValueTask DisposeAsync()
    {
        await DisposeAsyncCore();
        Dispose(false);
        GC.SuppressFinalize(this);
    }

    protected virtual async ValueTask DisposeAsyncCore()
    {
        if (_connection is not null
            && _connection.State is not ConnectionState.Closed)
        {
            await _connection.CloseAsync();
        }
    }

    protected virtual void Dispose(bool disposing)
    {
        if (_disposed)
        {
            return;
        }

        if (disposing)
        {
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
            }
        }

        _disposed = true;
    }
}

public class TestRunner
{
    private static readonly SqlDatabase _sql = new();

    public static TheoryData<string> AllTests() => _sql.AllTests("Tests");

    [Theory]
    [MemberData(nameof(AllTests))]
    public async Task TestObjects(string sql, CancellationToken token)
    {
        await _sql.ExecuteNonQueryAsync(sql, token);
    }
}