Link Search Menu Expand Document

IStatementBuilder


This interface is used to mark a class to be a statement builder object. I provides necessary methods for your to be able to override the way how the SQL statements are being constructed.

Methods

Below is the list of methods.

NameDescription
CreateAverageUsed to create a SQL statement for the Average operation.
CreateAverageAllUsed to create a SQL statement for the AverageAll operation.
CreateBatchQueryUsed to create a SQL statement for the BatchQuery operation.
CreateCountUsed to create a SQL statement for the Count operation.
CreateCountAllUsed to create a SQL statement for the Average operation.
CreateDeleteUsed to create a SQL statement for the Delete operation.
CreateDeleteAllUsed to create a SQL statement for the DeleteAll operation.
CreateExistsUsed to create a SQL statement for the Exists operation.
CreateInsertUsed to create a SQL statement for the Insert operation.
CreateInsertAllUsed to create a SQL statement for the InsertAll operation.
CreateMaxUsed to create a SQL statement for the Max operation.
CreateMaxAllUsed to create a SQL statement for the MaxAll operation.
CreateMergeUsed to create a SQL statement for the Merge operation.
CreateMergeAllUsed to create a SQL statement for the MergeAll operation.
CreateMinUsed to create a SQL statement for the Min operation.
CreateMinAllUsed to create a SQL statement for the MinAll operation.
CreateQueryUsed to create a SQL statement for the Query operation.
CreateQueryAllUsed to create a SQL statement for the QueryAll operation.
CreateSumUsed to create a SQL statement for the Sum operation.
CreateSumAllUsed to create a SQL statement for the SumAll operation.
CreateTruncateUsed to create a SQL statement for the Truncate operation.
CreateUpdateUsed to create a SQL statement for the Update operation.
CreateUpdateAllUsed to create a SQL statement for the UpdateAll operation.

Use-Cases

This is very useful if you wish to override the existing statement builder of the library, or wish to support the other RDBMS database providers.

Please visit the Statement Builder to learn more about the statement builder.

How to Implement?

You have to manually create a class that implements this interface.

public class OptimizedSqlServerStatementBuilder : IStatementBuilder
{
    private IDbSetting _dbSetting = new SqlServerDbSetting();

    public string CreateAverage(QueryBuilder queryBuilder,
        string tableName,
        Field field,
        QueryGroup where = null,
        string hints = null)
    {
        // Initialize the builder
        var builder = queryBuilder ?? new QueryBuilder();

        // Build the query
        builder.Clear()
            .Select()
            .Average(field, DbSetting, ConvertFieldResolver)
            .WriteText($"AS {"AverageValue".AsQuoted(DbSetting)}")
            .From()
            .TableNameFrom(tableName, DbSetting)
            .HintsFrom(hints)
            .WhereFrom(where, DbSetting)
            .End();

        // Return the query
        return builder.GetString();
    }

    ...

    public string CreateQuery(QueryBuilder queryBuilder,
        string tableName,
        IEnumerable<Field> fields,
        QueryGroup where = null,
        IEnumerable<OrderField> orderBy = null,
        int? top = null,
        string hints = null)
    {
        // Initialize the builder
        var builder = queryBuilder ?? new QueryBuilder();

        // Build the query
        builder.Clear()
            .Select()
            .TopFrom(top)
            .FieldsFrom(fields, DbSetting)
            .From()
            .TableNameFrom(tableName, DbSetting)
            .HintsFrom(hints)
            .WhereFrom(where, DbSetting)
            .OrderByFrom(orderBy, DbSetting)
            .End();

        // Return the query
        return builder.GetString();
    }

    ...
}

Usability

You can instantiate a new instance and pass it when you are calling any fluent methods.

var statementBuilder = new OptimizedSqlServerStatementBuilder();
using (var connection = new SqlConnection(connectionString))
{
    var people = connection.QueryAll<Person>(statementBuilder: statementBuilder);
}

Or, you can pass it on the constructor of BaseRepository or DbRepository.

// Repository class implementation
public class PersonRepository : BaseRepository<Person, SqlConnection>
{
    public PersonRepository(string connectionString)
        : base(connectionString, new OptimizedSqlServerStatementBuilder())
    { }
}

// Repository class usability
using (var repository = new PersonRepository(connectionString))
{
    var people = connection.QueryAll();
}

Or, you can use the StatementBuilderMapper class to map it with specific RDBMS data provider.

StatementBuilderMapper.Map(typeof(SqlConnection), new OptimizedSqlServerStatementBuilder(), true);

By using the StatementBuilderMapper, the library will automatically use the mapped statement builder when calling the DbConnection, BaseRepository or DbRepository methods. It will vary on the type of the DbConnection object you used.