/// <summary>
/// Generates the insert statement for this table.
/// </summary>
/// <param name="context"></param>
/// <param name="queryBuilder"></param>
/// <param name="properties"></param>
protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties)
{
// create a table modification query
var tableModificationQuery = new ModificationQueryBuilder(queryBuilder);
foreach (var column in Columns)
column.ToInsertStatement(context, tableModificationQuery, properties);
// if there are modified column add table modification query to the master query builder
if (tableModificationQuery.HasModifiedColumns)
queryBuilder.AppendQuery(tableModificationQuery.ToInsertStatement(Name));
}
/// <summary>
/// Generates the insert statement for this table.
/// </summary>
/// <param name="context"></param>
/// <param name="queryBuilder"></param>
/// <param name="properties"></param>
protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties)
{
// create a table modification query
var tableModificationQuery = new ModificationQueryBuilder(queryBuilder);
foreach (var column in Columns)
column.ToInsertStatement(context, tableModificationQuery, properties);
// if there are no modified column add table modification query to the master query builder
if (!tableModificationQuery.HasModifiedColumns)
return;
queryBuilder.PrependQuery("DECLARE @ScopeIdentity AS int");
queryBuilder.AppendQuery(tableModificationQuery.ToInsertStatement(Name));
queryBuilder.AppendQuery("SET @ScopeIdentity = SCOPE_IDENTITY()");
}
/// <summary>
/// Generates the update statement for this table.
/// </summary>
/// <param name="context"></param>
/// <param name="queryBuilder"></param>
/// <param name="record"> </param>
/// <param name="modifiedProperties"></param>
protected override void DoToUpdateStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, Record record, IPropertyBag modifiedProperties)
{
// create identity parameter
var idParameterName = queryBuilder.AddParameter("id", record.Id, DbType.Int32);
// loop through all the properties
foreach (var propertyName in Columns.Select(column => column.PropertyName))
{
// check if the property is modified
string rawModifiedValue;
if (!modifiedProperties.TryGet(context, propertyName, out rawModifiedValue))
continue;
// get the current values
var currentValues = GetCurrentValues(queryBuilder.Command, record, propertyName).ToList();
// check if there are new properties
var modifiedValues = (rawModifiedValue ?? string.Empty).Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToArray();
// get the deleted values
var deletedValues = currentValues.Except(modifiedValues, StringComparer.OrdinalIgnoreCase);
var newValues = modifiedValues.Except(currentValues, StringComparer.OrdinalIgnoreCase);
// create property parameter
var propertyParameterName = queryBuilder.AddParameter(propertyName, propertyName, DbType.String);
// generate the delete statements
foreach (var deletedValue in deletedValues)
{
// build the query
var valueModificationQuery = new ModificationQueryBuilder(queryBuilder);
// build clause
var valueParameterName = valueModificationQuery.AddParameter("value", deletedValue, DbType.String);
valueModificationQuery.AppendWhereClause("[id] = " + idParameterName + " AND [name] = " + propertyParameterName + " AND [value] = " + valueParameterName);
// append the query
queryBuilder.AppendQuery(valueModificationQuery.ToDeleteStatement(Name));
}
// generate the insert statements
foreach (var newValue in newValues)
{
// build the query
var valueModificationQuery = new ModificationQueryBuilder(queryBuilder);
// set column values
valueModificationQuery.AddColumnValue("id", idParameterName);
valueModificationQuery.AddColumnValue("name", propertyParameterName);
valueModificationQuery.AddColumnValue("value", newValue, DbType.String);
// append the query
queryBuilder.AppendQuery(valueModificationQuery.ToInsertStatement(Name));
}
}
}
/// <summary>
/// Generates the insert statement for this table.
/// </summary>
/// <param name="context"></param>
/// <param name="queryBuilder"></param>
/// <param name="properties"></param>
protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties)
{
// loop through all the properties
foreach (var propertyName in Columns.Select(column => column.PropertyName))
{
// check if there are any properties
var values = properties.Get(context, propertyName, string.Empty).Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToArray();
if (values.Length == 0)
continue;
// loop through each value and write an insert statement
foreach (var value in values)
{
// build the query
var valueModificationQuery = new ModificationQueryBuilder(queryBuilder);
// set column values
valueModificationQuery.AddColumnValue("id", "@ScopeIdentity");
valueModificationQuery.AddColumnValue("name", propertyName, DbType.String);
valueModificationQuery.AddColumnValue("value", value, DbType.String);
// append the query
queryBuilder.AppendQuery(valueModificationQuery.ToInsertStatement(Name));
}
}
}