Exemplo n.º 1
0
        /// <summary>
        /// Creates a SQL Statement for insert-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 inserted.</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 insert operation.</returns>
        public string CreateInsertAll(QueryBuilder queryBuilder,
                                      string tableName,
                                      IEnumerable <Field> fields = 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.");
            }

            // Ensure the primary is on the list if it is not an identity
            if (primaryField != null)
            {
                if (primaryField != identityField)
                {
                    var isPresent = fields.FirstOrDefault(f => f.Name.ToLower() == primaryField.Name.ToLower()) != null;
                    if (isPresent == false)
                    {
                        throw new InvalidOperationException("The non-identity primary field must be present during insert operation.");
                    }
                }
            }

            // Variables needed
            var databaseType     = (string)null;
            var insertableFields = fields
                                   .Where(f => f.Name.ToLower() != identityField?.Name.ToLower());

            // Check for the identity
            if (identityField != null)
            {
                var dbType = new ClientTypeToSqlDbTypeResolver().Resolve(identityField.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++)
            {
                queryBuilder.Insert()
                .Into()
                .TableNameFrom(tableName)
                .OpenParen()
                .FieldsFrom(insertableFields)
                .CloseParen()
                .Values()
                .OpenParen()
                .ParametersFrom(insertableFields, index)
                .CloseParen()
                .End();

                // Set the return field
                if (identityField != null)
                {
                    var returnValue = string.Concat(identityField.UnquotedName.AsParameter(index), " = ",
                                                    string.IsNullOrEmpty(databaseType) ?
                                                    "SCOPE_IDENTITY()" :
                                                    "CONVERT(", databaseType, ", SCOPE_IDENTITY())");
                    queryBuilder
                    .Set()
                    .WriteText(returnValue)
                    .End();
                }
            }

            // Return the query
            return(queryBuilder.GetString());
        }
Exemplo 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());
        }
Exemplo n.º 3
0
        /// <summary>
        /// Creates a SQL Statement for insert 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 inserted.</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 insert operation.</returns>
        public string CreateInsert(QueryBuilder queryBuilder,
                                   string tableName,
                                   IEnumerable <Field> fields = null,
                                   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 insertable fields must not be null for '{tableName}'.");
            }

            // Ensure the primary is on the list if it is not an identity
            if (primaryField != null)
            {
                if (primaryField != identityField)
                {
                    var isPresent = fields.FirstOrDefault(f => f.Name.ToLower() == primaryField.Name.ToLower()) != null;
                    if (isPresent == false)
                    {
                        throw new InvalidOperationException("The non-identity primary field must be present during insert operation.");
                    }
                }
            }

            // Variables needed
            var databaseType     = "BIGINT";
            var insertableFields = fields
                                   .Where(f => f.Name.ToLower() != identityField?.Name.ToLower());

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

            // Build the query
            (queryBuilder ?? new QueryBuilder())
            .Clear()
            .Insert()
            .Into()
            .TableNameFrom(tableName)
            .OpenParen()
            .FieldsFrom(insertableFields)
            .CloseParen()
            .Values()
            .OpenParen()
            .ParametersFrom(insertableFields)
            .CloseParen()
            .End();

            // Set the return value
            var result = identityField != null?
                         string.Concat("CONVERT(", databaseType, ", SCOPE_IDENTITY())") :
                             primaryField != null?primaryField.Name.AsParameter() : "NULL";

            queryBuilder
            .Select()
            .WriteText(result)
            .As("[Result]")
            .End();

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