ICommand

ICommand implementations

TLDR;

There is a base class provided which implement required ICommand interface with boilerplate code. Using it - simple command implementation would look something like this:

public sealed class ArtistUpdateCommand : MsSqlCommandBase
{
    // Taking via constructor object to be saved and storing it in private field
    private readonly Artist _dbObject;
    public ArtistUpdateCommand(Artist dbObject) => _dbObject;
    
    // Assigning passed in object to query paremeter as-is (its properties will be used).
    public override object Parameters => _dbObject;
    
    // Here @Name and @ArtistId are properties of Artist object (which is passed in in constructor)
    public override string SqlStatement => @"UPDATE Artist SET Name = @Name WHERE ArtistId = @ArtistId";
}

Overall approach

Actual data modification SQL statements should be wrapped into ICommand interface implementation classes. This ensures immutability of given parameters for command and standard way on how to implement and compose all these SQL commands within application. There are two interfaces - generic and non-generic with the same property and method demands, but non-generic ICommand is for commands without data return, whereas ICommand<T> is to be used when command also returns some data.

Provided BaseClasses with boilerplate implementations

For MS SQL Server there are two base classes provided in Abstractions package, which are covering most of the interface requirements, so your command classes can be much more slim with less boilerplate code:

• MsSqlCommandBase — to be used when command intention is to only modify data without returning anything.

• MsSqlCommandBase<T> — to be used when command also returns some data after modification.

It provides default Execute and ExcuteAsync methods. As it implements also IQueryValidator, SQL statement validation method is containing logic required for that purpose. (See Testing). As testing also requires SqlStatement and Parameters properties, when using this base class for implementation - put override for these properties.

public sealed class ArtistUpdateCommand : MsSqlCommandBase
{
    private readonly Artist _dbObject;
    public ArtistUpdateCommand(Artist dbObject) =>
        _dbObject = dbObject ?? throw new ArgumentNullException(nameof(dbObject), "No data passed for Artist update");

    public override object Parameters => _dbObject;
    public override string SqlStatement => @"
        UPDATE Artist
           SET Name = @Name
         WHERE ArtistId = @ArtistId";
}


public sealed class ArtistDeleteCommand : MsSqlCommandBase
{
    private readonly int _dbId;
    public ArtistDeleteCommand(int dbId)
    {
        if (dbId == 0)
        {
            throw new ArgumentNullException(nameof(dbId), "Artist delete got 0 as ID.");
        }
        _dbId = dbId;
    }

    public override object Parameters => new { id = _dbId };
    public override string SqlStatement => @"
        DELETE FROM Artist
         WHERE ArtistId = @id";
}


public sealed class ArtistCreateCommand : MsSqlCommandBase<int>
{
    private readonly Artist _dbObject;
    public ArtistCreateCommand(Artist dbObject) =>
        _dbObject = dbObject ?? throw new ArgumentNullException(nameof(dbObject), "No data passed for Artist create");

    public override object Parameters => _dbObject;

    // Two statements - first inserts data, second selects (returns) last inserted record autoincrement value from DB.
    public override string SqlStatement => @"
        INSERT INTO Artist (
            Name
        ) VALUES (
            @Name
        );
        SELECT CAST(SCOPE_IDENTITY() as int)";
}

public sealed class TestTableCreateCommand : MsSqlCommandBase
{
    public override string SqlStatement => "CREATE TABLE dbo.TestColumnTypes (Id INT PRIMARY KEY CLUSTERED NOT NULL, Title NVARCHAR(25) NOT NULL)";
}

ICommand members

• Properties
• Methods

These interface demands must be implemented:

Properties

string SqlStatement { get; }

ICommandValidator demand. A string, containing actual SQL statement, which will be executed against database connection. When using base class - this gets overridden 100% cases.

Examples:

// Returning ID (two statements)
public string SqlStatement => @"
INSERT INTO Artist (
    Name
) VALUES (
    @Name
);
SELECT CAST(SCOPE_IDENTITY() as int)";
 
public string SqlStatement => @"
UPDATE Artist
   SET Name = @Name
 WHERE ArtistId = @ArtistId";

public string SqlStatement => @"
DELETE FROM Artist
      WHERE ArtistId = @id";

object Parameters { get; }

ICommandValidator demand. Prepares anonymous object for command, if it needs parameters. You can set it to null, if it is not required (rare with commands). Also usually getting overridden when using base class.

Examples:

// No parameters needed - default (base class) implementation.
public object Parameters => null;

// Just some ID (@id)
public object Parameters => new { id = _objectId };

// More complex (@ParentId and @Name)
public object Parameters => new { ParentId = _objectId, Name = "Aero" };

// Strongly typed object (use its properties in SQL)
public object Parameters => _person;

Methods

Task<T> ExecuteAsync(IDatabaseSession session)

From ICommand<T> interface!

Actual method for asynchronous approach called when SQL needs to be executed and data returned. Usually contains only session method call, passing SqlStatement and Parameters parameters, but can be extended as any normal method to include much more preparation and data manipulation as part of it.

If not used (synchronous method is used) - implementation can be omitted.

Samples:

// Returns some int value (like inserted ID)
public async Task<int> ExecuteAsync(IDatabaseSession session)
    => await session.ExecuteAsync<int>(this.SqlStatement, this.Parameters);

T Execute(IDatabaseSession session)

From ICommand<T> interface!

Actual method for synchronous approach called when SQL needs to be executed and some data returned. Usually contains only session method call, passing SqlStatement and Parameters parameters, but can be extended as any normal method to include much more preparation and data manipulation as part of it.

If not used (async method is used), implementation can be omitted.

Samples:

// Returns some int (like inserted ID)
public int Execute(IDatabaseSession session)
    => session.Execute<int>(this.SqlStatement, this.Parameters);

Task ExecuteAsync(IDatabaseSession session)

From ICommand interface!

Actual method for asynchronous approach called when SQL needs to be executed without returning data. Usually contains only session method call, passing SqlStatement and Parameters parameters, but can be extended as any normal method to include much more preparation and data manipulation as part of it.

If not used (synchronous method is used) - implementation can be omitted.

Samples:

public async Task ExecuteAsync(IDatabaseSession session)
    => await session.ExecuteAsync(this.SqlStatement, this.Parameters);

void Execute(IDatabaseSession session)

From ICommand interface!

Actual method for synchronous approach called when SQL needs to be executed without data being returned. Usually contains only session method call, passing SqlStatement and Parameters parameters, but can be extended as any normal method to include much more preparation and data manipulation as part of it.

If not used (async method is used), implementation can be omitted.

Samples:

public void Execute(IDatabaseSession session)
    => session.Execute(this.SqlStatement, this.Parameters);

void Validate(IDatabaseSession databaseSession)

ICommandValidator interface demand.

Method which can be called by some validation logic where you can write code to validate SqlStatement. In case if your logic finds something being invalid in SQL statement:

throw new DatabaseStatementSyntaxException(result, this.SqlStatement);

Leave empty if you do not require any validation (or using base class)