/// <summary> /// /// </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) { // check if the property is not modified int newOrder; if (!modifiedProperties.TryGet(context, PropertyName, out newOrder)) return; // check if the record contains a pointer NodePointer pointer; if (!record.TryGet(context, "pointer", out pointer)) throw new InvalidOperationException("Could not update this record because it did not contain a pointer"); // don't update order for root nodes if (pointer.Depth == 1) return; // do not allow values smaller than 1 if (newOrder < 1) throw new InvalidOperationException("Can not set orders smaller than 1"); // assemble parameter var newOrderParameterName = queryBuilder.AddParameter("newOrder", newOrder, DbType.Int32); var oldOrderParameterName = queryBuilder.AddParameter("oldOrder", record.Get<int>(context, PropertyName), DbType.Int32); var parentIdParameterName = queryBuilder.AddParameter("parentId", pointer.Parent.Id, DbType.Int32); // update the orders before updating the order of the current node queryBuilder.PrependQuery(string.Format("UPDATE [Nodes] SET [order] = [order] + 1 WHERE (parentId = {0}) AND ([order] < {1} AND [order] >= {2})", parentIdParameterName, oldOrderParameterName, newOrderParameterName)); queryBuilder.PrependQuery(string.Format("UPDATE [Nodes] SET [order] = [order] - 1 WHERE (parentId = {0}) AND ([order] > {1} AND [order] <= {2})", parentIdParameterName, oldOrderParameterName, newOrderParameterName)); // update the column queryBuilder.AddColumnValue(PropertyName, newOrderParameterName); }
/// <summary> /// /// </summary> /// <param name="context"></param> /// <param name="queryBuilder"></param> /// <param name="properties"></param> protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties) { // build the select max order + 1 query only for none root nodes var newPointer = properties.Get<NodePointer>(context, "_newPointer"); queryBuilder.PrependQuery("DECLARE @order int = 1"); if (newPointer.Depth > 1) queryBuilder.PrependQuery(string.Format("SET @order = (SELECT ISNULL(MAX([order]) + 1, 1) FROM [Nodes] WHERE [parentId] = {0})", queryBuilder.AddParameter("parentId", newPointer.Parent.Id, DbType.Int32))); // add the column queryBuilder.AddColumnValue(PropertyName, "@order"); }
/// <summary> /// /// </summary> /// <param name="context"></param> /// <param name="queryBuilder"></param> /// <param name="properties"></param> protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties) { var newPointer = properties.Get<NodePointer>(context, "_newPointer"); queryBuilder.AddColumnValue("name", newPointer.Name.Trim(), DbType.String); queryBuilder.AddColumnValue("type", newPointer.Type.Trim(), DbType.String); queryBuilder.AddColumnValue("depth", newPointer.Depth, DbType.Int32); queryBuilder.AddColumnValue("parentId", newPointer.HasParent ? (object) newPointer.Parent.Id : null, DbType.Int32); queryBuilder.AddColumnValue("parentPointer", newPointer.HasParent ? newPointer.Parent.PointerString + NodePointer.PointerSeparator : null, DbType.String); queryBuilder.AddColumnValue("parentPath", newPointer.HasParent ? newPointer.Parent.PathString + NodePointer.PathSeparator : null, DbType.String); queryBuilder.AddColumnValue("parentStructure", newPointer.HasParent ? newPointer.Parent.StructureString + NodePointer.StructureSeparator : null, DbType.String); }
/// <summary> /// /// </summary> /// <param name="context"></param> /// <param name="queryBuilder"></param> /// <param name="newProperties"></param> protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag newProperties) { var extendedProperties = new PropertyBag(newProperties); // remove all properties starting with an underscore var unstoredPropertyNames = extendedProperties.Names.Where(candidate => candidate.StartsWith("_")).ToList(); foreach (var propertyName in unstoredPropertyNames) extendedProperties.Remove(propertyName); // get the conversion service var conversionService = context.Nucleus.ResolveSingle<IConversionService>(); // set the column value queryBuilder.AddColumnValue("extendedProperties", conversionService.Convert<byte[]>(context, extendedProperties), DbType.Binary); }
/// <summary> /// /// </summary> /// <param name="context"></param> /// <param name="queryBuilder"></param> /// <param name="properties"></param> protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties) { // allow identity insert on special cases, most likely used when fixing the repository integrity if (!properties.Get(context, "_allowIdenityInsert", false)) return; queryBuilder.PrependQuery("SET IDENTITY_INSERT [dbo].[Nodes] ON;"); // get the value of the column var value = properties.Get<object>(context, PropertyName); // add the parameter var parameterName = queryBuilder.AddParameter(ColumnName, value); // set the column value queryBuilder.AddColumnValue(ColumnName, parameterName); queryBuilder.AppendQuery("SET IDENTITY_INSERT [dbo].[Nodes] OFF;"); }
/// <summary> /// /// </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) { // allow update of relational column on special cases, most likely used when fixing the repository integrity if (modifiedProperties.Get(context, "_allowRelationPropertiesUpdate", false)) { string name; if (modifiedProperties.TryGet(context, "name", out name)) queryBuilder.AddColumnValue("name", name, DbType.String); string type; if (modifiedProperties.TryGet(context, "type", out type)) queryBuilder.AddColumnValue("type", type, DbType.String); int depth; if (modifiedProperties.TryGet(context, "depth", out depth)) queryBuilder.AddColumnValue("depth", depth, DbType.Int32); int parentId; if (modifiedProperties.TryGet(context, "parentId", out parentId)) queryBuilder.AddColumnValue("parentId", parentId, DbType.Int32); string parentPointer; if (modifiedProperties.TryGet(context, "parentPointer", out parentPointer)) queryBuilder.AddColumnValue("parentPointer", parentPointer, DbType.String); string parentPath; if (modifiedProperties.TryGet(context, "parentPath", out parentPath)) queryBuilder.AddColumnValue("parentPath", parentPath, DbType.String); string parentStructure; if (modifiedProperties.TryGet(context, "parentStructure", out parentStructure)) queryBuilder.AddColumnValue("parentStructure", parentStructure, DbType.String); return; } // make sure the relational intgrety is not comprimised if (modifiedProperties.Names.Intersect(ReservedPropertyName, StringComparer.OrdinalIgnoreCase).Any()) throw new InvalidOperationException("The relational properties can not be changed"); // get the pointer NodePointer pointer; if (!record.TryGet(context, "pointer", out pointer)) throw new InvalidOperationException("Could not update this record because it did not contain a pointer"); // add the id an pointer parameters var idParameterName = queryBuilder.AddParameter("id", pointer.Id, DbType.Int32); var pointerParameterName = queryBuilder.AddParameter("pointer", pointer.PointerString + "-%", DbType.String); // check if the name changed string newName; if (modifiedProperties.TryGetAndRemove(context, "name", out newName)) { newName = newName.Trim(); if (string.IsNullOrEmpty(newName)) throw new InvalidOperationException("Can not update column name with empty string"); if (newName.Contains(NodePointer.PathSeparator)) throw new InvalidOperationException(string.Format("Name '{0}' contains invalid characters", newName)); if (!pointer.Name.Equals(newName)) { // add the name column modification queryBuilder.AddColumnValue("name", newName, DbType.String); // update the paths var oldPathLengthParameterName = queryBuilder.AddParameter("oldPathLength", pointer.PathString.Length + 1, DbType.String); var newPathParameterName = queryBuilder.AddParameter("newPath", NodePointer.Rename(pointer, newName).PathString + NodePointer.PathSeparator, DbType.String); queryBuilder.AppendQuery(string.Format(@" UPDATE [Nodes] SET [parentPath] = {0} + RIGHT( [parentPath], LEN( [parentPath] ) - {1} ) WHERE ( [parentId] = {2} OR [parentPointer] LIKE {3} )", newPathParameterName, oldPathLengthParameterName, idParameterName, pointerParameterName)); } } // check if the type changed string newType; if (modifiedProperties.TryGetAndRemove(context, "type", out newType)) { newType = newType.Trim(); if (string.IsNullOrEmpty(newType)) throw new InvalidOperationException("Can not update column type with empty string"); if (newType.Contains(NodePointer.StructureSeparator)) throw new InvalidOperationException(string.Format("Type '{0}' contains invalid characters", newType)); if (!string.IsNullOrEmpty(newType) && !pointer.Type.Equals(newType, StringComparison.OrdinalIgnoreCase)) { // add the name column modification queryBuilder.AddColumnValue("type", newType, DbType.String); // update the structures var newStructureParameterName = queryBuilder.AddParameter("newStructure", NodePointer.ChangeType(pointer, newType).StructureString + NodePointer.StructureSeparator, DbType.String); var oldStructureLengthParameterName = queryBuilder.AddParameter("oldStructureLength", pointer.StructureString.Length + 1, DbType.Int32); queryBuilder.AppendQuery(string.Format("UPDATE [Nodes] SET [parentStructure] = {0} + RIGHT( [parentStructure], LEN( [parentStructure] ) - {1} ) WHERE ( [parentId] = {2} OR [parentPointer] LIKE {3} )", newStructureParameterName, oldStructureLengthParameterName, idParameterName, pointerParameterName)); } } }
/// <summary> /// /// </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) { // check if the property is not modified object input; if (!modifiedProperties.TryGet(context, PropertyName, out input)) return; // determine the value var value = GetValue(context, input); // add the parameter var parameterName = queryBuilder.AddParameter(ColumnName, value); // set the column value queryBuilder.AddColumnValue(ColumnName, parameterName); }
/// <summary> /// /// </summary> /// <param name="context"></param> /// <param name="queryBuilder"></param> /// <param name="properties"></param> protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties) { // get the value of the column var value = GetValue(context, properties.Get<object>(context, PropertyName)); // add the parameter var parameterName = queryBuilder.AddParameter(ColumnName, value); // set the column value queryBuilder.AddColumnValue(ColumnName, parameterName); }
/// <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)); } } }
/// <summary> /// /// </summary> /// <param name="context"></param> /// <param name="queryBuilder"></param> /// <param name="properties"></param> protected override void DoToInsertStatement(IMansionContext context, ModificationQueryBuilder queryBuilder, IPropertyBag properties) { // join the two tables on ID queryBuilder.AddColumnValue("id", "@ScopeIdentity"); }