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 are the methods available from this interface.

  • CreateAverage - used to create a SQL statement for the Average operation.
  • CreateAverageAll - used to create a SQL statement for the AverageAll operation.
  • CreateBatchQuery - used to create a SQL statement for the BatchQuery operation.
  • CreateCount - used to create a SQL statement for the Count operation.
  • CreateCountAll - used to create a SQL statement for the Average operation.
  • CreateDelete - used to create a SQL statement for the Delete operation.
  • CreateDeleteAll - used to create a SQL statement for the DeleteAll operation.
  • CreateExists - used to create a SQL statement for the Exists operation.
  • CreateInsert - used to create a SQL statement for the Insert operation.
  • CreateInsertAll - used to create a SQL statement for the InsertAll operation.
  • CreateMax - used to create a SQL statement for the Max operation.
  • CreateMaxAll - used to create a SQL statement for the MaxAll operation.
  • CreateMerge - used to create a SQL statement for the Merge operation.
  • CreateMergeAll - used to create a SQL statement for the MergeAll operation.
  • CreateMin - used to create a SQL statement for the Min operation.
  • CreateMinAll - used to create a SQL statement for the MinAll operation.
  • CreateQuery - used to create a SQL statement for the Query operation.
  • CreateQueryAll - used to create a SQL statement for the QueryAll operation.
  • CreateSum - used to create a SQL statement for the Sum operation.
  • CreateSumAll - used to create a SQL statement for the SumAll operation.
  • CreateTruncate - used to create a SQL statement for the Truncate operation.
  • CreateUpdate - used to create a SQL statement for the Update operation.
  • CreateUpdateAll - used 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.