public static Task <int> UpdateAsync <T>(this IDbConnection connection, T data, Expression <Func <T, bool> > predicate, bool setIdentity, params Expression <Func <T, object> >[] properties)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
if (predicate == null)
{
throw new ArgumentNullException(nameof(predicate));
}
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var dbKind = connection.GetDbKind();
var update = PrimitiveSql.CreateUpdate(dbKind, setIdentity, properties);
var where = PredicateSql.From(dbKind, predicate);
var param = where.Parameter.Merge(data, properties);
var builder = new StringBuilder();
builder.AppendLine(update);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(connection.ExecuteAsync(builder.ToString(), param));
}
/// <summary>
/// 指定されたレコードをテーブルに非同期的に挿入します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="data">挿入するデータ</param>
/// <param name="useSequence">シーケンスを利用するかどうか</param>
/// <param name="setIdentity">自動採番のID列に値を設定するかどうか</param>
/// <returns>影響した行数</returns>
public virtual Task <int> InsertAsync <T>(T data, bool useSequence, bool setIdentity)
{
var type = TypeHelper.GetElementType <T>() ?? typeof(T);
var sql = PrimitiveSql.CreateInsert(this.DbKind, type, useSequence, setIdentity);
return(this.Connection.ExecuteAsync(sql, data, this.Transaction, this.Timeout));
}
public static async Task <IReadOnlyList <T> > SelectAsync <T>(this IDbConnection connection, Expression <Func <T, bool> > predicate, params Expression <Func <T, object> >[] properties)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (predicate == null)
{
throw new ArgumentNullException(nameof(predicate));
}
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var select = PrimitiveSql.CreateSelect(properties);
var where = PredicateSql.From(connection.GetDbKind(), predicate);
var builder = new StringBuilder();
builder.AppendLine(select);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
var result = await connection.QueryAsync <T>(builder.ToString(), where.Parameter).ConfigureAwait(false);
return(result as IReadOnlyList <T>);
}
/// <summary>
/// バルク方式で指定のデータを挿入するためのコマンドを生成します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="data">挿入するデータ</param>
/// <returns>コマンド</returns>
private DbCommand CreateBulkInsertCommand <T>(IEnumerable <T> data)
{
//--- 実体化
data = data.Materialize();
//--- build DbCommand
var factory = DbProvider.GetFactory(this.DbKind);
dynamic command = factory.CreateCommand();
command.Connection = (dynamic)this.Connection;
command.CommandText = PrimitiveSql.CreateInsert <T>(this.DbKind, false, true);
command.BindByName = true;
command.ArrayBindCount = data.Count();
if (this.Timeout.HasValue)
{
command.CommandTimeout = this.Timeout.Value;
}
//--- bind params
foreach (var x in TableMappingInfo.Create <T>().Columns)
{
var getter = AccessorCache <T> .LookupGet(x.PropertyName);
dynamic parameter = factory.CreateParameter();
parameter.ParameterName = x.PropertyName;
parameter.DbType = x.ColumnType;
parameter.Value = data.Select(y => getter(y)).ToArray();
command.Parameters.Add(parameter);
}
return(command);
}
/// <summary>
/// 指定されたテーブルからすべてのレコードを非同期的に取得します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="properties">取得対象の列</param>
/// <returns>取得したレコード</returns>
public virtual async Task <IReadOnlyList <T> > SelectAsync <T>(Expression <Func <T, object> > properties)
{
var sql = PrimitiveSql.CreateSelect(properties);
var result = await this.Connection.QueryAsync <T>(sql, null, this.Transaction, this.Timeout).ConfigureAwait(false);
return(result as IReadOnlyList <T>);
}
/// <summary>
/// 指定されたレコードをバルク方式でテーブルに非同期的に挿入します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="data">挿入するデータ</param>
/// <returns>影響した行数</returns>
public override async Task <int> BulkInsertAsync <T>(IEnumerable <T> data)
{
//--- 挿入処理本体
Func <IEnumerable <T>, IDbTransaction, Task <int> > insert = async(collection, transaction) =>
{
var result = 0;
var sql = PrimitiveSql.CreateInsert <T>(this.DbKind, false, true);
foreach (var x in collection)
{
var value = await this.Connection.ExecuteAsync(sql, x, transaction, this.Timeout).ConfigureAwait(false);
Interlocked.Add(ref result, value);
}
return(result);
};
//--- トランザクションが外部から指定されている場合はそれを利用
if (this.Transaction != null)
{
return(await insert(data, this.Transaction).ConfigureAwait(false));
}
//--- トランザクションが外部から指定されていない場合は新規に作成
//--- SQLiteにおけるバルクインサートの魔法
using (var transaction = this.Connection.StartTransaction())
{
var result = await insert(data, transaction.Raw).ConfigureAwait(false);
transaction.Complete();
return(result);
}
}
/// <summary>
/// レコードを挿入し、そのレコードに自動採番されたIDを取得するSQLを生成します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <returns>SQL文</returns>
protected override string CreateInsertAndGetSql <T>()
{
var sequence = TableMappingInfo.Create <T>().Columns.First(x => x.IsPrimaryKey).Sequence;
return
($@"{PrimitiveSql.CreateInsert<T>(this.DbKind)};
select currval({sequence.FullName}) as Id;");
}
public void Truncate文生成()
{
var actual1 = PrimitiveSql.CreateTruncate(typeof(Person));
var actual2 = PrimitiveSql.CreateTruncate <Person>();
var expect = "truncate table dbo.Person";
actual1.Is(expect);
actual2.Is(expect);
}
public void Count文生成()
{
var actual1 = PrimitiveSql.CreateCount(typeof(Person));
var actual2 = PrimitiveSql.CreateCount <Person>();
var expect = "select count(*) as Count from dbo.Person";
actual1.Is(expect);
actual2.Is(expect);
}
public void Delete文生成()
{
var actual1 = PrimitiveSql.CreateDelete(typeof(Person));
var actual2 = PrimitiveSql.CreateDelete <Person>();
var expect = "delete from dbo.Person";
actual1.Is(expect);
actual2.Is(expect);
}
/// <summary>
/// 指定されたテーブルにおいて指定の条件に一致するレコード数を取得します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="predicate">抽出条件</param>
/// <returns>レコード数</returns>
public virtual ulong Count <T>(Expression <Func <T, bool> > predicate)
{
var count = PrimitiveSql.CreateCount <T>();
var where = PredicateSql.From(this.DbKind, predicate);
var builder = new StringBuilder();
builder.AppendLine(count);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.ExecuteScalar <ulong>(builder.ToString(), where.Parameter, this.Transaction, this.Timeout));
}
/// <summary>
/// 指定されたアクセストークンを削除します。
/// </summary>
/// <param name="token">アクセストークン</param>
/// <returns>削除に成功した場合true</returns>
public async Task <bool> DeleteAsync(string token)
{
var sql = PrimitiveSql.CreateDeleteSql <T_ACCESS_TOKEN>();
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var result = await connection.ExecuteAsync(sql, new { VALUE = token }).ConfigureAwait(false);
return(result == 1);
}
}
/// <summary>
/// 指定されたユーザーIDに対して発行されたトークンをすべて削除します。
/// </summary>
/// <param name="userId">ユーザーID</param>
/// <returns>削除した件数</returns>
public Task <int> DeleteAsync(int userId)
{
var builder = new StringBuilder();
builder.AppendLine(PrimitiveSql.CreateDeleteAllSql <T_ACCESS_TOKEN>());
builder.Append("where USR_ID = :userId");
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
return(connection.ExecuteAsync(builder.ToString(), new { userId = userId }));
}
}
/// <summary>
/// 指定されたテーブルから指定の条件に一致するレコードを非同期的に削除します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="predicate">削除条件</param>
/// <returns>影響した行数</returns>
public virtual Task <int> DeleteAsync <T>(Expression <Func <T, bool> > predicate)
{
var delete = PrimitiveSql.CreateDelete <T>();
var where = PredicateSql.From(this.DbKind, predicate);
var builder = new StringBuilder();
builder.AppendLine(delete);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.ExecuteAsync(builder.ToString(), where.Parameter, this.Transaction, this.Timeout));
}
/// <summary>
/// 有効期限が切れたトークンをすべて削除します。
/// </summary>
/// <returns>削除した件数</returns>
public Task <int> DeleteExpiredAsync()
{
var builder = new StringBuilder();
builder.AppendLine(PrimitiveSql.CreateDeleteAllSql <T_REQUEST_TOKEN>());
builder.Append("where EXPIRATION_TIME < :now");
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
return(connection.ExecuteAsync(builder.ToString(), new { now = DateTime.Now }));
}
}
/// <summary>
/// 指定されたテーブルから指定の条件に一致するレコードを取得します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="predicate">抽出条件</param>
/// <param name="properties">取得対象の列</param>
/// <returns>取得したレコード</returns>
public virtual IReadOnlyList <T> Select <T>(Expression <Func <T, bool> > predicate, Expression <Func <T, object> > properties)
{
var select = PrimitiveSql.CreateSelect(properties);
var where = PredicateSql.From(this.DbKind, predicate);
var builder = new StringBuilder();
builder.AppendLine(select);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.Query <T>(builder.ToString(), where.Parameter, this.Transaction, true, this.Timeout) as IReadOnlyList <T>);
}
/// <summary>
/// 指定の条件に一致するレコードを指定された情報で非同期的に更新します。
/// </summary>
/// <typeparam name="T">テーブルの型</typeparam>
/// <param name="data">更新するデータ</param>
/// <param name="predicate">更新条件</param>
/// <param name="properties">更新する列にマッピングされるプロパティ式のコレクション</param>
/// <param name="setIdentity">自動採番のID列に値を設定するかどうか</param>
/// <returns>影響した行数</returns>
public virtual Task <int> UpdateAsync <T>(T data, Expression <Func <T, bool> > predicate, Expression <Func <T, object> > properties, bool setIdentity)
{
var update = PrimitiveSql.CreateUpdate(this.DbKind, properties, setIdentity);
var where = PredicateSql.From(this.DbKind, predicate);
var param = where.Parameter.Merge(data, properties);
var builder = new StringBuilder();
builder.AppendLine(update);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.ExecuteAsync(builder.ToString(), param, this.Transaction, this.Timeout));
}
public void 特定1列のUpdate文生成()
{
var actual1 = PrimitiveSql.CreateUpdate(DbKind.SqlServer, typeof(Person), new [] { "Name" });
var actual2 = PrimitiveSql.CreateUpdate <Person>(DbKind.SqlServer, x => x.Name);
var actual3 = PrimitiveSql.CreateUpdate <Person>(DbKind.SqlServer, x => new { x.Name });
var expect =
@"update dbo.Person
set
名前 = @Name";
actual1.Is(expect);
actual2.Is(expect);
actual3.Is(expect);
}
public void 全列のUpdate文生成()
{
var actual1 = PrimitiveSql.CreateUpdate(DbKind.SqlServer, typeof(Person));
var actual2 = PrimitiveSql.CreateUpdate <Person>(DbKind.SqlServer);
var expect =
@"update dbo.Person
set
名前 = @Name,
Age = @Age,
HasChildren = @HasChildren";
actual1.Is(expect);
actual2.Is(expect);
}
public void 特定1列のSelect文生成()
{
var actual1 = PrimitiveSql.CreateSelect(typeof(Person), "Name");
var actual2 = PrimitiveSql.CreateSelect <Person>(x => x.Name);
var actual3 = PrimitiveSql.CreateSelect <Person>(x => new { x.Name });
var expect =
@"select
名前 as Name
from dbo.Person";
actual1.Is(expect);
actual2.Is(expect);
actual3.Is(expect);
}
public void 全列のSelect文生成()
{
var actual1 = PrimitiveSql.CreateSelect(typeof(Person));
var actual2 = PrimitiveSql.CreateSelect <Person>();
var expect =
@"select
Id as Id,
名前 as Name,
Age as Age,
HasChildren as HasChildren
from dbo.Person";
actual1.Is(expect);
actual2.Is(expect);
}
public static IReadOnlyList <T> Select <T>(this IDbConnection connection, params Expression <Func <T, object> >[] properties)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var sql = PrimitiveSql.CreateSelect(properties);
return(connection.Query <T>(sql) as IReadOnlyList <T>);
}
public void IDを設定するUpdate文生成()
{
var actual1 = PrimitiveSql.CreateUpdate(DbKind.SqlServer, typeof(Person), setIdentity: true);
var actual2 = PrimitiveSql.CreateUpdate <Person>(DbKind.SqlServer, setIdentity: true);
var expect =
@"update dbo.Person
set
Id = @Id,
名前 = @Name,
Age = @Age,
HasChildren = @HasChildren";
actual1.Is(expect);
actual2.Is(expect);
}
/// <summary>
/// 認証処理を行います。
/// </summary>
/// <param name="info">ユーザー認証情報</param>
/// <returns>認証されたかどうか</returns>
public async Task <bool> AuthenticateAsync(UserAuthInfo info)
{
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var sql = PrimitiveSql.CreateSelectSql <M_USER>();
var parameter = new { ID = info.Id };
var users = await connection.QueryAsync <M_USER>(sql, parameter).ConfigureAwait(false);
var user = users.FirstOrDefault();
return(user != null &&
Convert.ToBoolean(user.ACTIVE_FLG) &&
user.PASSWORD == info.Password);
}
}
public static async Task <IReadOnlyList <T> > SelectAsync <T>(this IDbConnection connection, params Expression <Func <T, object> >[] properties)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var sql = PrimitiveSql.CreateSelect(properties);
var result = await connection.QueryAsync <T>(sql).ConfigureAwait(false);
return(result as IReadOnlyList <T>);
}
/// <summary>
/// 指定されたトークンが有効かどうかを確認します。
/// </summary>
/// <param name="token">トークン</param>
/// <returns>有効かどうか</returns>
public async Task <bool> IsValidAsync(string token)
{
var sql = PrimitiveSql.CreateSelectSql <T_REQUEST_TOKEN>();
var parameter = new T_REQUEST_TOKEN {
VALUE = token
};
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var record = (await connection.QueryAsync <T_REQUEST_TOKEN>(sql, parameter)
.ConfigureAwait(false))
.FirstOrDefault();
return(record == null
? false
: DateTime.Now <= record.EXPIRATION_TIME);
}
}
public static Task <int> UpdateAsync <T>(this IDbConnection connection, T data, bool setIdentity, params Expression <Func <T, object> >[] properties)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var sql = PrimitiveSql.CreateUpdate(connection.GetDbKind(), setIdentity, properties);
return(connection.ExecuteAsync(sql, data));
}
public void シーケンスを利用するInsert文生成()
{
var actual1 = PrimitiveSql.CreateInsert(DbKind.SqlServer, typeof(Person));
var actual2 = PrimitiveSql.CreateInsert <Person>(DbKind.SqlServer);
var expect =
@"insert into dbo.Person
(
名前,
Age,
HasChildren
)
values
(
@Name,
next value for dbo.AgeSeq,
@HasChildren
)";
actual1.Is(expect);
actual2.Is(expect);
}
/// <summary>
/// InsertAndGetするためのパラメーターを生成します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="data">挿入するデータ</param>
/// <returns>パラメーター</returns>
private Tuple <DbCommand, DbParameter> CreateInsertAndGetParameter <T>(T data)
{
//--- command
var factory = DbProvider.GetFactory(this.DbKind);
dynamic command = factory.CreateCommand();
command.BindByName = true;
command.Connection = (dynamic)this.Connection;
if (this.Timeout.HasValue)
{
command.CommandTimeout = this.Timeout.Value;
}
//--- parameters
DbParameter output = null;
foreach (var x in TableMappingInfo.Create <T>().Columns)
{
dynamic parameter = factory.CreateParameter();
parameter.ParameterName = x.PropertyName;
parameter.DbType = x.ColumnType;
if (x.IsPrimaryKey)
{
parameter.Direction = ParameterDirection.Output;
output = parameter;
command.CommandText =
$@"{PrimitiveSql.CreateInsert<T>(this.DbKind)}
returning {x.ColumnName} into :{x.PropertyName}";
}
else
{
parameter.Direction = ParameterDirection.Input;
parameter.Value = AccessorCache <T> .LookupGet(x.PropertyName)(data);
}
command.Parameters.Add(parameter);
}
//--- ok
return(Tuple.Create((DbCommand)command, output));
}
public void IDを設定するInsert文生成()
{
var actual1 = PrimitiveSql.CreateInsert(DbKind.SqlServer, typeof(Person), setIdentity: true);
var actual2 = PrimitiveSql.CreateInsert <Person>(DbKind.SqlServer, setIdentity: true);
var expect =
@"insert into dbo.Person
(
Id,
名前,
Age,
HasChildren
)
values
(
@Id,
@Name,
next value for dbo.AgeSeq,
@HasChildren
)";
actual1.Is(expect);
actual2.Is(expect);
}
