CSharpSqlTests 1.2.0

There is a newer version of this package available.
See the version list below for details.
dotnet add package CSharpSqlTests --version 1.2.0                
NuGet\Install-Package CSharpSqlTests -Version 1.2.0                
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.2.0" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add CSharpSqlTests --version 1.2.0                
#r "nuget: CSharpSqlTests, 1.2.0"                
#r directive can be used in F# Interactive and Polyglot Notebooks. 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.2.0

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

CSharpSqlTests

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

A temporary localDb instance will be spun up, a dacpac will optionally be deployed into it 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

Hopefully the following examples speak for themselves!

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 LocalDbTestContext("SampleDb", log => sink.OnMessage(new DiagnosticMessage(log)));
        Context.DeployDacpac();
    }       

    public void Dispose()
    {
        Context.TearDown();
    }
}

// Or to run tests without using the IClassFixture use the following:
[Fact]
public void Connection_can_be_used_to_deploy_dacpac_and_run_stored_procedure_from_it()
{
    new LocalDbTestContext(DataBaseName, 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();
}

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 Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
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.8 183 1/11/2024
1.7.7 123 1/9/2024
1.7.6 145 1/3/2024
1.7.5 241 6/14/2023
1.7.4 186 6/14/2023
1.7.3 197 6/13/2023
1.7.2 1,303 2/8/2022
1.7.1 1,220 2/8/2022
1.7.0 1,232 2/6/2022
1.6.1 502 1/7/2022
1.6.0 510 1/7/2022
1.5.0 555 12/26/2021
1.4.0 604 12/26/2021
1.3.0 315 12/7/2021
1.2.0 331 12/7/2021
1.1.0 1,420 11/28/2021
1.0.0 422 11/12/2021

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