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])