/// <summary>
/// Bulk Insert or update of entities.
/// </summary>
/// <typeparam name="TEntity">The type being inserted.</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Collection of entity records</param>
/// <param name="expressions"></param>
/// <param name="token">Cancellation token</param>
/// <returns></returns>
public async Task <int> UpsertAsync <TEntity>(IConnectionInfo connectionInfo, IEnumerable <TEntity> data, Expression <Func <TEntity, object> >[] expressions, CancellationToken token = default) where TEntity : class, IEntity
{
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
var results = 0;
await using var connection = connectionInfo.GetConnection <T>();
await connection.EnsureOpenAsync(cancellationToken : token).ConfigureAwait(false);
using var transaction = connection.BeginTransaction();
try
{
results = await connection.MergeAllAsync(data, transaction : transaction, cancellationToken : token).ConfigureAwait(false);
transaction.Commit();
}
catch (Exception)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
// ReSharper disable once PossibleIntendedRethrow
throw ex;
}
}
return(results);
}
/// <summary>
/// Bulk delete of entity records.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Collection of entity records</param>
/// <param name="token">Cancellation token</param>
/// <returns></returns>
public async Task <int> DeleteAsync <TEntity>(IConnectionInfo connectionInfo, IEnumerable <TEntity> data, CancellationToken token = default) where TEntity : class, IEntity
{
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
await using var connection = connectionInfo.GetConnection <T>();
using var transaction = connection.BeginTransaction();
try
{
var deleted = await connection.DeleteAllAsync(data, transaction : transaction, cancellationToken : token).ConfigureAwait(false);
transaction.Commit();
return(deleted);
}
catch (Exception)
{
try
{
transaction.Rollback();
throw;
}
catch (Exception ex)
{
// ReSharper disable once PossibleIntendedRethrow
throw ex;
}
}
}
/// <summary>
/// Delete entity from table.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Entity record</param>
/// <param name="token">Cancellation token</param>
/// <returns>true if deleted, false if not found</returns>
public async Task <bool> DeleteAsync <TEntity>(IConnectionInfo connectionInfo, TEntity data, CancellationToken token = default) where TEntity : class, IEntity
{
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
var results = 0;
await using var connection = connectionInfo.GetConnection <T>();
await connection.EnsureOpenAsync(cancellationToken : token).ConfigureAwait(false);
using var transaction = connection.BeginTransaction();
try
{
results = await connection.DeleteAsync(data, cancellationToken : token).ConfigureAwait(false);
transaction.Commit();
}
catch (Exception)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
// ReSharper disable once PossibleIntendedRethrow
throw ex;
}
}
return(results > 0);
}
/// <summary>
/// Sql update command
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Entity record</param>
/// <param name="token">Cancellation token</param>
/// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
public async Task <bool> UpdateAsync <TEntity>(IConnectionInfo connectionInfo, TEntity data, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
var updated = await connection.UpdateAsync(data, cancellationToken : token).ConfigureAwait(false);
return(updated > 0);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <typeparam name="TKey"></typeparam>
/// <param name="connectionInfo"></param>
/// <param name="keys"></param>
/// <param name="token"></param>
/// <returns></returns>
public async Task <IEnumerable <TEntity> > QueryByKeyAsync <TEntity, TKey>(IConnectionInfo connectionInfo, IEnumerable <TKey> keys, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
var queryField = new QueryField(PrimaryCache.Get <TEntity>().AsField(), keys);
return(await connection.QueryAsync <TEntity>(queryField, cancellationToken : token).ConfigureAwait(false));
}
/// <summary>
/// Execute sql command to save an entry.
/// </summary>
/// <typeparam name="TKey">Entity key</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="sql">Sql query</param>
/// <param name="parameters">Sql parameters</param>
/// <param name="token">Cancellation token</param>
/// <returns>Save result</returns>
public async Task <SaveResult <TKey> > ExecuteSaveAsync <TKey>(IConnectionInfo connectionInfo, string sql, object parameters, CancellationToken token = default)
{
await using var connection = connectionInfo.GetConnection <T>();
var result = await connection.ExecuteScalarAsync(sql, parameters, cancellationToken : token).ConfigureAwait(false);
return(result != null ? new SaveResult <TKey>((TKey)result, true) : new SaveResult <TKey>((TKey)DefaultSqlId <TKey>()));
}
/// <summary>
/// Query all.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connectionInfo"></param>
/// <param name="token"></param>
/// <returns>Query result</returns>
public async Task <QueryResult <TEntity> > QueryAllAsync <TEntity>(IConnectionInfo connectionInfo, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
var result = (await connection.QueryAllAsync <TEntity>(cancellationToken: token).ConfigureAwait(false)).ToArray();
return(new QueryResult <TEntity> {
Records = result, TotalCount = result.Length
});
}
/// <summary>
/// Bulk insert entities asynchronously.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Collection of entity records</param>
/// <param name="token">Cancellation token</param>
/// <returns>number of records inserted</returns>
public async Task <int> InsertAsync <TEntity>(IConnectionInfo connectionInfo, IEnumerable <TEntity> data, CancellationToken token = default) where TEntity : class, IEntity
{
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
await using var connection = connectionInfo.GetConnection <T>();
var inserted = await connection.InsertAllAsync(data, cancellationToken : token).ConfigureAwait(false);
return(inserted);
}
/// <summary>
/// Query sql.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="sql">Sql query</param>
/// <param name="parameters">Sql parameters</param>
/// <param name="token">Cancellation token</param>
/// <returns>Query result</returns>
public async Task <QueryResult <TEntity> > QueryAsync <TEntity>(IConnectionInfo connectionInfo, string sql, object parameters, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
using var result = await connection.ExecuteQueryMultipleAsync(sql, parameters, cancellationToken : token).ConfigureAwait(false);
var entities = result.Extract <TEntity>()?.ToArray();
var scalar = result.Scalar <int>();
var totalCount = scalar != 0 ? scalar : entities?.Length ?? -1;
return(new QueryResult <TEntity> {
Records = entities, TotalCount = totalCount
});
}
/// <summary>
/// Insert or update an entity record.
/// </summary>
/// <typeparam name="TEntity">The type being inserted.</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Entity record</param>
/// <param name="expressions"></param>
/// <param name="token">Cancellation token</param>
/// <returns></returns>
public async Task <TEntity> UpsertAsync <TEntity>(IConnectionInfo connectionInfo, TEntity data, Expression <Func <TEntity, object> >[] expressions = null, CancellationToken token = default) where TEntity : class, IEntity
{
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
await using var connection = connectionInfo.GetConnection <T>();
var id = await connection.MergeAsync(data, cancellationToken : token).ConfigureAwait(false);
var propertyInfo = PrimaryCache.Get <TEntity>().PropertyInfo;
propertyInfo.SetValue(data, Convert.ChangeType(id, propertyInfo.PropertyType));
return(data);
}
/// <summary>
/// Bulk update entities asynchronously.
/// </summary>
/// <typeparam name="TEntity">Type to be updated</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Entity record</param>
/// <param name="token">Cancellation token</param>
/// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
public async Task <int> UpdateAsync <TEntity>(IConnectionInfo connectionInfo, IEnumerable <TEntity> data, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
return(await connection.UpdateAllAsync(data, cancellationToken : token).ConfigureAwait(false));
}
public async Task <TEntity> QueryOneAsync <TEntity>(IConnectionInfo connectionInfo, Expression <Func <TEntity, bool> > predicate, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
return((await connection.QueryAsync(predicate, cancellationToken: token).ConfigureAwait(false)).FirstOrDefault());
}
/// <summary>
/// Execute sql scalar command.
/// </summary>
/// <param name="connectionInfo"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="token"></param>
/// <returns></returns>
public async Task <object> ExecuteScalarAsync(IConnectionInfo connectionInfo, string sql, object parameters, CancellationToken token = default)
{
await using var connection = connectionInfo.GetConnection <T>();
return(await connection.ExecuteScalarAsync(sql, parameters, cancellationToken : token).ConfigureAwait(false));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connectionInfo"></param>
/// <param name="token"></param>
/// <returns></returns>
public async Task <int> QueryCountAsync <TEntity>(IConnectionInfo connectionInfo, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
return((int)await connection.CountAllAsync <TEntity>(cancellationToken : token).ConfigureAwait(false));
}
/// <summary>
/// Insert or update an entity record.
/// </summary>
/// <typeparam name="TEntity">The type being inserted.</typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="data">Entity record</param>
/// <param name="expressions"></param>
/// <param name="token">Cancellation token</param>
/// <returns></returns>
public async Task <int> UpsertAsync <TEntity>(IConnectionInfo connectionInfo, IEnumerable <TEntity> data, Expression <Func <TEntity, object> >[] expressions, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <SqlConnection>();
return(await connection.BulkMergeAsync(data, cancellationToken : token).ConfigureAwait(false));
}
/// <summary>
/// Delete by key.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <typeparam name="TKey"></typeparam>
/// <param name="connectionInfo">Connection info</param>
/// <param name="keys"></param>
/// <param name="token">Cancellation token</param>
/// <returns>Query result</returns>
public async Task <int> DeleteByKeyAsync <TEntity, TKey>(IConnectionInfo connectionInfo, IEnumerable <TKey> keys, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <SqlConnection>();
return(await connection.BulkDeleteAsync <TEntity>(keys.Cast <object>(), cancellationToken : token).ConfigureAwait(false));
}
public async Task <TEntity> QueryOneAsync <TEntity>(IConnectionInfo connectionInfo, string sql, object parameters, CancellationToken token = default) where TEntity : class, IEntity
{
await using var connection = connectionInfo.GetConnection <T>();
return((await connection.ExecuteQueryAsync <TEntity>(sql, parameters, cancellationToken: token).ConfigureAwait(false)).FirstOrDefault());
}
