Verify.SqlServer

Extends Verify to allow verification of SqlServer bits.

See Milestones for release notes.

NuGet package

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

Usage

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

^{snippet source | anchor}

SqlServer Schema

This test:

await Verify(connection);

^{snippet source | anchor}

Will result in the following verified file:

## Tables

### MyOtherTable

```sql
CREATE TABLE [dbo].[MyOtherTable](
	[Value] [int] NULL
) ON [PRIMARY]
```

### MyTable

```sql
CREATE TABLE [dbo].[MyTable](
	[Value] [int] NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable]
(
	[Value] ASC
) ON [PRIMARY]

CREATE TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);

ALTER TABLE [dbo].[MyTable] ENABLE TRIGGER [MyTrigger]
```

## Views

### MyView

```sql
CREATE VIEW MyView
AS
  SELECT Value
  FROM MyTable
  WHERE (Value > 10);
```

## StoredProcedures

### MyProcedure

```sql
CREATE PROCEDURE MyProcedure
AS
BEGIN
  SET NOCOUNT ON;
  SELECT Value
  FROM MyTable
  WHERE (Value > 10);
END;
```

## UserDefinedFunctions

### MyFunction

```sql
CREATE FUNCTION MyFunction(
  @quantity INT,
  @list_price DEC(10,2),
  @discount DEC(4,2)
)
RETURNS DEC(10,2)
AS
BEGIN
    RETURN @quantity * @list_price * (1 - @discount);
END;
```

## Synonyms

### synonym1

```sql
CREATE SYNONYM [dbo].[synonym1] FOR [MyTable]
```

Object types to include

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

^{snippet source | anchor}

Available values:

namespace VerifyTests.SqlServer;

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

^{snippet source | anchor}

Filtering

Objects can be dynamically filtered:

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

^{snippet source | anchor}

Recording

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

Call SqlRecording.StartRecording():

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!);

^{snippet source | anchor}

Will result in the following verified file:

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

^{snippet source | anchor}

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

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
    });

^{snippet source | anchor}

Interpreting recording results

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

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>();

^{snippet source | anchor}

Icon

Database designed by Creative Stall from The Noun Project.