/// <summary> /// /// </summary> /// <param name="tableName"></param> /// <param name="tempTableName"></param> /// <param name="fields"></param> /// <param name="qualifiers"></param> /// <param name="primaryField"></param> /// <param name="identityField"></param> /// <param name="hints"></param> /// <param name="dbSetting"></param> /// <param name="isReturnIdentity"></param> /// <returns></returns> private static string GetBulkMergeSqlText(string tableName, string tempTableName, IEnumerable <Field> fields, IEnumerable <Field> qualifiers, Field primaryField, Field identityField, string hints, IDbSetting dbSetting, bool isReturnIdentity) { // Validate the presence if (fields?.Any() != true) { throw new MissingFieldException("There are no field(s) defined."); } if (qualifiers?.Any() != true) { throw new MissingFieldException("There is no qualifier field(s) defined."); } // Variables needed var builder = new QueryBuilder(); // Insertable fields var insertableFields = fields .Where(field => string.Equals(field.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase) == false); // Updatable fields var updateableFields = fields .Where(field => field != identityField && field != primaryField) .Where(field => qualifiers.Any( q => string.Equals(q.Name, field.Name, StringComparison.OrdinalIgnoreCase)) == false); // Compose the statement builder.Clear() // MERGE T USING S .Merge() .TableNameFrom(tableName, dbSetting) .HintsFrom(hints) .As("T") .Using() .OpenParen() .Select() .Top() .WriteText("100 PERCENT") //.FieldsFrom(fields, dbSetting) .WriteText("*") // Including the [__RepoDb_OrderColumn] .From() .TableNameFrom(tempTableName, dbSetting); // Return Identity if (isReturnIdentity && identityField != null) { builder .OrderBy() .WriteText("[__RepoDb_OrderColumn]") .Ascending(); } // Continuation builder .CloseParen() .As("S") // QUALIFIERS .On() .OpenParen() .WriteText(qualifiers .Select( field => field.AsJoinQualifier("S", "T", dbSetting)) .Join(" AND ")) .CloseParen() // WHEN NOT MATCHED THEN INSERT VALUES .When() .Not() .Matched() .Then() .Insert() .OpenParen() .FieldsFrom(insertableFields, dbSetting) .CloseParen() .Values() .OpenParen() .AsAliasFieldsFrom(insertableFields, "S", dbSetting) .CloseParen() // WHEN MATCHED THEN UPDATE SET .When() .Matched() .Then() .Update() .Set() .FieldsAndAliasFieldsFrom(updateableFields, "T", "S", dbSetting); // Set the output if (isReturnIdentity == true && identityField != null) { builder .WriteText(string.Concat("OUTPUT INSERTED.", identityField.Name.AsField(dbSetting))) .As("[Result],") .WriteText("S.[__RepoDb_OrderColumn]") .As("[OrderColumn]"); } // End the builder builder.End(); // Return the sql return(builder.ToString()); }
/// <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()); }