sql-utilities/testContainers/Strata.SqlTools.SqlServer.TestContainers/SqlServerTestContainerFixture.cs

using NUnit.Framework;
using System.Data.SqlClient;
using Testcontainers.MsSql;
using DotNet.Testcontainers.Containers;
using DotNet.Testcontainers.Builders;

namespace Strata.SqlTools.Tests.SqlServer.TestContainers;

/// <summary>
/// Base class for SQL Server testcontainer tests that handles container lifecycle.
/// </summary>
[TestFixture]
[Category("Integration")]
public abstract class SqlServerTestContainerFixture
{
    protected MsSqlContainer? Container { get; set; }
    protected string? ConnectionString { get; set; }

    [OneTimeSetUp]
    [CancelAfter(300000)]  // 5 minutes for container startup + database initialization
    public async Task OneTimeSetup()
    {
        // Create and start SQL Server container
        Container = new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
            .WithPassword("SqlServerP@ss123")
            .WithCleanUp(true)
            .WithWaitStrategy(Wait.ForUnixContainer().UntilPortIsAvailable(1433))
            .Build();

        using var cts = new CancellationTokenSource(TimeSpan.FromMinutes(4));
        await Container.StartAsync(cts.Token);

        // Get connection string and wait for SQL Server to be ready
        ConnectionString = Container.GetConnectionString();
        await WaitForSqlServerReady(cts.Token);

        // Drop any existing tables and initialize schema
        await DropExistingTables();
        await InitializeDatabase();
    }

    /// <summary>
    /// Waits for SQL Server to be ready to accept connections with retry logic.
    /// </summary>
    private async Task WaitForSqlServerReady(CancellationToken cancellationToken)
    {
        if (string.IsNullOrEmpty(ConnectionString))
        {
            throw new InvalidOperationException("ConnectionString is not initialized");
        }

        const int maxRetries = 30;
        const int delayMs = 2000; // 2 seconds between retries

        for (int i = 0; i < maxRetries; i++)
        {
            try
            {
                using var connection = new SqlConnection(ConnectionString);
                await connection.OpenAsync(cancellationToken);

                // Try a simple query to ensure SQL Server is fully ready
                using var command = connection.CreateCommand();
                command.CommandText = "SELECT 1";
                await command.ExecuteScalarAsync(cancellationToken);
                return;
            }
            catch (SqlException) when (i < maxRetries - 1)
            {
                await Task.Delay(delayMs, cancellationToken);
            }
        }

        throw new InvalidOperationException($"SQL Server did not become ready after {maxRetries} attempts");
    }

    /// <summary>
    /// Drops existing test tables to ensure clean state.
    /// </summary>
    private async Task DropExistingTables()
    {
        if (string.IsNullOrEmpty(ConnectionString))
            return;

        try
        {
            using var connection = new SqlConnection(ConnectionString);
            await connection.OpenAsync();

            using var command = connection.CreateCommand();
            command.CommandText = @"
                IF EXISTS (SELECT * FROM sys.tables WHERE name = 'orders')
                DROP TABLE orders;

                IF EXISTS (SELECT * FROM sys.tables WHERE name = 'products')
                DROP TABLE products;

                IF EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
                DROP TABLE users;
            ";

            await command.ExecuteNonQueryAsync();
        }
        catch
        {
            // Ignore errors - tables might not exist
        }
    }

    [OneTimeTearDown]
    public async Task OneTimeTearDown()
    {
        if (Container != null)
        {
            await Container.StopAsync();
            await Container.DisposeAsync();
        }
    }

    /// <summary>
    /// Override to initialize the test database with tables, etc.
    /// </summary>
    protected virtual async Task InitializeDatabase()
    {
        if (string.IsNullOrEmpty(ConnectionString))
            return;

        using var connection = new SqlConnection(ConnectionString);
        await connection.OpenAsync();

        using var command = connection.CreateCommand();
        command.CommandText = @"
            -- Drop existing tables and constraints to ensure clean state
            IF EXISTS (SELECT * FROM sys.tables WHERE name = 'orders')
            DROP TABLE orders;

            IF EXISTS (SELECT * FROM sys.tables WHERE name = 'products')
            DROP TABLE products;

            IF EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
            DROP TABLE users;

            -- Create users table
            CREATE TABLE users (
                id INT IDENTITY(1,1) PRIMARY KEY,
                name NVARCHAR(255) NOT NULL,
                email NVARCHAR(255) UNIQUE NOT NULL,
                active BIT DEFAULT 1,
                created_at DATETIME DEFAULT GETUTCDATE()
            );

            -- Create orders table with foreign key
            CREATE TABLE orders (
                id INT IDENTITY(1,1) PRIMARY KEY,
                user_id INT NOT NULL,
                order_total DECIMAL(10, 2) NOT NULL,
                created_at DATETIME DEFAULT GETUTCDATE(),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            );

            -- Create products table
            CREATE TABLE products (
                id INT IDENTITY(1,1) PRIMARY KEY,
                name NVARCHAR(255) NOT NULL,
                price DECIMAL(10, 2) NOT NULL,
                in_stock BIT DEFAULT 1
            );
        ";

        await command.ExecuteNonQueryAsync();
    }

    /// <summary>
    /// Executes a SQL query and returns the result set.
    /// </summary>
    protected async Task<List<Dictionary<string, object>>> ExecuteQuery(string sql)
    {
        if (string.IsNullOrEmpty(ConnectionString))
            throw new InvalidOperationException("ConnectionString is not initialized");

        var results = new List<Dictionary<string, object>>();

        using var connection = new SqlConnection(ConnectionString);
        await connection.OpenAsync();

        using var command = connection.CreateCommand();
        command.CommandText = sql;

        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var row = new Dictionary<string, object>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                row[reader.GetName(i)] = reader.GetValue(i) ?? DBNull.Value;
            }
            results.Add(row);
        }

        return results;
    }

    /// <summary>
    /// Executes a non-query SQL statement.
    /// </summary>
    protected async Task ExecuteNonQuery(string sql)
    {
        if (string.IsNullOrEmpty(ConnectionString))
            throw new InvalidOperationException("ConnectionString is not initialized");

        using var connection = new SqlConnection(ConnectionString);
        await connection.OpenAsync();

        using var command = connection.CreateCommand();
        command.CommandText = sql;
        await command.ExecuteNonQueryAsync();
    }

    /// <summary>
    /// Clears all test data from tables by deleting and resetting identity seeds.
    /// </summary>
    protected async Task ClearTestData()
    {
        // Delete in reverse dependency order
        await ExecuteNonQuery("DELETE FROM orders");
        await ExecuteNonQuery("DELETE FROM users");
        await ExecuteNonQuery("DELETE FROM products");

        // Reseed identity columns to 0, which makes next INSERT use 1
        // This works even if identity was previously higher
        await ExecuteNonQuery("DBCC CHECKIDENT ('users', RESEED, 0)");
        await ExecuteNonQuery("DBCC CHECKIDENT ('orders', RESEED, 0)");
        await ExecuteNonQuery("DBCC CHECKIDENT ('products', RESEED, 0)");
    }
}