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