Setup Subcutaneous testing with SqlServer

[NathanTe]

Hey,

First of all amazing template, I build a financial app with ease.

Now I'm getting around to writing my tests 😞.

I see you used Sqlite for you test db in your integration tests.
I would like to use an SqlServer test db but can't seem to get it to work.

Error: Microsoft.Data.SqlClient.SqlException : Cannot open database "Testing" requested by the login. The login failed.

Tried:

• creating the database 'Testing' in advance using SSMS.
• removing the ensureDeleted();

The code based on your template is below.
Hopefully one you guys can help me further 😃.

Thanks in advance,
Nathan

// WebAppFactory
protected override void ConfigureWebHost(IWebHostBuilder builder)
{
    TestDatabase = SqlServerTestDatabase.CreateAndInitialize();

    builder.ConfigureTestServices(services =>
    {
        services
            .RemoveAll<DbContextOptions<AppDbContext>>()
            .AddDbContext<AppDbContext>((sp, options) => options.UseSqlServer(TestDatabase.Connection));
    });

// SqlServerTestDatabase
public class SqlServerTestDatabase : IDisposable
{
    public SqlConnection Connection { get; }

    public static SqlServerTestDatabase CreateAndInitialize()
    {
        var testDatabase = new SqlServerTestDatabase("Server=localhost;Database=Testing;Trusted_Connection=True;TrustServerCertificate=true;");

        testDatabase.InitializeDatabase();

        return testDatabase;
    }

    public void InitializeDatabase()
    {
        Connection.Open();
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(Connection)
            .Options;

        using var context = new AppDbContext(options, null!);
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
    }

    public void ResetDatabase()
    {
        Connection.Close();

        InitializeDatabase();
    }

    public void Dispose()
    {
        Connection.Close();
    }

    private SqlServerTestDatabase(string connectionString)
    {
        Connection = new SqlConnection(connectionString);
    }
}

[eric-net]

Modify SqlServerTestDatabase to Handle Database Creation:

public void InitializeDatabase()
{
    // Open the connection to the server (without specifying a database)
    Connection.Open();

    // Check if the database exists
    var cmd = new SqlCommand("IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Testing') BEGIN CREATE DATABASE Testing END", Connection);
    cmd.ExecuteNonQuery();

    // Close the connection and reopen with the correct database
    Connection.Close();
    Connection.ChangeDatabase("Testing");

    // Initialize the database context to apply migrations or creation logic
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(Connection)
        .Options;

    using var context = new AppDbContext(options, null!);
    context.Database.EnsureCreated();
}