private static void ValidateUpsert(UpsertCommand command, AddValidation e)
{
if (command.Fields?.Count > 0)
{
var fieldIndex = 0;
var fieldPrefix = string.Empty;
foreach (var field in command.Fields)
{
fieldIndex++;
fieldPrefix = $"Fields[{fieldIndex}]";
ValidateRootField(field, command, fieldPrefix, e);
}
foreach (var fieldName in command.Fields.Duplicates(x => x.Name))
{
if (fieldName.IsPropertyName())
{
e($"Field '{fieldName}' has been added twice.", nameof(command.Fields));
}
}
}
ValidateFieldNames(command, command.FieldsInLists, nameof(command.FieldsInLists), e);
ValidateFieldNames(command, command.FieldsInReferences, nameof(command.FieldsInReferences), e);
}
private Uri GetRequestUri <T>(UpsertCommand <T> command)
{
string requestUri = this.GetMessageBase <T>() + "/";
List <KeyValuePair <string, string> > queryStringParameters = new List <KeyValuePair <string, string> >();
queryStringParameters.AddRange(this.GetAdditionalParameters(command.AdditionalParameters));
queryStringParameters.ForEach(x => { requestUri = QueryHelpers.AddQueryString(requestUri, x.Key, x.Value); });
return(new Uri(requestUri, UriKind.Relative));
}
private static void ValidateUpsert(UpsertCommand command, AddValidation e)
{
if (command.Fields?.Count > 0)
{
var fieldIndex = 0;
var fieldPrefix = string.Empty;
foreach (var field in command.Fields)
{
fieldIndex++;
fieldPrefix = $"Fields[{fieldIndex}]";
if (!field.Partitioning.IsValidPartitioning())
{
e(Not.Valid("Partitioning"), $"{fieldPrefix}.{nameof(field.Partitioning)}");
}
ValidateField(field, fieldPrefix, e);
if (field.Nested?.Count > 0)
{
if (field.Properties is ArrayFieldProperties)
{
var nestedIndex = 0;
var nestedPrefix = string.Empty;
foreach (var nestedField in field.Nested)
{
nestedIndex++;
nestedPrefix = $"{fieldPrefix}.Nested[{nestedIndex}]";
if (nestedField.Properties is ArrayFieldProperties)
{
e("Nested field cannot be array fields.", $"{nestedPrefix}.{nameof(nestedField.Properties)}");
}
ValidateField(nestedField, nestedPrefix, e);
}
}
else if (field.Nested.Count > 0)
{
e("Only array fields can have nested fields.", $"{fieldPrefix}.{nameof(field.Partitioning)}");
}
if (field.Nested.Select(x => x.Name).Distinct().Count() != field.Nested.Count)
{
e("Fields cannot have duplicate names.", $"{fieldPrefix}.Nested");
}
}
}
if (command.Fields.Select(x => x.Name).Distinct().Count() != command.Fields.Count)
{
e("Fields cannot have duplicate names.", nameof(command.Fields));
}
}
}
private static void ValidateRootField(UpsertSchemaField field, UpsertCommand command, string prefix, AddValidation e)
{
if (field == null)
{
e(Not.Defined("Field"), prefix);
}
else
{
if (!field.Partitioning.IsValidPartitioning())
{
e(Not.Valid("Partitioning"), $"{prefix}.{nameof(field.Partitioning)}");
}
ValidateField(field, prefix, e);
if (field.Nested?.Count > 0)
{
if (field.Properties is ArrayFieldProperties)
{
var nestedIndex = 0;
var nestedPrefix = string.Empty;
foreach (var nestedField in field.Nested)
{
nestedIndex++;
nestedPrefix = $"{prefix}.Nested[{nestedIndex}]";
ValidateNestedField(nestedField, field.Nested, nestedPrefix, e);
}
}
else if (field.Nested.Count > 0)
{
e("Only array fields can have nested fields.", $"{prefix}.{nameof(field.Partitioning)}");
}
foreach (var fieldName in field.Nested.Duplicates(x => x.Name))
{
if (fieldName.IsPropertyName())
{
e($"Field '{fieldName}' has been added twice.", $"{prefix}.Nested");
}
}
}
}
}
private static void ValidateUpsert(UpsertCommand command, AddValidation e)
{
if (command.Fields?.Count > 0)
{
var fieldIndex = 0;
var fieldPrefix = string.Empty;
foreach (var field in command.Fields)
{
fieldIndex++;
fieldPrefix = $"Fields[{fieldIndex}]";
ValidateRootField(field, fieldPrefix, e);
}
if (command.Fields.Select(x => x?.Name).Distinct().Count() != command.Fields.Count)
{
e("Fields cannot have duplicate names.", nameof(command.Fields));
}
}
}
private static void ValidateFieldNames(UpsertCommand command, FieldNames?fields, string path, AddValidation e)
{
if (fields != null)
{
var fieldIndex = 0;
var fieldPrefix = string.Empty;
foreach (var fieldName in fields)
{
fieldIndex++;
fieldPrefix = $"{path}[{fieldIndex}]";
var field = command?.Fields?.Find(x => x.Name == fieldName);
if (string.IsNullOrWhiteSpace(fieldName))
{
e(Not.Defined("Field"), fieldPrefix);
}
else if (field == null)
{
e($"Field is not part of the schema.", fieldPrefix);
}
else if (field?.Properties.IsForApi() != true)
{
e($"Field cannot be an UI field.", fieldPrefix);
}
}
foreach (var duplicate in fields.Duplicates())
{
if (!string.IsNullOrWhiteSpace(duplicate))
{
e($"Field '{duplicate}' has been added twice.", path);
}
}
}
}
public DateTimeFieldBuilder(UpsertSchemaField field, UpsertCommand schema)
: base(field, schema)
{
}
public StringFieldBuilder(UpsertSchemaField field, UpsertCommand schema)
: base(field, schema)
{
}
private HttpContent GetHttpContent <T>(UpsertCommand <T> command)
{
return(new StringContent(this.serializerService.Serialize(command.Entity)));
}
public HttpRequestMessage GetRequestMessage <T>(UpsertCommand <T> command)
{
return(this.GetRequestMessage <T>(this.GetRequestUri <T>(command), this.GetHttpContent <T>(command), HttpMethod));
}
/***************************************************/
/**** Execute Methods ****/
/***************************************************/
public Output <List <object>, bool> ExecuteCommand(UpsertCommand upsert, ActionConfig actionConfig = null)
{
Output <List <object>, bool> result = new Output <List <object>, bool> {
Item1 = new List <object>(), Item2 = false
};
if (upsert == null)
{
return(result);
}
List <Type> objectTypes = upsert.ObjectsToUpsert.Select(x => x.GetType()).Distinct().ToList();
if (objectTypes.Count != 1)
{
string message = "The SQL adapter only allows to upsert objects of a single type to a table."
+ "\nRight now you are providing objects of the following types: "
+ objectTypes.Select(x => x.ToString()).Aggregate((a, b) => a + ", " + b);
Engine.Base.Compute.RecordError(message);
return(result);
}
List <object> updateObjects = new List <object>();
List <object> insertObjects = new List <object>();
var primaryKeyValue = System.Convert.ChangeType(upsert.DefaultPrimaryKeyValue, upsert.PrimaryKeyType);
foreach (object o in upsert.ObjectsToUpsert)
{
var objValue = o.PropertyValue(upsert.PrimaryKey);
if (objValue == null)
{
insertObjects.Add(o);
}
else
{
var propValue = System.Convert.ChangeType(o.PropertyValue(upsert.PrimaryKey), upsert.PrimaryKeyType);
if (propValue == null || propValue.Equals(primaryKeyValue))
{
insertObjects.Add(o);
}
else
{
updateObjects.Add(o);
}
}
}
try
{
using (SqlConnection connection = new SqlConnection(m_ConnectionString))
{
connection.Open();
result.Item1.AddRange(InsertObjects(connection, upsert.Table, insertObjects));
connection.Close();
}
}
catch (Exception ex)
{
BH.Engine.Base.Compute.RecordError($"Error in inserting {insertObjects.Count} items. Error was {ex.ToString()}");
}
try
{
using (SqlConnection connection = new SqlConnection(m_ConnectionString))
{
connection.Open();
foreach (object o in updateObjects)
{
Dictionary <string, object> changes = Convert.ToDictionary(o);
if (changes == null)
{
continue;
}
string where = upsert.PrimaryKey + "=" + changes[upsert.PrimaryKey].ToString();
changes.Remove(upsert.PrimaryKey); //Don't update the primary key
string changesToSet = changes.Select(x =>
{
string val = x.Value.ToString();
if (!x.Value.GetType().IsPrimitive)
{
val = $"'{val}'";
}
else if (x.Value is bool)
{
val = System.Convert.ToInt32(x.Value).ToString();
}
return($"{x.Key} = {val}");
}).Aggregate((a, b) => a + ", " + b);
string commandString = $"UPDATE {upsert.Table} SET {changesToSet} WHERE {where}";
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = commandString;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
BH.Engine.Base.Compute.RecordError($"Error in updating item where {where}. Exception was {ex.ToString()}");
}
}
connection.Close();
}
}
catch (Exception ex)
{
BH.Engine.Base.Compute.RecordError($"Error in updating items. Error was {ex.ToString()}");
return(result);
}
result.Item1.AddRange(updateObjects);
result.Item2 = true;
return(result);
}
public UpsertHttpApiCommand(UpsertCommand <T> command, IRequestMessageBuilderFactory requestMessageBuilderFactory)
{
this.command = command;
this.requestBuilder = requestMessageBuilderFactory.GetRequestMessageBuilder <UpsertRequestMessageBuilder>();
}
public NumberFieldBuilder(UpsertSchemaField field, UpsertCommand schema)
: base(field, schema)
{
}
public AssetFieldBuilder(UpsertSchemaField field, UpsertCommand schema)
: base(field, schema)
{
}
protected FieldBuilder(UpsertSchemaField field, UpsertCommand schema)
{
this.field = field;
this.schema = schema;
}
public BooleanFieldBuilder(UpsertSchemaField field, UpsertCommand schema)
: base(field, schema)
{
}
