Testing Your Repository - lobodava/artisan-orm GitHub Wiki

A repository in Artisan.Orm is a thin layer over SqlConnection, stored procedures, and a small mapping pipeline. Mocking SqlDataReader and SqlCommand is technically possible but rarely worth the effort — the bugs that break in real life are mismatches between the C# mapper and the SQL schema, parameter type mismatches, transaction interactions, deadlock-prone procedures. Those don't show up against a mock.

Recommended approach: integration tests against a real SQL Server instance with a known seeded schema. The Artisan.Orm test suite (Tests/) is the canonical reference — read it.

Tests/
├── Tests.csproj              # net8.0; net10.0 (multi-target)
├── appsettings.json          # connection string for the test database
├── AppSettings.cs            # binds the JSON to a typed object
├── DAL/                      # repositories + models exercised by tests
│   ├── Users/
│   ├── Records/
│   ├── GrandRecords/
│   └── Folders/
├── DataServices/             # higher-level services on top of repositories
└── Tests/                    # the actual [TestClass] files
    ├── UserRepositoryTest.cs
    ├── RecordRepositoryTest.cs
    ├── BulkCopyTest.cs
    ├── ReadToListsTest.cs
    └── ...

The Database/ project contains the schema, procedures, and seed data. Use a dedicated test database: its post-deployment folder generator truncates sample folder data. A build produces a DACPAC but does not deploy SQL changes. Apply the required object updates before running integration tests against an existing database.

For the 4.1.0 source, build the complete solution with Visual Studio MSBuild and SSDT targets, then run Tests and Artisan.Orm.Analyzers.Tests for both .NET 8 and .NET 10. Run the framework targets sequentially when they share the seeded database. Analyzer tests and parameter-construction tests do not require a live schema; integration tests do.

Using MSTest 4:

using Artisan.Orm;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Tests.DAL.Users;
using Tests.DAL.Users.Models;

namespace Tests.Tests;

[TestClass]
public class UserRepositoryTest
{
    private Repository _repository = null!;

    [TestInitialize]
    public void TestInitialize()
    {
        var appSettings = new AppSettings();
        _repository = new Repository(appSettings.ConnectionStrings.DatabaseConnection);

        // Reset any rows the test might create from a previous run.
        _repository.ExecuteCommand(cmd =>
            cmd.UseSql("delete from dbo.Users where Id > 14;"));
    }

    [TestCleanup]
    public void TestCleanup() => _repository.Dispose();

    [TestMethod]
    public void GetUserById()
    {
        var user = _repository.GetUserById(1);

        Assert.IsNotNull(user);
        Assert.AreEqual(1, user.Id);
    }

    [TestMethod]
    public async Task GetUserByIdAsync()
    {
        var user = await _repository.GetUserByIdAsync(1);

        Assert.IsNotNull(user);
        Assert.AreEqual(1, user.Id);
    }
}

Same pattern works with xUnit / NUnit — replace [TestInitialize] with the framework's equivalent constructor or [SetUp].

{
    "ConnectionStrings": {
        "DatabaseConnection": "Data Source=.\\SQLEXPRESS;Initial Catalog=Artisan;Integrated Security=True;Pooling=False;TrustServerCertificate=True;"
    }
}

Two notes:

• Pooling=False is intentional in the test config. Without pooling, every using var repo = ... does a full TDS handshake — slower per-test, but it surfaces connection-leak bugs that the pool would otherwise mask. Don't copy this to production configs.

• The test database is shared, not freshly created per test. Tests assume a known seeded schema — IDs 1..14 in dbo.Users are reserved "hero" rows that tests don't delete. That contract lives in [TestInitialize] cleanup clauses.

Three patterns in increasing isolation cost:

The suite combines cleanup-based fixtures and transaction-based regressions. MERGE can participate in the surrounding transaction. BulkCopy must be given the applicable transaction rather than opening an unrelated connection or using an incompatible internal transaction. Choose isolation based on the operations under test; rollback is not a general way to undo external side effects or sequence/identity allocation.

async Task test methods work in MSTest 4, xUnit, and NUnit. With Artisan.Orm's async API:

[TestMethod]
public async Task ReadWithCancellationToken()
{
    using var cts = new CancellationTokenSource();

    var read = await _repository.ReadToAsync<User>(
        "dbo.GetUserById", cts.Token,
        new Microsoft.Data.SqlClient.SqlParameter("@Id", 1));

    Assert.IsNotNull(read);
    Assert.AreEqual(1, read.Id);
}

This example passes a token through the inherited API; it does not test cancellation because the token is never cancelled. A cancellation regression must cancel before or during the operation and assert an OperationCanceledException (or a derived cancellation exception). Do not put Assert.Fail() inside a catch-all block that also catches the assertion failure. The current sample's GetUserByIdAsync(int) wrapper has no token overload; use a token-aware API or add an appropriate wrapper in the consuming application.

For tests that exercise IAsyncEnumerable<T>, import Tests.DAL.Records.Models for this sample's Record type and mapper:

[TestMethod]
public async Task StreamsAllRecords()
{
    var count = 0;
    await foreach (var _ in _repository.ReadToAsyncEnumerable<Record>("dbo.GetRecords"))
        count++;

    Assert.IsTrue(count > 0);
}

MSTest 4 ships an analyzer (MSTEST0017) that flags Assert.AreEqual(actual, expected) calls — the documented order is (expected, actual). Following the analyzer keeps assertion failure messages readable: "Expected: 1, Actual: 0" instead of the inverse.

Assert.AreEqual(expected: 1, actual: user.Id);   // explicit names — always safe
Assert.AreEqual(1, user.Id);                     // canonical positional

If your CI runs unit tests on machines without SQL Server, gate the integration tests:

[TestClass]
public class UserRepositoryTest
{
    [ClassInitialize]
    public static void Init(TestContext _)
    {
        try
        {
            using var conn = new SqlConnection(
                new AppSettings().ConnectionStrings.DatabaseConnection);
            conn.Open();
        }
        catch (Exception ex)
        {
            Assert.Inconclusive($"Test database unreachable: {ex.Message}");
        }
    }

    // ... [TestMethod] ...
}

Or split into two test projects: a fast, DB-free Tests.Unit and an integration Tests.Integration that only runs when an env var (RUN_INTEGRATION_TESTS=1) is set.

After applying a schema or procedure change to a test database, exercise its contract with known data and assert the mapped values. This can expose ordinal/type/nullability mistakes that compilation cannot detect. An illustrative smoke test using the current sample's separate repositories is:

[ClassInitialize]
public static void SmokeTest(TestContext context)
{
    var connectionString = new AppSettings().ConnectionStrings.DatabaseConnection;
    using var users = new Tests.DAL.Users.Repository(connectionString);
    using var graphs = new Tests.DAL.GrandRecords.Repository(connectionString);
    using var folders = new Tests.DAL.Folders.Repository(connectionString);

    Assert.AreEqual(1, users.GetUserById(1).Id);
    Assert.IsNotNull(graphs.GetGrandRecords());
    var root = folders.GetUserRootFolder(14);
    Assert.AreEqual(root.Id, folders.GetFolderTree(root.Id).Id);
}

This is a smoke check, not proof of every SQL contract. Assert distinctive values across columns, null cases, every expected row, result-set transitions, TVP writes, and transaction outcomes in focused tests. Ordinal mappers can intentionally ignore column names, and a manual TVP mapper can deliberately differ from the C# model; test its declared SQL contract rather than imposing a one-property-per-column rule. These checks need an explicitly configured database connection. The Roslyn analyzer does not inspect the live database.

Use the current tests as examples of focused failure cases:

• ReadProjectionRegressionTest: first-row preservation and supplied factories, including scalar/nullable projections and SQL NULL in column zero.
• ExternalTransactionTest: borrowed connection/transaction ownership and unsupported nested/completed transactions.
• MappingCacheTest and ParameterCacheTest: runtime type/schema identity and procedure metadata isolation without sharing mutable values.
• StringParameterTest and DataReplyTest: normalization flags and explicit successful status handling.
• FolderSqlRegressionTest: a folder move with remaining old/new siblings and descendants, complete long hierarchy codes, and null/root/short-code compatibility. Its write fixture is rolled back.

When a regression concerns SQL code, demonstrate the failure against the previous SQL object and success against the corrected one in an isolated test environment. Do not treat a successful C# build as evidence that the database received the fix. See the 4.1.0 overview.

See also:

• Test suite in the repo
• Database project in the repo
• About Connection Strings
• Performance Notes — Pooling=True matters in production