/// <inheritdoc /> public virtual SqlQuery GetDelete(TEntity entity) { var sqlQuery = new SqlQuery(); var whereSql = " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => TableName + "." + p.ColumnName + " = @" + p.PropertyName)); if (!LogicalDelete) { sqlQuery.SqlBuilder.Append("DELETE FROM " + TableName + whereSql); } else { sqlQuery.SqlBuilder.Append("UPDATE " + TableName + " SET " + StatusPropertyName + " = " + LogicalDeleteValue); if (HasUpdatedAt) { UpdatedAtProperty.SetValue(entity, DateTime.UtcNow); sqlQuery.SqlBuilder.Append(", " + UpdatedAtPropertyMetadata.ColumnName + " = @" + UpdatedAtPropertyMetadata.PropertyName); } sqlQuery.SqlBuilder.Append(whereSql); } sqlQuery.SetParam(entity); return(sqlQuery); }
/// <inheritdoc /> public virtual SqlQuery GetDelete(TEntity entity) { var sqlQuery = new SqlQuery(); var whereSql = string.Format(" WHERE {0}", string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName)))); if (!LogicalDelete) { sqlQuery.SqlBuilder.AppendFormat("DELETE FROM {0}{1}", TableName, whereSql); } else { sqlQuery.SqlBuilder.AppendFormat("UPDATE {0} SET {1} = {2}", TableName, StatusPropertyName, LogicalDeleteValue); if (HasUpdatedAt) { UpdatedAtProperty.SetValue(entity, DateTime.UtcNow); sqlQuery.SqlBuilder.Append(string.Format(", {0} = @{1}", UpdatedAtPropertyMetadata.ColumnName, UpdatedAtPropertyMetadata.PropertyName)); } sqlQuery.SqlBuilder.Append(whereSql); } sqlQuery.SetParam(entity); return(sqlQuery); }
public SqlQuery GetSelectByIds(TEntity entity) { var query = new SqlQuery(entity); query.SqlBuilder.Append("SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => p.ColumnName + " = @" + p.PropertyName))); return(query); }
/// <inheritdoc /> public virtual SqlQuery GetUpSert(TEntity entity) { var properties = (IsIdentity ? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase)) : SqlProperties).ToList(); if (HasUpdatedAt) { UpdatedAtProperty.SetValue(entity, DateTime.UtcNow); } if (HasModifiedAt) { ModifiedAtProperty.SetValue(entity, DateTime.Now); } var query = new SqlQuery(entity); var valueFields = string.Join(", ", KeySqlProperties.Select(p => " @" + p.PropertyName)); var selectFields = string.Join(", ", KeySqlProperties.Select(p => p.PropertyName)); var where = string.Join(" AND ", KeySqlProperties.Select(p => "target." + p.ColumnName + " = @" + p.PropertyName)); //override where if we have attributes if (KeyUpsertSqlProperties.Any()) { where = string.Join(" AND ", KeyUpsertSqlProperties.Select(p => "target." + p.ColumnName + " = @" + p.PropertyName)); valueFields = string.Join(", ", KeyUpsertSqlProperties.Select(p => " @" + p.PropertyName)); selectFields = string.Join(", ", KeyUpsertSqlProperties.Select(p => p.PropertyName)); } //exclude update at when has modified is enabled var update = string.Empty; if (HasModifiedAt) { update = "UPDATE SET " + string.Join(", ", properties.Where(e => e.PropertyName != UpdatedAtProperty?.Name) .Select(p => p.ColumnName + " = @" + p.PropertyName)); } else { //exclude both modified and updated at update = "UPDATE SET " + string.Join(", ", properties.Where(e => e.PropertyName != UpdatedAtProperty?.Name && e.PropertyName != ModifiedAtProperty?.Name) .Select(p => p.ColumnName + " = @" + p.PropertyName)); } var insert = "INSERT " + "(" + string.Join(", ", properties.Select(p => p.ColumnName)) + ")" // columNames + " VALUES (" + string.Join(", ", properties.Select(p => "@" + p.PropertyName)) + ")"; // values query.SqlBuilder.Append( $"MERGE {TableName} AS target USING (SELECT {valueFields}) AS source ({selectFields}) ON ({where}) WHEN MATCHED THEN {update} WHEN NOT MATCHED THEN {insert} OUTPUT Inserted.{KeySqlProperties.First().PropertyName}; "); return(query); }
/// <inheritdoc /> public virtual SqlQuery GetDelete(TEntity entity) { var sqlQuery = new SqlQuery(); var whereAndSql = string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName))); if (!LogicalDelete) { sqlQuery.SqlBuilder .Append("DELETE FROM ") .Append(TableName) .Append(" WHERE ") .Append(whereAndSql); } else { sqlQuery.SqlBuilder .Append("UPDATE ") .Append(TableName) .Append(" SET ") .Append(StatusPropertyName) .Append(" = ") .Append(LogicalDeleteValue); if (HasUpdatedAt) { var attribute = UpdatedAtProperty.GetCustomAttribute <UpdatedAtAttribute>(); var offset = attribute.TimeKind == DateTimeKind.Local ? new DateTimeOffset(DateTime.Now) : new DateTimeOffset(DateTime.UtcNow); if (attribute.OffSet != 0) { offset = offset.ToOffset(TimeSpan.FromHours(attribute.OffSet)); } UpdatedAtProperty.SetValue(entity, offset.DateTime); sqlQuery.SqlBuilder .Append(", ") .Append(UpdatedAtPropertyMetadata.ColumnName) .Append(" = @") .Append(UpdatedAtPropertyMetadata.PropertyName); } sqlQuery.SqlBuilder .Append(" WHERE ") .Append(whereAndSql); } sqlQuery.SetParam(entity); return(sqlQuery); }
/// <inheritdoc /> public virtual SqlQuery GetDelete(TEntity entity) { var sqlQuery = new SqlQuery(); var whereAndSql = string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName))); if (HasRowVersion) { whereAndSql += " AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName; } if (!LogicalDelete) { sqlQuery.SqlBuilder .Append("DELETE FROM ") .Append(TableName) .Append(" WHERE ") .Append(whereAndSql); } else { sqlQuery.SqlBuilder .Append("UPDATE ") .Append(TableName) .Append(" SET ") .Append(StatusPropertyName) .Append(" = ") .Append(LogicalDeleteValue); if (HasUpdatedAt) { UpdatedAtProperty.SetValue(entity, DateTime.UtcNow); sqlQuery.SqlBuilder .Append(", ") .Append(UpdatedAtPropertyMetadata.ColumnName) .Append(" = @") .Append(UpdatedAtPropertyMetadata.PropertyName); } sqlQuery.SqlBuilder .Append(" WHERE ") .Append(whereAndSql); } sqlQuery.SetParam(entity); LogSqlQuery(sqlQuery); return(sqlQuery); }
/// <inheritdoc /> public virtual SqlQuery GetDelete(TEntity entity, bool isLogicDelete = true) { var sqlQuery = new SqlQuery(); var cacheKey = _sqlCacheKey + "GetDelete" + "_" + isLogicDelete; if (StringCache.ContainsKey(cacheKey)) { sqlQuery.SqlBuilder.Append(StringCache[cacheKey]); } else { var whereSql = $" WHERE {string.Join(" AND ", KeySqlProperties.Select(p => $" {GetPropertyName(p.Name)} = @{p.Name} "))}"; if (!isLogicDelete) { sqlQuery.SqlBuilder.Append($"DELETE FROM {TableName} {whereSql}"); } else { sqlQuery.SqlBuilder.Append($"UPDATE {TableName} SET {Config.LogicDeleteSql} "); foreach (var prop in Config.UpdateCommonProperty(entity.autoid)) { sqlQuery.SqlBuilder.Append($", {GetPropertyName(prop.Key)} = @{prop.Key} "); } sqlQuery.SqlBuilder.Append(whereSql); } StringCache.TryAdd(cacheKey, sqlQuery.GetSql()); } var dic = new Dictionary <string, object>(); foreach (var keySqlProperty in KeySqlProperties) { dic.Add(keySqlProperty.Name, keySqlProperty.GetValue(entity)); } if (isLogicDelete) { foreach (var prop in Config.UpdateCommonProperty(entity.autoid)) { dic.Add(prop.Key, prop.Value); } } sqlQuery.SetParam(dic); return(sqlQuery); }
/// <inheritdoc /> public virtual SqlQuery GetDelete(TEntity entity) { var sqlQuery = new SqlQuery(TableName, QueryType.Delete); var whereAndSql = string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName))); if (!LogicalDelete) { sqlQuery.SqlBuilder .Append("DELETE FROM ") .Append(TableName) .Append(" WHERE ") .Append(whereAndSql); } else { sqlQuery.SqlBuilder .Append("UPDATE ") .Append(TableName) .Append(" SET ") .Append(LogicalDeletePropertyMetadata.ColumnName) .Append(" = ") .Append(LogicalDeleteProperty.PropertyType.IsDateTime() ? $"'{DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss")}'" : GetLogicalDeleteValue()); if (HasUpdatedAt) { UpdatedAtProperty.SetValue(entity, DateTime.UtcNow); sqlQuery.SqlBuilder .Append(", ") .Append(UpdatedAtPropertyMetadata.ColumnName) .Append(" = @") .Append(UpdatedAtPropertyMetadata.PropertyName); } sqlQuery.SqlBuilder .Append(" WHERE ") .Append(whereAndSql); } sqlQuery.SetParam(entity); return(sqlQuery); }
/// <summary> /// /// </summary> public virtual SqlQuery GetDelete(TEntity entity) { var sqlQuery = new SqlQuery(entity); var whereSql = " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => p.ColumnName + " = @" + p.Name)); if (!LogicalDelete) { sqlQuery.SqlBuilder.Append("DELETE FROM " + TableName + whereSql); } else { if (HasDateChanged) { DateChangedProperty.SetValue(entity, DateTime.UtcNow); } sqlQuery.SqlBuilder.Append("UPDATE " + TableName + " SET " + StatusPropertyName + " = " + LogicalDeleteValue + whereSql); } return(sqlQuery); }
public override SqlQuery GetUpdate(TEntity entity) { var properties = SqlProperties.Where( p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase))).ToArray(); if (!properties.Any()) { throw new ArgumentException("Can't update without [Key]"); } if (HasUpdatedAt) { UpdatedAtProperty.SetValue(entity, DateTime.UtcNow); } var query = new SqlQuery(entity); query.SqlBuilder.Append("UPDATE " + TableName + " SET " + string.Join(", ", properties.Where(p => !p.IgnoreUpdate).Select(p => p.ColumnName + " = @" + p.PropertyName)) + " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => p.ColumnName + " = @" + p.PropertyName))); return(query); }
/// <summary> /// Performs bulk upsert operation /// </summary> /// <param name="entities"></param> /// <returns></returns> public virtual SqlQuery GetBulkUpSert(IEnumerable <TEntity> entities) { var query = new SqlQuery(); var t = TableName.Replace("[", "").Replace("]", ""); var tmpTable = $"#{t}_{new Random(100).Next()}"; //get bulk insert var bulkSql = this.GetTempTableInsert(entities, tmpTable); var bulkUpdateSql = this.GetTempTableUpdate(entities); var on = string.Join(" AND ", KeySqlProperties.Select(p => "target." + p.ColumnName + " = source." + p.ColumnName)); query.SqlBuilder.Append(this.CreateTempTable(entities, tmpTable)); query.SqlBuilder.Append(";"); query.SqlBuilder.Append($"MERGE {TableName} as target "); query.SqlBuilder.Append($"USING {tmpTable} as source "); query.SqlBuilder.Append($"ON {on} "); query.SqlBuilder.Append($"WHEN MATCHED THEN {bulkUpdateSql} "); query.SqlBuilder.Append($"WHEN NOT MATCHED THEN {bulkSql}"); query.SqlBuilder.Append(";"); query.SqlBuilder.Append(this.DropTempTable(tmpTable)); return(query); }
/// <inheritdoc /> public virtual SqlQuery GetBulkUpdate(IEnumerable <TEntity> entities) { var entitiesArray = entities as TEntity[] ?? entities.ToArray(); if (!entitiesArray.Any()) { throw new ArgumentException("collection is empty"); } var entityType = entitiesArray[0].GetType(); var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.Name.Equals(p.Name, StringComparison.OrdinalIgnoreCase))).ToArray(); var query = new SqlQuery(); var parameters = new Dictionary <string, object>(); for (var i = 0; i < entitiesArray.Length; i++) { if (i > 0) { query.SqlBuilder.Append("; "); } query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name + i}"))} WHERE {string.Join(" AND ", KeySqlProperties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name + i}"))} "); // ReSharper disable PossibleNullReferenceException foreach (var property in properties) { parameters.Add(property.Name + i, entityType.GetProperty(property.Name).GetValue(entitiesArray[i], null)); } foreach (var property in KeySqlProperties) { parameters.Add(property.Name + i, entityType.GetProperty(property.Name).GetValue(entitiesArray[i], null)); } // ReSharper restore PossibleNullReferenceException } query.SetParam(parameters); return(query); }
/// <inheritdoc /> public virtual SqlQuery GetUpdate(TEntity entity) { var query = new SqlQuery(entity); var cacheKey = _sqlCacheKey + "GetUpdate"; if (StringCache.ContainsKey(cacheKey)) { query.SqlBuilder.Append(StringCache[cacheKey]); } else { var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.Name.Equals(p.Name, StringComparison.OrdinalIgnoreCase))).ToArray(); if (!properties.Any()) { throw new ArgumentException("Can't update without [Key]"); } query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))} WHERE {string.Join(" AND ", KeySqlProperties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))}"); StringCache.TryAdd(cacheKey, query.GetSql()); } return(query); }
/// <inheritdoc /> public virtual SqlQuery GetUpdate(TEntity entity) { var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase))); if (HasUpdatedAt) { UpdatedAtProperty.SetValue(entity, DateTime.UtcNow); } var query = new SqlQuery(entity); query.SqlBuilder.Append("UPDATE " + TableName + " SET " + string.Join(", ", properties.Select(p => p.ColumnName + " = @" + p.PropertyName)) + " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => p.ColumnName + " = @" + p.PropertyName))); return(query); }
private SqlQuery GetSelect(Expression <Func <TEntity, bool> > predicate, bool firstOnly, int pageNo, int pageSize, bool includeLogicalDeleted, object from = null, object to = null, string columnName = "", bool betweenQuery = false, params Expression <Func <TEntity, object> >[] includes) { var sqlQuery = InitBuilderSelect(firstOnly); var joinsBuilder = AppendJoinToSelect(sqlQuery, includes); sqlQuery.SqlBuilder .Append(" FROM ") .Append(TableName) .Append(" "); if (includes.Any()) { sqlQuery.SqlBuilder.Append(joinsBuilder); } AppendWherePredicateQuery(sqlQuery, predicate, QueryType.Select, includeLogicalDeleted); if (betweenQuery) { sqlQuery.SqlBuilder .Append(predicate == null && !LogicalDelete ? "WHERE" : "AND") .Append(" ") .Append(TableName) .Append(".") .Append(columnName) .Append(" BETWEEN '") .Append(from) .Append("' AND '") .Append(to) .Append("'"); } if (firstOnly && (Config.SqlProvider == SqlProvider.MySQL || Config.SqlProvider == SqlProvider.PostgreSQL)) { sqlQuery.SqlBuilder.Append(" LIMIT 1"); } if (!firstOnly && pageNo > 0 && pageSize > 0) { int offset = (pageNo - 1) * pageSize; int fetch = pageSize; string keyProperties = string.Join(',', KeySqlProperties.Select(x => x.PropertyName).ToList()); sqlQuery.SqlBuilder.Append($" ORDER BY {keyProperties}"); if (Config.SqlProvider == SqlProvider.MSSQL) { sqlQuery.SqlBuilder.Append($" OFFSET {offset} ROWS FETCH NEXT {fetch} ROWS ONLY;"); } else if (Config.SqlProvider == SqlProvider.MySQL || Config.SqlProvider == SqlProvider.PostgreSQL) { sqlQuery.SqlBuilder.Append($" LIMIT {offset}, {fetch};"); } //Count Pages var countPagesQuery = new SqlQuery(); countPagesQuery.SqlBuilder.Append($" SELECT CAST((CEILING(CAST(COUNT(*) AS DECIMAL) / CAST({fetch} AS DECIMAL))) AS INT) "); countPagesQuery.SqlBuilder .Append(" FROM ") .Append(TableName) .Append(" "); if (includes.Any()) { countPagesQuery.SqlBuilder.Append(joinsBuilder); } AppendWherePredicateQuery(countPagesQuery, predicate, QueryType.Select, includeLogicalDeleted); if (betweenQuery) { countPagesQuery.SqlBuilder .Append(predicate == null && !LogicalDelete ? "WHERE" : "AND") .Append(" ") .Append(TableName) .Append(".") .Append(columnName) .Append(" BETWEEN '") .Append(from) .Append("' AND '") .Append(to) .Append("'"); } sqlQuery.SqlBuilder.Append(countPagesQuery.GetSql()); } LogSqlQuery(sqlQuery); return(sqlQuery); }
public virtual SqlQuery GetDeleteByIds(string ids) { var sqlQuery = new SqlQuery(); sqlQuery.SqlBuilder.Append("DELETE FROM " + TableName + " WHERE " + string.Join(" ", KeySqlProperties.Select(p => p.ColumnName + " IN (" + ids + ")"))); return(sqlQuery); }