/// <summary>
/// Insert a new row into the database.
/// </summary>
/// <typeparam name="TEntity">Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider"/>.</typeparam>
/// <param name="connection">Connection create and execute our command on.</param>
/// <param name="entity">entity to insert into the database.</param>
/// <remarks>
/// <para>
/// Will assign the PK value to the
/// </para>
/// </remarks>
/// <example>
/// <code>
/// var user = new User(10, "Jonas");
/// connection.Insert(user);
/// </code>
/// </example>
public static object Insert <TEntity>(this IDbConnection connection, TEntity entity)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateCommand())
{
try
{
mapper.CommandBuilder.InsertCommand(cmd, entity);
var keys = mapper.GetKeys(entity);
if (keys.Length == 1)
{
var id = cmd.ExecuteScalar();
if (id != null && id != DBNull.Value)
{
mapper.Properties[keys[0].Key].SetProperty(entity, id);
}
return(id);
}
return(cmd.ExecuteScalar());
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// Insert a new row into the database.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider" />
/// .
/// </typeparam>
/// <param name="unitOfWork">Unit of work to execute command in.</param>
/// <param name="entity">entity to insert into the database.</param>
/// <returns>Task to wait on for completion</returns>
/// <remarks>
/// <para>
/// Will assign the PK value to the
/// </para>
/// </remarks>
/// <example>
/// <code>
/// var user = new User(10, "Jonas");
/// using (var uow = UnitOfWorkFactory.Create())
/// {
/// await uow.InsertAsync(user);
/// }
/// </code>
/// </example>
public static async Task <object> InsertAsync <TEntity>(this IAdoNetUnitOfWork unitOfWork, TEntity entity)
{
if (unitOfWork == null)
{
throw new ArgumentNullException("unitOfWork");
}
if (EqualityComparer <TEntity> .Default.Equals(default(TEntity), entity))
{
throw new ArgumentNullException("entity");
}
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = (DbCommand)unitOfWork.CreateCommand())
{
try
{
mapper.CommandBuilder.InsertCommand(cmd, entity);
var value = await cmd.AssignAutoIncrementIfConfigured(entity, mapper);
return(value ?? await cmd.ExecuteScalarAsync());
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// Truncate table (remove all rows without filling the transaction log)
/// </summary>
/// <typeparam name="TEntity">Type of entity (must have a mapping registred in the <see cref="EntityMappingProvider"/>)</typeparam>
/// <param name="unitOfWork">Uow to extend</param>
public static void Truncate <TEntity>(this IAdoNetUnitOfWork unitOfWork)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = unitOfWork.CreateCommand())
{
mapper.CommandBuilder.TruncateCommand(cmd);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Find a collection of entities
/// </summary>
/// <typeparam name="TEntity">Type of entity (must have a mapping registred in the <see cref="EntityMappingProvider"/>)</typeparam>
/// <param name="unitOfWork">Uow to extend</param>
/// <param name="parameters">The parameters.</param>
/// <returns>Collection (can be empty if no entries are found)</returns>
/// <example>
/// <code>
/// // will generate a SQL clause: WHERE FirstName Like 'A%' AND LastName LIKE 'B%'
/// uow.First(new { FirstName = "A%", LastName = "B%" });
/// </code>
/// </example>
public static IList <TEntity> ToList <TEntity>(this IAdoNetUnitOfWork unitOfWork, object parameters)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = unitOfWork.CreateCommand())
{
cmd.CommandText = $"SELECT * FROM {mapper.TableName} WHERE ";
var args = parameters.ToDictionary();
foreach (var parameter in args)
{
cmd.AddParameter(parameter.Key, parameter.Value);
if (parameter.Value is string value && value.Contains("%"))
{
cmd.CommandText += mapper.Properties[parameter.Key].ColumnName + " LIKE @" + parameter.Key + " AND ";
}
/// <summary>
/// Get an entity.
/// </summary>
/// <typeparam name="TEntity">Type of entity to load, must have an mapper registered in <see cref="EntityMappingProvider"/>.</typeparam>
/// <param name="connection">connection to load entity from</param>
/// <param name="constraints">dynamic specifying the properties to use. All parameters are joined with "AND" in the resulting SQL query. Any parameter with '%' in the value will be using LIKE instead of '='</param>
/// <returns>Found entity</returns>
/// <remarks>
/// <para>Uses <see cref="EntityMappingProvider"/> to find the correct <c><![CDATA[ICrudEntityMapper<TEntity>]]></c></para>
/// </remarks>
/// <example>
/// <code>
/// <![CDATA[
/// public User GetUser(int userId)
/// {
/// return _connection.First<User>(new { Id = userId });
/// }
/// ]]>
/// </code>
/// <para>
/// You can also use % for LIKE searches:
/// </para>
/// <code>
/// <![CDATA[
/// return _connection.First<User>(new { FirstName = 'Jon%', LastName = 'Gau%' });
/// ]]>
/// </code>
/// <para>
/// Which will translate into "WHERE FirstName LIKE 'Jon%' AND LastName LIKE 'Gau%'"
/// </para>
/// </example>
/// <exception cref="EntityNotFoundException">Failed to find an entity mathing the query</exception>
public static TEntity First <TEntity>(this IDbConnection connection, object constraints)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
var mapping = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateDbCommand())
{
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE ", mapping.TableName);
cmd.ApplyConstraints(mapping, constraints);
return(cmd.First(mapping));
}
}
/// <summary>
/// DELETE a row from the table.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider" />
/// .
/// </typeparam>
/// <param name="unitOfWork">Unit of work to execute command in.</param>
/// <param name="entity">Uses the primary key column(s), as defined in the mapping, to remove the entry.</param>
/// <returns>Task to wait on for completion.</returns>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task DeleteUser(int userId)
/// {
/// return await _unitOfWork.DeleteAsync(new User { Id = userId });
/// }
/// ]]>
/// </code>
/// </example>
public static async Task DeleteAsync <TEntity>(this IAdoNetUnitOfWork unitOfWork, TEntity entity)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = (DbCommand)unitOfWork.CreateCommand())
{
try
{
mapper.CommandBuilder.DeleteCommand(cmd, entity);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// Truncate a table (remove all rows)
/// </summary>
/// <typeparam name="TEntity">Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider"/>.</typeparam>
/// <param name="connection">Connection to create and execute our command on</param>
public static void Truncate <TEntity>(this IDbConnection connection)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateCommand())
{
try
{
mapper.CommandBuilder.TruncateCommand(cmd);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// Update an existing entity
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to load, must have an mapper registered in
/// <see cref="EntityMappingProvider" />.
/// </typeparam>
/// <param name="connection">Connection to use</param>
/// <param name="entity">Entity to update.</param>
/// <returns>Task to wait on for completion</returns>
/// <remarks>
/// <para>
/// Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[ICrudEntityMapper<TEntity>]]></c>
/// </para>
/// </remarks>
public static async Task UpdateAsync <TEntity>(this IDbConnection connection, TEntity entity)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateDbCommand())
{
try
{
mapper.CommandBuilder.UpdateCommand(cmd, entity);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// DELETE a row from the table.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider" />
/// .
/// </typeparam>
/// <param name="unitOfWork">Unit of work to execute command in.</param>
/// <param name="constraints">Constraints to be used. any field with '%' in the name will return in <c>LIKE</c> queries.</param>
/// <returns>Task to wait on for completion.</returns>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task DeleteUser(int userId)
/// {
/// await _unitOfWork.DeleteAsync(new { Id = userId });
/// }
/// ]]>
/// </code>
/// </example>
public static async Task DeleteAsync <TEntity>(this IAdoNetUnitOfWork unitOfWork, object constraints)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = (DbCommand)unitOfWork.CreateCommand())
{
try
{
cmd.CommandText = $"DELETE FROM {mapper.TableName} WHERE ";
cmd.ApplyConstraints(mapper, constraints);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// DELETE a row from the table.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to load, must have an mapper registered in
/// <see cref="EntityMappingProvider" />.
/// </typeparam>
/// <param name="connection">DB connection.</param>
/// <param name="constraints">
/// dynamic specifying the properties to use. All parameters are joined with "AND" in the
/// resulting SQL query. Any parameter with '%' in the value will be using LIKE instead of '='
/// </param>
/// <returns>Task to wait on for completion.</returns>
/// <remarks>
/// <para>
/// Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[ICrudEntityMapper<TEntity>]]></c>
/// </para>
/// </remarks>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task DeleteUser(int userId)
/// {
/// await connection.DeleteAsync(new { Id = userId });
/// }
/// ]]>
/// </code>
/// </example>
public static async Task DeleteAsync <TEntity>(this IDbConnection connection, object constraints)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateDbCommand())
{
try
{
cmd.CommandText = string.Format("DELETE FROM {0} WHERE ", mapper.TableName);
cmd.ApplyConstraints(mapper, constraints);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// Insert an entity into the database
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to load, must have an mapper registered in
/// <see cref="EntityMappingProvider" />.
/// </typeparam>
/// <param name="connection">Connection to use</param>
/// <param name="entity">Entity to insert.</param>
/// <returns>Task to wait on for completion</returns>
/// <remarks>
/// <para>
/// Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[ICrudEntityMapper<TEntity>]]></c>
/// </para>
/// </remarks>
public static async Task <object> InsertAsync <TEntity>(this IDbConnection connection, TEntity entity)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateDbCommand())
{
try
{
mapper.CommandBuilder.InsertCommand(cmd, entity);
var value = await cmd.AssignAutoIncrementIfConfigured(entity, mapper);
return(value ?? await cmd.ExecuteScalarAsync());
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// Fetches the first row if found.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to load, must have an mapper registered in
/// <see cref="EntityMappingProvider" />.
/// </typeparam>
/// <param name="unitOfWork">UnitOfWork to invoke <c>ExecuteReaderAsync()</c> on (through a created <c>DbCommand</c>).</param>
/// <param name="constraints">
/// dynamic specifying the properties to use. All parameters are joined with "AND" in the
/// resulting SQL query. Any parameter with '%' in the value will be using LIKE instead of '='
/// </param>
/// <returns>
/// Entity if found; otherwise <c>null</c>.
/// </returns>
/// <remarks>
/// <para>Use this method when an entity is expected to be returned.</para>
/// <para>
/// Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[ICrudEntityMapper<TEntity>]]></c>
/// </para>
/// </remarks>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task<User> GetUser(int userId)
/// {
/// return await _connection.FirstOrDefaultAsync<User>(new { Id = userId });
/// }
/// ]]>
/// </code>
/// <para>
/// You can also use % for LIKE searches:
/// </para>
/// <code>
/// <![CDATA[
/// return await _connection.FirstOrDefaultAsync<User>(new { FirstName = 'Jon%', LastName = 'Gau%' });
/// ]]>
/// </code>
/// <para>
/// Which will translate into "WHERE FirstName LIKE 'Jon%' AND LastName LIKE 'Gau%'"
/// </para>
/// </example>
public static Task <TEntity> FirstOrDefaultAsync <TEntity>(this IAdoNetUnitOfWork unitOfWork, object constraints)
{
if (unitOfWork == null)
{
throw new ArgumentNullException("unitOfWork");
}
if (constraints == null)
{
throw new ArgumentNullException("constraints");
}
var mapping = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = unitOfWork.CreateDbCommand())
{
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE ", mapping.TableName);
cmd.ApplyConstraints(mapping, constraints);
return(cmd.FirstOrDefaultAsync <TEntity>());
}
}
/// <summary>
/// Fetches the first row if found.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to load, must have an mapper registered in
/// <see cref="EntityMappingProvider" />.
/// </typeparam>
/// <param name="unitOfWork">UnitOfWork to invoke <c>ExecuteReaderAsync()</c> on (through a created <c>DbCommand</c>).</param>
/// <param name="query">Query or short query (<c><![CDATA["projectId = @id AND dateCreated < @minDate"]]></c>)</param>
/// <param name="parameters">
/// Anonymous object (<c>new { id = dto.ProjectId, @minDate = dto.MinDate }</c>), a dictionary or
/// a value array
/// </param>
/// <returns>
/// Entity if found; otherwise <c>null</c>.
/// </returns>
/// <remarks>
/// <para>Use this method when an entity is expected to be returned.</para>
/// <para>
/// Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[ICrudEntityMapper<TEntity>]]></c>
/// </para>
/// </remarks>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task<User> GetUser(int userId)
/// {
/// return await _connection.FirstOrDefaultAsync<User>("WHERE age < @Age", new { Age = minAge });
/// }
/// ]]>
/// </code>
/// </example>
public static Task <TEntity> FirstOrDefaultAsync <TEntity>(this IAdoNetUnitOfWork unitOfWork, string query,
params object[] parameters)
{
if (unitOfWork == null)
{
throw new ArgumentNullException("unitOfWork");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
var mapping = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = unitOfWork.CreateDbCommand())
{
cmd.ApplyQuerySql(mapping, query, parameters);
return(cmd.FirstOrDefaultAsync <TEntity>());
}
}
/// <summary>
/// Fetches the first row if found.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to load, must have an mapper registered in
/// <see cref="EntityMappingProvider" />.
/// </typeparam>
/// <param name="connection">Connection to invoke <c>ExecuteReaderAsync()</c> on (through a created <c>DbCommand</c>).</param>
/// <param name="query">Query or short query (<c><![CDATA["projectId = @id AND dateCreated < @minDate"]]></c>)</param>
/// <param name="parameters">
/// Anonymous object (<c>new { id = dto.ProjectId, @minDate = dto.MinDate }</c>), a dictionary or
/// a value array
/// </param>
/// <returns>
/// Entity if found; otherwise <c>null</c>.
/// </returns>
/// <remarks>
/// <para>Use this method when an entity is expected to be returned.</para>
/// <para>
/// Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[ICrudEntityMapper<TEntity>]]></c>
/// </para>
/// </remarks>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task<User> GetUser(int userId)
/// {
/// return await _connection.FirstOrDefaultAsync<User>("WHERE age < @Age", new { Age = minAge });
/// }
/// ]]>
/// </code>
/// </example>
public static Task <TEntity> FirstOrDefaultAsync <TEntity>(this IDbConnection connection, string query,
params object[] parameters)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
var mapping = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateDbCommand())
{
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE ", mapping.TableName);
cmd.ApplyQuerySql(mapping, query, parameters);
return(cmd.FirstOrDefaultAsync <TEntity>());
}
}
/// <summary>
/// DELETE a row from the table.
/// </summary>
/// <typeparam name="TEntity">Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider"/>.</typeparam>
/// <param name="connection">Unit of work to execute command in.</param>
/// <param name="entity">Uses the primary key column(s), as defined in the mapping, to remove the entry.</param>
/// <example>
/// <code>
/// <![CDATA[
/// public void DeleteUser(int userId)
/// {
/// connection.Delete(new User { Id = userId });
/// }
/// ]]>
/// </code>
/// </example>
public static void Delete <TEntity>(this IDbConnection connection, TEntity entity)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateCommand())
{
try
{
mapper.CommandBuilder.DeleteCommand(cmd, entity);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// DELETE a row from the table.
/// </summary>
/// <typeparam name="TEntity">Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider"/>.</typeparam>
/// <param name="connection">Unit of work to execute command in.</param>
/// <param name="constraints"><c>dynamic</c> specifying the properties to use. All parameters are joined with "AND" in the resulting SQL query. Any parameter with '%' in the value will be using LIKE instead of '='</param>
/// <example>
/// <code>
/// <![CDATA[
/// public void DeleteUser(int userId)
/// {
/// connection.Delete(new { Id = userId });
/// }
/// ]]>
/// </code>
/// <para>Alternative syntax:</para>
/// <code>
/// <![CDATA[
/// public void DeleteUser(SomeDTO dto)
/// {
/// connection.Delete(new { dto.Id });
/// }
/// ]]>
/// </code>
/// </example>
public static void Delete <TEntity>(this IDbConnection connection, object constraints)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
using (var cmd = connection.CreateCommand())
{
try
{
cmd.CommandText = "DELETE FROM " + mapper.TableName + " WHERE ";
cmd.ApplyConstraints(mapper, constraints);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// DELETE a row from the table.
/// </summary>
/// <typeparam name="TEntity">
/// Type of entity to use, must have an mapper registered in <see cref="EntityMappingProvider" />
/// .
/// </typeparam>
/// <param name="unitOfWork">Unit of work to execute command in.</param>
/// <param name="entity">Uses the primary key column(s), as defined in the mapping, to remove the entry.</param>
/// <returns>Task to wait on for completion.</returns>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task DeleteUser(int userId)
/// {
/// return await _unitOfWork.DeleteAsync(new User { Id = userId });
/// }
/// ]]>
/// </code>
/// </example>
public static async Task DeleteAsync <TEntity>(this IAdoNetUnitOfWork unitOfWork, TEntity entity)
{
var mapper = EntityMappingProvider.GetCrudMapper <TEntity>();
await DeleteAsync(unitOfWork, mapper, entity);
}
