Awesome

<img src="/src/icon.png" height="30px"> Verify.SqlServer

Extends Verify to allow verification of SqlServer bits.

See Milestones for release notes.

NuGet package

https://nuget.org/packages/Verify.SqlServer/

Usage

<a id='snippet-Enable'></a>

[ModuleInitializer]
public static void Init() =>
    VerifySqlServer.Initialize();

^{<a href='/src/Tests/ModuleInit.cs#L3-L9' title='Snippet source file'>snippet source</a> | <a href='#snippet-Enable' title='Start of snippet'>anchor</a>}

SqlServer Schema

This test:

<a id='snippet-SqlServerSchema'></a>

await Verify(connection);

^{<a href='/src/Tests/Tests.cs#L84-L88' title='Snippet source file'>snippet source</a> | <a href='#snippet-SqlServerSchema' title='Start of snippet'>anchor</a>}

Will result in the following verified file:

Object types to include

<a id='snippet-SchemaInclude'></a>

await Verify(connection)
    // include only tables and views
    .SchemaIncludes(DbObjects.Tables | DbObjects.Views);

^{<a href='/src/Tests/Tests.cs#L390-L396' title='Snippet source file'>snippet source</a> | <a href='#snippet-SchemaInclude' title='Start of snippet'>anchor</a>}

Available values:

<a id='snippet-DbObjects.cs'></a>

namespace VerifyTests.SqlServer;

[Flags]
public enum DbObjects
{
    StoredProcedures = 1,
    Synonyms = 2,
    Tables = 4,
    UserDefinedFunctions = 8,
    Views = 16,
    All = StoredProcedures | Synonyms | Tables | UserDefinedFunctions | Views
}

^{<a href='/src/Verify.SqlServer/SchemaValidation/DbObjects.cs#L1-L12' title='Snippet source file'>snippet source</a> | <a href='#snippet-DbObjects.cs' title='Start of snippet'>anchor</a>}

Filtering

Objects can be dynamically filtered:

<a id='snippet-SchemaFilter'></a>

await Verify(connection)
    // include tables & views, or named MyTrigger
    .SchemaFilter(
        _ => _ is TableViewBase ||
             _.Name == "MyTrigger");

^{<a href='/src/Tests/Tests.cs#L415-L423' title='Snippet source file'>snippet source</a> | <a href='#snippet-SchemaFilter' title='Start of snippet'>anchor</a>}

Recording

Recording allows all commands executed to be captured and then (optionally) verified.

Call SqlRecording.StartRecording():

<a id='snippet-Recording'></a>

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
Recording.Start();
await using var command = connection.CreateCommand();
command.CommandText = "select Value from MyTable";
var value = await command.ExecuteScalarAsync();
await Verify(value!);

^{<a href='/src/Tests/Tests.cs#L199-L209' title='Snippet source file'>snippet source</a> | <a href='#snippet-Recording' title='Start of snippet'>anchor</a>}

Will result in the following verified file:

<a id='snippet-Tests.RecordingUsage.verified.txt'></a>

{
  target: 42,
  sql: {
    Text: select Value from MyTable,
    HasTransaction: false
  }
}

^{<a href='/src/Tests/Tests.RecordingUsage.verified.txt#L1-L7' title='Snippet source file'>snippet source</a> | <a href='#snippet-Tests.RecordingUsage.verified.txt' title='Start of snippet'>anchor</a>}

Sql entries can be explicitly read using SqlRecording.FinishRecording, optionally filtered, and passed to Verify:

<a id='snippet-RecordingSpecific'></a>

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
Recording.Start();
await using var command = connection.CreateCommand();
command.CommandText = "select Value from MyTable";
var value = await command.ExecuteScalarAsync();

await using var errorCommand = connection.CreateCommand();
errorCommand.CommandText = "select Value from BadTable";
try
{
    await errorCommand.ExecuteScalarAsync();
}
catch
{
}

var entries = Recording
    .Stop()
    .Select(_ => _.Data);
//Optionally filter results
await Verify(
    new
    {
        value,
        sqlEntries = entries
    });

^{<a href='/src/Tests/Tests.cs#L276-L306' title='Snippet source file'>snippet source</a> | <a href='#snippet-RecordingSpecific' title='Start of snippet'>anchor</a>}

Interpreting recording results

Recording results can be interpreted in a a variety of ways:

<a id='snippet-RecordingReadingResults'></a>

var entries = Recording.Stop();

// all sql entries via key
var sqlEntries = entries
    .Where(_ => _.Name == "sql")
    .Select(_ => _.Data);

// successful Commands via Type
var sqlCommandsViaType = entries
    .Select(_ => _.Data)
    .OfType<SqlCommand>();

// failed Commands via Type
var sqlErrorsViaType = entries
    .Select(_ => _.Data)
    .OfType<ErrorEntry>();

^{<a href='/src/Tests/Tests.cs#L332-L351' title='Snippet source file'>snippet source</a> | <a href='#snippet-RecordingReadingResults' title='Start of snippet'>anchor</a>}

Icon

Database designed by Creative Stall from The Noun Project.