Add integration tests that exercise real consumer scenarios

[DaveRMaltby]

Summary

Unit tests validate individual components but miss integration-level bugs. We need tests that exercise the providers the way a real consumer would:

Suggested Test Scenarios

ADO.NET Consumer

1. Full CRUD lifecycle: Open connection → CREATE TABLE → INSERT rows → SELECT → UPDATE → DELETE → verify
2. DataAdapter Fill/Update round-trip with all row states (Added, Modified, Deleted)
3. Transaction commit/rollback with verification
4. Multiple concurrent connections to same file
5. Connection string variations (folder-as-database vs file-as-database)

EF Core Consumer

1. DbContext with model → EnsureCreated → Add entities → SaveChanges → query back → verify
2. LINQ queries: Where, OrderBy, Select projection, Include, GroupBy
3. Migration scenario (if supported)
4. Multiple providers (CSV, JSON, XML) with same model

Cross-Provider

1. Same test suite runs against all 4 providers (CSV, JSON, XML, XLS read-only)
2. Verify data fidelity across providers (write with JSON, read with CSV of same data)

Why This Matters

Multiple bugs were found during this session that unit tests missed — Transaction Dispose not rolling back, FileDataReader breaking connection reuse, identity collisions, XML corruption under concurrency.

[DaveRMaltby]

Code Examples

ADO.NET Full CRUD Lifecycle

[Theory]
[InlineData("json")]
[InlineData("csv")]
[InlineData("xml")]
public void AdoNet_FullCrudLifecycle(string provider)
{
    using var tempDir = new TempDirectory();
    var connectionString = $"DataSource={tempDir.Path}";
    using var connection = CreateConnection(provider, connectionString);
    connection.Open();

    // CREATE TABLE
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "CREATE TABLE customers (id INT, name TEXT, email TEXT)";
        cmd.ExecuteNonQuery();
    }

    // INSERT
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO customers (id, name, email) VALUES (1, 'Alice', 'alice@test.com')";
        cmd.ExecuteNonQuery();
    }

    // SELECT
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM customers WHERE id = 1";
        using var reader = cmd.ExecuteReader();
        Assert.True(reader.Read());
        Assert.Equal("Alice", reader.GetString(reader.GetOrdinal("name")));
        Assert.False(reader.Read());
    }

    // UPDATE
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "UPDATE customers SET email = 'alice@new.com' WHERE id = 1";
        var affected = cmd.ExecuteNonQuery();
        Assert.Equal(1, affected);
    }

    // DELETE
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "DELETE FROM customers WHERE id = 1";
        cmd.ExecuteNonQuery();
    }

    // Verify empty
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "SELECT COUNT(*) FROM customers";
        Assert.Equal(0, Convert.ToInt32(cmd.ExecuteScalar()));
    }
}

EF Core DbContext Round-Trip

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime CreatedAt { get; set; }
    public decimal Balance { get; set; }
}

[Theory]
[InlineData("json")]
[InlineData("csv")]
[InlineData("xml")]
public void EfCore_CrudWithTypedEntities(string provider)
{
    using var tempDir = new TempDirectory();
    var options = CreateDbContextOptions(provider, tempDir.Path);
    
    // Create and seed
    using (var ctx = new TestDbContext(options))
    {
        ctx.Database.EnsureCreated();
        ctx.Customers.Add(new Customer 
        { 
            Id = 1, Name = "Alice", 
            CreatedAt = DateTime.UtcNow, 
            Balance = 100.50m 
        });
        ctx.SaveChanges();
    }

    // Read back in fresh context
    using (var ctx = new TestDbContext(options))
    {
        var customer = ctx.Customers.Single(c => c.Name == "Alice");
        Assert.Equal(100.50m, customer.Balance);
        Assert.Equal(2026, customer.CreatedAt.Year);
    }
}

DataAdapter Fill/Update Round-Trip

[Fact]
public void DataAdapter_FillAndUpdateAllRowStates()
{
    using var connection = new JsonConnection($"DataSource={tempDir.Path}");
    connection.Open();
    SeedTable(connection, "orders", new[] { (1, "Widget"), (2, "Gadget"), (3, "Doohickey") });

    var adapter = new JsonDataAdapter("SELECT * FROM orders", connection);
    var builder = new JsonCommandBuilder(adapter);
    var dataSet = new DataSet();
    adapter.Fill(dataSet, "orders");

    // Modify rows
    dataSet.Tables["orders"].Rows[0]["name"] = "Updated Widget";  // Modified
    dataSet.Tables["orders"].Rows[1].Delete();                     // Deleted
    dataSet.Tables["orders"].Rows.Add(4, "New Item");              // Added

    // Update should handle all three row states
    var affected = adapter.Update(dataSet, "orders");
    Assert.Equal(3, affected);

    // Verify by re-reading
    var verify = new DataSet();
    adapter.Fill(verify, "orders");
    Assert.Equal(3, verify.Tables["orders"].Rows.Count);
    Assert.Contains(verify.Tables["orders"].AsEnumerable(), 
        r => r.Field<string>("name") == "Updated Widget");
    Assert.Contains(verify.Tables["orders"].AsEnumerable(), 
        r => r.Field<string>("name") == "New Item");
    Assert.DoesNotContain(verify.Tables["orders"].AsEnumerable(), 
        r => r.Field<string>("name") == "Gadget");
}

Async Consumer

[Fact]
public async Task Async_FullWorkflow_DoesNotBlockThreadPool()
{
    using var connection = new JsonConnection(connectionString);
    await connection.OpenAsync();

    using var cmd = connection.CreateCommand();
    cmd.CommandText = "SELECT * FROM products";
    
    await using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        var name = await reader.GetFieldValueAsync<string>(0);
        Assert.False(string.IsNullOrEmpty(name));
    }
}