/// <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); }