CSharpSqlTests 1.7.2

.NET Standard 2.0
dotnet add package CSharpSqlTests --version 1.7.2
NuGet\Install-Package CSharpSqlTests -Version 1.7.2
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="CSharpSqlTests" Version="1.7.2" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add CSharpSqlTests --version 1.7.2
#r "nuget: CSharpSqlTests, 1.7.2"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install CSharpSqlTests as a Cake Addin
#addin nuget:?package=CSharpSqlTests&version=1.7.2

// Install CSharpSqlTests as a Cake Tool
#tool nuget:?package=CSharpSqlTests&version=1.7.2

CSharpSqlTests

A testing framework for sql related tests using a nice fluent C# api

Target an existing sql instance, or an existing localDb, or have a temporary localDb instance spun up specially, optionally deploy a dacpac and then tests can be executed each within their own SqlTransaction.

Given, When and Then helper classes are supplied:

  • Given: used to seed test data, remove FK constraints etc
  • When: used to run a stored procedure/reader query/scalar query etc whatever you are trying to test
  • Then: a nice way to do assertions and/or check the result of a query from the When step

Test data can be expressed as markdown/specflow tables in the tests, which are easier to read than plain sql strings

Getting started

Hopefully the following examples speak for themselves!

// Install either CSharpSqlTests.xUnit or CSharpSqlTests.NUnit nuget packages, depending on your choice of test framework, they both bring in the core package CSharpSqlTests.

// The quickest and easiest way to start writing tests would be something like this which uses the connection directly rather than any test helpers:
[Fact]
public void Connection_can_be_used_to_deploy_dacpac_and_run_stored_procedure_from_it()
{
    new DbTestContext(DatabaseName, 
        DbTestContextMode.TemporaryLocalDbInstance, 
        writeToOutput: message => _testOutputHelper.WriteLine(message))
        .DeployDacpac()
        .RunTest((connection, transaction) =>
        {
            var cmd = connection.CreateCommand();
            cmd.CommandText = "spAddTwoNumbers";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.AddParameterWithValue("@param1", 2);
            cmd.AddParameterWithValue("@param2", 3);
            cmd.Transaction = transaction;

            var returnParameter = cmd.AddReturnParameter("@ReturnVal");

            cmd.ExecuteNonQuery();
            var result = returnParameter.Value;

            result.Should().NotBeNull();
            result.Should().Be(5);
        }).TearDown();
}
// but here each test will get its own localDb instance and DacPac deployment etc which can be expensive.

// A better way would be to share a single context accross all tests in a class, use xUnit's `IClassFixture<T>` like this:
public class SampleDatabaseTestsUsingASingleContext : IClassFixture<LocalDbContextFixture>
{
    private readonly ITestOutputHelper _testOutputHelper;
    private readonly LocalDbContextFixture _localDbContextFixture;
    private readonly LocalDbTestContext _context;

    public SampleDatabaseTestsUsingASingleContext(ITestOutputHelper testOutputHelper, LocalDbContextFixture localDbContextFixture)
    {
        _testOutputHelper = testOutputHelper;
        _localDbContextFixture = localDbContextFixture;
        _context = _localDbContextFixture.Context;
    }
        
    [Fact]
    public void helper_classes_can_be_used_to_deploy_dacpac_and_run_stored_procedure_from_it()
    {
        _context.RunTest((connection, transaction) =>
        {
            Given.UsingThe(_context);

            When.UsingThe(_context)
                .TheStoredProcedureIsExecuted("spAddTwoNumbers", out var returnValue, ("@param1", 5), ("@param2", 12));

            Then.UsingThe(_context)
                .TheLastQueryResultShouldBe(17);

            // Or
            // returnValue.Should().Be(17);
        });
    }

    [Fact]
    public void test_dropping_fk_constring_reduce_seeding_requirements() 
    {
        _context.RunTest((connection, transaction) => 
        {
            var expectedOrder = @"
                | Id | Customers_Id | DateCreated | DateFulfilled  | DatePaid | ProductName | Quantity | QuotedPrice | Notes       |
                | -- | ------------ | ----------- | -------------- | -------- | ----------- | -------- | ----------- | ----------- |
                | 23 | 1            | 2021/07/21  | 2021/08/02     | null     | Apples      | 21       | 5.29        | emptyString |";

            Given.UsingThe(_context)
            .TheFollowingSqlStatementIsExecuted("ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Customers;")
            .And.TheFollowingDataExistsInTheTable("Orders", expectedOrder);

            When.UsingThe(_context)
            .TheStoredProcedureIsExecutedWithReader("spFetchOrderById", ("OrderId", 23));

            // Either assert on the whole result
            Then.UsingThe(_context)
            .TheReaderQueryResultsShouldBe(expectedOrder);

            // Or just assert on a subset
            Then.UsingThe(_context)
                .TheReaderQueryResultsShouldContain(@"| Id |
                                                      | -- |
                                                      | 23 |");

        });
    }    
}

// The above xunit tests use the following IClassFixture class, which enables the localdb instance to be spun up once, to be used by each test and afterwards torn down.
public class LocalDbContextFixture : IDisposable
{
    public LocalDbTestContext Context;

    public LocalDbContextFixture(IMessageSink sink)
    {
        Context = new DbTestContext(DatabaseName, 
                    DbTestContextMode.ExistingDatabaseViaConnectionString, 
                    existingDatabaseConnectionString: "Server=.\\SQLExpress; Integrated Security=true", 
                    writeToOutput: message => _testOutputHelper.WriteLine(message));
        Context.DeployDacpac(); // If the DacPac name does not match the database name, pass the DacPac name in here, or an absolute path to the file.
    }       

    public void Dispose()
    {
        Context.TearDown(); // this closes connections and tidies up the temporary localDb instance
    }
}

This is mainly written to be an improvement in user friendliness over some of the t-SQL based test frameworks available

Feel free to contribute

license is MIT

enjoy 😃

Product Versions
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows net7.0 net7.0-android net7.0-ios net7.0-maccatalyst net7.0-macos net7.0-tvos net7.0-windows
.NET Core netcoreapp2.0 netcoreapp2.1 netcoreapp2.2 netcoreapp3.0 netcoreapp3.1
.NET Standard netstandard2.0 netstandard2.1
.NET Framework net461 net462 net463 net47 net471 net472 net48 net481
MonoAndroid monoandroid
MonoMac monomac
MonoTouch monotouch
Tizen tizen40 tizen60
Xamarin.iOS xamarinios
Xamarin.Mac xamarinmac
Xamarin.TVOS xamarintvos
Xamarin.WatchOS xamarinwatchos
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (2)

Showing the top 2 NuGet packages that depend on CSharpSqlTests:

Package Downloads
CSharpSqlTests.xUnit

A simple framework for running sql tests against a temprary localdb instance, optionally deploying a dacpac, using a nice fluent c# api. This package contains some xUnit specific assertions.

CSharpSqlTests.NUnit

A simple framework for running sql tests against a temprary localdb instance, optionally deploying a dacpac, using a nice fluent c# api. This package contains some NUnit specific assertions.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.7.2 917 2/8/2022
1.7.1 825 2/8/2022
1.7.0 851 2/6/2022
1.6.1 349 1/7/2022
1.6.0 338 1/7/2022
1.5.0 396 12/26/2021
1.4.0 450 12/26/2021
1.3.0 237 12/7/2021
1.2.0 255 12/7/2021
1.1.0 1,345 11/28/2021
1.0.0 351 11/12/2021

1.7.2) Re-ordered DbTestContext constructor arguments
1.7.1) ExistingDbViaConnectionStringContext.CreateConnection now returns a Microsoft.Data.SqlClient SqlConnection
1.7.0) Added support for targetting standalone sql instances via connection string.
1.6.1) When using normal persistent localDb instance, only stop afterwards if it wasn't already started.
1.6.0) Added support for running in an ordinary persistent localDb instance i.e. for SqlCover etc, also some internal improvements around use of IDataReader and some tidying up.
1.5.0) Removed some experimental code for parallel execution - which is not supported currently.
1.4.0) Added some additional methods, the ability to set transaction isolation level to aid debugging, fixed bug when using Guids.
1.3.0) Added some more When and Then methods and a ValueAt() method for looking up data from a TabularData.
1.2.0) Then has some new query methods. Given, When and Then now have a public context to allow the use of extension methods, PDBs included in package for debugging.
1.1.0) targeted netstandard2, made Given, When and Then classes partial and added some triple slash comments.
1.0.0) initial version