/// <summary> /// Generates a UPDATE query that applies a set of values passed with a /// anonymous object, to a set of rows that fulfill the passed <see cref="IQueryCondition"/>. /// It is mandatory to use a anonymous object that only contains the Properties /// that should be updated, because a passed domain-object would result in a query /// updating all columns and that is not the desired behaviour most of the time. /// </summary> /// <param name="objectContainingChanges">the anonymous object containig the updated values</param> /// <param name="updateCondition">the condition, which rows should be updated</param> /// <returns>a query-<see cref="string"/> and a <see cref="QueryParameter[]"/> /// that contains the updated values and the values used in the WHERE clause. /// Both types of parameters are sanitized.</returns> public (string query, QueryParameter[] queryParameters) GenerateUpdateQuery( object objectContainingChanges, IQueryCondition updateCondition) { if (updateCondition is null) { throw new ArgumentNullException(nameof(updateCondition)); } if (objectContainingChanges is null) { throw new ArgumentNullException(nameof(objectContainingChanges)); } if (objectContainingChanges.GetType() == typeof(T)) { throw new InvalidOperationException( "You are not allowed to pass the domain object itself" + " -> pass a anonymous object containing the changes to apply"); } foreach (var currentProperty in objectContainingChanges.GetType().GetProperties()) { if (currentProperty.Name == "Id") { throw new InvalidOperationException( "Attempted to update Id column -> forbidden operation"); } else if (!typeof(T).GetProperties().Any(p => p.Name == currentProperty.Name)) { throw new InvalidOperationException( $"Property {currentProperty.Name} does not exist for type {typeof(T).Name}"); } var correctType = typeof(T).GetProperties() .First(p => p.Name == currentProperty.Name) .PropertyType; if (!(correctType == currentProperty.PropertyType)) { throw new InvalidOperationException( $"Property-Type mismatch of property {currentProperty.Name}; " + $"expected type {correctType.FullName}, actual type {currentProperty.PropertyType.FullName}"); } } var queryStringBuilder = new StringBuilder(); var queryParameters = new List <QueryParameter>(); queryStringBuilder.Append($"UPDATE [Hurace].[{typeof(T).Name}] SET"); AppendColumnValuesWithAssignment(queryStringBuilder, queryParameters, objectContainingChanges); queryStringBuilder.Append(" WHERE "); updateCondition.AppendTo(queryStringBuilder, queryParameters); return(queryStringBuilder.ToString(), queryParameters.ToArray()); }
/// <summary> /// Generates a DELETE query that removes all rows from the db, that fulfill /// a passed condition. /// </summary> /// <param name="deleteCondition">if a row fulfills this condition, the generated query will delete it.</param> /// <returns>a query-<see cref="string"/> and a <see cref="QueryParameter[]"/> that /// contains all parameters used in the WHERE-clause in a sanitized way.</returns> public (string query, QueryParameter[] queryParameters) GenerateDeleteQuery(IQueryCondition deleteCondition) { if (deleteCondition is null) { throw new ArgumentNullException(nameof(deleteCondition)); } var queryStringBuilder = new StringBuilder(); var queryParameters = new List <QueryParameter>(); queryStringBuilder.Append($"DELETE FROM [Hurace].[{typeof(T).Name}] WHERE "); deleteCondition.AppendTo(queryStringBuilder, queryParameters); return(queryStringBuilder.ToString(), queryParameters.ToArray()); }
/// <summary> /// Generates a SELECT query that supplies the executer with either all rows, or rows /// that fall into passed rules /// </summary> /// <param name="selectCondition">gets transformed into a where clause</param> /// <returns>a <see cref="ValueTuple{string, QueryParameter[]}" containing the /// select query as a string and optionally the parameters of the /// passed condition/>a query-<see cref="string"/> and a <see cref="QueryParameter[]"/> /// that contains the sanitized values of the passed <see cref="IQueryCondition"/></returns> public (string query, QueryParameter[] queryParameters) GenerateSelectQuery(IQueryCondition selectCondition = null) { var queryStringBuilder = new StringBuilder(); queryStringBuilder.Append("SELECT "); AppendColumnNames(queryStringBuilder); queryStringBuilder.Append($" FROM [Hurace].[{typeof(T).Name}]"); var queryParameters = new List <QueryParameter>(); if (selectCondition != null) { queryStringBuilder.Append(" WHERE "); selectCondition.AppendTo(queryStringBuilder, queryParameters); } return(queryStringBuilder.ToString(), queryParameters.ToArray()); }