# IQuery implementations

## TLDR;
There are base classes provided which implement required `IQuery` interface. In simplest case scenario it needs only SQL statement to be specified in it *and that's it!*:

```csharp
public sealed class GetAllRecordsQuery : MsSqlQueryMultipleBase<SampleData>
{
    public override string SqlStatement => "SELECT * FROM SampleDataTable";
}
```

For single parameter it becomes a bit more bigger:

```csharp
public sealed class SampleDataByIdQuery : MsSqlQuerySingleBase<SampleData>
{
    private readonly int _id;
    public SampleDataByIdQuery(int objectId) => _id = objectId;
    public override object Parameters => new { id = _id };
    public override string SqlStatement => @"SELECT Field1, Field2 FROM SampleDataTable WHERE Id = @id";
}
```

For more complex cases and variations - keep reading :-)




## Overall approach
Actual data retrieval SQL statements should be wrapped into `IQuery` interface implementation classes. This ensures immutability of given parameters for query and standard approach on how to implement and compose all SQL queries within application.




## 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 query classes can be much more slim with less boilerplate code:

* `MsSqlQuerySingleBase` — to be used when query is intended to retrieve single record or single value from database.

* `MsSqlQueryMultipleBase` — to be used when query is intended to retrieve multiple records or values (`IEnumerable<T>`) from database.

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](Testing)). As testing also requires `SqlStatement` and `Parameters` properties, when using this base class for implementation - put `override` for these properties.

```csharp
// Your implementation becomes much less boilerplate:
public sealed class GetAllRecordsQuery : MsSqlQueryMultipleBase<SampleData>
{
    public override string SqlStatement => "SELECT * FROM SampleDataTable";
    // No parameters needed, so Parameters property can be omitted.
}

public sealed class GetNamesByRefIdQuery : MsSqlQueryMultipleBase<string>
{
    // Getting parameter(s) for query in the constructor and storing them in private field(s)
    private readonly int _id;
    public GetNamesByRefIdQuery(int id) => _id = id;

    // Assign private fields in dynamic abstract object in a way query can consume them
    public override object Parameters => new { RefId = _id };
    
    // Query itself, taking @RefId paramter from Parameters property (above)
    public override string SqlStatement => "SELECT Name FROM Table WHERE FkId = @RefId";
}

public sealed class SampleDataByIdQuery : MsSqlQuerySingleBase<SampleData>
{
    private readonly int _objectId;
    public SampleDataByIdQuery(int objectId) => _objectId = objectId;

    public override object Parameters => new { id = _objectId };
    public override string SqlStatement => @"
        SELECT Field1, Field2
          FROM SampleDataTable
         WHERE Id = @id";
}

public sealed class SampleDataSearchQuery : MsSqlQueryMultipleBase<SampleData>
{
    // Taking stronngly typed object as parameters (its properties) into query class
    private readonly SearchFields _searchData;
    public SampleDataSearchQuery(SearchFields searchOn) => _searchData = searchOn;

    // Just setting typed object to Parameters for query as is (Dapper will use its properties)
    public override object Parameters => _searchData;
    
    // @Name and @Surname are properties of SearchFields class.
    public override string SqlStatement => @"
        SELECT Field1, Field2
          FROM SampleDataTable
         WHERE FirstName LIKE CONCAT('%',@Name,'%') AND FamilyName LIKE CONCAT('%',@Surname,'%')";
}

// Just for reference - search object passed into query above
public class SeachFields
{
    public string Name { get; set; }
    public string Surname { get; set; }
}
```


### Base class EXECUTE overrides
There is already shown necessary overrides for properties `SqlStatement` and `Parameters` used in samples above.

Same way you can override `Execute` method to add or change logic there:

```csharp
public override object Parameters => _dbObject;

public override async Task<int> ExecuteAsync(IDatabaseSession session)
{
    // Modify something in passed in Parameters
    _dbObject.DateModified = DateTime.Now;

    // Issue additional Command for some logging in database (beware - tests are not checking inline statements!)
    await session.ExecuteAsync("INSERT INTO Log (statement) VALUES (@stt)", new { stt = "insert new data" });

    return await session.ExecuteAsync<int>(this.SqlStatement, this.Parameters);
}

```





## IQuery members

* [Properties](#properties)
* [Methods](#methods)

If you would implement pure `IQuery` interface without base class use, these interface demands must be implemented:

#### Properties

##### `string SqlStatement { get; }`
*`IQueryValidator` demand.*
A string, containing actual SQL statement, which will be executed against database connection. This property is the one getting overridden *always* when using base classes, so everything here applies to base class usage as well.

Examples:
```csharp
// Without parameters
public string SqlStatement => @"
SELECT AlbumId,
       ArtistId,
       Title
  FROM Album";
 
// With parameter (MS SQL)
public string SqlStatement => @"
SELECT AlbumId,
       ArtistId,
       Title
  FROM Album
 WHERE ArtistId = @id";
```

##### `object Parameters { get; }`
*`IQueryValidator` demand.*
Prepares anonymous object for query, if it needs parameters. You can set it to null, if it is not required. It usually is getting overridden when using base classes, so everything here applies to base class usage as well.

Examples:
```csharp
// No parameters needed - this is code implemented in base class
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)`
Actual method for asynchronous approach called when SQL needs to be executed. 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:
```csharp
// Getting many records without query parameters
public async Task<IEnumerable<Album>> ExecuteAsync(IDatabaseSession session)
    => await session.QueryAsync<Album>(this.SqlStatement);

// Getting one record
public async Task<Album> ExecuteAsync(IDatabaseSession session)
    => await session.QueryFirstOrDefaultAsync<Album>(this.SqlStatement, this.Parameters);
```

#### `T Execute(IDatabaseSession session)`
Actual method for synchronous approach called when SQL needs to be executed. 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:
```csharp
// Getting many records without query parameters
public IEnumerable<Album> Execute(IDatabaseSession session)
    => session.Query<Album>(this.SqlStatement);

// Getting one record
public Album Execute(IDatabaseSession session)
    => session.QueryFirstOrDefault<Album>(this.SqlStatement, this.Parameters);
```

#### `void Validate(IDatabaseSession databaseSession)`
*`IQueryValidator` demand.*
Method which can be called by some validation logic where you can write code to validate `SqlStatement`. In case your validation logic finds something being invalid in it:
```csharp
throw new DatabaseStatementSyntaxException(result, this.SqlStatement);
```
Leave empty if you do not need any validation. Base classes implement this method with some additional requirements in database itself (see [Testing](QueryTesting.md))

### Entire pure IQuery implementation samples
Although it is strongly encouraged to use Query base classes (see topic above), here are few samples on how to implement `IQuery` interface directly.

```csharp
// Simplest, almost empty implementation
public sealed class GetAllRecordsQuery : IQuery<IEnumerable<SampleData>>
{
    public string SqlStatement => "SELECT * FROM SampleDataTable";
    public async Task<IEnumerable<SampleData>> ExecuteAsync(IDatabaseSession session)
            => await session.QueryAsync<SampleData>(this.SqlStatement);
    public IEnumerable<SampleData> Execute(IDatabaseSession session)
            => session.Query<SampleData>(this.SqlStatement);
}

// With parameter
public sealed class GetNamesByRefIdQuery : IQuery<IEnumerable<string>>
{
    private readonly int _id;
    public GetNamesByRefIdQuery(int id) => _id = id;
    
    public string SqlStatement => "SELECT Name FROM Table WHERE FkId = @RefId";
    public object Parameters => new { RefId = _id };
    public async Task<IEnumerable<string>> ExecuteAsync(IDatabaseSession session)
            => await session.QueryAsync<SampleData>(this.SqlStatement, this.Parameters);
    public IEnumerable<string> Execute(IDatabaseSession session)
            => session.Query<SampleData>(this.SqlStatement, this.Parameters);
}

// With IQueryValidator implementation 
public sealed class GetNamesByRefIdQuery : IQuery<IEnumerable<string>>, IQueryValidator
{
    private readonly int _id;
    public GetNamesByRefIdQuery(int id) => _id = id;
    
    public string SqlStatement => "SELECT Name FROM Table WHERE FkId = @RefId";
    public object Parameters => new { RefId = _id };
    public async Task<IEnumerable<string>> ExecuteAsync(IDatabaseSession session)
            => await session.QueryAsync<SampleData>(this.SqlStatement, this.Parameters);
    public IEnumerable<string> Execute(IDatabaseSession session)
            => session.Query<SampleData>(this.SqlStatement, this.Parameters);
    public void Validate(IDatabaseSession session)
    {
        // Your validation logic here
    }
}
```