Пример #1
0
        /// <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);
        }
        /// <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);
        }
Пример #3
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, Dictionary <string, object> setPropertyDict)
        {
            var propNameExceptItems = setPropertyDict.Keys.Except(SqlProperties.Select(p => p.PropertyName));

            if (propNameExceptItems.Any())
            {
                string keys = string.Join(",", propNameExceptItems.Select(p => p));
                throw new ArgumentException(string.Concat(nameof(setPropertyDict), "content error detail:", $" [{keys}] not equal entity column name"));
            }
            var properties = SqlProperties
                             .Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate &&
                                    setPropertyDict.Any(k => k.Key.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)))
                             .ToArray();

            var query = new SqlQuery();

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" ");
            query.SqlBuilder.Append("SET ");
            query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
            query.SqlBuilder.Append(" ");
            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            var parameters = (Dictionary <string, object>)query.Param;

            foreach (var metadata in properties)
            {
                var value = setPropertyDict.FirstOrDefault(p => p.Key.Equals(metadata.PropertyName, StringComparison.OrdinalIgnoreCase)).Value;
                parameters.Add($"{typeof(TEntity).Name}{metadata.PropertyName}", value);
            }
            return(query);
        }
Пример #4
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate);

            if (HasUpdatedAt)
            {
                UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
            }

            var query = new SqlQuery(entity);

            query.SqlBuilder.Append(string.Format("UPDATE {0} SET {1} ", TableName,
                                                  string.Join(", ", properties.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName)))));
            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            var parameters = new Dictionary <string, object>();
            var entityType = entity.GetType();

            foreach (var property in properties)
            {
                parameters.Add(property.PropertyName, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
            }

            var whereParam = query.Param as Dictionary <string, object>;

            if (whereParam != null)
            {
                parameters.AddRange(whereParam);
            }

            query.SetParam(parameters);

            return(query);
        }
Пример #5
0
        /// <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);
        }
Пример #6
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, object setPropertyObj)
        {
            var setProperties = setPropertyObj.GetType().GetProperties();
            var properties    = SqlProperties
                                .Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate &&
                                       setProperties.Any(k => k.Name.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)))
                                .ToArray();

            var query = new SqlQuery();

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" ");
            query.SqlBuilder.Append("SET ");
            query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
            query.SqlBuilder.Append(" ");
            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            var parameters = (Dictionary <string, object>)query.Param;

            foreach (var metadata in properties)
            {
                var setProp = setProperties.FirstOrDefault(p => p.Name.Equals(metadata.PropertyName, StringComparison.OrdinalIgnoreCase));
                if (setProp == null)
                {
                    continue;
                }
                parameters.Add($"{typeof(TEntity).Name}{metadata.PropertyName}", setProp.GetValue(setPropertyObj));
            }
            return(query);
        }
Пример #7
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(TEntity entity)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).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(string.Format("UPDATE {0} SET {1} WHERE {2}", TableName,
                                                  string.Join(", ", properties.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))),
                                                  string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
                                                              .Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName)))
                                                  ));

            return(query);
        }
Пример #8
0
        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);
        }
Пример #9
0
        /// <inheritdoc />
        public virtual SqlQuery GetInsert(TEntity entity)
        {
            var properties =
                (IsIdentity
                    ? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase))
                    : SqlProperties).ToList();

            var keyProperty = KeySqlProperties.Where(s => s.PropertyInfo.PropertyType == typeof(Guid) || s.PropertyInfo.PropertyType == typeof(Guid?)).FirstOrDefault();

            #region If There Is No Identity Property
            if (!IsIdentity && keyProperty != null)
            {
                var oldKey = keyProperty.PropertyInfo.GetValue(entity, null);
                if (oldKey == null || (Guid?)oldKey == default(Guid?))
                {
                    keyProperty.PropertyInfo.SetValue(entity, Guid.NewGuid());
                }
            }
            #endregion

            if (HasCreatedAt)
            {
                CreatedAtProperty.SetValue(entity, DateTime.UtcNow);
            }

            if (HasUpdatedAt)
            {
                UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
            }

            var query = new SqlQuery(TableName, entity, QueryType.Insert);

            query.SqlBuilder.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})", TableName, string.Join(", ", properties.Select(p => p.ColumnName)),
                                          string.Join(", ", properties.Select(p => "@" + p.PropertyName)));   // values

            if (IsIdentity)
            {
                switch (Config.SqlProvider)
                {
                case SqlProvider.MSSQL:
                    query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
                    break;

                case SqlProvider.MySQL:
                    query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
                    break;

                case SqlProvider.PostgreSQL:
                    query.SqlBuilder.Append(" RETURNING " + IdentitySqlProperty.ColumnName);
                    break;

                default:
                    throw new ArgumentOutOfRangeException();
                }
            }

            return(query);
        }
Пример #10
0
        /// <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);
        }
Пример #11
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(TEntity entity)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) &&
                                                 !p.IgnoreUpdate && !p.RowVersionProp).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.Select(p => p.ColumnName + " = @" + p.PropertyName))
                                    + " WHERE " + string.Join(" AND ",
                                                              KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
                                                              .Select(p => p.ColumnName + " = @" + p.PropertyName)));

            if (HasRowVersion)
            {
                query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
            }

            switch (Config.SqlProvider)
            {
            case SqlProvider.MSSQL:
                //query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
                query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ",
                                                                                                 KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
                                                                                                 .Select(p => p.ColumnName + " = @" + p.PropertyName)));
                break;

            case SqlProvider.MySQL:
                //query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
                query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ",
                                                                                                 KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
                                                                                                 .Select(p => p.ColumnName + " = @" + p.PropertyName)));
                break;

            case SqlProvider.PostgreSQL:
                throw new ArgumentOutOfRangeException();

            default:
                throw new ArgumentOutOfRangeException();
            }

            LogSqlQuery(query);
            return(query);
        }
Пример #12
0
        /// <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.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();

            var query = new SqlQuery();

            var parameters = new Dictionary <string, object>();

            for (var i = 0; i < entitiesArray.Length; i++)
            {
                if (HasUpdatedAt)
                {
                    UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
                }

                if (i > 0)
                {
                    query.SqlBuilder.Append("; ");
                }

                query.SqlBuilder.Append(string.Format("UPDATE {0} SET {1} WHERE {2}", TableName,
                                                      string.Join(", ", properties.Select(p => string.Format("{0} = @{1}{2}", p.ColumnName, p.PropertyName, i))),
                                                      string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
                                                                  .Select(p => string.Format("{0} = @{1}{2}", p.ColumnName, p.PropertyName, i)))
                                                      ));

                // ReSharper disable PossibleNullReferenceException
                foreach (var property in properties)
                {
                    parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
                }

                foreach (var property in KeySqlProperties.Where(p => !p.IgnoreUpdate))
                {
                    parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
                }

                // ReSharper restore PossibleNullReferenceException
            }

            query.SetParam(parameters);

            return(query);
        }
Пример #13
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity, params Expression <Func <TEntity, object> >[] includes)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();

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

            var query = new SqlQuery();

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" ");

            if (includes?.Length > 0)
            {
                var joinsBuilder = AppendJoinToUpdate(entity, query, includes);
                query.SqlBuilder.Append("SET ");
                query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
                query.SqlBuilder.Append(joinsBuilder);
            }
            else
            {
                query.SqlBuilder.Append("SET ");
                query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
            }

            query.SqlBuilder
            .Append(" ");

            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            var parameters = (Dictionary <string, object>)query.Param;

            foreach (var metadata in properties)
            {
                parameters.Add($"{entity.GetType().Name}{metadata.PropertyName}", entity.GetType().GetProperty(metadata.PropertyName).GetValue(entity, null));
            }

            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);
        }
Пример #15
0
        /// <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);
        }
Пример #16
0
        /// <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);
        }
Пример #17
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(TEntity entity)
        {
            var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate);

            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.Where(p => !p.IgnoreUpdate).Select(p => p.ColumnName + " = @" + p.PropertyName)));

            return(query);
        }
Пример #18
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, object entity)
        {
            var objectProperties = entity.GetType().GetProperties().ToList();

            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.Ignore)
                             .Where(s => objectProperties.Any(x => x.Name == s.PropertyName) || (HasUpdatedAt && UpdatedAtProperty.Name == s.PropertyName))
                             .ToArray();

            var query = new SqlQuery(TableName, entity, QueryType.Update);

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" SET ");

            query.SqlBuilder.Append(string.Join(", ", properties
                                                .Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))));

            query.SqlBuilder
            .Append(" ");

            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            var parameters = new Dictionary <string, object>();
            var entityType = entity.GetType();

            foreach (var property in properties)
            {
                if (property.PropertyName == UpdatedAtProperty.Name)
                {
                    parameters.Add(property.PropertyName, $"{DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss")}");
                }
                else
                {
                    parameters.Add(property.PropertyName, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
                }
            }

            if (query.Param is Dictionary <string, object> whereParam)
            {
                parameters.AddRange(whereParam);
            }

            query.SetParam(parameters);

            return(query);
        }
Пример #19
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity)
        {
            var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate);

            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 + $" = {SqlParamPrefix}" + p.PropertyName)) + " ");
            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            return(query);
        }
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();

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

            var query = new SqlQuery(entity);

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" SET ");

            query.SqlBuilder.Append(string.Join(", ", properties
                                                .Select(p => $"{p.ColumnName} = @{p.PropertyName}")));

            query.SqlBuilder
            .Append(" ");

            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            var entityType = entity.GetType();
            var parameters = properties.ToDictionary(property => property.PropertyName, property => entityType.GetProperty(property.PropertyName)?.GetValue(entity, null));

            if (query.Param is Dictionary <string, object> whereParam)
            {
                parameters.AddRange(whereParam);
            }

            query.SetParam(parameters);

            return(query);
        }
        /// <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);
        }
        /// <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.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();

            var query = new SqlQuery();

            var parameters = new Dictionary <string, object>();

            for (var i = 0; i < entitiesArray.Length; i++)
            {
                if (HasUpdatedAt)
                {
                    UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
                }

                query.SqlBuilder.Append(" UPDATE " + TableName + " SET " + string.Join(", ", properties.Select(p => p.ColumnName + " = @" + p.PropertyName + i))
                                        + " WHERE " + string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate).Select(p => p.ColumnName + " = @" + p.PropertyName + i)));

                foreach (var property in properties)
                {
                    parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
                }

                foreach (var property in KeySqlProperties.Where(p => !p.IgnoreUpdate))
                {
                    parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
                }
            }

            query.SetParam(parameters);

            return(query);
        }
        /// <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);
        }
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(TEntity entity)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();

            if (!properties.Any())
            {
                throw new ArgumentException("Can't update without [Key]");
            }

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

            var query = new SqlQuery(entity);

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" SET ");

            query.SqlBuilder.Append(string.Join(", ", properties
                                                .Select(p => $"{p.ColumnName} = @{p.PropertyName}")));

            query.SqlBuilder.Append(" WHERE ");

            query.SqlBuilder.Append(string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
                                                .Select(p => $"{p.ColumnName} = @{p.PropertyName}")));

            return(query);
        }
Пример #25
0
        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);
        }
Пример #26
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity)
        {
            var query = new SqlQuery(entity);

            var cacheKey = _sqlCacheKey + "GetUpdateByExpression";

            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)));
                query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))} ");

                StringCache.TryAdd(cacheKey, query.GetSql());
            }

            AppendWherePredicateQuery(query, predicate);

            return(query);
        }
Пример #27
0
        /// <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);
        }
Пример #28
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity, object userId)
        {
            var query = new SqlQuery(entity);

            var cacheKey = _sqlCacheKey + "GetUpdateByExpression";

            if (SqlStringCache.ContainsKey(cacheKey))
            {
                query.SqlBuilder.Append(SqlStringCache[cacheKey]);
            }
            else
            {
                var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.Name.Equals(p.Name, StringComparison.OrdinalIgnoreCase)));
                query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))} ");

                SqlStringCache.TryAdd(cacheKey, query.GetSql());
            }

            AppendWherePredicateQuery(query, predicate);

            //处理公共字段
            var commonValues = Config.UpdateCommonProperty(userId);

            foreach (var common in commonValues)
            {
                if (query.Param.Keys.Contains(common.Key))
                {
                    query.Param[common.Key] = common.Value;
                }
                else
                {
                    query.Param.Add(common.Key, common.Value);
                }
            }

            return(query);
        }
Пример #29
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(TEntity entity, params Expression <Func <TEntity, object> >[] includes)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();

            if (!properties.Any())
            {
                throw new ArgumentException("Can't update without [Key]");
            }

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

            var query = new SqlQuery();

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" ");

            if (includes?.Length > 0)
            {
                var joinsBuilder = AppendJoinToUpdate(entity, query, includes);
                query.SqlBuilder.Append("SET ");
                query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
                query.SqlBuilder.Append(joinsBuilder);
            }
            else
            {
                query.SqlBuilder.Append("SET ");
                query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
            }

            query.SqlBuilder.Append(" WHERE ");

            query.SqlBuilder.Append(string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
                                                .Select(p => $"{TableName}.{p.ColumnName} = @{entity.GetType().Name}{p.PropertyName}")));

            if (query.Param == null || !(query.Param is Dictionary <string, object> parameters))
            {
                parameters = new Dictionary <string, object>();
            }

            foreach (var metadata in properties.Concat(KeySqlProperties))
            {
                parameters.Add($"{entity.GetType().Name}{metadata.PropertyName}", entity.GetType().GetProperty(metadata.PropertyName).GetValue(entity, null));
            }

            query.SetParam(parameters);

            return(query);
        }
Пример #30
0
        /// <inheritdoc />
        public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity, Expression <Func <TEntity, object> > propertiesToUpdate)
        {
            var properties = SqlProperties.Where(p =>
                                                 !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate && !p.RowVersionProp).ToArray();

            if (propertiesToUpdate != null)
            {
                string[] updateProperties = ExpressionHelper.GetMemberName(propertiesToUpdate);
                if (HasMandatoryProp)
                {
                    properties = properties.Where(x =>
                                                  updateProperties.Contains(x.PropertyName) ||
                                                  MandatoryUpdateProperty.Select(y => y.Name).Contains(x.PropertyName))
                                 .ToArray();
                }
                else
                {
                    properties = properties.Where(x =>
                                                  updateProperties.Contains(x.PropertyName))
                                 .ToArray();
                }
            }

            if (HasUpdatedAt)
            {
                UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
            }

            var query = new SqlQuery(entity);

            query.SqlBuilder
            .Append("UPDATE ")
            .Append(TableName)
            .Append(" SET ");

            query.SqlBuilder.Append(string.Join(", ", properties
                                                .Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))));

            query.SqlBuilder
            .Append(" ");

            AppendWherePredicateQuery(query, predicate, QueryType.Update);

            if (HasRowVersion)
            {
                query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
            }

            switch (Config.SqlProvider)
            {
            case SqlProvider.MSSQL:
                query.SqlBuilder.Append("; SELECT * FROM " + TableName + " ");
                AppendWherePredicateQuery(query, predicate, QueryType.Update);
                if (HasRowVersion)
                {
                    query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
                }
                break;

            case SqlProvider.MySQL:
                query.SqlBuilder.Append("; SELECT * FROM " + TableName + " ");
                AppendWherePredicateQuery(query, predicate, QueryType.Update);
                if (HasRowVersion)
                {
                    query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
                }
                break;

            case SqlProvider.PostgreSQL:
                query.SqlBuilder.Append("; SELECT * FROM " + TableName + " ");
                AppendWherePredicateQuery(query, predicate, QueryType.Update);
                if (HasRowVersion)
                {
                    query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
                }
                break;

            default:
                throw new ArgumentOutOfRangeException();
            }

            var parameters = new Dictionary <string, object>();
            var entityType = entity.GetType();

            foreach (var property in properties)
            {
                parameters.Add(property.PropertyName, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
            }

            if (query.Param is Dictionary <string, object> whereParam)
            {
                parameters.AddRange(whereParam);
            }

            query.SetParam(parameters);

            LogSqlQuery(query);
            return(query);
        }