DapperAid 1.2.1

DapperAid

DapperAidは、DapperによるデータベースのCRUD操作を支援するSQL自動生成・実行ライブラリです。

• データベースのSelect, Insert, Update, Deleteの操作を、IDbConnection / IDbTransactionの拡張メソッドとして提供します。
• 実行SQLは、POCOオブジェクトに付与した属性に基づき、内蔵のクエリビルダが自動生成します。
• 実行SQLのWhere条件は、POCOオブジェクトのKey項目の値、または、ラムダ式（式木）の記述をもとに生成されます。
• 属性付与/メソッド引数指定により、生成実行されるSQLの内容をカスタマイズできます。
  （必要な部分だけ手書きのSQLを混在させることもある程度可能です）
  • Select時のfor update指定、orderby列指定、offset / limit条件、groupby要否、distinct指定など
  • Select, Insert, Update対象とするカラムの限定
  • Insert時 / Update時の設定値(設定せずDBデフォルト値に任せることも可)
  • Insert時のIdentity/AutoIncrement自動採番値把握(各DBMS対応)
• その他オプション機能（使用任意）:
  • 簡易コードファースト（POCO定義内容からCreateTableのSQLを生成）
  • SQL実行ログ取得（クエリビルダが生成したSQLの内容をトレース確認可能）
• 対応DBMS： Oracle, MySQL, Postgres, SQLite, SQLServer, MS-Access, DB2

DapperAid is a SQL automatic generation and execution library that assists database CRUD operation using Dapper.

• Provides Select, Insert, Update and Delete operations of the database as extension methods of IDbConnection / IDbTransaction.
• Execution SQL is automatically generated by the built-in query builder based on the attribute given to the POCO object.
• The execution SQL Where condition is generated based on the value of the key item of POCO object or the description of lambda expression (expression tree).
• You can customize the contents of generated SQL by specifying attribute assignment / method argument specification.
  (It is also possible to mix handwritten SQL in specific places.)
  • Row-Lock, Order-by, offset / limit conditions, need of group-by, specification of distinct and so on at the time of Select
  • Select / Insert / Update only specific columns
  • Setting value at Insert / Update (It is also possible to leave it to the DB default value without setting)
  • Retrieve inserted Identity / AutoIncrement value (for each DBMS)
• Other extra features (use is optional) :
  • A little code-first (Generate Create-Table SQL from POCO definition contents)
  • SQL execution log acquisition (It is possible to view the SQL generated by the query builder)

Installation

from NuGet https://www.nuget.org/packages/DapperAid

PM> Install-Package DapperAid

> dotnet add package DapperAid

Examples

Sample table

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using DapperAid.DataAnnotations;

[Table("Members")]
[SelectSql(DefaultOtherClauses = "order by Id")]
class Member
{
    [Key]
    [InsertValue(false, RetrieveInsertedId = true)]
    [DapperAid.Ddl.DDL("INTEGER")] // (for extra feature, generating Create-Table-SQL as SQLite Identity Column)
    public int Id { get; set; }

    public string Name { get; set; }

    [Column("Phone_No")]
    public string Tel { get; set; }

    [InsertValue("CURRENT_TIMESTAMP"), UpdateValue(false)]
    public DateTime? CreatedAt { get; set; }

    [InsertValue("CURRENT_TIMESTAMP"), UpdateValue("CURRENT_TIMESTAMP")]
    public DateTime? UpdatedAt { get; private set; }

    [NotMapped]
    public string TemporaryPassword { get; set; }
}

• Members declared as "Property" are subject to automatic SQL generation / execution.
  • A Readonly-property can only be specified as a Where-clause-column or update value.
  • A Writeonly-Property can only be specified as a Selection column.
• See About Table Attributes for attribute details.

Initializing

using DapperAid;

QueryBuilder queryBuilderInstance = new QueryBuilder.Sqlite(); // (example for SQLite)

Create an instance corresponding to your DBMS from below. <a id="querybuilders"></a>

• new QueryBuilder.Oracle()
• new QueryBuilder.MySql()
• new QueryBuilder.Postgres()
• new QueryBuilder.SQLite()
• new QueryBuilder.SqlServer()
• new QueryBuilder.MsAccess()
• new QueryBuilder.DB2()

These instance generates appropriate SQL statement for your DBMS.
(You can also customize the QueryBuilder class as needed)

If you want to tie an instance only to a specific DB connection, write as follows.

// When linking with a DB connection object
connection.UseDapperAid(queryBuilderInstance);

// When linking with a DB connection string
queryBuilderInstance.MapDbConnectionString(yourDbDataSource.ConnectionString);

Executing CRUD

using System.Collections.Generic;
using System.Data;

IDbConnection connection;

Select<T>([ where[, targetColumns][, otherClauses]]) : returns list<T>

    IReadOnlyList<Member> list1 = connection.Select<Member>();
    // -> select (all columns) from Members order by Id

    IReadOnlyList<Member> list2 = connection.Select<Member>(
        r => r.Name == "TEST");
    // -> select (all columns) from Members where "Name"=@Name(="TEST") order by Id

    IReadOnlyList<Member> list3 = connection.Select<Member>(
        r => r.Name != "TEST", 
        r => new { r.Id, r.Name });
    // -> select "Id", "Name" from Members where "Name"<>@Name order by Id

    IReadOnlyList<Member> list4 = connection.Select<Member>(
        r => r.Tel != null,
        $"ORDER BY {nameof(Member.Name)} LIMIT 5 OFFSET 10");
    // -> select (all columns) from Members where Phone_No is not null
    //           ORDER BY Name LIMIT 5 OFFSET 10

    IReadOnlyList<Member> list5 = connection.Select<Member>(
        r => r.Tel != null,
        r => new { r.Id, r.Name },
        $"ORDER BY {nameof(Member.Name)} LIMIT 5 OFFSET 10");
    // -> select "Id", "Name" from Members where Phone_No is not null
    //           ORDER BY Name LIMIT 5 OFFSET 10

SelectFirst<T>([ where[, targetColumns][, otherClauses]]) : returns one row or exception

SelectFirstOrDefault<T>([ where[, targetColumns][, otherClauses]]) : returns one row or null

    Member first1 = connection.SelectFirst<Member>();
    // -> Execute connection.QueryFirst<Member>(sql) instead of connection.Query<Member>(sql).

    Member? firstOrDefault1 = connection.SelectFirstOrDefault<Member>();
    // -> Execute connection.QueryFirstOrDefault<Member>(sql) instead of connection.Query<Member>(sql).

    Member? selectForUpdate = connection.SelectFirst<Member>(
        r => r.Id == 1,
        otherClauses: "FOR UPDATE");
    // -> select (all columns) from Members where "Id"=@Id FOR UPDATE

Select<TFrom, TColumns>([ where[, otherClauses]]) : returns list<TColumns>

    class SelColumns {
        public string Name { get; private set; }
        public string Tel { get; private set; }
        [Column("CURRENT_TIMESTAMP")]
        public DateTime Now { get; set; }
    }

    IReadOnlyList<SelColumns> listS1 = connection.Select<Member, SelColumns>(
        r => r.Tel != null
    );
    // -> select "Name", Phone_No as "Tel", CURRENT_TIMESTAMP as "Now"
    //           from Members where Phone_No is not null order by Id

SelectFirst<TFrom, TColumns>([ where[, otherClauses]]) : returns one row or exception

SelectFirstOrDefault<TFrom, TColumns>([ where[, otherClauses]]) : returns one row or null

    SelColumns first2 = connection.SelectFirst<Member, SelColumns>(
        r => r.Tel == null
    );
    // -> Execute connection.QueryFirst<SelColumns>(sql) instead of connection.Query<SelColumns>(sql).

    SelColumns? firstOrDefault2 = connection.SelectFirstOrDefault<Member, SelColumns>(
        r => r.Tel == null
    );
    // -> Execute connection.QueryFirstOrDefault<SelColumns>(sql) instead of connection.Query<SelColumns>(sql).

Select(by Key [, targetColumns[, otherClauses]]) : returns one row or null

    Member? select1 = connection.Select(
        () => new Member { Id = 1 });
    // -> select "Id", "Name", Phone_No as "Tel", "CreatedAt", "UpdatedAt" from Members where "Id"=@Id(=1)

    Member? select2 = connection.Select(
        () => new Member { Id = 1 },
        r => new { r.Id, r.Name });
    // -> select "Id", "Name" from Members where "Id"=@Id

    Member? selectForUpdate = connection.Select(
        () => new Member { Id = 1 },
        otherClauses: "FOR UPDATE");
    // -> select (all columns) from Members where "Id"=@Id FOR UPDATE

Count<T>([where]) : returns the number of rows

    ulong count1 = connection.Count<Member>();
    // -> select count(*) from Members

    ulong count2 = connection.Count<Member>(
        r => (r.Id >= 3 && r.Id <= 9));
    // -> select count(*) from Members where "Id">=@Id(=3) and "Id"<=@P01(=9)

Insert(record[, targetColumns]) : returns 1(inserted row)

    var rec1 = new Member { Name = "InsertTest", Tel = "177" };
    int insert1 = connection.Insert(rec1);
    // -> insert into Members("Name", Phone_No, "CreatedAt", "UpdatedAt")  
    //                values (@Name, @Tel, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

    var rec2 = new Member { Name = "ParticularColumnOnly1", CreatedAt = null };
    int insert2 = connection.Insert(rec2,
        r => new { r.Name, r.CreatedAt });
    // -> insert into Members("Name", "CreatedAt") values (@Name, @CreatedAt(=null))

InsertAndRetrieveId(record[, targetColumns]) : returns 1(inserted row)

    var rec3 = new Member { Name = "IdentityTest", Tel = "7777" };
    int insert3 = connection.InsertAndRetrieveId(rec3);
    // -> insert into Members("Name", Phone_No, "CreatedAt", "UpdatedAt")
    //    values (@Name, @Tel, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ; select LAST_INSERT_ROWID()
    Trace.WriteLine("insertedID=" + rec3.Id); // The value assigned to the "Id" column is set

• Note: In these examples, the [InsertValue] attribute is specified that
  the "Id" column is autoincrement and obtains the registered value.

Insert(specifiedColumnValue) : returns 1(inserted row)

    int insertX = connection.Insert(
        () => new Member { Id = 888, Name = "ParticularColumnOnly2" });
    // -> insert into Members("Id", "Name") values (@Id, @Name)

InsertRows(records[, targetColumns]) : returns the number of inserted rows

    int insertMulti = connection.InsertRows(new[] {
        new Member { Name = "MultiInsert1", Tel = null },
        new Member { Name = "MultiInsert2", Tel = "999-999-9999" },
        new Member { Name = "MultiInsert3", Tel = "88-8888-8888" },
    });
    // -> insert into Members("Name", Phone_No, "CreatedAt", "UpdatedAt") values
    //   ('MultiInsert1', null, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    //   ('MultiInsert2', '999-999-9999', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    //   ('MultiInsert3', '88-8888-8888', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

• Note: MultiRow-Insert is a SQL92 feature and is supported by DB2, MySQL, PostgreSQL, SQL Server, SQLite (3.7.11 or later), Oracle ("INSERT ALL" statement), and so on.
• Note: If there are many records (by default, more than 1000), the query will be executed in multiple statements. You can use the queryBuilder.MultiInsertRowsPerQuery property to change the number of records inserted by a single query.

<a id="insertorupdate"></a>InsertOrUpdate(record[, insertTargetColumns[, updateTargetColumns]]) : returns 1(inserted or updated row)

    var upsertRow = new Member { Id = 1, Name = "UpsertTest", Tel = "7777" };
    int upsertSingle = connection.InsertOrUpdate(upsertRow);
    // -> insert into Members("Id", "Name", Phone_No, "CreatedAt", "UpdatedAt")
    //    values (@Id, @Name, @Tel, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    //    on conflict("Id") do update set "Name"=excluded."Name", ...

• Note: This method generates the UpSert(Merge) statement. If the record already exists, the record is updated. Otherwise, a record is inserted. The generated SQL differs for each DBMS: "MERGE INTO" for SQLServer, Oracle, and DB2; "INSERT ... ON CONFLICT UPDATE" for Postgres and SQLite; "INSERT ... ON DUPLICATE KEY UPDATE " for MySQL.
• Note: If you set the queryBuilder.SupportsUpsert property to false, updates will be performed using simple Update and Insert statements instead of Upsert(Merge).
  If you are using a DBMS that does not support UpSert such as SQLite less than 3.24(2018-06-04), PostgreSQL less than 9.5(2016-01-07), MS-Access, please set the property to false.

InsertOrUpdateRows(records[, insertTargetColumns[, updateTargetColumns]]) : returns the number of inserted (or updated) rows

    var upsertData = new[] {
        new Dept { Code = 110, Name = "Sales"},
        new Dept { Code = 120, Name = "Marketing"},
        new Dept { Code = 130, Name = "Publicity"},
    };
    int upsertMulti = connection.InsertOrUpdateRows(upsertData);
    // -> insert into Dept("Code", "Name", ....)
    //    values (110, "Sales", ...), (120, "Marketing", ...), (130, "Publicity", ...)
    //    on conflict("Code") do update set "Name"=excluded."Name", .... ..

• Note: This method genetares and executes UpSert(Merge) statement. See also InsertOrUpdate().

Update(record[, targetColumns]) : returns the number of updated rows

    var rec1 = new Member { Id = 555, ... };
    int update1 = connection.Update(rec1);
    // update Members set "Name"=@Name, Phone_No=@Tel, "UpdatedAt"=CURRENT_TIMESTAMP where "Id"=@Id

    var rec2 = new Member { Id = 666, Tel = "123-456-7890" };
    int update2 = connection.Update(rec2, r => new { r.Tel });
    // -> update Members set Phone_No=@Tel where "Id"=@Id

Update(specifiedColumnValue, where) : returns the number of updated rows

    int update3 = connection.Update(
        () => new Member { Name = "updateName" },
        r => r.Tel == "55555-5-5555");
    // -> update Members set "Name"=@Name where Phone_No=@Tel

Delete(record) : returns the number of deleted rows

    var delRec = new Member { Id = 999, ... };
    int delete1 = connection.Delete(delRec);
    // -> delete from Members where "Id"=@Id

Delete<T>(where) : returns the number of deleted rows

    int delete2 = connection.Delete<Member>(
        r => r.Name == null);
    // -> delete from Members where "Name" is null

Truncate<T>()

    connection.Truncate<Member>();
    // -> truncate table Members 
    //    (For DBMS without "truncate" syntax, execute delete instead)

(Extra) DDLAttribute.GenerateCreateSQL<T>() : returns the "create table" script

using DapperAid.Ddl;

    var createTableSql = DDLAttribute.GenerateCreateSQL<Member>();
    // ->  create table Members
    //     (
    //      "Id" INTEGER,
    //      "Name",
    //      Phone_No,
    //      "CreatedAt",
    //      "UpdatedAt",
    //      primary key( "Id")
    //     )
    connection.Execute(createTableSql);

• Note: If you use this feature, you should describe [DDL] attribute in each column
  and specify database column types, constraints, default values, etc.
• Note: DDLAttribute.GenerateTableDefTSV<T>() method is also provided, and returns tab-delimited text of table definition contents.

(Extra) LoggableDbConnection class

using System.Data;
using System.Data.SQLite; // (example for SQLite)
using DapperAid.DbAccess;

IDbConnection GetYourDbConnection()
{
    // Prepare a normal DB connection 
    var connectionSb = new SQLiteConnectionStringBuilder { DataSource = ":memory:" };
    var conn = new SQLiteConnection(connectionSb.ToString());
    conn.Open();

    // Set into LoggableDbConnection object
    return new LoggableDbConnection(conn,
        errorLogger: (Exception ex, DbCommand cmd) =>
        {   // Write Error Log
            Trace.WriteLine(ex.ToString() + (cmd != null ? ":" + cmd.CommandText : null));
        },
        traceLogger: (string resultSummary, long mSec, DbCommand cmd) =>
        {   // Write SQL Execution Trace Log
            Trace.WriteLine(resultSummary + "(" + mSec + "ms)" + (cmd != null ? ":" + cmd.CommandText : null));
        });
}

• The log method specified in the argument is called when SQL is executed / error occurs.
  By using this, you can check the contents of the SQL generated by DapperAid.
  Implement it to be logged.

About Where Clause

Expression trees in LambdaExpression is converted to SQL search condition.
Condition values are bound to parameters.

Comparison Operator

    int? val1 = 100; // (bound to @IntCol)
    .Select<T>(t => t.IntCol == val1); // -> where "IntCol"=@IntCol
    .Select<T>(t => t.IntCol != val1); // -> where "IntCol"<>@IntCol
    .Select<T>(t => t.IntCol < val1); // -> where "IntCol"<@IntCol
    .Select<T>(t => t.IntCol > val1); // -> where "IntCol">@IntCol
    .Select<T>(t => t.IntCol <= val1); // -> where "IntCol"<=@IntCol
    .Select<T>(t => t.IntCol >= val1); // -> where "IntCol">=@IntCol

    // If the value is null, SQL is also generated as "is"
    int? val2 = null; 
    .Select<T>(t => t.IntCol == val2); // -> where "IntCol" is null
    .Select<T>(t => t.IntCol != val2); // -> where "IntCol" is not null

    // can also compare columns and columns.
    .Select<T>(t => t.IntCol == t.OtherCol); // -> where "IntCol"="OtherCol"

SQL-specific comparison operators in, like, and between are also supported.

using DapperAid; // uses "SqlExpr" static class

    string[] inValues = {"111", "222", "333"}; // (bound to @TextCol)
    .Select<T>(t => t.TextCol == SqlExpr.In(inValues)); // -> where "TextCol" in @TextCol
    
    string likeValue = "%test%"; // (bound to @TextCol)
    .Select<T>(t => t.TextCol == SqlExpr.Like(likeValue)); // -> where "TextCol" like @TextCol

    int b1 = 1; // (bound to @IntCol)
    int b2 = 99; // (bound to @P01)
    .Select<T>(t => t.IntCol == SqlExpr.Between(b1, b2)); // -> where "IntCol" between @IntCol and @P01

    // when "!=" is used, SQL is also generated as "not"
    .Select<T>(t => t.TextCol != SqlExpr.In(inValues)); // -> where "TextCol" not in @TextCol

• Note: IN conditionals are further expanded by Dapper's List Support feature.

Logical Operator

Supports And(&&), Or(||), Not(!).

    .Select<T>(t => t.TextCol == "111" && t.IntCol < 200);
    // -> where "TextCol"=@TextCol and "IntCol"<@IntCol

    .Select<T>(t => t.TextCol == "111" || t.IntCol < 200);
    // -> where ("TextCol"=@TextCol) or ("IntCol"<@IntCol)

    .Select<T>(t => !(t.TextCol == "111" || t.IntCol < 200));
    // -> where not(("TextCol"=@TextCol) or ("IntCol"<@IntCol))

It can also be combined with the condition judgment not based on SQL.

    // The part where the boolean value is found in advance is not converted to SQL, and is omitted
    string text1 = "111";
    .Select<T>(t => text1 == null || t.TextCol == text1); // -> where "TextCol"=@TextCol
    .Select<T>(t => text1 != null && t.TextCol == text1); // -> where "TextCol"=@TextCol

    // If the result is determined only by the left side, SQL is not generated
    string text2 = null;
    .Select<T>(t => text2 == null || t.TextCol == text2); // -> where true
    .Select<T>(t => text2 != null && t.TextCol == text2); // -> where false

Ternary operators (cond ? trueCond : falseCond) are also supported.

    int intVal = -1;
    .Select(t.CondCol == 1 ? t.IntCol > intVal : t.IntCol < intVal) // -> where (("CondCol"=1 and "IntCol">@IntCol) or ("CondCol"<>1 and "IntCol"<@IntCol))`
    .Select(intVal < 0 ? t.IntCol == null : t.IntCol > intVal) // -> where "IntCol">@IntCol
    .Select(intVal > 0 ? t.IntCol == null : t.IntCol > intVal) // -> where "IntCol" is null`

SQL direct description

You can describe conditional expressions and subqueries directly.

using DapperAid; // uses "SqlExpr" static class

    .Select<T>(t => t.TextCol == SqlExpr.In<string>("select text from otherTable where..."));
    // --> where "TextCol" in(select text from otherTable where...)

    .Select<T>(t => SqlExpr.Eval("ABS(IntCol) < 5"));
    // --> where ABS(IntCol) < 5

    .Select<T>(t => SqlExpr.Eval("(exists(select * from otherTable where...))"));
    // --> where (exists(select * from otherTable where...))

You can also bind parameter values by using SqlExpr.In(...) / SqlExpr.Eval(...).

    int intVal = 99; // (bound to @P00, @P01 or such name)

    .Select<T>(t => t.TextCol == SqlExpr.In<string>("select text from otherTable where a=", intVal, " or b=", intVal))
    // -->  where "TextCol" in(select text from otherTable where a=@P00 or b=@P01)

    .Select<T>(t => SqlExpr.Eval("IntCol < ", intVal, " or IntCol2 > ", intVal));
    // --> where IntCol < @P00 or IntCol2 > @P01 

    var idRegex = "userIdRegexPattern"; // (bound to @P00)
    var pwText = "passswordText"; // (bound to @P01)

    .Select<T>(t => SqlExpr.Eval("id~", idRegex, " AND pw=CRYPT(", pwText, ", pw)"));
    // --> where id~@P00 AND pw=CRYPT(@P01, pw) -- works only Postgres

If you want to descrive only the value expression, use SqlExpr.Eval<T>(...).

    .Select<TableX>(t => t.pw == SqlExpr.Eval<string>("CRYPT('password', pw)"));
    // --> select (columns) from TableX where "pw"=CRYPT('password', pw)

    .Select<TableX>(t => t.pw == SqlExpr.Eval<string>("CRYPT(", pwText, ", pw)"));
    // --> select (columns) from TableX where "pw"=CRYPT(@P00, pw)

• Note: SqlExpr.Eval<T>(...) can also be used as the Value below.
  • Select<Table>(() => new Table { column = Value, ...})
  • Insert<Table>(() => new Table { column = Value, ...})
  • Update<Table>(() => new Table { column = Value, ...}[, where ])

    var pwText = "passswordText"; // (bound to @P00)
    var salt = "hashsalt"; // (bound to @P01)
    
    .Select(() => new TableX {
        pw = SqlExpr.Eval<string>("CRYPT(", pwText, ", pw)")
    });
    // --> select (columns) from TableX where "pw"=CRYPT(@P00, pw)

    .Insert(() => new TableX {
        pw = SqlExpr.Eval<string>("CRYPT(", pwText, ",", salt, ")")
    });
    // --> insert into TableX("pw") values(CRYPT(@P00,@P01))

    .Update(() => new TableX {
        pw = SqlExpr.Eval<string>("CRYPT(", pwText, ",", salt, ")")
    }, r => { ... });
    // --> update TableX set "pw"=CRYPT(@P00,@P01) where ...

<a id="attributes"></a>About Table Attributes

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using DapperAid.DataAnnotations;
using DapperAid.Ddl; // (for extra feature)

for Class

[Table] : apply if tablename != classname or you want to customize the from clause

    [Table("TABLE_NAME")] // specify table name
    // -> select .... from TABLE_NAME

    [Table("TABLE_NAME", Schema = "SCHEMA_NAME")] // specify schema
    // -> select .... from SCHEMA_NAME.TABLE_NAME

    [Table("TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.ID=T2.ID")] // join
    // -> select .... from TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.ID=T2.ID
    // Note: Also specify the acquisition source table in the column definition

[SelectSql] : apply if you want to customize select statement

    [SelectSql(Beginning = "SELECT DISTINCT")] // customize the beginning of select sql
    // -> SELECT DISTINCT ... from ....

    [SelectSql(BaseWhereClauses = "deleted_at IS NULL")] // append where condition of select sql
    // -> select ... from .... where deleted_at IS NULL and .....

    [SelectSql(GroupByKey = true)] // generate group-by clause
    // -> select ... from ... where ... GROUP BY (colums with [Key] attributes)

    [SelectSql(DefaultOtherClauses = "ORDER BY NAME NULLS LAST")] // append the end of select sql by default
    // -> select ... from ... where ... ORDER BY NAME NULLS LAST 
    //    (when {otherClauses} is not specified)

(for extra feature) [DDL] : apply if you want to specify a table constraint of DDL

    [DDL("FOREIGN KEY (C1,C2) REFERENCES MASTERTBL(C1,C2)")] // specify FK
    // -> create table ...(
    //     ...,
    //     primary key ...,
    //     FOREIGN KEY (C1,C2) REFERENCES MASTERTBL(C1,C2)
    //    )

for Properties

[Column] : apply if columnname != propertyname or you want to customize the column values to retrieve

    [Column("COLUMN_NAME")] // specify column name
    public string ColumnName { get; set; }
    //   -> select ... COLUMN_NAME as "ColumnName", ... 

    [Column("T1.CODE")] // specify table alias and column name
    public string T1Code { get; set; }
    //   -> select ... T1.CODE as "T1Code", ... 

    [Column("MONTH(DateOfBirth)")] // customize value
    public int BirthMonth { get; set; }
    //   -> select ... MONTH(DateOfBirth) as "BirthMonth", ... 

    [Column("COUNT(*)")] // tally value
    public int TotalCount { get; set; }
    //   -> select ... COUNT(*) as "TotalCount", ... 

[Key] : apply if you want to update/delete by record-object, or use [Select(GroupByKey = true)]

    [Key]
    // -> update/delete .... where (columns with [Key] attributes)=@(bindvalue)
    
    // when [SelectSql(GroupByKey = true)] is applied to the class
    // -> select .... where ... GROUP BY (colums with [Key] attributes)

• Note: You can also specify [Key] for multiple columns (as a composite key)

<a id="InsertValueattribute"></a>[InsertValue] : apply if you want to modify the insert value

    [InsertValue("CURRENT_TIMESTAMP")] // Specify the value to set with SQL instead of bind value
    public DateTime CreatedAt { get; set; }
    // -> insert into ...(..., "CreatedAt", ...) values(..., CURRENT_TIMESTAMP, ...)

    [InsertValue("date(@DateOfBirth)")] // Edit bind value with SQL
    public DateTime DateOfBirth
    // -> insert into ...(..., "BirtyDay", ...) values(..., date(@DateOfBirth), ...)

    // Do not set column (DB default value is set)
    [InsertValue(false)] 

    // Default value(Identity etc.) is set, and obtain the value when InsertAndRetrieveId() is called
    [InsertValue(false, RetrieveInsertedId = true)] 

    // set sequence value and obtain (works only PostgreSQL, Oracle)
    [InsertValue("nextval(SEQUENCENAME)", RetrieveInsertedId = true)]

• Note: If you call Insert() with the target column explicitly specified,
  The bind value is set instead of the value by this attribute.

[UpdateValue] : apply if you want to modify the value on update

    [UpdateValue("CURRENT_TIMESTAMP")] : // Specify the value to set with SQL instead of bind value
    public DateTime UpdatedAt { get; set; }
    // -> update ... set ..., "UpdatedAt"=CURRENT_TIMESTAMP, ....

    [UpdateValue("COALESCE(@DCnt, 0)")] // Edit bind value with SQL
    public Int? DCnt { get; set; }
    // -> update ... set ..., "DCnt"=COALESCE(@DCnt, 0), ...

    // Do not set column (not be updated)
    [UpdateValue(false)] 

• Note: If you call Update() with the target column explicitly specified,
  The bind value is set instead of the value by this attribute.

[NotMapped] : Denotes that a property should be excluded from database mapping

    [NotMapped] // Do not select, insert, update 
    public Object NotMappedProperty { get; set; }

(for extra feature) <a id="ddlattribute"></a>[DDL] : apply if you want to specify database column types, constraints, default values, etc.

    [DDL("NUMERIC(5) DEFAULT 0 NOT NULL")]
    public int Value { get; set; }
    // -> create table ...(
    //      :
    //     Value NUMERIC(5) DEFAULT 0 NOT NULL,
    //      : 

Misc.

when the error "The value of type (TypeName) cannot be represented as a sql literal." occured

• Call QueryBuilder.AddSqlLiteralConverter() to specify the function that converts the data value to an SQL representation.

using NetTopologySuite.Geometries;

    var queryBuilderInstance = new QueryBuilder.Postgres();
    // Here is an example of geometry type SQL.
    queryBuilderInstance.AddSqlLiteralConverter<Geometry>(geom =>
    {
        var binaryHex = string.Concat(geom.AsBinary().Select(b => $"{b:X2}"));
        return $"'{binaryHex}'::geometry";
    });

When you want to execute a query during transaction.

• use extension methods in IDbTransaction. It provides the same method as the IDbConnection extension method.

When you want to execute a asynchronus query.

• use ～～Async methods.

When not using as an extension method.

• use QueryRunner class. It Provides almost the same content as an extension method as an instance method.

When you want to use only the SQL generation function.

• Use the QueryBuilder class appropriate for your DBMS.

License

MIT License.

About Author

hnx8(H.Takahashi) is a software developer in Japan.
(I wrote English sentences relying on Google translation. Please let me know if you find a strange expression)