Interactions with SqlLocalDB via a SqlConnection.
https://nuget.org/packages/LocalDb/
The snippets use the following helper class:
public static class TestDbBuilder
{
public static async Task CreateTable(SqlConnection connection)
{
await using var command = connection.CreateCommand();
command.CommandText = "create table MyTable (Value int);";
await command.ExecuteNonQueryAsync();
}
static int intData;
public static async Task<int> AddData(SqlConnection connection)
{
await using var command = connection.CreateCommand();
var addData = intData;
intData++;
command.CommandText = $"""
insert into MyTable (Value)
values ({addData});
""";
await command.ExecuteNonQueryAsync();
return addData;
}
public static async Task<List<int>> GetData(SqlConnection connection)
{
var values = new List<int>();
await using var command = connection.CreateCommand();
command.CommandText = "select Value from MyTable";
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
values.Add(reader.GetInt32(0));
}
return values;
}
}SqlInstance needs to be initialized once.
To ensure this happens only once there are several approaches that can be used:
In the static constructor of a test.
If all tests that need to use the SqlInstance existing in the same test class, then the SqlInstance can be initialized in the static constructor of that test class.
[TestFixture]
public class Tests
{
static SqlInstance sqlInstance = new(
name: "StaticConstructorInstance",
buildTemplate: TestDbBuilder.CreateTable);
[Test]
public async Task Test()
{
await using var database = await sqlInstance.Build();
await TestDbBuilder.AddData(database);
var data = await TestDbBuilder.GetData(database);
AreEqual(1, data.Count);
}If multiple tests need to use the SqlInstance, then the SqlInstance should be initialized in the static constructor of test base class.
[TestFixture]
public abstract class TestBase
{
static SqlInstance instance;
static TestBase() =>
instance = new(
name: "TestBaseUsage",
buildTemplate: TestDbBuilder.CreateTable);
public static Task<SqlDatabase> LocalDb(
[CallerFilePath] string testFile = "",
string? databaseSuffix = null,
[CallerMemberName] string memberName = "") =>
instance.Build(testFile, databaseSuffix, memberName);
}
public class Tests :
TestBase
{
[Test]
public async Task Test()
{
await using var database = await LocalDb();
await TestDbBuilder.AddData(database);
var data = await TestDbBuilder.GetData(database);
AreEqual(1, data.Count);
}
}A common mistake is to call Build() once (e.g. in a static constructor) and reuse the resulting ConnectionString across all tests:
// WRONG: defeats per-test isolation
static Connection()
{
var database = sqlInstance.Build("shared").GetAwaiter().GetResult();
ConnectionString = database.ConnectionString;
}
// All tests share the same database
public static SqlConnection OpenConnection() => new(ConnectionString);This defeats the purpose of LocalDb's template cloning. All tests share the same database, requiring manual cleanup (DELETE FROM ...) between tests, preventing parallel execution, and risking test interference.
Instead, call Build() in each test method to get an isolated database clone:
// CORRECT: each test gets its own database
[Test]
public async Task MyTest()
{
await using var database = await sqlInstance.Build();
var connection = database.Connection;
// connection points to a fresh database cloned from the template
}Usage inside a test consists of two parts:
await using var database = await sqlInstance.Build();
await TestDbBuilder.AddData(database);
var data = await TestDbBuilder.GetData(database);
AreEqual(1, data.Count);await TestDbBuilder.AddData(database);
var data = await TestDbBuilder.GetData(database);
AreEqual(1, data.Count);The above are combined in a full test:
[TestFixture]
public class SnippetTests
{
static SqlInstance sqlInstance = new(
name: "Snippets",
buildTemplate: TestDbBuilder.CreateTable);
[Test]
public async Task TheTest()
{
await using var database = await sqlInstance.Build();
await TestDbBuilder.AddData(database);
var data = await TestDbBuilder.GetData(database);
AreEqual(1, data.Count);
}
[Test]
public async Task TheTestWithDbName()
{
await using var database = await sqlInstance.Build("TheTestWithDbName");
await TestDbBuilder.AddData(database);
var data = await TestDbBuilder.GetData(database);
AreEqual(1, data.Count);
}BuildShared creates a single database from the template once and reuses it across calls. This is useful for query-only tests that don't need per-test isolation.
[Test]
public async Task SharedDatabase()
{
using var instance = new SqlInstance(
"SharedDatabase",
TestDbBuilder.CreateTable);
await using var database = await instance.BuildShared();
var data = await TestDbBuilder.GetData(database);
AreEqual(0, data.Count);
}Pass useTransaction: true to get an auto-rolling-back transaction, allowing writes without affecting other tests.
Note: useTransaction: true means that on test failure the resulting database cannot be inspected (since the transaction is rolled back). A workaround when debugging a failure is to temporarily remove useTransaction: true.
[Test]
public async Task SharedDatabase_WithTransaction()
{
using var instance = new SqlInstance(
"SharedDb_Tran",
TestDbBuilder.CreateTable);
await using (var database =
await instance.BuildShared(useTransaction: true))
{
NotNull(database.Transaction);
await using var command =
database.Connection.CreateCommand();
command.Transaction = database.Transaction;
command.CommandText =
"insert into MyTable (Value) values (42);";
await command.ExecuteNonQueryAsync();
}
// Data should be rolled back
await using var database2 =
await instance.BuildShared();
var data = await TestDbBuilder.GetData(database2);
AreEqual(0, data.Count);
}