/// <summary>
/// Updates an entity.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connection">Open DbConnection</param>
/// <param name="entity">The updated entity.</param>
/// <returns>True if updated. Otherwise false.</returns>
public static async Task <bool> Update <TEntity>(this DbConnection connection, TEntity entity)
{
var type = typeof(TEntity);
var name = AttributeParser.GetTableName(type);
var properties = AttributeParser.GetAllProperties(type);
var collections = AttributeParser.GetAllCollections(type);
var key = AttributeParser.GetKey(type);
var keyColumn = AttributeParser.GetColumnName(key);
var updateAttributes = properties.Except(collections).Except(AttributeParser.GetAllIgnored(type)).Where(pi => pi != key).ToList();
var sql = new StringBuilder($"UPDATE {name} SET ");
for (var i = 0; i < updateAttributes.Count; i++)
{
sql.Append($"{AttributeParser.GetColumnName(updateAttributes[i])} = @{updateAttributes[i].Name}");
if (i < updateAttributes.Count - 1)
{
sql.Append(", ");
}
}
sql.Append($" WHERE {keyColumn} = @{key.Name}");
var updated = await connection.Execute(sql.ToString(), entity).ConfigureAwait(false);
return(updated > 0);
}
public static void AddParams(this DbCommand command, object param)
{
_ = command ?? throw new ArgumentNullException($"{nameof(command)} must not be null!");
_ = param ?? throw new ArgumentNullException($"{nameof(param)} must not be null!");
var properties = AttributeParser.GetAllProperties(param.GetType()).ToList();
foreach (var propertyInfo in properties)
{
object value = null;
if (AttributeParser.HasForeignKey(propertyInfo))
{
value = AttributeParser.GetKey(propertyInfo.PropertyType)
.GetValue(propertyInfo.GetValue(param));
}
// Enum or Nullable<Enum>
else if (propertyInfo.PropertyType.IsEnum ||
propertyInfo.PropertyType.IsGenericType &&
propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>) &&
propertyInfo.PropertyType.GetGenericArguments()[0].IsEnum &&
propertyInfo.GetValue(param) != null)
{
value = propertyInfo.GetValue(param).ToString();
}
command.AddParam(propertyInfo.Name, value ?? propertyInfo.GetValue(param));
}
}
/// <summary>
/// Returns a list of entities.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connection">Open DbConnection</param>
/// <returns>Entities of Type TEntity</returns>
public static Task <IEnumerable <TEntity> > GetAll <TEntity>(this DbConnection connection)
{
var type = typeof(TEntity);
var tableName = AttributeParser.GetTableName(type);
var sql = $"SELECT * FROM {tableName}";
return(connection.Query <TEntity>(sql));
}
public static async Task <IEnumerable <TEntity> > Query <TEntity>(this DbConnection connection, string sql,
object param = null)
{
_ = connection ?? throw new ArgumentNullException($"{nameof(connection)} must not be null!");
using var command = connection.CreateCommand();
command.CommandText = sql;
if (param != null)
{
command.AddParams(param);
}
var items = new List <TEntity>();
using var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
while (reader.Read())
{
var entity = (TEntity)Activator.CreateInstance(typeof(TEntity));
var properties = AttributeParser.GetAllProperties(entity.GetType()).ToList();
if (typeof(TEntity) == typeof(int))
{
entity = (TEntity)Convert.ChangeType(reader[0], typeof(Int32), CultureInfo.InvariantCulture);
}
foreach (var propertyInfo in properties)
{
if (AttributeParser.HasForeignKey(propertyInfo))
{
MethodInfo method = typeof(MapperExtensions).GetMethod("Get");
MethodInfo generic = method.MakeGenericMethod(propertyInfo.PropertyType);
var columnValue = Convert.ChangeType(reader[AttributeParser.GetColumnName(propertyInfo)], typeof(int), CultureInfo.InvariantCulture);
var task = (Task)generic.Invoke(null, new object[] { connection, columnValue });
await task.ConfigureAwait(false);
var resultProperty = task.GetType().GetProperty("Result");
propertyInfo.SetValue(entity, resultProperty.GetValue(task));
}
else if (propertyInfo.PropertyType.IsEnum)
{
var parsedEnum = Enum.Parse(propertyInfo.PropertyType, (string)reader[AttributeParser.GetColumnName(propertyInfo)]);
propertyInfo.SetValue(entity, parsedEnum);
}
else if (!reader.IsDBNull(reader.GetOrdinal(AttributeParser.GetColumnName(propertyInfo))))
{
var value = Convert.ChangeType(reader[AttributeParser.GetColumnName(propertyInfo)],
propertyInfo.PropertyType,
CultureInfo.InvariantCulture);
propertyInfo.SetValue(entity, value);
}
}
items.Add(entity);
}
return(items);
}
/// <summary>
/// Returns a single entity by a single id.
/// Id must be marked with [Key] attribute.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connection">Open DbConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <returns>Entity of Type TEntity</returns>
public static async Task <TEntity> Get <TEntity>(this DbConnection connection, dynamic id)
{
var type = typeof(TEntity);
var tableName = AttributeParser.GetTableName(type);
var key = AttributeParser.GetKey(type);
var keyColumn = AttributeParser.GetColumnName(key);
var sql = $"SELECT * FROM {tableName} WHERE {keyColumn} = @Id";
return((await connection.Query <TEntity>(sql, new { Id = id }).ConfigureAwait(false)).FirstOrDefault());
}
/// <summary>
/// Deletes an entity.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connection">Open DbConnection</param>
/// <param name="id">Id of the entity to delete, must be marked with [Key] attribute</param>
/// <returns>True if deleted. Otherwise false if not found.</returns>
public static async Task <bool> Delete <TEntity>(this DbConnection connection, dynamic id)
{
var type = typeof(TEntity);
var tableName = AttributeParser.GetTableName(type);
var key = AttributeParser.GetKey(type);
var keyColumn = AttributeParser.GetColumnName(key);
var sql = $"DELETE FROM {tableName} WHERE {keyColumn} = @Id";
var deleted = await connection.Execute(sql, new { Id = id }).ConfigureAwait(false);
return(deleted > 0);
}
/// <summary>
/// Inserts an entity and returns an id.
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="connection">Open DbConnection</param>
/// <param name="entity">The new entity.</param>
/// <returns>Id of inserted entity</returns>
public static async Task <int> Insert <TEntity>(this DbConnection connection, TEntity entity)
{
var type = typeof(TEntity);
var name = AttributeParser.GetTableName(type);
var properties = AttributeParser.GetAllProperties(type);
var collections = AttributeParser.GetAllCollections(type);
var key = AttributeParser.GetKey(type);
if (AttributeParser.GetKeyIsGenerated(key))
{
properties = properties.Where(pi => pi != key);
}
var insertAttributes = properties.Except(collections).Except(AttributeParser.GetAllIgnored(type)).ToList();
var columns = new StringBuilder(null);
for (var i = 0; i < insertAttributes.Count; i++)
{
columns.Append(AttributeParser.GetColumnName(insertAttributes[i]));
if (i < insertAttributes.Count - 1)
{
columns.Append(", ");
}
}
var parameters = new StringBuilder(null);
for (var i = 0; i < insertAttributes.Count; i++)
{
parameters.Append($"@{insertAttributes[i].Name}");
if (i < insertAttributes.Count - 1)
{
parameters.Append(", ");
}
}
var sql = $"INSERT INTO {name} ({columns}) VALUES ({parameters})";
await connection.Execute(sql, entity).ConfigureAwait(false);
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT last_insert_rowid()";
var identity = await command.ExecuteScalarAsync().ConfigureAwait(false);
return(Convert.ToInt32(identity, CultureInfo.InvariantCulture));
}