Database Unit Test

UnitTest1.cs

using Microsoft.Data.SqlClient;

namespace database_test_runner
{
    public class SQL : IDisposable
    {
        private readonly SqlConnection _connection;

        public SQL()
        {
            // var connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING");
            // ArgumentNullException.ThrowIfNull(connectionString, "SQL_CONNECTION_STRING environment variable is not set");
            
            var connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Database=NDC;User ID=sa;Password=P@ssw0rd!;";
            _connection = new SqlConnection(connectionString);
            _connection.Open();
        }

        public void Dispose() => _connection?.Close();

        public bool Connected => _connection.State == System.Data.ConnectionState.Open;

        public async Task ExecuteScalarAsync(string sql)
        {
            using var command = _connection.CreateCommand();
            command.CommandText = sql;
            _ = await command.ExecuteScalarAsync();
        }

        public TheoryData<string> AllTests()
        {
            const string sql = """
                SELECT 'Tests.' + p.name
                FROM sys.procedures AS p
                JOIN sys.schemas AS s ON p.schema_id = s.schema_id
                WHERE s.name = 'Tests'
                """;

            using var command = new SqlCommand(sql, _connection);
            using var reader = command.ExecuteReader();

            var result = new TheoryData<string>();
            while (reader.Read())
            {
                result.Add(reader.GetString(0));
            }
            return result;
        }
    }

    public class UnitTest1
    {
        private static readonly SQL _sql = new();

        [Theory]
        [MemberData(nameof(AllTests))]
        public async Task TestObjects(string sql)
            => await _sql.ExecuteScalarAsync(sql);

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