/// <summary>
/// Return an enumerable which uses lazy loading of each row.
/// </summary>
/// <typeparam name="TEntity">Type of entity to map</typeparam>
/// <param name="unitOfWork">Connection to invoke <c>ExecuteReader()</c> on (through a created <c>DbCommand</c>).</param>
/// <param name="ownsConnection">
/// <c>true</c> if the connection should be disposed together with the command/datareader. See
/// remarks.
/// </param>
/// <param name="query">Query or short query (<c>"id = @1"</c>)</param>
/// <param name="parameters"></param>
/// <returns>Lazy loaded enumerator</returns>
/// <remarks>
/// <para>
/// For more information about the "query" and "parameters" arguments, see <see cref="CommandExtensions.ApplyQuerySql{TEntity}"/>.
/// </para>
/// <para>
/// The returned enumerator will not map each row until it's requested. To be able to do that the
/// connection/command/datareader is
/// kept open until the enumerator is disposed. Hence it's important that you make sure that the enumerator is
/// disposed when you are
/// done with it.
/// </para>
/// <para>Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[IEntityMapper<TEntity>]]></c>.</para>
/// </remarks>
/// <example>
/// <code>
/// // All these examples are valid:
/// <![CDATA[
/// var users = unitOfWork.ToEnumerable<User>(true, "Age < 10");
/// var users = unitOfWork.ToEnumerable<User>(true, "SELECT * FROM Users WHERE Age = 37");
/// var users = unitOfWork.ToEnumerable<User>(true, "FirstName = @name", new { name = user.FirstName });
/// var users = unitOfWork.ToEnumerable<User>(true, "FirstName = @1 AND Age < @2", 'A%', 35);
/// var users = unitOfWork.ToEnumerable<User>(true, "SELECT * FROM Users WHERE Age = @age LIMIT 1, 10", new { age = submittedAge });
/// var users = unitOfWork.ToEnumerable<User>(true, "SELECT * FROM Users WHERE Age = @1 LIMIT 1, 10", user.FirstName);
/// ]]>
/// </code>
/// </example>
public static IEnumerable <TEntity> ToEnumerable <TEntity>(this IAdoNetUnitOfWork unitOfWork,
bool ownsConnection, string query, params object[] parameters)
{
if (unitOfWork == null)
{
throw new ArgumentNullException("unitOfWork");
}
var mapping = EntityMappingProvider.GetMapper <TEntity>();
var cmd = unitOfWork.CreateDbCommand();
try
{
cmd.ApplyQuerySql(mapping, query, parameters);
var reader = cmd.ExecuteReader();
return(new AdoNetEntityEnumerable <TEntity>(cmd, reader, mapping, ownsConnection));
}
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.GetMapper <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>
/// Get an entity.
/// </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>Found entity</returns>
/// <remarks>
/// <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.FirstAsync<User>("WHERE id = @id", new { id = UserId });
/// }
/// ]]>
/// </code>
/// <para>
/// Which will translate into:
/// </para>
/// <code>
/// command.CommandText = "SELECT * FROM Users WHERE id = @id";
/// var p = command.CreateParameter();
/// p.Name = "id";
/// p.Value = userId;
/// command.Parameters.Add(p);
/// </code>
/// </example>
/// <exception cref="EntityNotFoundException">Failed to find an entity mathing the query</exception>
public static Task <TEntity> FirstAsync <TEntity>(this IAdoNetUnitOfWork unitOfWork, string query, object parameters)
{
if (unitOfWork == null)
{
throw new ArgumentNullException("unitOfWork");
}
if (query == null)
{
throw new ArgumentNullException("query");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
var mapping = EntityMappingProvider.GetMapper <TEntity>();
using (var cmd = unitOfWork.CreateDbCommand())
{
cmd.ApplyQuerySql(mapping, query, parameters);
return(cmd.FirstAsync(mapping));
}
}
/// <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.GetMapper <TEntity>();
using (var cmd = (DbCommand)unitOfWork.CreateCommand())
{
try
{
mapper.CommandBuilder.InsertCommand(cmd, entity);
var keys = mapper.GetKeys(entity);
if (keys.Length == 1)
{
var id = await cmd.ExecuteScalarAsync();
if (id != null && id != DBNull.Value)
{
mapper.Properties[keys[0].Key].SetColumnValue(entity, id);
}
return(id);
}
return(await cmd.ExecuteScalarAsync());
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
}
/// <summary>
/// Return an enumerable which uses lazy loading of each row.
/// </summary>
/// <typeparam name="TEntity">Type of entity to map</typeparam>
/// <param name="connection">Connection to invoke <c>ExecuteReaderAsync()</c> on (through a created <c>DbCommand</c>).</param>
/// <param name="ownsConnection">
/// <c>true</c> if the connection should be disposed together with the command/datareader. See
/// remarks.
/// </param>
/// <param name="query">Query or short query (<c>"id = @1"</c>)</param>
/// <param name="parameters"></param>
/// <returns>Lazy loaded enumerator</returns>
/// <remarks>
/// <para>
/// For more information about the "query" and "parameters" arguments, see <see cref="CommandExtensions.ApplyQuerySql{TEntity}"/>.
/// </para>
/// <para>
/// The returned enumerator will not map each row until it's requested. To be able to do that the
/// connection/command/datareader is
/// kept open until the enumerator is disposed. Hence it's important that you make sure that the enumerator is
/// disposed when you are
/// done with it.
/// </para>
/// <para>Uses <see cref="EntityMappingProvider" /> to find the correct <c><![CDATA[IEntityMapper<TEntity>]]></c>.</para>
/// </remarks>
/// <example>
/// <code>
/// // All these examples are valid:
/// <![CDATA[
/// var users = await connection.ToEnumerable<User>(true, "Age < 10");
/// var users = await connection.ToEnumerable<User>(true, "SELECT * FROM Users WHERE Age = 37");
/// var users = await connection.ToEnumerable<User>(true, "FirstName = @name", new { name = user.FirstName });
/// var users = await connection.ToEnumerable<User>(true, "FirstName = @1 AND Age < @2", 'A%', 35);
/// var users = await connection.ToEnumerable<User>(true, "SELECT * FROM Users WHERE Age = @age LIMIT 1, 10", new { age = submittedAge });
/// var users = await connection.ToEnumerable<User>(true, "SELECT * FROM Users WHERE Age = @1 LIMIT 1, 10", user.FirstName);
/// ]]>
/// </code>
/// </example>
public static async Task <IEnumerable <TEntity> > ToEnumerableAsync <TEntity>(this IDbConnection connection,
bool ownsConnection, string query, params object[] parameters)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
var mapping = EntityMappingProvider.GetMapper <TEntity>();
var cmd = connection.CreateDbCommand();
try
{
cmd.ApplyQuerySql(mapping, query, parameters);
var reader = await cmd.ExecuteReaderAsync();
return(new AdoNetEntityEnumerable <TEntity>(cmd, reader, mapping, ownsConnection));
}
catch (Exception e)
{
throw cmd.CreateDataException(e);
}
}
/// <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, dynamic parameters)
{
var mapper = EntityMappingProvider.GetMapper <TEntity>();
using (var cmd = unitOfWork.CreateCommand())
{
cmd.CommandText = string.Format("SELECT TOP 100 * FROM {0} WHERE ", mapper.TableName);
var args = ObjectExtensions.ToDictionary(parameters);
foreach (var parameter in args)
{
Data.CommandExtensions.AddParameter(cmd, parameter.Key, parameter.Value);
if (parameter.Value is string && parameter.Value.Contains("%"))
{
cmd.CommandText += mapper.Properties[parameter.Key].ColumnName + " LIKE @" + parameter.Key + " AND ";
}
else
{
cmd.CommandText += mapper.Properties[parameter.Key].ColumnName + " = @" + parameter.Key + " AND ";
}
}
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 4, 4);
return(cmd.ToList <TEntity>());
}
}
/// <summary>
/// Fetches the first found entity asynchronously
/// </summary>
/// <param name="cmd">Command to invoke <c>ExecuteReaderAsync()</c> on.</param>
/// <returns>
/// entity
/// </returns>
/// <exception cref="EntityNotFoundException">Failed to find specified entity.</exception>
/// <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[IEntityMapper<TEntity>]]></c>
/// </para>
/// </remarks>
/// <example>
/// <code>
/// <![CDATA[
/// public async Task<User> GetUser(int userId)
/// {
/// using (var command = connection.CreateCommand())
/// {
/// cmd.CommandText = "SELECT * FROM Users WHERE Id = @id";
/// cmd.AddParameter("id", userId);
/// return await cmd.FirstAsync<User>();
/// }
/// }
/// ]]>
/// </code>
/// </example>
public static Task <TEntity> FirstAsync <TEntity>(this IDbCommand cmd)
{
var mapping = EntityMappingProvider.GetBaseMapper <TEntity>();
return(FirstAsync(cmd, 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>();
await DeleteAsync(unitOfWork, mapper, entity);
}