/// <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 a table modification query var tableModificationQuery = new ModificationQueryBuilder(queryBuilder); foreach (var column in Columns) column.ToUpdateStatement(context, tableModificationQuery, record, modifiedProperties); // if there are no modified column add table modification query to the master query builder if (tableModificationQuery.HasModifiedColumns) queryBuilder.AppendQuery(tableModificationQuery.ToUpdateStatement(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) { // 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> /// Prepares an insert query. /// </summary> /// <param name="context"></param> /// <param name="connection">The connection.</param> /// <param name="transaction">The transaction.</param> /// <param name="properties"></param> /// <returns></returns> public void Prepare(IMansionContext context, SqlConnection connection, SqlTransaction transaction, IPropertyBag properties) { // validate arguments if (connection == null) throw new ArgumentNullException("connection"); if (transaction == null) throw new ArgumentNullException("transaction"); if (properties == null) throw new ArgumentNullException("properties"); // get the values var typeName = properties.Get<string>(context, "type", null); if (string.IsNullOrWhiteSpace(typeName)) throw new InvalidOperationException("A record must have a type"); // retrieve the type var type = typeService.Load(context, typeName); // get the schema of the root type var schema = Resolver.Resolve(context, type); // set the full text property SqlServerUtilities.PopulateFullTextColumn(context, type, properties, properties); // create the commands command = connection.CreateCommand(); command.CommandType = CommandType.Text; command.Transaction = transaction; // prepare the query var queryBuilder = new ModificationQueryBuilder(command); // loop through all the tables in the schema and let them prepare for insert foreach (var table in schema.Tables) table.ToInsertStatement(context, queryBuilder, properties); // finish the complete insert statement queryBuilder.AppendQuery("SELECT @ScopeIdentity"); // set the command text command.CommandText = queryBuilder.ToStatement(); }
/// <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> /// Prepares an insert query. /// </summary> /// <param name="context"></param> /// <param name="connection">The connection.</param> /// <param name="transaction">The transaction.</param> /// <param name="parent"></param> /// <param name="properties"></param> /// <returns></returns> public void Prepare(IMansionContext context, SqlConnection connection, SqlTransaction transaction, NodePointer parent, IPropertyBag properties) { // validate arguments if (connection == null) throw new ArgumentNullException("connection"); if (transaction == null) throw new ArgumentNullException("transaction"); if (parent == null) throw new ArgumentNullException("parent"); if (properties == null) throw new ArgumentNullException("properties"); // get the values var name = properties.Get<string>(context, "name", null); if (string.IsNullOrWhiteSpace(name)) throw new InvalidOperationException("The node must have a name"); var typeName = properties.Get<string>(context, "type", null); if (string.IsNullOrWhiteSpace(typeName)) throw new InvalidOperationException("The node must have a type"); // retrieve the type var type = typeService.Load(context, typeName); // get the schema of the root type var schema = Resolver.Resolve(context, type); // set the full text property SqlServerUtilities.PopulateFullTextColumn(context, type, properties, properties); // create the new pointer name = NodePointer.MakeSafeName(name); var newPointer = NodePointer.Parse(string.Join(NodePointer.PointerSeparator, new[] {parent.PointerString, 0.ToString(CultureInfo.InvariantCulture)}), string.Join(NodePointer.StructureSeparator, new[] {parent.StructureString, type.Name}), string.Join(NodePointer.PathSeparator, new[] {parent.PathString, name})); properties.Set("_newPointer", newPointer); // create the commands command = connection.CreateCommand(); command.CommandType = CommandType.Text; command.Transaction = transaction; // prepare the query var queryBuilder = new ModificationQueryBuilder(command); // loop through all the tables in the schema and let them prepare for insert foreach (var table in schema.Tables) table.ToInsertStatement(context, queryBuilder, properties); // finish the complete insert statement queryBuilder.AppendQuery("SELECT @ScopeIdentity"); // set the command text command.CommandText = queryBuilder.ToStatement(); }
/// <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)); } } }