DBのSelect,Insert,Update,Delete操作を拡張メソッドで提供します。 属性/ラムダ式の指定をもとに内蔵クエリビルダが実行SQLを自動生成します。SQL記述不要でDBを参照更新できます。 This library provides CRUD operations of the database as Extension Methods. By specifying Attribute / Lambda-Expression, You can perform database query/update using Dapper without writing SQL.
$ dotnet add package DapperAidDapperAidは、DapperによるデータベースのCRUD操作を支援するSQL自動生成・実行ライブラリです。
DapperAid is a SQL automatic generation and execution library that assists database CRUD operation using Dapper.
from NuGet https://www.nuget.org/packages/DapperAid
PM> Install-Package DapperAid
> dotnet add package DapperAid
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; }
}using DapperAid;
QueryBuilder queryBuilderInstance = new QueryBuilder.Sqlite(); // (example for SQLite)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);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 10SelectFirst<T>([ where[, targetColumns][, otherClauses]]) : returns one row or exceptionSelectFirstOrDefault<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).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 IdSelectFirst<TFrom, TColumns>([ where[, otherClauses]]) : returns one row or exceptionSelectFirstOrDefault<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 UPDATECount<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 setInsert(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)queryBuilder.MultiInsertRowsPerQuery property to change the number of records inserted by a single query.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", ...queryBuilder.SupportsUpsert property to false, updates will be performed using simple Update and Insert statements instead of Upsert(Merge).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", .... ..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"=@IdUpdate(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=@TelDelete(record) : returns the number of deleted rows var delRec = new Member { Id = 999, ... };
int delete1 = connection.Delete(delRec);
// -> delete from Members where "Id"=@IdDelete<T>(where) : returns the number of deleted rows int delete2 = connection.Delete<Member>(
r => r.Name == null);
// -> delete from Members where "Name" is nullTruncate<T>() connection.Truncate<Member>();
// -> truncate table Members
// (For DBMS without "truncate" syntax, execute delete instead)DDLAttribute.GenerateCreateSQL<T>() : returns the "create table" scriptusing DapperAid.Ddl;
var createTableSql = DDLAttribute.GenerateCreateSQL<Member>();
// -> create table Members
// (
// "Id" INTEGER,
// "Name",
// Phone_No,
// "CreatedAt",
// "UpdatedAt",
// primary key( "Id")
// )
connection.Execute(createTableSql);DDLAttribute.GenerateTableDefTSV<T>() method is also provided, and returns tab-delimited text of table definition contents.LoggableDbConnection classusing 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));
});
}Expression trees in LambdaExpression is converted to SQL search condition.
Condition values are bound to parameters.
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 @TextColSupports 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 falseTernary 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`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 PostgresIf 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)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 ...using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using DapperAid.DataAnnotations;
using DapperAid.Ddl; // (for extra feature)[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(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)[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)
// )[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) [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)][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)] [NotMapped] : Denotes that a property should be excluded from database mapping [NotMapped] // Do not select, insert, update
public Object NotMappedProperty { get; set; }[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,
// : 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";
});IDbTransaction.
It provides the same method as the IDbConnection extension method.QueryRunner class.
It Provides almost the same content as an extension method as an instance method.QueryBuilder class appropriate for your DBMS.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)