C# example code for my fam

public const string DatabaseName = "DatabaseName";

enum DatabaseType
{
    Production,
    TestingInProdDb,
    TestingInTestDb
}

class Database
{
    DatabaseType  dbType;
    uint   runId;
    bool   tempDb;

    this(DatabaseType ty)
    {
        this.dbType = ty;

        // Figure out what random number to append to test tables.
        switch ( this.dbType )
        {
            case DatabaseType.Production:
                this.runId = 0;
                break;

            case DatabaseType.TestingInProdDb:
            case DatabaseType.TestingInTestDb:
                // Pseudocode assumes `rand()` returns unsigned integer between `0` and `uint.max`
                // Note that negative values and floats would be bad here:
                // this has to be something that can be embedded into names.
                this.runId = rand();
                break;

            default:
                throw Exception("Unknown database type {0}", this.dbType);
        }

        // Because Database creation requires admin priveleges on the SQL server,
        // we also just look for whether the test database exists already,
        // and if it does, we just use that. (That way, a sysadmin could
        // grant restricted priveleges just for the testrunner.)
        //
        // It's also perfectly valid to place the test tables in the same
        // database as the target/production tables that the program will
        // be using, as long as they are uniquely named*. That database is
        // guaranteed to exist, however, it does mean the testrunner needs
        // potentially invasive access rights to that database (ex: create
        // and drop tables).
        //
        // It's all tradeoffs, and what works best is going to be situational.
        //
        // * As commented below, the test-tables-in-production also has
        // this strong disadvantage: if any tables in this program's code don't
        // annotate/affix their table names correctly, then the test code
        // would end up accidentally messing with production tables!
        //
        this.tempDb = false;
        if ( !dbExists(String.Format("SELECT * FROM sys.databases where Name='{0}'", DatabaseName)) ) {
            createDb(this.name()); // Requires admin priveleges.
            this.tempDb = true;
        }
    }

    void teardown()
    {
        if ( tempDb ) {
            deleteDb(TestDbName);
        }
    }

    string name()
    {
        switch ( this.dbType )
        {
            case DatabaseType.Production:      return DatabaseName;
            case DatabaseType.TestingInProdDb: return DatabaseName;
            case DatabaseType.TestingInTestDb: return String.Format("TEST_{0}_{1}", DatabaseName, this.runId);
            default: throw Exception("Unknown database type {0}", this.dbType);
        }
    }

    // If testing ever gets performed by using test tables that are colocated
    // in the production database, then something like this method MUST be
    // used EVERYWHERE in the program's code.
    //
    // This does lend a strong recommendation for creating separate test databases:
    // even if some part of code mistakenly uses the wrong table name, then
    // it wouldn't mess with production data!
    string affixTableName(string tableName)
    {
        switch ( this.dbType )
        {
            case DatabaseType.Production:      return tableName;
            case DatabaseType.TestingInProdDb: return String.Format("TEST_{0}_{1}", tableName, this.runId);
            case DatabaseType.TestingInTestDb: return tableName;
            default: throw Exception("Unknown database type {0}", this.dbType);
        }
    }

    void createTestTables()
    {
        exec("CREATE TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableFoo"));
        exec("CREATE TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBar"));
        exec("CREATE TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBaz"));
    }

    void dropTestTables()
    {
        exec("DROP TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableFoo"));
        exec("DROP TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBar"));
        exec("DROP TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBaz"));
    }
}

class Tester
{
    Client         test1Client;
    string[string] test1Data;

    Client         test2Client;
    string[string] test2Data;

    void populateData()
    {
        // Assumption: Clients are "stateless" and do not
        test1Client = new TypeOfTest1Client();
        test1Data["id"]          = "12345";
        test1Data["status"]      = "initial";
        test1Data["description"] = "first test";
        ...

        test2Client = new TypeOfTest2Client();
        test2Data["id"]          = "54321";
        test2Data["status"]      = "sent";
        test2Data["description"] = "foo, bar, baz";
        ...
    }

    void runTests()
    {
        Database db = setupDatabase(TestingInTestDb.Testing);

        // From Microsoft Docs:
        // ```
        // When an exception is handled by a catch block,
        // the finally block is executed after execution of that catch block
        // (even if another exception occurs during execution of the catch block).
        // ```
        // source: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/statements/exception-handling-statements
        // Notably, if you just need something equivalent to `scope(exit)`,
        // then you can also elide the catch(...) block entirely and
        // use a `finally` block by itself. It will always get executed, regardless
        // of whether exceptions get thrown or not.
        try
            runTestsWithDb(db);
        finally
            db.teardown(db);
    }

    void runTestsWithDb(Database db)
    {
        Client c;

        c = test1Client;
        testEmptyCsv(db, c);
        testValidCsv(db, c, test1Data, CsvFlavor.NeverQuote);
        testValidCsv(db, c, test1Data, CsvFlavor.AlwaysQuote);
        testValidCsv(db, c, test1Data, CsvFlavor.QuoteEveryOther);
        testValidCsv(db, c, test1Data, CsvFlavor.QuoteOnlyWhenNeeded);

        c = test2Client;
        testEmptyCsv(db, c);
        testMalformedCsv(db, c, test2Data, CsvFlavor.NeverQuote);
        testValidCsv(    db, c, test2Data, CsvFlavor.AlwaysQuote);
        testMalformedCsv(db, c, test2Data, CsvFlavor.QuoteEveryOther);
        testValidCsv(    db, c, test2Data, CsvFlavor.QuoteOnlyWhenNeeded);

        // Another possible variance in client data:
        // Positional columns vs columns with headers
    }

    void testValidCsv(Database db, Client client, string[string] testData, CsvFlavor csvFlavor)
    {
        db.createTestTables();
        try {
            // Ugly name reflects ugliness of try-catch-finally lol.
            actuallyTestValidCsv(db, client, testData, csvFlavor);
        }
        finally {
            db.dropTestTables();
        }


    }

    void actuallyTestValidCsv(Database db, Client client, string[string] testData, CsvFlavor csvFlavor)
    {
        // This method will probably be client-specific, because it would need
        // to emit the data in that client's particular layout.
        filename = writeCsvFile(client, client.csvDirectoryPath, testData, csvFlavor);

        // If you're testing a program's ability to watch for files to appear
        // in a directory (like `client.csvDirectoryPath` in above line), then
        // you might not want to call this directly.
        //
        // Instead:
        // * If the program deletes files after processing them, then you might
        //   have a loop or system call that waits for the file to disappear
        //   (assuming you've also confirmed that it was created in the first place...
        //   somehow).
        // * If the program doesn't delete files after processing them, then
        //   the tester has to have some other way of knowing when the
        //   code for processing has completed. (Left as exercise for reader...)
        //
        // In either case, it would need a timeout, so that indefinite waits
        // could be caught and registered as test failure.
        client.ProcessFileData(db, filename);

        // Assumption: At this point in execution, we KNOW that the program
        // has finished processing the CSV file and has already
        // created all database records. If it couldn't, then it has already
        // thrown an exception or otherwise errored-out.

        // You would probably test more than 1 row in most cases, but I'm just
        // handling the n=1 case here to keep the example simple.
        row = exec("SELECT ... FROM {0}.dbo.{1} foo WHERE foo.id = {2}",
            db.name, db.affixTableName(DatabaseName), testData["id"]);

        assert(row.exists());
        assert(row["status"] == testData["status"]);
        assert(row["description"] == testData["description"]);
        ...

        // We're still dealing with just 1 (CSV) row of sample data, but what if
        // that one line in the CSV file resulted in multiple SQL rows somehow?
        // Well, it might look like this:
        lines = exec("SELECT ... FROM {0}.dbo.{1} bar WHERE bar.id = {2}"
            db.name, db.affixTableName(DatabaseName), testData["id"]);
        assert(lines.exists);
        foreach(line in lines)
        {
            assert(line["status"] == testData["status"]);
            assert(line["xyz"]    == testData[String.format("xyz{0}", line.number)]);
        }

        // Then there'd be some code for handling the "baz" table...
        // etc.
    }
}