public static string CreateFromDataAndTableSetup(BsonDocument data, SqlTableSetup tableSetup)
{
var jObject = JObject.Parse(DataEncoder.DecodeToJson(data));
var properties = jObject.Properties().Where(property => property.Name != tableSetup.IdColumnName).ToList();
var query = $"INSERT INTO {tableSetup.TableName} ({string.Join(", ", properties.Select(property => property.Name))}) "
+ $"OUTPUT INSERTED.{tableSetup.IdColumnName} "
+ $"VALUES ({string.Join(", ", properties.Select(property => $"'{JTokenStringify.Stringify(property.Value)}'"))})";
return(query);
}
public static string CreateFromDataAndTableSetup(BsonDocument data, SqlTableSetup tableSetup, string id)
{
var jObject = JObject.Parse(DataEncoder.DecodeToJson(data));
var properties = jObject.Properties().Where(property => property.Name != tableSetup.IdColumnName).ToList();
var keyValuePairs = string.Join(", ", properties.Where(property => property.Name != tableSetup.IdColumnName)
.ToDictionary(property => property.Name, property => JTokenStringify.Stringify(property.Value))
.Select(kvp => $"{kvp.Key} = '{kvp.Value}'"));
var query = $"UPDATE {tableSetup.TableName} SET {keyValuePairs} WHERE {tableSetup.IdColumnName} = '{id}'";
return(query);
}
public static BsonDocument BuildFromDelimitedColumns(SqlDataReader reader, SqlTableSetup tableSetup = null)
{
var columnNames = reader.GetColumnSchema().Select(x => x.ColumnName);
var jObject = new JObject();
foreach (var columnName in columnNames)
{
var propertyName = GetPropertyName(columnName, tableSetup);
jObject.EnsureParent(propertyName);
var value = reader[columnName];
jObject[propertyName] = value == null || value is DBNull ? null : value.ToString();
}
return(DataEncoder.Encode(jObject.ToString()));
}
private static GenericDataContainer BuildContainer(SqlDataReader reader, SqlTableSetup tableSetup = null)
{
var id = (string)reader["Id"];
var originalSubmitter = (string)reader["OriginalSubmitter"];
var createdTimeUtc = (DateTime)reader["CreatedTimeUtc"];
var submitter = (string)reader["Submitter"];
var submissionTimeUtc = (DateTime)reader["SubmissionTimeUtc"];
var data = BuildData(reader);
return(new GenericDataContainer(
id,
originalSubmitter,
createdTimeUtc,
submitter,
submissionTimeUtc,
ApiVersion.Current,
data));
}
public static GenericDataContainer Reconstruct(SqlDataReader reader, SqlTableSetup tableSetup = null)
{
return(BuildContainer(reader, tableSetup));
}
private static string GetPropertyName(string columnName, SqlTableSetup tableSetup)
{
return(columnName.Replace(GenericDataContainerTraversal.PathDelimiter, '.'));
}
private async Task <GenericDataContainer> BuildContainer(SqlDataReader reader, SqlTableSetup tableSetup)
{
var id = (int)reader[tableSetup.IdColumnName];
var metadata = await metadataStorage.GetFromIdAsync(tableSetup.DataType, id.ToString());
if (metadata == null)
{
var utcNow = DateTime.UtcNow;
metadata = new GenericDataContainer(
id.ToString(),
"unknown",
utcNow,
"unknown",
utcNow,
ApiVersion.Current,
new BsonDocument());
}
var data = BsonDocumentBuilder.BuildFromDelimitedColumns(reader, tableSetup);
return(new GenericDataContainer(
id.ToString(),
metadata.OriginalSubmitter,
metadata.CreatedTimeUtc,
metadata.Submitter,
metadata.SubmissionTimeUtc,
metadata.ApiVersion,
data));
}