Esempio n. 1
0
        /// <summary>
        /// Creates a SQL Statement for update-all operation.
        /// </summary>
        /// <param name="queryBuilder">The query builder to be used.</param>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="fields">The list of fields to be updated.</param>
        /// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
        /// <param name="batchSize">The batch size of the operation.</param>
        /// <param name="primaryField">The primary field from the database.</param>
        /// <param name="identityField">The identity field from the database.</param>
        /// <returns>A sql statement for update-all operation.</returns>
        public string CreateUpdateAll(QueryBuilder queryBuilder,
                                      string tableName,
                                      IEnumerable <Field> fields,
                                      IEnumerable <Field> qualifiers,
                                      int batchSize         = Constant.DefaultBatchOperationSize,
                                      DbField primaryField  = null,
                                      DbField identityField = null)
        {
            // Ensure with guards
            GuardTableName(tableName);
            GuardPrimary(primaryField);
            GuardIdentity(identityField);

            // Ensure the fields
            if (fields?.Any() != true)
            {
                throw new InvalidOperationException($"The list of fields cannot be null or empty.");
            }

            // Check the qualifiers
            if (qualifiers?.Any() == true)
            {
                // Check if the qualifiers are present in the given fields
                var unmatchesQualifiers = qualifiers?.Where(field =>
                                                            fields?.FirstOrDefault(f =>
                                                                                   field.UnquotedName.ToLower() == f.UnquotedName.ToLower()) == null);

                // Throw an error we found any unmatches
                if (unmatchesQualifiers?.Any() == true)
                {
                    throw new InvalidQualifierFieldsException($"The qualifiers '{unmatchesQualifiers.Select(field => field.Name).Join(", ")}' are not " +
                                                              $"present at the given fields '{fields.Select(field => field.Name).Join(", ")}'.");
                }
            }
            else
            {
                if (primaryField != null)
                {
                    // Make sure that primary is present in the list of fields before qualifying to become a qualifier
                    var isPresent = fields?.FirstOrDefault(f => f.UnquotedName.ToLower() == primaryField.UnquotedName.ToLower()) != null;

                    // Throw if not present
                    if (isPresent == false)
                    {
                        throw new InvalidQualifierFieldsException($"There are no qualifier field objects found for '{tableName}'. Ensure that the " +
                                                                  $"primary field is present at the given fields '{fields.Select(field => field.Name).Join(", ")}'.");
                    }

                    // The primary is present, use it as a default if there are no qualifiers given
                    qualifiers = primaryField.AsField().AsEnumerable();
                }
                else
                {
                    // Throw exception, qualifiers are not defined
                    throw new NullReferenceException($"There are no qualifier field objects found for '{tableName}'.");
                }
            }

            // Gets the updatable fields
            fields = fields
                     .Where(f => f.UnquotedName.ToLower() != primaryField?.UnquotedName.ToLower() &&
                            f.UnquotedName.ToLower() != identityField?.UnquotedName.ToLower() &&
                            qualifiers.FirstOrDefault(q => q.UnquotedName.ToLower() == f.UnquotedName.ToLower()) == null);

            // Check if there are updatable fields
            if (fields?.Any() != true)
            {
                throw new InvalidOperationException("The list of updatable fields cannot be null or empty.");
            }

            // Build the query
            (queryBuilder ?? new QueryBuilder())
            .Clear();

            // Iterate the indexes
            for (var index = 0; index < batchSize; index++)
            {
                queryBuilder
                .Update()
                .TableNameFrom(tableName)
                .Set()
                .FieldsAndParametersFrom(fields, index)
                .WhereFrom(qualifiers, index)
                .End();
            }

            // Return the query
            return(queryBuilder.GetString());
        }
Esempio n. 2
0
        /// <summary>
        /// Creates a SQL Statement for merge-all operation.
        /// </summary>
        /// <param name="queryBuilder">The query builder to be used.</param>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="fields">The list of fields to be merged.</param>
        /// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
        /// <param name="batchSize">The batch size of the operation.</param>
        /// <param name="primaryField">The primary field from the database.</param>
        /// <param name="identityField">The identity field from the database.</param>
        /// <returns>A sql statement for merge operation.</returns>
        public string CreateMergeAll(QueryBuilder queryBuilder,
                                     string tableName,
                                     IEnumerable <Field> fields,
                                     IEnumerable <Field> qualifiers = null,
                                     int batchSize         = Constant.DefaultBatchOperationSize,
                                     DbField primaryField  = null,
                                     DbField identityField = null)
        {
            // Ensure with guards
            GuardTableName(tableName);
            GuardPrimary(primaryField);
            GuardIdentity(identityField);

            // Verify the fields
            if (fields?.Any() != true)
            {
                throw new NullReferenceException($"The list of fields cannot be null or empty.");
            }

            // Check the qualifiers
            if (qualifiers?.Any() == true)
            {
                // Check if the qualifiers are present in the given fields
                var unmatchesQualifiers = qualifiers.Where(field =>
                                                           fields.FirstOrDefault(f =>
                                                                                 field.UnquotedName.ToLower() == f.UnquotedName.ToLower()) == null);

                // Throw an error we found any unmatches
                if (unmatchesQualifiers?.Any() == true)
                {
                    throw new InvalidQualifierFieldsException($"The qualifiers '{unmatchesQualifiers.Select(field => field.Name).Join(", ")}' are not " +
                                                              $"present at the given fields '{fields.Select(field => field.Name).Join(", ")}'.");
                }
            }
            else
            {
                if (primaryField != null)
                {
                    // Make sure that primary is present in the list of fields before qualifying to become a qualifier
                    var isPresent = fields?.FirstOrDefault(f => f.Name.ToLower() == primaryField.Name.ToLower()) != null;

                    // Throw if not present
                    if (isPresent == false)
                    {
                        throw new InvalidQualifierFieldsException($"There are no qualifier field objects found for '{tableName}'. Ensure that the " +
                                                                  $"primary field is present at the given fields '{fields.Select(field => field.Name).Join(", ")}'.");
                    }

                    // The primary is present, use it as a default if there are no qualifiers given
                    qualifiers = primaryField.AsField().AsEnumerable();
                }
                else
                {
                    // Throw exception, qualifiers are not defined
                    throw new NullReferenceException($"There are no qualifier field objects found for '{tableName}'.");
                }
            }

            // Get the insertable and updateable fields
            var insertableFields = fields
                                   .Where(field => field.Name.ToLower() != identityField?.Name.ToLower());
            var updateableFields = fields
                                   .Where(field => field.Name.ToLower() != primaryField?.Name.ToLower() && field.Name.ToLower() != identityField?.Name.ToLower());

            // Variables needed
            var databaseType = (string)null;

            // Check for the identity
            if (identityField != null)
            {
                var dbType = new ClientTypeToSqlDbTypeResolver().Resolve(identityField.Type);
                if (dbType != null)
                {
                    databaseType = new SqlDbTypeToStringNameResolver().Resolve(dbType.Value);
                }
            }
            else if (primaryField != null)
            {
                var dbType = new ClientTypeToSqlDbTypeResolver().Resolve(primaryField.Type);
                if (dbType != null)
                {
                    databaseType = new SqlDbTypeToStringNameResolver().Resolve(dbType.Value);
                }
            }

            // Build the query
            (queryBuilder ?? new QueryBuilder())
            .Clear();

            // Iterate the indexes
            for (var index = 0; index < batchSize; index++)
            {
                // MERGE T USING S
                queryBuilder.Merge()
                .TableNameFrom(tableName)
                .As("T")
                .Using()
                .OpenParen()
                .Select()
                .ParametersAsFieldsFrom(fields, index)
                .CloseParen()
                .As("S")
                // QUALIFIERS
                .On()
                .OpenParen()
                .WriteText(qualifiers?
                           .Select(
                               field => field.AsJoinQualifier("S", "T"))
                           .Join(" AND "))
                .CloseParen()
                // WHEN NOT MATCHED THEN INSERT VALUES
                .When()
                .Not()
                .Matched()
                .Then()
                .Insert()
                .OpenParen()
                .FieldsFrom(insertableFields)
                .CloseParen()
                .Values()
                .OpenParen()
                .AsAliasFieldsFrom(insertableFields, "S")
                .CloseParen()
                // WHEN MATCHED THEN UPDATE SET
                .When()
                .Matched()
                .Then()
                .Update()
                .Set()
                .FieldsAndAliasFieldsFrom(updateableFields, "S");

                // Set the output
                var outputField = identityField ?? primaryField;
                if (outputField != null)
                {
                    queryBuilder
                    .WriteText(string.Concat("OUTPUT INSERTED.", outputField.Name))
                    .As("[Result]");
                }

                // End the builder
                queryBuilder.End();
            }

            // Return the query
            return(queryBuilder.GetString());
        }