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